04 | 11 | 2024
Latest Articles
Popular Articles

Data Guard

Redo log management in physical standby database

User Rating:  / 1
PoorBest 

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.