Wednesday, October 12, 2011

Open Standby in Read-write Mode When Primary is Lost

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


8 comments:

Anonymous said...

This resets the logs and the archive sequence is started a fresh from sequence no 1

Unknown said...

Hello Neeraj,
Suppose hardware of my primary database is crashed and i have used the steps given by you to convert the standby to primary,but after few hours/days problem of the server has been resolved and i want to go the earlier stat i.e make current primary to standby and earlier primary as primary.

How can i do that?as i have test this in my test database but i am not able to do so.
please suggest.

Thanks,
Pankaj

Abdul said...

super.. Its worked for me thanks a lot

Abdul said...

Super.. Its worked for me.By following the process i changed my Primary database mode from READ ONLY to READ WRITE.Thanks a lot

NEERAJ VISHEN said...

Thanks Abdul ...

Cheers !!!!!

Anonymous said...

It seems inconsistent that the database_role is PHYSICAL STANDBY when it is now a PRIMARY.

Why not finish recovery and do this:
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Thanks.

Pramod said...

Hi,

Good Presentation.

But DATABASE_ROLE is wrong below as DB is PRIMARY Now !.

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

Unknown said...

Thanks Neeraj.

Its really a good note.

Thanks,
Nowfal