Lets see together the steps to resolve problems if a physical standby database has lost or corrupted archived redo data or has an unresolveable archive gap.
1. Stop the managed recovery process (MRP) On the standby database
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 :
SQL> SELECT CURRENT_SCN FROM V$DATABASE; SQL> SELECT MIN(FHSCN) from x$kcvfh;
(In ideal situation the above 2 queries will return the almost same SCN. However if there is huge difference its better to take backup using the SCN from second query (lesser SCN), as one of the datafile may bebehind.)
CURRENT_SCN --------------------- 55622885
3. 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 55622885 DATABASE FORMAT '/apps/oracle/backup/ForStandby_%U' tag 'FORSTANDBY';
4. Transfer all backup sets created on the primary system to the standby system.
scp /apps/oracle/backup/ForStandby_* standby_node:/apps/oracle/backup
5. In RMAN, connect to the STANDBY database and run the above command :
RMAN> CATALOG START WITH '/apps/oracle/backup'; using target database control file instead of recovery catalog searching for all files that match the pattern /apps/oracle/backup List of Files Unknown to the Database ===================================== FileName: /apps/oracle/backup/ForStandby_2lkglss4_1_1 FileName: /apps/oracle/backup/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 ======================= FileName: /apps/oracle/backup/ForStandby_2lkglss4_1_1 FileName: /apps/oracle/backup/ForStandby_2mkglst8_1_1
(This means that any files under the backup folder and folders which have backup as their prefix are cataloged by RMAN, and then you can use and restore and recover these backup files)
6. Recover the STANDBY database with the cataloged incremental backup :
RMAN> RECOVER DATABASE NOREDO; starting recover at 25-AUG-21 ... ... Finished recover at 25-AUG-21
7. In RMAN, connect to the PRIMARY database and create a standby control file backup :
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/apps/oracle/backup/ForStandbyCTRL.bck';
8. Copy the standby control file backup to the STANDBY system :
scp /apps/oracle/backup/ForStandbyCTRL.bck standby_node:/apps/oracle/backup
9. We now need to refresh the standby controlfile from primary controlfile (for standby) backup. However, since the datafile names are likely different than primary, let’s save the name of datafiles on standby first, which we can refer after restoring controlfile from primary backup to verify if any discrepancy. So, run below query from Standby and save results for further use :
SQL> set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
10. From RMAN, connect to STANDBY database and restore the standby control file :
RMAN> SHUTDOWN; RMAN> STARTUP NOMOUNT; RMAN> RESTORE STANDBY CONTROLFILE FROM '/apps/oracle/backup/ForStandbyCTRL.bck';
11. Shut down the STANDBY database and startup mount :Â
RMAN> SHUTDOWN; RMAN> STARTUP MOUNT;
12. 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 '/app/oracle/db_std/oradata/'; RMAN> SWITCH DATABASE TO COPY;
At this point, you can compare the query output from step 9) for any discrepancy (other than newly added datafiles) by running the same query as in Step 9) to ensure we have all the datafiles added in standby.
13. If the STANDBY database needs to be configured for FLASHBACK use the below step to enable  :Â
SQL> ALTER DATABASE FLASHBACK OFF; SQL> ALTER DATABASE FLASHBACK ON;
14. On standby database, clear all standby redo log groups : Â
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3; .....
15. On standby database, start the MRP : Â
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
I hope that was clear for you 😉Â
Founder of RakDBA.com
Expert database administrator on Oracle & SQL Server databases with 14 years experience. Certified for Oracle and MSCP for SQL SERVER.