How To Open The Standby Database When The Primary Is Lost in oracle11g ?

How To Open The Standby Database When The Primary Is Lost in oracle11g ?
July 02, 2020


Scenario:- So this scenario shows how to open your standby database in read/write mode when you dont have any access(Lost) on primary database.
Here i'm trying to make a test case to demonstrate the following scenario.Currenly both primary and standby database are in sync.

Oracle version :-  11.2.0.1.0
Primary Database : prim
Standby Database : stand

At primary database:-
SYS @ catdb >select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26
  
  
At standby database:-
SYS @ catdb >select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26

So at this situation we totally power off the primary database server to test the scenario.

At primary:-
SYS @ catdb >shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ catdb >exit

[root@server1 ~]# poweroff

Now at standby database:-
Open the database in mount stage.
SYS @ catdb >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.

SYS @ catdb >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 STANDBY DATABASE FINISH;

Database altered.

Once done, now activate the standby database:-
SYS @ catdb >ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

Check the status:-
SYS @ catdb >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.
SYS @ catdb >ALTER DATABASE OPEN;

Database altered.

SYS @ catdb >select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SYS @ catdb >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.