修改之前是主备库相差一个日志文件,如果switchover ,有数据丢失的风险,修改后,主库的日志可以实时传输,并且实时应用到standby,有个AFFIRM的过程,主库commit之前会确认日止在备库已经应用。

从alert看,非ADG的alert的关键词是Media Recovery Log ,ADG的alert关键词是Recovery of Online Redo Log

版本:
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 23 07:04:35 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

修改前参数
alter system set log_archive_dest_2=’SERVICE=dsedidg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dsedidg’ scope=both ;

修改方法:
–主库
alter system set log_archive_dest_2=’SERVICE=dsedidg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dsedidg’ scope=both ;

–备库
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;

修改过程中的alert日志
主库

Tue Sep 23 07:09:05 2014
Thread 1 advanced to log sequence 9724 (LGWR switch)
Current log# 115 seq# 9724 mem# 0: /tmpdata/dsedi_redo/DSEDI/onlinelog/o1_mf_115_b1fl2bx4_.log
Tue Sep 23 07:09:05 2014
LNS: Standby redo logfile selected for thread 1 sequence 9724 for destination LOG_ARCHIVE_DEST_2
Tue Sep 23 07:10:58 2014
Thread 1 advanced to log sequence 9725 (LGWR switch)
Current log# 116 seq# 9725 mem# 0: /tmpdata/dsedi_redo/DSEDI/onlinelog/o1_mf_116_b1fl2l3v_.log
Tue Sep 23 07:10:59 2014
LNS: Standby redo logfile selected for thread 1 sequence 9725 for destination LOG_ARCHIVE_DEST_2
Tue Sep 23 07:11:35 2014
ALTER SYSTEM SET log_archive_dest_2=’SERVICE=dsedidg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dsedidg’ SCOPE=BOTH;
Tue Sep 23 07:12:20 2014
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
Tue Sep 23 07:12:20 2014
NSS2 started with pid=424, OS id=15003
LGWR: Standby redo logfile selected for thread 1 sequence 9726 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 9726 (LGWR switch)
Current log# 117 seq# 9726 mem# 0: /tmpdata/dsedi_redo/DSEDI/onlinelog/o1_mf_117_b1fl2lyk_.log
Tue Sep 23 07:12:23 2014
ARC3: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARC3: Archive log rejected (thread 1 sequence 9725) at host ‘dsedidg’
Tue Sep 23 07:12:55 2014
******************************************************************
LGWR: Setting ‘active’ archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected for thread 1 sequence 9727 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 9727 (LGWR switch)
Current log# 118 seq# 9727 mem# 0: /tmpdata/dsedi_redo/DSEDI/onlinelog/o1_mf_118_b1fl2mxs_.log
Tue Sep 23 07:19:45 2014
LGWR: Standby redo logfile selected for thread 1 sequence 9728 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 9728 (LGWR switch)
Current log# 119 seq# 9728 mem# 0: /tmpdata/dsedi_redo/DSEDI/onlinelog/o1_mf_119_b1fl2o0y_.log

备库
Tue Sep 23 07:09:05 2014
RFS[9]: Selected log 122 for thread 1 sequence 9724 dbid -187089236 branch 852921582
Tue Sep 23 07:09:05 2014
Archived Log entry 6484 added for thread 1 sequence 9723 ID 0xf4d927ab dest 1:
Tue Sep 23 07:09:09 2014
Media Recovery Log /ecdata/dsedi/arch/1_9723_852921582.dbf
Media Recovery Waiting for thread 1 sequence 9724 (in transit)
Tue Sep 23 07:10:59 2014
RFS[9]: Selected log 121 for thread 1 sequence 9725 dbid -187089236 branch 852921582
Tue Sep 23 07:10:59 2014
Archived Log entry 6485 added for thread 1 sequence 9724 ID 0xf4d927ab dest 1:
Tue Sep 23 07:11:02 2014
Media Recovery Log /ecdata/dsedi/arch/1_9724_852921582.dbf
Media Recovery Waiting for thread 1 sequence 9725 (in transit)
Tue Sep 23 07:11:48 2014
alter database recover managed standby database cancel
Tue Sep 23 07:11:49 2014
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/dsedidg/dsedidg/trace/dsedidg_pr00_20718.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Tue Sep 23 07:11:51 2014
MRP0: Background Media Recovery process shutdown (dsedidg)
Managed Standby Recovery Canceled (dsedidg)
Completed: alter database recover managed standby database cancel
Tue Sep 23 07:12:00 2014
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (dsedidg)
Tue Sep 23 07:12:00 2014
MRP0 started with pid=27, OS id=1750
MRP0: Background Managed Standby Recovery process started (dsedidg)
started logmerger process
Tue Sep 23 07:12:05 2014
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 40 slaves
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 9725 (in transit)
Recovery of Online Redo Log: Thread 1 Group 121 Seq 9725 Reading mem 0
Mem# 0: /tmpdata/dsedi_redo/DSEDIDG/onlinelog/o1_mf_121_b1f7z6xz_.log
Completed: alter database recover managed standby database using current logfile disconnect from session
Tue Sep 23 07:12:23 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Assigned to RFS process 1972
RFS[10]: Selected log 122 for thread 1 sequence 9726 dbid -187089236 branch 852921582
Tue Sep 23 07:12:24 2014
Archived Log entry 6486 added for thread 1 sequence 9725 ID 0xf4d927ab dest 1:
Tue Sep 23 07:12:24 2014
Media Recovery Waiting for thread 1 sequence 9726 (in transit)
Recovery of Online Redo Log: Thread 1 Group 122 Seq 9726 Reading mem 0
Mem# 0: /tmpdata/dsedi_redo/DSEDIDG/onlinelog/o1_mf_122_b1f82xkx_.log
Tue Sep 23 07:12:55 2014
RFS[10]: Selected log 121 for thread 1 sequence 9727 dbid -187089236 branch 852921582
Tue Sep 23 07:12:55 2014
Archived Log entry 6487 added for thread 1 sequence 9726 ID 0xf4d927ab dest 1:
Tue Sep 23 07:12:55 2014
Media Recovery Waiting for thread 1 sequence 9727 (in transit)
Recovery of Online Redo Log: Thread 1 Group 121 Seq 9727 Reading mem 0
Mem# 0: /tmpdata/dsedi_redo/DSEDIDG/onlinelog/o1_mf_121_b1f7z6xz_.log
Tue Sep 23 07:19:45 2014
RFS[10]: Selected log 122 for thread 1 sequence 9728 dbid -187089236 branch 852921582
Tue Sep 23 07:19:45 2014
Media Recovery Waiting for thread 1 sequence 9728 (in transit)
Recovery of Online Redo Log: Thread 1 Group 122 Seq 9728 Reading mem 0
Mem# 0: /tmpdata/dsedi_redo/DSEDIDG/onlinelog/o1_mf_122_b1f82xkx_.log
Tue Sep 23 07:19:45 2014
Archived Log entry 6488 added for thread 1 sequence 9727 ID 0xf4d927ab dest 1:

修改之前是主备库相差一个日志文件,修改后,最大的区别是主库的日志可以实时传输,并且实时应用到standby。
从alert看,非ADG的alert的关键词是Media Recovery Log ,ADG的alert关键词是Recovery of Online Redo Log

修改后的参数
sys@DSEDI>show parameter log_archive

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_config string DG_CONFIG=(dsedi,dsedidg)
log_archive_dest string
log_archive_dest_1 string LOCATION=/ecdata/dsedi/arch LG
WR VALID_FOR=(ALL_LOGFILES,ALL
_ROLES) DB_UNIQUE_NAME=dsedi
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICE=dsedidg LGWR SYNC AFFI
RM VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE) DB_UNIQUE_NAME=d
sedidg
log_archive_dest_20 string