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!!!!