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
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
STEP #3 converting standby role to the primary role
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
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
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;
- Now the Standby is open as a new Primary Database