oracle日志传输延时,Oracle 11g Data Guard 物理备库开启日志延时应用流程

1、在备库上面，关闭日志实时应用。

如果物理备库开启日志实时应用，则会导致参数设置失败。如果不是延时应用，则只有当主库发生归档时，备库才会启动归档日志的应用，在备库启动日志应用后，会按照设置的DELAY值进行延时应用。sqlplus / as sysdba

ALTER DATABASE RECOVER MANAGED STANDBY

DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY

DATABASE DISCONNECT;

2、在主库上面，设置DELAY参数

sqlplus / as sysdba

--查询远程归档日志的目录

Show parameter log_archive_dest_2

--设置DELAY参数，DELAY参数的单位是分，如设置2小时，则为120

alter system set

LOG_ARCHIVE_DEST_2='SERVICE=misdbdg ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=misdbdg DELAY=120' scope=both;

--切换归档日志

alter system switch logfile;

3、备库关闭日志延时应用，恢复到日志实时应用在备库上面执行下面命令

sqlplus / as sysdba

ALTER DATABASE RECOVER MANAGED STANDBY

DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY

DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

如果物理备库没有开启日志的实时应用，则在备库使用下面命令关闭 DELAY 参数

sqlplus / as sysdba

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

4、如果是物理备库，并开启了实时应用的话，会导致参数设置失效

下面是主库 alert 日志的报错信息

ALTER SYSTEM SET log_archive_dest_2='SERVICE=newdbdg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=newdbdg DELAY=5' SCOPE=BOTH;

Tue Nov 24 17:29:58 2015

Thread 1 advanced to log sequence 51 (LGWR switch)

Current log# 5 seq# 51 mem# 0: /u01/app/oracle/oradata/newdb/redo05a.log

Current log# 5 seq# 51 mem# 1: /u01/app/oracle/oradata/newdb/redo05b.log

Tue Nov 24 17:29:58 2015

******************************************************************

TT00: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

Tue Nov 24 17:29:58 2015

Archived Log entry 68 added for thread 1 sequence 50 ID 0xbae9db91 dest 1:

Tue Nov 24 17:29:59 2015

WARNING: Managed Standby Recovery started with REAL TIME APPLY

DELAY 5 minutes specified at primary ignored

WARNING: Managed Standby Recovery started with REAL TIME APPLY

DELAY 5 minutes specified at primary ignored

TT00: Standby redo logfile selected for thread 1 sequence 51 for destination LOG_ARCHIVE_DEST_2

下面是备库 alert 日志的报错信息

Thu Nov 26 15:07:17 2015

WARNING: Managed Standby Recovery started with REAL TIME APPLY

DELAY 5 minutes specified at primary ignored