Switchover and failover in oracle 11g Data - Guard

Switchover

Allows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Data Guard configuration with its new role.

Failover

Changes a standby database to the primary role in response to a primary database failure. If the primary database was not operating in either maximum protection mode or maximum availability mode before the failure, some data loss may occur. If Flashback Database is enabled on the primary database, it can be reinstated as a standby for the new primary database once the reason for the failure is corrected. verify the  primary database can be switched to the standby role.

Preparing for a Role Transition

        1)        Verify that there are no redo transport errors or redo gaps at the standby database by querying the V$ARCHIVE_DEST_STATUS view on the primary database.

SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

STATUS GAP_STATUS

--------- ------------------------

VALID NO GAP

       2)       Ensure temporary files exist on the standby database that match the temporary files on the primary database.

        3)       Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

        4)       Before performing a switchover to a physical standby database that is in real-time query mode, consider bringing all instances of that standby database to the mounted but not open state to achieve the fastest possible role transition and to cleanly terminate any user sessions connected to the physical standby database prior to the role transition

Switchover Steps

        1)       Check Primary and standby databases are ready to  switchover

On Primary database

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

 -----------------

 TO STANDBY

 1 row selected

·          A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly.

On Secondary Database

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

-----------------

TO_PRIMARY

1 row selected

·          A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and that redo transport is configured and working properly.

         2)       Initiate the switchover on the primary database.

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

 
         3) Shut down and then mount the former primary database.

SHUTDOWN ABORT;

STARTUP MOUNT;

   4)  Switch the target physical standby database role to the primary role.

  ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

   5)    Open the new primary database.

ALTER DATABASE OPEN;

  6)    Start Redo Apply on the new physical standby database.

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Steps for failover

        1)       If the primary database can be mounted, it may be possible to flush any unsent archived and current redo from the primary database to the standby database. If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode.

ALTER SYSTEM FLUSH REDO TO target_db_name;

       
         2)    Verify that the standby database has the most recently archived redo log file for each primary database redo thread.

   SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

   THREAD       LAST

---------- ----------

         1       3555

       3)       If possible, copy the most recently archived redo log file for each primary database redo thread to the standby database if it does not exist there, and register it. This must be done for each redo thread.

     ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filename ';

         
          4)       Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

THREAD#    LOW_SEQUENCE# HIGH_SEQUENCE#

---------- ------------- --------------

         1            3555          3555

                                                  

         5)       Stop Redo Apply.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

           
         6)       Finish applying all received redo data.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

           
         7)       Verify that the target standby database is ready to become a primary database.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

-----------------

TO_PRIMARY

         8)       Switch the physical standby database to the primary role.

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

         
         9)       Open the new primary database.

ALTER DATABASE OPEN;