The steps in this post can used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an unresolvable archive gap.
1. Make sure you stop the managed recovery process (MRP) before going ahead with further steps.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2. On the STANDBY DATABASE, find the SCN which will be used for the incremental backup at the primary database. You need to use the ‘lowest SCN’ from the the 3 queries below :
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
--------------
3164433
SQL> select min(fhscn) from x$kcvfh;
MIN(FHSCN)
----------------
3162298
SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ;
MIN(F.FHSCN)
----------------
3162298
3. You need to use the ‘lowest SCN‘ from the the 3 queries, which here is -> SCN: 3162298. In RMAN, connect to the PRIMARY database and create an incremental backup from the SCN derived in the previous step:
RMAN> BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
4. Transfer all backup sets created on the primary system to the standby system.
primary $ scp /tmp/ForStandby_* standby:/tmp
On the STANDBY catalog the backuppieces:
RMAN> CATALOG START WITH '/tmp/ForStandby'; using target database control file instead of recovery catalog searching for all files that match the pattern /tmp/ForStandby List of Files Unknown to the Database ===================================== File Name: /tmp/ForStandby_2lkglss4_1_1 File Name: /tmp/ForStandby_2mkglst8_1_1 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /tmp/ForStandby_2lkglss4_1_1 File Name: /tmp/ForStandby_2mkglst8_1_1
5. Recover the STANDBY database with the cataloged incremental backup:
RMAN> RECOVER DATABASE NOREDO; starting recover at 03-JUN-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=28 devtype=DISK channel ORA_DISK_1: starting incremental datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: +DATA/mystd/datafile/system.297.688213333 destination for restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335 destination for restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333 channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished recover at 03-JUN-09
6. In RMAN, connect to the PRIMARY database and create a standby control file backup:
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
7. Copy the standby control file backup to the STANDBY system.
primary $ scp /tmp/ForStandbyCTRL.bck standby:/tmp
8. Take a backup of location of datafile at standby in case the datafiles name/location are different than the primary.
SQL> spool datafile_names_step8.txt set lines 200 col name format a60 select file#, name from v$datafile order by file# ; spool off
9. From RMAN, connect to STANDBY database and restore the standby control file:
RMAN> SHUTDOWN IMMEDIATE ; RMAN> STARTUP NOMOUNT; RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck'; Starting restore at 03-JUN-09 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=36 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 output filename=+DATA/mystd/controlfile/current.257.688583989 Finished restore at 03-JUN-09
10. Shut down the STANDBY database and startup mount:
SQL> SHUTDOWN; SQL> STARTUP MOUNT;
11. Since the controlfile is restored from PRIMARY, the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations. Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.
RMAN> CATALOG START WITH '+DATA/mystd/datafile/'; List of Files Unknown to the Database ===================================== File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773 File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773 File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775 File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333 File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333 File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333 File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333 File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
12. Switch the datafiles to its correct names at the standby site :
RMAN> SWITCH DATABASE TO COPY; datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333" datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335" datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
13. On standby database, clear all standby redo log groups:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP [group number];
Verify :
Run the queries from step 2 again to confirm that the incremental apply has moved the datafiles forward. The SCN should now be bigger than its initial values.
14. Start the MRP process on standby
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
You may also take and incremental backup of the specific datafiles whose SCN number at standby is behind that in the primary database. In that way you can reduce the backup and restore time.