Sync the Physical Standby Using RECOVER FROM SERVICE in Oracle 12c

Oracle introduced the new RECOVER FROM SERVICE command in Oracle 12c to simplify syncing a standby database when the Data Guard configuration goes out of sync.

This command reduces the manual effort required in earlier versions.

Before 12c – Traditional Method

Previously, to resync the standby you had to:

Create a standby control file from PROD and move it to the standby server.
Identify the SCN number of the standby and take an RMAN backup from that SCN on primary.
Copy the backup to standby.
Mount the standby with the new standby control file.
Apply the incremental RMAN backup to standby.
Restart managed recovery on the standby database.

In 12c – Using `RECOVER FROM SERVICE`

With RECOVER FROM SERVICE, Oracle automates most of this:

Creates incremental backup from the SCN at the standby.
Transfers the backup to standby.
Applies the backup to bring standby in sync.

Step-by-Step Recovery Process

1. Check SCN Gap Between Primary and Standby

On both primary and standby:


SELECT CURRENT_SCN FROM V$DATABASE;

Check SCN differences at the file level:


SELECT HXFIL file_num, SUBSTR(HXFNM,1,40), fhscn 
FROM x$kcvfh;

2. Stop Managed Recovery on Standby


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3. Mount Standby


SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

4. Run RMAN Recovery from Primary Service

rman target /

Connect RMAN on standby and run:


RMAN> 
RECOVER DATABASE FROM SERVICE PROD NOREDO USING COMPRESSED BACKUPSET;

(PROD should be resolvable via tnsnames.ora and point to primary DB)

5. Verify SCNs Again

Run the same SCN queries on both primary and standby. File SCNs should now match.

6. Restore Standby Control File

If control file SCN is still behind:


SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
RESTORE STANDBY CONTROLFILE FROM SERVICE PROD;
ALTER DATABASE MOUNT;

7. Check File Locations on Standby


rman target /
REPORT SCHEMA;

If paths differ from primary, catalog and switch:


CATALOG START WITH '/u01/app/oracle/oradata/clone/';
SWITCH DATABASE TO COPY;

8. Handle Newly Added Datafiles (if any)


SELECT file# 
FROM v$datafile 
WHERE creation_change# >= <SCN_FROM_STEP5>;

Restore new files if needed:


RUN {
   SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/standby';
   RESTORE DATAFILE 21 FROM SERVICE prod;
}

9. Adjust Redo Logs if Needed


ALTER DATABASE CLEAR LOGFILE GROUP <n>;
ALTER DATABASE RENAME FILE '/path/oldredo.log' TO '/path/newredo.log';

10. Restart Managed Recovery

ALTER DATABASE OPEN READ ONLY; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Sync the Physical Standby Using RECOVER FROM SERVICE in Oracle 12c