Open Standby Database in Read-write Mode When Primary is Lost Completely

Open Standby Database in Read-write Mode When Primary is Lost Completely

There may be scenario where Primary database is lost and we are only left with the standby database. In this scenario's we have to open the standby database in read-write mode. Below are the steps to convert standby database to Primary database.

1.)  Open standby database in mount state : 

SQL> select name,open_mode from v$database;
NAME       OPEN_MODE
------     -------------
stby      READ ONLY

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
 
Total System Global Area      263639040  bytes
Fixed Size                             1373964      bytes
Variable Size                         230689012  bytes
Database Buffers                  25165824     bytes
Redo Buffers                        6410240       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

2.) Recover if there is any archive logs:

SQL>recover standby database;
ORA-01153: an incompatible media recovery is active

To solve this issue, we cancel the media recovery by using the below command .

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> recover standby database


ORA-00279: change 2698969 generated at 10/05/2011 16:46:58 needed for thread
ORA-00289: suggestion : D:\ARCHIVE\ARC0000000133_0761068614.0001
ORA-00280: change 2698969 for thread 1 is in sequence #133

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

3.) Finish the Recovery process :  

The below command will perform the role transition as quickly as possible with little or no data loss and without rendering other standby databases unusable and to open the database in read-write mode we fire the below command :

SQL>alter database recover managed standby database finish;
Database altered.

4.) Activate the Standby Database : 

SQL> alter database activate physical standby database ;
Database altered.

5.) Check the new status

SQL> select open_mode ,protection_mode , database_role from v$database ;

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

6.) Open the Database

SQL> alter database open ;
Database altered.

SQL> select open_mode ,protection_mode , database_role from v$database ;

OPEN_MODE       PROTECTION_MODE             DATABASE_ROLE
---------           ----------------------        --------------------
READ WRITE       MAXIMUM PERFORMANCE      PHYSICAL STANDBY


======================************============================