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
------ -------------
NOIDA 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
Enjoy :-)
