Thursday, July 21, 2011

Recover a Failed Primary Database in Oracle10g Data Guard Configuration

When you failover a primary database to a physical standby database, the standby database assumes the role of the primary database. Oracle records the SCN at which the standby database became new primary in V$DATABASE.STANDBY_BECAME_PRIMARY_SCN. From this point onwards, SCN chain is advanced in the new primary database by all subsequent transactions. Now the former primary database can no longer participate in the Data Guard configuration unless and until it is recovered upto SCN stored in V$DATABASE.STANDBY_BECAME_PRIMARY_SCN.

After resolving failover issues, you may be able to restart the former primary database, but this database is out of SCN alignment and you must recover it. Once you recover the former primary database, it can be joined in Data Guard configuration.

If enabled, Flashback Backup Logs may be the quickest and easiest option to perform the recovery. Otherwise, RMAN backups with archived logs would get you the desired result.

Perform the following query to obtain the SCN at which the physical standby database became the primary database:


To recover database using RMAN, run the following RMAN block:

    { SET UNTIL SCN <standby_became_primary_scn + 1>;

With Flashback option enabled, run the following:

SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

Please note a subtle difference in the above-mentioned two recovery options. RMAN sets SCN at standby_became_primary_scn + 1, but Flashback database option uses standby_became_primary_scn. If you are wondering why, then here is the explanation:

SCN in RMAN block specifies an upper, non-inclusive limit. RMAN selects only files that can be used to restore or recover up to but not including the specified SCN. Flashback Database specifies an inclusive SCN value. Flashback database recovers upto, including standby_became_primary_scn.

No comments:

Post a Comment