Synchronize a Gap on the Standby DB when the Archived Logs are Lost
Summary
Case Description: The archived logs are not present on the Standby DB and they are not available on the PROD Server,
so now the standby had lagged far behind the primary, many required archived logs to close the gap were removed and no backup of them was available.
In order to close the gap, we will perform an incremental backup that will contain all transactions since the last SCN recorded on the standby database.
Using this technique is convenient if the total amount of transactions that make the gap is less than the database size.
Action Items
In order to avoid this problem to happen in the future, we recommend the following action items
- Setup script for daily backup and cleanup of the recovery area
- Setup job to check for archivelog destination free space and executing the backup and cleanup script if required before the scheduled run.
Implementation Steps
Cancel Recovery on Standby
SQL> alter database recover managed standby database cancel;
Trying to recover on Standby
SQL> recover standby database;
ORA-00279: change 11021986 generated at 01/09/2011 11:39:03 needed for thread 1
ORA-00289: suggestion : /arch/ORACLE/ORADATA/SATI/ARCHIVE/1_205_674755717.ARC
ORA-00280: change 11021986 for thread 1 is in sequence #205
Check current_scn on Standby
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
11021980
Create an Incremental Backup from this SCN on the Primary DB
Z:\backup> rman target sys@PROD_PRIMARY
RMAN> backup incremental from scn 11021980 database tag 'FORSTANDBY' FORMAT '/BACKUP/FOR_STANDBY_%U';
Backup the Controlfile for ‘Standby’ on the Primary DB
RMAN> backup current controlfile for standby format '/BACKUP/FORSTDBYCTRL.bkp';
Transfer the Incremental Backup Sets from Primary DB to the Standby Server (Location = /Temp/Incr_Backup)
cd /BACKUP
copy FOR_STANDBY_*.* \\STANDBY\ /TEMP/INCR_BACKUP
copy FORSTDBYCTRL.bkp \\STANDBY\ /TEMP/INCR_BACKUP
Restore controlfile on the Standby
RMAN> RESTORE STANDBY CONTROLFILE FROM '/TEMP/INCR_BACKUP/FORSTDBYCTRL.BKP';
Catalog the Incremental Backups on the Standby Server
Note that for the catalog command to succeed you will need to move the backups to be within the Flash Recovery Area.
RMAN> catalog start with '/FRA/SATISTD/BACKUPSET';
Recover the Database and Cleanup Redologs on the Standby Server
RMAN> recover database noredo;
…
…
SQL> alter database flashback off;
SQL> alter database flashback on;
SQL> alter database recover managed standby database disconnect from session;
If more archived logs were created on the primary since the finish of the SCN based incremental backup then you can copy them over and recover the standby database using the command : “recover standby database;”
Enable the broker at both sites and check
When enabling the broker again it will take over the responsibility of managing the site and will resynchronize both sites
SQL> alter system set dg_broker_start=true scope=both;
