Switchover Steps

Once the standby database is setup using Data Guard and is configured properly, we should test switchover and failover scenarios to be better prepared for a real life disaster situation.

A switchover allows the primary database to switch roles with its standby database. There is no data loss during a switchover. We can switch back to the original Primary database later by performing another switchover. A switchover is performed on a database when the need arises to upgrade or change configuration settings on the Primary database. The Standby database can temporarily be used as the Primary database for zero downtime while the actual Primary database is being upgraded or changed.

Switchover Configuration:
--------------------------
Database Role---------Service-----
Primary-------------ROCKPRIM
Standby-------------ROCKSTD

Before Switchover:
--------------------------

1. Verify the primary database instance is open and the standby database instance is mounted.
2. Verify there are no active users connected to the databases, if there are active sessions then kill active sessions.
3. Make sure the last redo data transmitted from the Primary database was applied on the standby database. Issue the following commands on Primary database and Standby database to find out:

select sequence#, applied from v$archvied_log; or select max(sequence#)
from v$archived_log where applied = ‘YES’;

4. Make sure that log_archive_dest_2 entry is specified in both databases.

Switchover Steps:
------------------
1. Initiate the switchover on the primary database ROCKPRIM:

SQL> conn sys/****@ROCKPRIM as sysdba
SQL> alter database commit to switchover to physical standby with session shutdown;

2. After step 1 finishes, Switch the original physical standby database ROCKSTD to primary role:

SQL> conn sys/****@ROCKSTD as sysdba
SQL> alter database commit to switchover to primary;

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

3. Immediately after issuing command in step 2. Shut down and restart the former primary instance ROCKPRIM:

SQL> conn sys/****@ROCKPRIM as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> select database_role from v$database;

DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY


* Connect at new primary database ROCKSTD, and perform a SWITCH LOGFILE to start sending redo data to the standby database ROCK.