Data Guard Switchover to a Physical Standby

A switchover can be used to reduce downtime for planned outages such as patching to the database or host OS and hardware upgrades. In a switchover the primary database is transitioned to the standby role and the standby database is transitioned to the primary role.

This document will detail the steps to perform a manual switchover.

Verify there is no log file gap between the primary and the standby database.

You can determine the status by querying V$ARCHIVE_DEST_STATUS.

SQL> select status, gap_status
  2  from v$archive_dest_status
  3  where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

SQL>

It is important that both the STATUS is VALID and that GAP_STATUS has the value NO GAP.

Verify that the standby database has temporary files that match the primary.

Primary

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oradata/proddb/temp01.dbf

SQL>

Standby

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/standby/temp01.dbf

SQL> 

Verify there is no delay in effect for applying redo on standby. If there is a delay remove it.

You can determine if there is a delay in applying archive logs by looking at the DELAY_MINS column of V$ARCHIVE_DEST.

SQL> select delay_mins from v$archive_dest where dest_id = 2; 

DELAY_MINS
----------
         0

SQL>

If there is a delay you can use the following to disable the delay for the physical standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

Verify that the primary can be switched to the standby role.

The SWITCHOVER_STATUS of V$DATABASE indicates whether switchover is possible at this time.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL>

The SWITCHOVER_STATUS should have the value of TO STANDBY or SESSIONS ACTIVE. If not then redo transport is not functioning properly and you will not be able to perform the switchover.

If you received SESSIONS ACTIVE in the prior step you might have connections that may prevent the switchover. Verify the active connections by querying V$SESSION.

SQL> select sid, process, program
  2  from v$session where type = 'USER';

       SID PROCESS                  PROGRAM
---------- ------------------------ ------------------------------------------------
        42 3536                     emagent@prod.localdomain (TNS V1-V3)
        47 3536                     emagent@prod.localdomain (TNS V1-V3)
        53 13544                    sqlplus@prod.localdomain (TNS V1-V3)
        61 3536                     emagent@prod.localdomain (TNS V1-V3)
        67 8266                     oracle@dreco.localdomain (TNS V1-V3)

SQL>

You should verify the connected sessions even if the SWITCHOVER_STATUS is TO STANDBY.

Switch the Primary to the Standby Role

The command to switch the primary database to a standby database is ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY. If your database has a SWITCHOVER_STATUS of ACTIVE SESSIONS you will need to append the WITH SESSION SHUTDOWN clause.

Note: the alter session statement is not required for switchover. I typically set the trace identifier prior to performing tasks such as this to make identifying any trace file generated during the process easier.

SQL> alter session set tracefile_identifier='SWITCHOVERTEST_06032010';

Session altered.

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby 
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected


SQL>

If you get this error you have connected sessions that need to be shutdown. Re-issue the command with the WITH SESSION SHUTDOWN clause.

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

Database altered.

SQL> 

If you still get the error run the query presented earlier to identify the connection sessions. Note: The connected sessions reported in the output of the query against V$SESSION earlier were shutdown with the WITH SESSION SHUTDOWN clause.

Next we need to shutdown and then mount the new standby (former primary).

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             620759112 bytes
Database Buffers          205520896 bytes
Redo Buffers                2433024 bytes
Database mounted.
SQL> 

After mounting the database notice that alert.log states that the database is now a Physical Standby database.

Thu Jun 03 11:12:34 2010
Successful mount of redo thread 1, with mount id 460768413
Physical Standby Database mounted.

At this time we have two standby databases. Next we need to prepare the original standby and convert it to the new primary.

Verify that the standby is ready to be switched to the primary role.

Again we look at SWITCHOVER_STATUS of V$DATABASE. This time we are looking for TO PRIMARY or SESSIONS ACTIVE.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL>

Here we see that there are active sessions on the standby database. A quick check of V$SESSION will show us the active sessions.

SQL> select sid, process, program
  2  from v$session where type = 'USER';

       SID PROCESS                  PROGRAM
---------- ------------------------ ------------------------------------------------
        19 23147                    sqlplus@dreco.localdomain (TNS V1-V3)
        36 18592                    oracle@prod.localdomain (TNS V1-V3)
        42 1234                     OMS

SQL>

As with the switchover to standby, some connected sessions may prevent the switchover to primary. Since we have a SWITCHOVER_STATUS of SESSIONS ACTIVE we will need to apply the WITH SESSION SHUTDOWN clause.

The command to switch the standby database to a primary database is ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY. If your database has a SWITCHOVER_STATUS of ACTIVE SESSIONS you will need to append the WITH SESSION SHUTDOWN clause.

SQL> alter session set tracefile_identifier='SWITCHOVERTEST_STBY_06032010';

Session altered.

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

Database altered.

SQL>

Next we open the new primary database.

SQL> alter database open;

Database altered.

SQL>

If you take a look in the alert.log you see that our former standby database has been mounted as the primary.

Standby became primary SCN: 3363769
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary with session shutdown

Note: Before going on you should check the LOG_ARCHIVE_DEST_n parameters. You may need to set up a LOG_ARCHIVE_DEST_n parameter to write archive logs to the new primary.

SQL> alter system set log_archive_dest_2='service="proddb" LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="proddb" net_timeout=30 valid_for=(all_logfiles,primary_role)' scope = both;

System altered.

SQL>

Start Redo Apply on the new physical standby database

As the last step we will restart the Redo Apply services on the new standby. Below is an exampled of enabling real-time apply. If for some reason you do not want to use real-time apply remove the USING CURRENT LOGFILE from the command.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> 

Switch the log files a couple of times on the new primary and check the status.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>/

System altered.

SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

SQL>

The switchover is complete.

Keep in mind that if your primary and standby database are being monitored through Enterprise Manager you will need to reflect these changes manually. Enterprise Manager will not know about the switchover or that the primary is now the standby and old standby is now the new primary. It will just report the blocked connection to the old primary.