Standby Database Failover
Oracle 10g introduced the ability to perform a “switchover” as well as a “failover”. Switchovers allow the primary and standby databases to switch roles with no data loss. This can be used to reduce downtime for hardware maintenance and operating system upgrades.
A failover results in invalidating the primary database and is typically used when a disaster is declared. It involves determining that the primary instance has failed and that service restoration will require a long enough delay that a failover to the standby database is required. It should be noted that a failover is not reversible. Once a failover is started the primary and standby databases become two separate databases and a full instantiation will be required to either transition back to the primary database server or to reverse roles and use the former primary server as a new standby server.
From the Oracle documentation:
“Database failover transitions one of the standby databases to the role of primary database. You should perform a failover only when a catastrophic failure occurs on the primary database, and there is no possibility of recovering the primary database in a timely manner. The failed primary database is discarded, and the target standby database assumes the primary role.”
The following will show the steps to initiate a manual failover to a standby database.
In 11.2 the SQL command: ALTER SYSTEM FLUSH REDO can be performed on a mounted primary database to insure a zero data loss failover. If possible flush redo on the primary database:
SQL> ALTER SYSTEM FLUSH REDO TO dbsid_dg;
Verify gap status and archive log file status:
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2 OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- --------- ------------------------------ ---------------
3 ONLINE /u01/oradata/DBSID/log03a.rdo NO
2 ONLINE /u01/oradata/DBSID/log02a.rdo NO
1 ONLINE /u01/oradata/DBSID/log01a.rdo NO
4 STANDBY /u01/oradata/DBSID/srl01a.rdo NO
5 STANDBY /u01/oradata/DBSID/srl02a.rdo NO
6 STANDBY /u01/oradata/DBSID/srl03a.rdo NO
7 STANDBY /u01/oradata/DBSID/srl04a.rdo NO
7 rows selected.
On the standby database issue the following commands to failover:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
If this statement completes without error then issue the following:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
If a value of “TO PRIMARY” or “SESSIONS ACTIVE” is returned then continue with the following switchover command. If not then make sure Redo Apply is active and wait until all redo has been applied.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
You should include the “WITH SESSION SHUTDOWN” option if the switchover status was not “TO PRIMARY”.
Open the new primary database:
SQL> ALTER DATABASE OPEN;
If there were errors issuing the “RECOVER MANAGED STANDBY DATABASE FINISH” command then there may be some data loss, but the standby can still be converted to a primary database with the following commands:
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Open the database with:
SQL> ALTER DATABASE OPEN;
Data Guard Failover
The following will show a Data Guard initiated fail over to a standby database using manual commands with the Data Guard Broker command line tool.
Failover with dgmgrl
DGMGRL> show configuration
Protection Mode: MaxPerformance
ORADB - Primary database
ORADB_DG - Physical standby database
Current status for "ORADB":
DGMGRL> failover to ORADB_DG;