Monday, December 12, 2011

FAILOVER Database with out DG Broker

           MANUAL FAILOVER ON PHYSICAL STANDBY DATABASE
 
This note describes how to perform manual failover operations in physical standby databases with out DG Broker.
 If you are performing for RAC, then you can perform with the single instance and keep other instance in the 
shutdown mode.
 
After a Failover, the Standby Database becoming a Primary now, cannot switchback to become a 
Standby Database again. So you can perform Failover only once.
 
A Failover can be performed when all or most of the information until the Unavailability of the Primary Database
 was propagated to the Standby. The usage of Standby Redo Logs is a great advantage here. If you have no
 Standby Redo Logs available, you will always encounter some Data Loss (depending on the Changes since
 the latest Log Switch). To perform a Failover just follow these steps:
 
STEP#1 The Primary Database is down for any reason
 
Verify a Standby Redo Log is in use for Primary current Online Redo Log. You then find in the ALERT.LOG
of the Standby something like: RFS:  If this is the case run the following commands:
   
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  
  This cancels the normal managed Recovery. 
 
STEP#2 To get the Standby Redo Log Information is still required. Therefore issue this command:
 
   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
   
 
If you have the standby redo log then go to STEP 3
 
  2 (a) If a Standby Redo Log is not used for any reason and then run this one:
 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
 
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;
 
Please keep in mind that this one causes (Minimal) Data Loss as the latest  information from the down Primary
 Database is not available anymore. 
 
Once this is complete (This performs a complete Recovery or incomplete   Recovery until the last SCN
 included in the latest archived Log available at the Standby), you can now make the Standby Database a 
Primary:
  
STEP #3 converting standby role to the primary role
 
 
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.
   
WARNING: This will only succeed if the correct RECOVER FINISH-statement was issued before.
 If you forgot the 'SKIP STANDBY LOGFILE' although you have no Standby RedoLogs, the COMMIT
 to Switchover will fail with the error that more Media Recovery is required here.
 
If above statement fail then perform the below step else go for STEP 4
 
STEP (3A)
If the COMMIT TO SWITCHOVER fails for any reason you have to use the ACTIVATE command 
which forces the Failover (and may cause Data Loss !!)
  
   SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
 
STEP # 4 Shutdown and restart the Database after this command ended successfully:
 
   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP
   
- Now the Standby is open as a new Primary Database 

Happy learning!!!!

1 comment:

  1. Excellent handy notes which can save not only time beyond that, thanks for sharing :)

    ReplyDelete