测试备库应用主库日志时,alter database recover managed standby database语句中,有无using current logfile选项的区别
一、配置信息
库版本:11.2.0.1
DG保护模式:最大性能
DG配置方式:物理备库 LGWR ASYNC
详细如下:
主库配置信息:
[oracle@db u01]$ sqlplus / as sysdba
--库版本11.2.0.1
SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 17 13:31:33 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--主库状态是open
SQL> select status from v$instance;
STATUS
------------
OPEN
--主库配置了standby redo
SQL> select group#,thread#,sequence#,status from v$standby_log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------
4 0 0 UNASSIGNED
5 0 0 UNASSIGNED
6 0 0 UNASSIGNED
7 0 0 UNASSIGNED
--主库的配置信息
SQL> set lines 200
SQL> col name for a20
SQL> col value for a100
SQL> select name,value from v$parameter where name in ('log_archive_dest_1','log_archive_dest_2');
NAME VALUE
-------------------- ----------------------------------------------------------------------------------------------------
log_archive_dest_1 location=/u01/arclog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcl
log_archive_dest_2 service=db2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db2
--主库的保护模式及open模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE READ WRITE
备库配置信息:
[oracle@db2 ~]$ sqlplus / as sysdba
--库版本11.2.0.1
SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 17 13:36:13 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--备库状态也是open,11g DG的备库是可以open的
SQL> select status from v$instance;
STATUS
------------
OPEN
--备库配置了standby redo
SQL> select group#,thread#,sequence#,status from v$standby_log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------
4 1 47 ACTIVE
5 0 0 UNASSIGNED
6 0 0 UNASSIGNED
7 0 0 UNASSIGNED
--备库的配置信息
SQL> set lines 200
SQL> col name for a20
SQL> col value for a100
SQL> select name,value from v$parameter where name in ('log_archive_dest_1','log_archive_dest_2');
NAME VALUE
-------------------- ----------------------------------------------------------------------------------------------------
log_archive_dest_1 location=/u01/arclog/ valid_for=(all_logfiles,all_roles) db_unique_name=db2
log_archive_dest_2 service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl
--备库的保护模式及open模式
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY
二、测试备库应用redo时有无using current logfile的区别
1.无using current logfile
备库执行:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> col name for a50
SQL> col value for a50
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 13:57:07
apply lag +00 00:40:55 08/17/2013 13:57:07
apply finish time +00 00:00:05.000
estimated startup time 22
--查出备库跟主库间有40分钟的应用延迟(我在主库执行alter database recover managed standby database cancel;后,去吃饭了)
主库切换一下redo
SQL> alter system archive log current;
System altered.
再查备库与主库的延迟:
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 14:01:14
apply lag +00 00:00:21 08/17/2013 14:01:14
apply finish time +00 00:00:00.001
estimated startup time 22
--这次apply lag 是21秒了,我觉得主库切换redo后,备库才会应用日志。我们不切换主库redo,等一小会儿,再查备库与主库的应用延迟
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 14:03:45
apply lag +00 00:02:52 08/17/2013 14:03:45
apply finish time +00 00:00:01.000
estimated startup time 22
--这次成了2份52秒了。
主库切换一下redo,备库马上查看跟主库的应用延迟:
SQL> alter system archive log current;
System altered.
以下备库连查了三次:
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 14:04:53
apply lag +00 00:03:59 08/17/2013 14:04:53
apply finish time +00 00:00:01.000
estimated startup time 22
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 14:04:56
apply lag +00 00:04:01 08/17/2013 14:04:56
apply finish time +00 00:00:01.000
estimated startup time 22
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 14:05:03
apply lag +00 00:00:00 08/17/2013 14:05:03
apply finish time +00 00:00:00.000
estimated startup time 22
在主库切换redo后,备库跟主库的延迟没有立即归零,而是在继续增大,这充分证明了在主库切换归档后(此时备库也产生新归档),备库才会开始应用日志。
如果主库一直不切换redo,备库跟主库的差距会越来越大。
也许视图不能说明什么,我们进行DML操作,模拟下实际应用.
主库建张表:
SQL> create table t (id number);
Table created.
主库切日志:
SQL> alter system archive log current;
System altered.
主库切日志后,备库能查到新建的表:
SQL> select * from t;
ID
----------
0
主库插入数据,并提交
SQL> insert into t values (1);
1 row created.
SQL> commit;
Commit complete.
备库能查数据:
SQL> select * from t;
ID
----------
0
SQL> select * from t;
ID
----------
0
主库提交后,备库查询若干次,都查不到数据!
再查备库与主库的应用延迟:
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 14:23:59
apply lag +00 00:02:50 08/17/2013 14:23:59
apply finish time +00 00:00:00.001
estimated startup time 22
备库被主库拉开了2分50秒。
主库再切日志:
SQL> alter system archive log current;
System altered.
备库能查到了:
SQL> select * from t;
ID
----------
1
呵呵,无using current logfile感觉很像是ARCH ASYNC。
2.有using current logfile
备库先退出应用主库日志:
SQL> alter database recover managed standby database cancel;
Database altered.
备库使用using current logfile方式应用主库日志:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
检查备库与主库的应用延迟:
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 14:34:54
apply lag +00 00:00:00 08/17/2013 14:34:54
apply finish time +00 00:00:00.000
estimated startup time 22
没用apply lag,而且一直是没有延迟。
SQL> select name,value,datum_time from v$dataguard_stats;
NAME VALUE DATUM_TIME
-------------------------------------------------- -------------------------------------------------- ------------------------------
transport lag +00 00:00:00 08/17/2013 14:35:00
apply lag +00 00:00:00 08/17/2013 14:35:00
apply finish time +00 00:00:00.000
estimated startup time 22
主库插入数据测试:
SQL> insert into t values (2);
1 row created.
SQL> commit;
Commit complete.
无须等主库切换归档,备库很快就就能查到:
SQL> select * from t;
ID
----------
1
2
三、参考官方文档
关于USING CURRENT LOGFILE官方文档有如下解释:
Specify USING BACKUP CONTROLFILE if you want to use a backup control file instead of the current control file.
--备库的控制文件确实是从主库备份来的。
USING CURRENT LOGFILE Clause Specify USING CURRENT LOGFILE to invoke real-time apply, which recovers redo from the standby redo log files as soon as they are written, without requiring them to be archived first at the physical standby database.
--这句写得很明白,大体意思是:指定USING CURRENT LOGFILE会调用实施应用,它从standby redo中还原信息并尽快写到数据文件中,不需要在备库上先归档。
另外在官方文档上还看到了这句话:
FINISH Specify FINISH to complete applying all available redo data in preparation for a failover.
所以,在failover时,执行alter database recover managed standby database finish;是有必要的。
四、总结
无using current logfile感觉像下面这张图,主库切换日志后,备库才从归档文件挖掘出变化,然后应用到库文件中。
而using current logfile感觉像下面这张图,备库根据接收到的redo信息,实时应用到备库上,即便是最大性能。