Data Guard Switchover/Failover Scenarios

Switchovers Involving a Physical Standby Database:

Step 1. On primary database check the status:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS;
-----------------
TO STANDBY
1 row selected

It should give: TO STANDBY or SESSION ACTIVE

Step 2. Initiate the switchover on the primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
(If Switchover_status is to_standby)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
(If Switchover_status is session active)

Step 3. Shut down the former primary instance, and restart and mount the database:

SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP MOUNT;
At this point in the switchover process, both databases are configured as standby databases

On the target physical standby database:

Step 4.
On standby database check the status:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

It should give:
TO PRIMARY or SESSION ACTIVE

Step 5.
Switch the target physical standby database role to the primary role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
(If Switchover_status is to_primary)

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
(If Switchover_status is session active)

Step 6.
If the standby database has never been opened before in readonly mode then open the database:
SQL> Alter database open; (10gR2)

Or else: (10gR1)
SQL> Shutdown immediate;
SQL> Startup;

Step7. If necessary, restart redo apply service on standby database( if you need details check How to start or stop redo apply heading)

Step 8. Perform a log switch on primary database

SQl> Alter system switch logfile;


Failovers Involving a Physical Standby Database:


Step 1:
Identify and resolve any gaps in the archived redo log files.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 90 92

In this example the gap compressed in the seq# 90,91 and 92.

Copy all the missing archived redo log files from primary and register it to the standby.

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

- Repeate the step until all gaps are resolved

Step 2
Copy any other missing archived redo log files and register it to standby database.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST
---------- ----------
1 100

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

After all available archived redo log files have been registered, query the
V$ARCHIVE_GAP to verify no additional gaps were introduced

Step 3
Initiate a failover on the target physical standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Step4
Convert the physical standby database to the primary role.

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 5
If the standby database has never been opened before in readonly mode then open the database:
SQL> Alter database open;
Or else:
SQL> Shutdown immediate;
SQL> Startup;