The Application DBA  

Standby Database Failover

Overview

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.”

Manual Failover

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;
 
    THREAD       LAST
---------- ----------
         1        117
 
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.

<SQL>
 
On the standby database issue the following commands to failover:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
Database altered.

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
 
Configuration
  Name:            ORADB
  Enabled:         YES
  Protection Mode: MaxPerformance
  Databases:
    ORADB - Primary database
    ORADB_DG - Physical standby database
 
Current status for "ORADB":
SUCCESS
 
DGMGRL> failover to ORADB_DG;

Copyright 1998-2013 AppsDBA Consulting. All Rights Reserved.