Standby Database Switchover | Failover

Switch Over:

Switch over means to switch the roles between the Primary DB and the Standby DB, so after the Switch over the Primary DB will act as the standby DB, and the Standby DB will act as the Primary DB.
This approach is usually being used during the DR drills, where the standby database will be opened in READ/WRITE mode, to allow production applications to use it during the drill. Once the drill is done, the DBA can switch back the roles between both sites to get back to the original status, without the need to recreate any of the primary or standby databases.

Before the switch over: Do the following checking
---------------------------

1= Verify that the last sequence# received and the last sequence# already applied on the standby database:

SQL> select max(al.sequence#) "Last Received",max(lh.sequence#) "Last Applied" from v$archived_log al, v$log_history lh;

If the two numbers are the same then the standby has applied all the archives been sent from the primary site.

If not, follow these steps:
------------------------------

2= Verify that the standby is in mount state:

SQL> select open_mode from v$database;

3= Determine if there is any archive gap on the physical standby database:

SQL> select * from v$archive_gap;

4= Verify that the managed recovery process is running:

SQL> select process,status from v$managed_standby;

-If you do not see MRP process this mean the managed recovery not working so start it by:

SQL> recover managed standby database disconnect;


On the Primary DB:
-----------------------
SQL> select switchover_status from v$database;
STATUS should be ‘SESSIONS ACTIVE’ or ‘TO STANDBY’

On the Standby DB:
-----------------------
SQL> select switchover_status from v$database;
STATUS should be ‘SESSIONS ACTIVE’ or ‘TO PRIMARY’


Switch over Steps:   
=============
1- Apply all pending archives on the Standby DB:
-   --------------------------------------------------------

>Disconnect all sessions.
>Stop REDO apply:
> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
> For RAC:
> Stop all instance except single instance.
> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE; --Do not wait for the network.

2-  On the Primary DB:
-    ------------------------
> Stop all RAC instance except one instance:
> Disconnect all sessions...

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;

3-  On the Standby DB:
-    ------------------------
=> Ensure that all archives has been applied.

=> Ensure there is no connected sessions on the Standby DB.....

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

4-  On the old Primary DB: (New Standby)
-    ----------------------------
Disable all archive processes that send the archives to the old standby:

SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

* Check if any of the other archive dest parameters is enabled e.g. log_archive_dest_state_3 ,4 ,5 ... 

5-  On the old Standby DB: (New Primary)
-    -----------------------------
Enable the log_archive_dest_2 to transfer the archives to the old primary (new standby):

SQL> Alter system set log_archive_dest_2='service="PRIMARY" db_unique_name=PRIMARY compression=enable' scope=both;

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

SQL> Select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;

DATABASE_ROLE OPEN_MODE  PROTECTION_MODE

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

PRIMARY MOUNTED    MAXIMUM PERFORMANCE

SQL> ALTER DATABASE OPEN;

SQL> Alter system set standby_file_management='AUTO';

6-  On the old Primary DB: (New Standby)

-    ----------------------------
Restart the database in mount mode to act as a standby DB and start to apply the shipped archives from the old standby (new primary).

SQL> SHUTDOWN IMMEDIATE;
SQL> startup NOMOUNT;
SQL> alter database MOUNT standby database;
SQL> recover managed standby database disconnect;


7-  On the old Standby DB: (New Primary)
-    -----------------------------

- If it's a RAC setup, start up all instances.
- Modify the connection string inside application config files to point to the old standby DB (new primary)


Switch Back:  From New Primary to the new DR
=========
On old DR:
-----------
SQL> ALTER SYSTEM SWITCH LOGFILE;

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

On old Primary: 
---------------
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

On old DR: Disbale sending archives to old Primary
-----------
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

On old Primary: 
---------------
SQL> ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH;
SQL> Select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;

DATABASE_ROLE OPEN_MODE  PROTECTION_MODE
---------------- ---------- --------------------
PRIMARY MOUNTED    MAXIMUM PERFORMANCE

SQL> ALTER DATABASE OPEN;

On DR:
-------
SQL>SHUTDOWN IMMEDIATE;
SQL>startup NOMOUNT;
SQL>alter database MOUNT standby database;
SQL>recover managed standby database disconnect;
SQL>Select DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE from v$database;


Fail Over:

The FAIL OVER approach is used in the real disaster scenarios where the primary site is not exist/accessible anymore, because of flood, earthquake, war, power failure, or whatever the catastrophe caused in the primary site.

All the failover steps will be done on the DR site.

Normal Fail over:
============
On the Standby DB:
------------------------
Terminate the managed recovery mode:
-----------------------------------------------
SQL>recover managed standby database nodelay;

SQL>alter database recover managed standby database finish;

Open the Standby database in READ/WRITE mode to act as a primary DB:
-------------------------------------------------------------------------------------------
SQL>alter database commit to switchover to primary;

SQL>alter database open;


Forced Fail over:
============
SQL>alter database recover managed standby database finish force;

SQL>alter database activate standby database [skip standby logfile];

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database open resetlogs;