oracle 时间延后分钟,Oracle DataGuard 设置延迟日志应用

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设置的时间只是理论上的延迟日志应用，而实际日志延迟应用可能会更长。

参考：