Flashing Back an Activated Standby Database (Primary) into a Physical Standby Database

Hi,

One of my critical primary database was gone to the down due to an uncertain cause. We couldn’t open it. I started the recovery on primary. During recovery, We also enabled flashback database and activated the standby as primary. When our team controls the new primary (activated standby database) the old primary is recovered and opened. We decided to use the old primary. But we activated standby database and want to back it into physical standby.

Assume that a failover has been performed to a physical standby database and that Flashback Database was enabled at the time of the failover. The following steps bring the activated standby database (Now it is primary) back into the Oracle Data Guard configuration as a physical standby 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;

Shut down the activated standby database mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in the previous step.

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

To convert the database to a physical standby database, issue the following statement on the activated standby database:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

To start transporting redo to the new physical standby database, perform the following steps on the primary database:

Issue the following query to see the current state of the archive destinations:

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

Enable the destination:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

Ensure the standby database begins receiving redo data from the primary database, and verify it was sent successfully.

Start Redo Apply on the physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Redo applying automatically stops each time it encounters a redo record that is generated as the result of a role transition, so Redo Apply needs to be restarted one or more times until it has applied beyond the SCN at which the primary database became the primary database.