Data Guard
Redo log management in physical standby database
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Wednesday, 27 April 2016 16:14
-
Last Updated on Wednesday, 27 April 2016 16:14
-
Published on Wednesday, 27 April 2016 16:14
-
Written by Guy Lambregts
-
Hits: 12944
Redo log management in physical standby database
How to add redo log file members to a physical standby database
DGMGRL> edit database 'STANDBY_DB' set property StandbyFileManagement=manual;
Property "standbyfilemanagement" updated
DGMGRL> edit database 'STANDBY_DB' set state='APPLY-OFF';
Succeeded.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo04b.log' to group 4;
Database altered.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo05b.log' to group 5;
Database altered.
We cannot add members to the redo log group which is currently the current one ( check v$log.status )
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo06b.log' to group 6;
alter database add logfile member '/u03/oradata/PRIMARY_DB/redo06b.log' to group 6
*
ERROR at line 1:
ORA-00314: log 6 of thread 1, expected sequence# 3077 doesn't match 0
ORA-00312: online log 6 thread 1: '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_6_9roz1fw5_.log'
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo07b.log' to group 7;
Database altered.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo08b.log' to group 8;
Database altered.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo09b.log' to group 9;
Database altered.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo10b.log' to group 10;
Database altered.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo11b.log' to group 11;
Database altered.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo12b.log' to group 12;
Database altered.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo13b.log' to group 13;
Database altered.
We force a log switch, afterwards we can add a log file member to the group which was previously the current one ( check v$log.status )
PRIMARY_DB > alter system archive log current;
System altered.
STANDBY_DB > alter database add logfile member '/u03/oradata/PRIMARY_DB/redo06b.log' to group 6;
Database altered.
How to drop redo log file members from a physical standby database
To drop log redo log file members we need to clear first the redo log group ( check v$log.status )
STANDBY_DB > alter database clear logfile group 4;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_4_9roz15lf_.log';
Database altered.
STANDBY_DB > alter database clear logfile group 5;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_5_9roz19cr_.log';
Database altered.
STANDBY_DB > alter database clear logfile group 6;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_6_9roz1fw5_.log';
Database altered.
STANDBY_DB > alter database clear logfile group 7;
Database altered.
We cannot drop a member from a redo log file group which is currently the current one ( check v$log.status )
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_7_9roz1mnx_.log';
alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_7_9roz1mnx_.log'
*
ERROR at line 1:
ORA-01609: log 7 is the current log for thread 1 - cannot drop members
ORA-00312: online log 7 thread 1: '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_7_9roz1mnx_.log'
ORA-00312: online log 7 thread 1: '/u03/oradata/PRIMARY_DB/redo07b.log'
STANDBY_DB > alter database clear logfile group 8;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_8_9roz1yko_.log';
Database altered.
STANDBY_DB > alter database clear logfile group 9;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_9_9roz22dt_.log';
Database altered.
STANDBY_DB > alter database clear logfile group 10;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_10_9roz28dv_.log';
Database altered.
STANDBY_DB > alter database clear logfile group 11;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_11_9roz2j5d_.log';
Database altered.
STANDBY_DB > alter database clear logfile group 12;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_12_9roz2v4q_.log';
Database altered.
STANDBY_DB > alter database clear logfile group 13;
Database altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_13_9roz3229_.log';
Database altered.
We force a log switch, afterwards we can drop a log file member from the group which was previously the current one ( check v$log.status )
PRIMARY_DB > alter system archive log current;
System altered.
STANDBY_DB > alter database drop logfile member '/u01/fast_recovery_area/STANDBY_DB/onlinelog/o1_mf_7_9roz1mnx_.log';
Database altered.
DGMGRL> edit database 'STANDBY_DB' set property StandbyFileManagement=auto;
Property "standbyfilemanagement" updated
DGMGRL> edit database 'STANDBY_DB' set state='APPLY-ON';
Succeeded.