A common problem with standby databases is the loss of one or more archive logs that have not yet been applied. The solution was usually to rebuild the standby database, which, while relatively easy, can be quite time-consuming. Some people suggested this to a worried DBA asking for help in the Oracle Technet forum; thankfully, if you are using Oracle 10gR2, the solution is much simpler and faster. Sounds good? Then, let’s go through the procedure.
1. Stop the standby database synchronization
STDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;2. Get the SCN to which the standby database has synchronized
STDB> SELECT CURRENT_SCN FROM V$DATABASE;3. Obtain an incremental backup of the primary database with RMAN, from the SCN obtained in the previous step
RMAN> BACKUP INCREMENTAL FROM SCN <SCN from previous step>
DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';4. Catalog the backup of the previous step in the standby database
RMAN> CATALOG START WITH '/tmp/ForStandby';5. Recover the standby database with the backup that has already been cataloged
RMAN> RECOVER DATABASE NOREDO;6. In the primary database, create a new standby controlfile
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';7. Stop the standby database and start it in nomount mode
RMAN> SHUTDOWN;
RMAN> STARTUP NOMOUNT;8. Restore the standby controlfile obtained in step (6) in the standby database
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';9. Stop the standby database and then start it in mount mode
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;10. Clear the standby redo logs in the standby database
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 1;
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 2;
STDB> ALTER DATABASE CLEAR LOGFILE GROUP 3;11. If Flashback Database was enabled, re-enable it.
STDB> ALTER DATABASE FLASHBACK OFF;
STDB> ALTER DATABASE FLASHBACK ON;12. Restart the recovery of the standby database
STDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;This procedure applies not only when an archivelog has been lost, but also when nologging operations have been performed on the primary database or when there is a significant synchronization delay.
You can review the complete procedure in the manual. Oracle Data Guard Concepts and Administration.
