Reinstating a Physical Standby Using Backups Instead of Flashback

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.2.0 to 10.2.0.3
Information in this document applies to any platform.
Goal

After a Data Guard failover to a physical standby database, using flashback database is still the MAA recommendation to reinstate the original primary database since it is the simplest and fastest approach. However, if for some reason the flashback data is not available or flashback database was not enabled before the failure, you can use the steps provided in this document to reinstate the original primary database using existing backups instead of flashback database.
Solution

Oracle highly recommends utilizing flashback database to reinstate a physical standby after a Data Guard Failover. The procedure is simpler and safer. When using a controlfile restored from a backup instead of flashing back with the current controlfile, the backup controlfile may not reflect the same files as the data dictionary. However this can be avoided if a standby controlfile is created after each backup.

Prerequisites:
• Backups of all data files exist.
• Standby controlfile backups exist and were taken at the failed primary database using the ALTER DATABASE CREATE STANDBY CONTROLFLE command after each database backup.
• Database version is 10.2.0.2 or higher. Please refer to Alert Note 308698.1 and Note.368276.1.

Step 1: Retrieve STANDBY_BECAME_PRIMARY_SCN after Data Guard Failover
From the new primary:

SQL> select to_char(STANDBY_BECAME_PRIMARY_SCN) from v$database;

Step 2: Remove divergent archivelogs from the original, failed primary database

Remove any archivelogs created at the time of or after the Failover operation. The failed primary, if it was isolated from the standby, could have divergent archive logs not consistent with the current primary database. To ensure you do not ever apply these divergent archive logs, they must be deleted from your backups and flash recovery area. You can use the following RMAN command to delete the relevant archive logs from the flash recovery area.

RMAN> DELETE ARCHIVELOG FROM SCN STANDBY_BECAME_PRIMARY_SCN;

Step 3: Restore backup of the original, failed primary database

RMAN> RESTORE DATABASE;

Step 4: Startup standby database using the physical standby controlfile

If the standby controlfile backup needs to be restored, you will need to ensure the spfile or initialization files point to the standby control file and remount the new standby database.

SQL> startup mount

If a standby controlfile does not exist from the failed primary database, then follow steps 1 and 2.2 through 2.8 in Note 368276.1 to:

1. Clean up the flash recovery area
2. Create a new standby controlfile from the new primary database
3. Restart the new standby database with the new standby controlfile
4. Rename data files and log files

Step 5: Ship a new archive from the primary to the new physical standby database

SQL> alter system set log_archive_dest_state_n = ENABLE;
SQL> alter system archive log current;

From the primary, you can verify if the standby received the archive by querying:

SQL> select dest_id, dest_name, status, type, error, destination
2 > from v$archive_dest_status
3 > where dest_id = ;

Step 6: Restart managed recovery

SQL> recover managed standby database using current logfile through all switchover disconnect;

Step 7: Verify the new standby’s data files and control file are in sync

Once the standby is in sync with the primary, you can do a quick validation check to ensure all the data files are present. The following steps stop managed recovery and open the physical standby read only. The query checks if any data files found in the data dictionary do not match exactly the data files listed in the control file.

SQL> recover managed standby database cancel;
SQL> alter database open;
SQL> select file#, CRSCNBAS from file$
2> MINUS
3> select file#, creation_change# from v$datafile;

If no rows are returned, the data dictionary and controlfile metadata are in sync. Restart managed recovery as described in step 6.

References

Metalink Doc ID: [ID 416310.1]

NOTE:308698.1 - ALERT: Reinstating physical standby databases after a Data Guard Failover
NOTE:368276.1 - Steps to workaround issue described in Alert 308698.1
NOTE:416314.1 - Reinstating a Logical Standby Using Backups Instead of Flashback Database