isitdevops.com/databases

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

On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

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

Step 2 Flash back the failed primary database.

To create a new physical standby database, shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in Step 1:

SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

Step 3 Convert the database to a physical standby database.

Perform the following steps on the old primary database:

1. Issue the following statement on the old primary database:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

This statement will dismount the database after successfully converting the control file to a standby control file.

2. Shut down and restart the database:

SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT;

Step 4 Restart transporting redo to the new physical standby database.

Step 5 Start Redo Apply.

Start Redo Apply or real-time apply on the new physical standby database:

To start Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

To start real-time apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE    2> USING CURRENT LOGFILE DISCONNECT;