DataGuard作为一种常见的高可用方式，那对于数据的实时性也是要求很高，对保证主从数据的一致性和在故障的时候快速切换的要求都很高。
11g Active Data Guard，给我们带来了提供容灾功能的同时，还可以将Standby开启到OPEN状态。那么我们的Standby就能够充分利用起来，而不是单单为了容灾。我们可以用来做读写分离和报表查询等等功能。
我们将Standby作为报表系统使用那么对应数据的实时性并不是很高，那么我们可以对Standby设置延迟日志应用。
下面来介绍下如何针对物理DataGuard设置延时日志应用的二种方法：
方法一：
直接在启动备库同步的时候设置延迟日志应用参数：
alter database recover managed standby database delay 5 disconnect from session;（delay 5 这里表示 延迟5分钟后在对日志进行应用）
测试如下：
standby:
14:27:07 SQL> conn / as sysdba
Connected.
14:29:41 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;---取消实时日志应用
Database altered.
Elapsed: 00:00:04.01
14:29:49 SQL> alter database recover managed standby database delay 5 disconnect from session;---设置延迟5分钟日志应用
Database altered.
Elapsed: 00:00:16.14
primary切换日志：
14:28:38 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:01.68
查看你standby log：
Sun Oct 26 14:30:29 2014
Archived Log entry 9 added for thread 1 sequence 26 ID 0x57663c2f dest 1:
Sun Oct 26 14:30:32 2014
RFS[1]: No standby redo logfiles available for thread 1
RFS[1]: Opened log for thread 1 sequence 27 dbid 1466306607 branch 861902659
Sun Oct 26 14:30:34 2014
Media Recovery Delayed for 5 minute(s) (thread 1 sequence 26)--可以发现日志将在5分钟后延迟
由于设置5分钟后才应用日志，观察5分钟后备库出现的日志：
un Oct 26 14:30:29 2014
Archived Log entry 9 added for thread 1 sequence 26 ID 0x57663c2f dest 1:
Sun Oct 26 14:30:32 2014
RFS[1]: No standby redo logfiles available for thread 1
RFS[1]: Opened log for thread 1 sequence 27 dbid 1466306607 branch 861902659
Sun Oct 26 14:30:34 2014
Media Recovery Delayed for 5 minute(s) (thread 1 sequence 26)
Sun Oct 26 14:35:29 2014
Media Recovery Log /opt/oracle/oradata/ora/archive/1_26_861902659.dbf
Media Recovery Waiting for thread 1 sequence 27 (in transit)--5分钟后日志已经应用
14:34:50 SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 NO
9 rows selected.
Elapsed: 00:00:00.01
14:35:28 SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 IN-MEMORY
9 rows selected.
Elapsed: 00:00:00.00
检查同步情况：
主库：
14:42:06 SQL> conn text/xxxx
Connected.
14:46:54 SQL> select count(1) from text_obiect_two;
COUNT(1)
----------
74511
Elapsed: 00:00:00.02
14:46:57 SQL> create table text_obiect_three as select * from sys.dba_objects;
Table created.
Elapsed: 00:00:25.40
15:16:25 SQL> select count(1) from text_obiect_three;
COUNT(1)
----------
74512
Elapsed: 00:00:00.04
15:16:32 SQL> conn / as sysdba
Connected.
15:17:46 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:01.30
查看standby日志：
Archived Log entry 11 added for thread 1 sequence 28 rlc 861902659 ID 0x57663c2f dest 2:
Sun Oct 26 15:17:53 2014
Media Recovery Delayed for 5 minute(s) (thread 1 sequence 28)
RFS[1]: Selected log 7 for thread 1 sequence 29 dbid 1466306607 branch 861902659
5分钟后备库日志：
Sun Oct 26 15:17:51 2014
Archived Log entry 11 added for thread 1 sequence 28 rlc 861902659 ID 0x57663c2f dest 2:
Sun Oct 26 15:17:53 2014
Media Recovery Delayed for 5 minute(s) (thread 1 sequence 28)
RFS[1]: Selected log 7 for thread 1 sequence 29 dbid 1466306607 branch 861902659
Sun Oct 26 15:22:51 2014
Media Recovery Log /opt/oracle/oradata/ora/archive/1_28_861902659.dbf
Sun Oct 26 15:23:42 2014
Media Recovery Waiting for thread 1 sequence 29 (in transit)—表示已应用
备库：
15:27:29 SQL> select count(1) from text_obiect_three;
COUNT(1)
----------
74512
方法二：
通过修改log_archive_dest_n 参数上使用“DELAY="，比如：DELAY=5(单位为分钟)，表示延时5分钟
SQL> alter system set log_archive_dest_2='service=standby reopen=60 lgwr async delay=5 valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=both;
测试如下：
standby：
断开同步：
21:11:34 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
Elapsed: 00:00:05.02
修改主库log_archive_dest_2参数：
21:10:27 SQL> show parameter log_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string service=standby reopen=60 lgw
r async valid_for=(online_logf
iles,primary_role) db_unique_n
ame=standby
21:11:06 SQL> alter system set log_archive_dest_2='service=standby reopen=60 lgwr async delay=5 valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=both;
System altered.
Elapsed: 00:00:01.56
21:15:04 SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=standby reopen=60 lgw
r async delay=5 valid_for=(onl
ine_logfiles,primary_role) db_
unique_name=standby
启standby同步：
21:11:41 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Elapsed: 00:00:08.04
验证是否延时：
主库操作：
21:21:23 SQL> conn text/xxxx
Connected.
21:33:14 SQL> create table txt_obiect_th as select * from sys.dba_objects;
Table created.
Elapsed: 00:00:04.62
21:33:33 SQL> select count(1) from txt_obiect_th;
COUNT(1)
----------
74555
Elapsed: 00:00:00.04
21:33:44 SQL> conn / as sysdba
Connected.
21:33:49 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:04.90
查看standby redo log应用和数据库日志：
Elapsed: 00:00:00.01
21:28:16 SQL> select count(1) from txt_obiect_th;
select count(1) from txt_obiect_th
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.00
21:36:03 SQL> conn / as sysdba
Connected.
21:36:10 SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
SEQUENCE# APPLIED
---------- ---------
29 YES
30 YES
31 YES
32 YES
33 NO
16 rows selected.
Elapsed: 00:00:00.01
注：由于日志还没有应用到备库，所以新建的表还找不到。
Sun Oct 26 21:34:05 2014
Archived Log entry 16 added for thread 1 sequence 33 rlc 861902659 ID 0x57663c2f dest 2:
Sun Oct 26 21:34:07 2014
Media Recovery Delayed for 5 minute(s) (thread 1 sequence 33)—发现有5分钟的延迟
RFS[3]: Selected log 7 for thread 1 sequence 34 dbid 1466306607 branch 861902659
观察5分钟后备库的redo log和数据库日志的变化：
Sun Oct 26 21:34:05 2014
Archived Log entry 16 added for thread 1 sequence 33 rlc 861902659 ID 0x57663c2f dest 2:
Sun Oct 26 21:34:07 2014
Media Recovery Delayed for 5 minute(s) (thread 1 sequence 33)
RFS[3]: Selected log 7 for thread 1 sequence 34 dbid 1466306607 branch 861902659
Sun Oct 26 21:39:09 2014
Media Recovery Log /opt/oracle/oradata/ora/archive/1_33_861902659.dbf
Media Recovery Waiting for thread 1 sequence 34 (in transit)--发现已经应用日志了
21:36:13 SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
SEQUENCE# APPLIED
---------- ---------
29 YES
30 YES
31 YES
32 YES
33 YES---已经变成YES了
16 rows selected.
Elapsed: 00:00:00.01
21:40:08 SQL> conn text/xxxx
Connected.
21:40:14 SQL> select count(1) from txt_obiect_th;
COUNT(1)
----------
74555
Elapsed: 00:00:00.04
5分钟的延迟过后日志已应用到备库，主库新建的表也已备库找到。
小结：在启动同步中设置delay参数和在LOG_ARCHIVE_DEST_n参数中设置是一样的，如果你想重启其中实时日志应用那么你只需取消同步重新开启实时同步就OK了。这里需要注意的是delay设置的时间只是理论上的延迟日志应用，而实际日志延迟应用可能会更长。
参考： 