Recovering standby database using scn number when it is out of sync on 2 node RAC with RMAN

Note: TESTED ON 2 NODE RAC DATABASE which is on Oracle 10g.

on Standby database side


1) Shut down the second instance or use SRVCTL command

SRVCTL STOP INSTANCE -i DBSTDBY012 -d DBSTDBY01

2) As usual The primary instance of the standby database will be in recovery mode cancel the recovery.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3)find the current_scn of primary instance of the standby database.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1.0126E+13

SQL> col CURRENT_SCN format 999999999999999

SQL> select current_scn from v$database;
CURRENT_SCN
----------------
10126033017541

SQL>
SQL>Shutdown immediate;
On primary side
Take the backup on primary database with current scn of the standby database.

4) rman target /
 
BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 10126033017541 DATABASE FORMAT '/app01/backup/rman/ForStandby_%U' tag 'FORSTANDBY';


5) Once the backup is completed scp the backup files to standby server

scp <backupfile_name or * if multiple backup files are there > oracle@<standby servername>:/app01/backup/rman(<---- Location on the standby side)

On Standby database side

register the rman backup files

6) rman target /

CATALOG START WITH '/app01/backup/rman/ForStandby';

RMAN>RECOVER DEVICE TYPE DISK DATABASE NOREDO ;

7) On primary side

RMAN>BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/app01/backup/rman/ForStandbyCTRL.bck';

scp <ForStandbyCTRL.bck> oracle@<standby servername>:/app01/backup/rman(<---- Location on the standby side)

8) On Standby database side
RMAN> SHUTDOWN;
RMAN> STARTUP NOMOUNT;

RMAN>RESTORE STANDBY CONTROLFILE FROM '/app01/backup/rman/ForStandbyCTRL.bck';(The recreating of standby controlfile varies from restore to restore)

RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

9) Start the second instance of the standby database.
STARTUP NOMOUNT
ALTER DATABASE MOUNT;
OR USE
SRVCTL START INSTANCE -i DBSTDBY012 -d DBSTDBY01

Use this querys to check the sync is in place or not.

SELECT THREAD#,SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO' order by sequence#;


select sequence#, applied, to_char(first_time,'dd-mon-yyyy hh:mi:ss'), to_char(next_time,'dd-mon-yyyy hh:mi:ss') from v$archived_log order by sequence#;