Step by Step Guide on How To Reinstate Failed Primary Database into Physical Standby

Step by Step Guide on How To Reinstate Failed Primary Database into Physical Standby 

GOAL

Step by Step Guide on How To Reinstate Failed Primary Database into Physical Standby.

SOLUTION

There are two ways to Reinstate Failed Primary Database: 
1. Using Flashback
2. Without using Flashback

1. Reinstate Failed Primary Database using Flashback 
Prerequisites for Reinstate Failed Primary Database: 
1. Flashback Database must have been enabled on the database prior to the failover
2. There must be sufficient flashback logs on that database to flashback up to the scn when standby becomes the primary
3. The database to be reinstated and the new primary database must have network connectivity
4. Failed primary database should be part of broker configuration (in case of using EM or DGMGRL).

Methods to Reinstate database using Flashback: 
A. Using Enterprise Manager
B. Using DGMGRL
C. Using SQL*PLUS

A. Using Enterprise Manager  

On the Data Guard Overview page, click the Database must be reinstated link.
This brings up the General Properties page that provides a Reinstate button. After you click the Reinstate button, Enterprise Manager begins reinstating the database.When the process is complete, the database will be enabled as a standby database to the new primary database, and Enterprise Manager displays the Data Guard Overview page.

B. Using DGMGRL 
1. Startup the Failed Primary Database in the Mount stage:

SQL> startup mount

 2. Issue the following command while connected to any database in the broker configuration, except the database that is to be reinstated:

DGMGRL> REINSTATE DATABASE ;

If the REINSTATE DATABASE fails to start the Failed Primary Database, then manually start the database in mount stage and reissue the reinstate command.

C. Using SQL*PLUS

Step 1 Determine the Standby Became Primary SCN.
Step 2 Flashback the Failed Primary Database.
Step 3 Convert to physical standby database.
Step 4 Restart Redo Transport.
Step 5 Start Redo Apply.

Step 1 Determine the SCN at which the old standby database became the primary database. 

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

Step 2 Flashback the Failed Primary Database to SCN standby_became_primary_scn.

SQL> SHUTDOWN IMMEDIATE;
SQL> startup mount
SQL> FLASHBACK DATABASE TO SCN ;

Step 3 Convert the database to a physical standby database and Restart database in mount stage.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 4 Restart Redo Transport to the New Physical Standby Database.

1. If you have not set the remote archive destination on current primary then set remote archive destination:

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE= VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=' SCOPE=BOTH;

2. Enable the destination

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

3. Perform a log switch to ensure that standby database begins receiving redo data from the new primary database

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

Step 5 Start Redo Apply.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Please see also fallowing docu:

Oracle® Data Guard Concepts and Administration
11g Release 2 (11.2)

13.2 Converting a Failed Primary Into a Standby Database Using Flashback Database

2. Without Using Flashback 

Use Note 416310.1 : Reinstating a Physical Standby Using Backups Instead of Flashback

REFERENCES

NOTE:376340.1 - ALERT: Reinstating old primary database as a logical standby database after a Data Guard "No Data Loss" Failover
NOTE:416310.1 - Reinstating a Physical Standby Using Backups Instead of Flashback


-=============================================================