How To Open the Standby Database When the Primary Is Lost

In this case how to open your standby database in read/write mode when you don’t have any access (Lost) on Primary database.

NOTE: Currently both Primary and Standby database are in sync.

PRIMDARY Database: PRIMDB

STANDBY Database: STANDDBDB

Primary database: –

SQL> select thread#, max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1             33

Standby database: –

SQL> select thread#, max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1             33

In this situation we powered off the Primary database server to test the scenario.

At Primary: –

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Now at Standby database: –

Open the database in mount stage.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.

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

OPEN_MODE     PROTECTION_MODE      DATABASE_ROLE
———— ——————– —————-
MOUNTED       MAXIMUM PERFORMANCE  PHYSICAL STANDBY

Finish the Recovery process in Standby database:-

SQL>ALTER DATABASE RECOVER MANAGED STANDDBBY DATABASE FINISH;

Database altered.

Once done, now activate the Standby database: –

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDDBBY DATABASE;

Database altered.

Check the status: –

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

OPEN_MODE     PROTECTION_MODE      DATABASE_ROLE
———– ——————– —————-
MOUNTED       MAXIMUM PERFORMANCE   PRIMARY

Now open the database in read-write mode.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

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

OPEN_MODE         PROTECTION_MODE      DATABASE_ROLE
—————– ——————– —————-
READ WRITE        MAXIMUM PERFORMANCE  PRIMARY

So, finally the previous Standby database is now a new Primary database with read-write mode in open stage.