How to Open the Standby Database When the Primary Is Lost

In critical scenarios where the Primary Oracle Database is lost or unrecoverable, and only the Standby Database remains, we must convert the standby into a new primary and open it in read-write mode.

Below is the step-by-step guide to safely activate the standby database.

🔹 Step 1: Startup and Mount the Standby Database

SQL> STARTUP MOUNT;

📄 Output Example:

ORACLE instance started.

Total System Global Area  1073741824 bytes
Fixed Size                  8902824 bytes
Variable Size             301990776 bytes
Database Buffers          738197504 bytes
Redo Buffers                7876608 bytes
Database mounted.

🔹 Step 2: Check Database Role & Status

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

📄 Output:

NAME     OPEN_MODE            DATABASE_ROLE
------   ------------------   ----------------
STBY     MOUNTED              PHYSICAL STANDBY

🔹 Step 3: Cancel Managed Recovery (If Active)

If the database was applying logs, you’ll get:

SQL> RECOVER STANDBY DATABASE;
ORA-01153: an incompatible media recovery is active

➡️ Cancel it:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

📄 Output:

Database altered.

🔹 Step 4: Finish the Recovery Process

Use the following command to perform the final transition:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

📄 Output:

Database altered.

🔹 Step 5: Activate the Standby Database

Now convert the standby to a primary:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

📄 Output:

Database altered.

⚠️ Note: This is an irreversible action — after activation, the standby can no longer receive logs from the old primary.

🔹 Step 6: Confirm New Role

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

📄 Output:

NAME     OPEN_MODE            DATABASE_ROLE
------   ------------------   ----------------
STBY     MOUNTED              PRIMARY

🔹 Step 7: Open the Database in Read-Write Mode

SQL> ALTER DATABASE OPEN;

📄 Output:

Database altered.

✅ Final Verification

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

📄 Expected Result:

OPEN_MODE  |  DATABASE_ROLE
-----------|----------------
READ WRITE |  PRIMARY

🔍 Summary