Performing Planned Switchover/Switchback Operations with Oracle Dataguard

What is Switchover:

A switchover is a role reversal between the primary database and one of its standby databases. A

Switchover operation guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the primary role. The transition occurs without having to recreate either database.

Prerequisites:

1. Verify that there is network connectivity between the primary and standby locations.

2. Each location in the Data Guard configuration should have connectivity through Oracle Net to the primary database and to all associated standby databases.

3. Verify that there are no active users connected to the databases.

4. For switchover operations involving a physical standby database, the primary database instance is open and the standby database instance is mounted.

5. The standby database that you plan to transition to the primary role must be mounted before you begin the switchover operation. Ideally, the physical standby database will also be actively recovering archived redo logs when the database roles are switched.

6. Remove any redo data application delay in effect on the standby database.

7. Ensure the Primary Database and Standby Database is in sync. after issuing number of log switches (ideally the number of log switches should be equal to the number of Redo Log Groups present in your Primary instance) on Primary instance.

Switchover Operation:

1. Verify that it is possible to perform a switchover operation. On the primary query the switchover_status

column of v$database to verify that switchover to standby is possible.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

In order to perform a switchover all sessions to the database need to be disconnected. In version 901 this

was a manual process. In version 9.2.0 this process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command.

If SWITCHOVER_STATUS returns SESSIONS ACTIVE then you should either disconnect all sessions

manually or when performing step 2 you should append the “with session shutdown” clause. For example:

SQL> alter database commit to switchover to standby with session shutdown;

2. Convert the primary database to the new standby:

SQL> alter database commit to switchover to standby with session shutdown;
Database altered.

3. Shutdown the former primary and mount as a standby database:

SQL> shutdown immediate
ORA01507:database not mounted

ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 85020944 bytes

Fixed Size 454928 bytes

Variable Size 71303168 bytes

Database Buffers 12582912 bytes

Redo Buffers 679936 bytes

SQL> alter database mount standby database;
Database altered.

4. Defer the remote archive destination on the old primary:

SQL> alter system set log_archive_dest_state_2=defer scope=both;

5. Verify that the physical standby can be converted to the new primary:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

SWITCHOVER PENDING

Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown

clause to the command in step 6.

6. Convert the physical standby to the new primary:

SQL> alter database commit to switchover to primary with session shutdown;
Database altered.

If you are on version 9.0.1 then you should first cancel managed recovery prior to issuing the above

command. If you are on 9.2.0 and have started managed recovery with the “through all switchover” clause then you should also cancel managed recovery before issuing the above command.

7. Shutdown and startup the new primary:

SQL> shutdown immediate
ORA01507: database not mounted

ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 85020944 bytes

Fixed Size 454928 bytes

Variable Size 71303168 bytes

Database Buffers 12582912 bytes

Redo Buffers 679936 bytes

Database mounted.

Database opened.

8. Enable remote archiving on the new primary to the new standby:

SQL> alter system set log_archive_dest_state_2=enable scope=both;

9. Start managed recover on the new standby database:

SQL> alter database recover managed standby database disconnect from session;
Database altered