测试备库应用主库日志时有无using current logfile选项的区别
来源：互联网 发布：ipad4软件更新打不开 编辑：程序博客网 时间：2024/05/02 02:35
测试备库应用主库日志时，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信息，实时应用到备库上，即便是最大性能。
- 测试备库应用主库日志时有无using current logfile选项的区别
- 测试备库应用主库日志时有无using current logfile选项的区别
- 使用using current logfile实现DG备库实时更新
- 11G新特性 Active Data Guard特性应用及实时同步测试--using current logfile
- ORACLE dataguard 主备库延迟 using current logfile
- 访问property时有无self.的区别
- muduo库的LogFile日志文件类剖析
- ORA-38500: USING CURRENT LOGFILE option not available without stand
- ORA-38500: USING CURRENT LOGFILE option not available without stand
- alter system switch logfile和alter system archive log current的区别
- alter system switch logfile 和 alter system archive log current 的区别
- alter system switch logfile 和 alter system archive log current 的区别
- alter system switch logfile和alter system archive log current 的区别
- alter system switch logfile和alter system archive log current的区别
- alter system switch logfile和alter system archive log current的区别
- alter system switch logfile和alter system archive log current的区别
- alter system switch logfile和alter system archive log current 的区别
- 'alter system switch logfile' 和 'alter system archive log current'的区别
- Android Uri、UriMatcher、ContentUris详解
- 获取农历节日.节气
- spark internal - 作业调度
- java初学感谢三
- 2014校选题（六）--简单数学题
- 测试备库应用主库日志时有无using current logfile选项的区别
- SVN服务器客户端以及环境的搭建和使用
- 微软数据结构+算法面试100题
- erlang lists操作
- 【蓝桥杯练习系统】 基础练习 查找整数
- 屏幕分辨率、屏幕尺寸以及屏幕适配和图片分辨率
- iOS开发学习笔记 (一)表视图基础 Table Views
- Android复习之GridView
- 获取农历方法
