Manual Reinstate old Primary Using Flashback

In this article, we will learn how to reinstate old primary using flashback (Oracle Data Guard). This is a lengthy task of rebuilding the corrupt or lost primary database, but with flashback technology, it is a very easy task now.

Steps to Reinstate old Primary Using Flashback in Oracle DataGuard

Read here Reinstate with DG Broker

Step 1: Check the Flashback status

Check whether the flashback is enabled or not on the current primary using the following command.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
YES

In my case you can see, my flashback is enabled.

Step 2: Check the SCN number on the current Primary

To perform reinstate we need the "standby_became_primary_scn" column value from the v$database view.

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

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
3453881

Now we have an SCN number from where the current database became a Primary Database.

Step 3: Start the old Primary till the mount

Start failed primary till the mount stage, remember don't open it.

SQL>startup mount

Step 4: Perform the Flashback on the old Primary

Now, it's time to perform a flashback with the above SCN number (which we check in step 2) using the below query.

SQL> flashback database to scn 3453881;

flashback complete.

ORA-38743 Solution:

Just startup opening your old primary database and then shutdown, as you can see in the below screenshot.

Step 5: Convert old Primary to Standby

After the flashback, now we need to change the database role from Primary to standby.

SQL> Alter Database Convert To Physical Standby;

Database altered.

Step 6: Restart the Database and check

Finally, we have successfully reinstated the failed primary. In the last step take bounce the database then check and start the MRP process.

SQL> shu immediate
SQL> startup mount

Check the details:

SQL>SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;

Start the MRP process

Using the following command you can start the MRP process.

SQL> alter database recover managed standby database disconnect from session;