Performing a Role Transition Using Switchover

---

First, let's define a role transition so that we are all on the same page. A database can operate in one of two modes in a Data Guard configuration: primary or standby. When we change the role of either a primary or standby, we call this a role transition. We have two methods that we can use to change the roles: switchover and failover. Which one you use is a very important decision. Choosing the wrong one can result in extra work on the part of the DBA to get the configuration back in sync, although a lot of that work has been eliminated with the advent of Flashback Database, which we will discuss in the section 'Using Flashback After a Failover.'

A switchover allows a primary and standby to reverse roles without any data loss and without any need to re-create the previous primary. In contrast, a failover implies data loss and can result in the need for the old primary to be re-created. Switchovers are normally performed for planned maintenance. For example, if the primary host needed to replace a faulty CPU that required downtime, we could perform a switchover and have users automatically redirected to the new primary. The impact to the user base could be greatly reduced, thus increasing our availability.

A switchover can be performed using either a physical or logical standby. However, there are some issues you should be aware of. If you have a configuration with a primary database, a physical standby, and a logical standby, and you perform a switchover to the logical standby, your physical standby will no longer be a part of the configuration and must be re-created. In the same scenario, if you perform a switchover to the physical standby, the logical standby remains in the configuration and does not need to be re-created. For this reason it can be stated that a physical standby is a better option for a switchover candidate than a logical standby when multiple standby types exist in the configuration.

The secret to successfully performing a switchover is proper planning and testing. Following is a list of items that should be considered prior to performing a switchover:

• First and most important, verify that the initialization parameter for both the primary and the standby support both roles.  Pay special attention to the VALID_FOR attributes to the LOG_ARCHIVE_DEST_n parameter, as this will play greatly into the switchover.

• Verify that the primary and standby host each have TNS aliases that point to one another and that those aliases function correctly. Also, verify that those functioning aliases are the ones used in the LOG_ARCHIVE_DEST_n parameters.

• For a fast and efficient switchover, disconnect all user connections. If that is not feasible, restrict the user activity as much as possible. It is possible to failover user connections if they have connected via an OCI application and the proper transparent application failover setup has been performed.

• Verify that both the primary and standby temporary tablespaces are populated with tempfiles.

• Have the standby, either physical or logical, applying changes from the primary. Verify that the application of redo is current with the primary. Using the real-time apply method will speed up the switchover.

• If the primary is a RAC database, you must shut down all instances but one.

HA Workshop: Performing Switchover with a Physical Standby

Workshop Notes

Once you have reviewed the above recommendations, you are ready to perform the switchover. We highly recommend testing switchover in your test environment prior to attempting it on your production system. This testing will root out any small configuration errors and make the production event smooth and painless.

Step 1.  On the primary database, query the V$DATABASE view to verify that the SWITCHOVER_STATUS column indicates that a switchover is possible.

select switchover_status from v$database;

If SWITCHOVER_STATUS returns a value of TO_STANDBY, everything is good. If the query returns SESSIONS ACTIVE, you should perform the SWITCHOVER command with the SESSION SHUTDOWN clause.

Step 2.  Convert the primary database into a physical standby.

alter database commit to switchover to physical standby;

If the SWITCHOVER_STATUS column in Step 1 returned SESSIONS ACTIVE, issue the following command:

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

Step 3.  Shut down and restart the old primary as a new standby.

shutdown immediate; startup mount;

Congratulations, you now have two standbys.

Step 4.  When we converted the primary to a standby, we generated a marker in the redo stream and sent that marker to the standby. That marker states that no more redo has been generated. As soon as the standby receives and recovers that marker, it is eligible to become a primary database. Query the SWITCHOVER_STATUS column of V$DATABASE on the standby to ensure that the marker has been recovered and it is ready for the switchover to primary.

select switchover_status from v$database;

If SWITCHOVER_STATUS returns TO_PRIMARY, the marker has been recovered and you can proceed with the SWITCHOVER TO PRIMARY command. If the status is SESSIONS ACTIVE, you should either disconnect active sessions or issue the SWITCHOVER command with the SESSION SHUTDOWN clause. If the status states NOT ALLOWED, the marker has not been received and recovered by the standby, and switchover cannot proceed.

Step 5.  Convert the standby to a primary database.

alter database commit to switchover to primary;

Or, if the SWITCHOVER_STATUS returned SESSIONS ACTIVE:

alter database commit to switchover to primary with session shutdown;

Step 6.  Shut down and restart the new primary database.

shutdown immediate; startup;

At this point, the switchover process is complete. If you have configured the VALID_FOR attribute to the LOG_ARCHIVE_DEST_n parameter, your new primary is already configured to send redo to the new standby. If you so desire, you can start managed recovery on the new primary to have it begin applying changes. If you performed the switchover to do maintenance on the primary host, you can shut down the new standby and perform the required maintenance. Before shutting down the standby, you should consider the protection mode you are in on your new primary and make sure that the conditions for that protection mode are satisfied.

HA Workshop: Performing Switchover with Logical Standby

Workshop Notes

After validating the requirements, we are ready to switch over to our logical standby. As we stated earlier, we highly recommend testing the switchover in a test environment prior to performing in production to root out any small configuration errors.

Step 1.  On the primary database, query the SWITCHOVER_STATUS column of the V$DATABASE view to see if a switchover is allowed.

select switchover_status from v$database;

If the SWITCHOVER_STATUS column states TO STANDBY, TO LOGICAL STANDBY, or SESSIONS ACTIVE, your configuration is ready to perform the switchover.

Step 2.  New to Oracle Database 10g is the ability to prepare the primary database and logical standby for a switchover, thus reducing the time to complete the switchover. On the primary, issue the command to prepare the primary for the conversion to a logical standby.

alter database prepare to switchover to logical standby;

Step 3.  On the logical standby, issue the command to prepare it to become a primary database. This command instructs the logical standby to begin transmitting the LogMiner dictionary to the current primary that will soon be the new standby. Depending on the size of the LogMiner dictionary, this command could take a few minutes to complete.

alter database prepare to switchover to primary;

Step 4.  At this point, we need to query the SWITCHOVER_STATUS column of V$DATABASE on the primary to see if the receipt of the LogMiner dictionary from the standby has completed. Once the status states TO LOGICAL STANDBY, we are ready to proceed.

Step 5.  On the primary database, issue the statement to convert the primary into a logical standby.

alter database commit to switchover to logical standby;

This command will wait for all current transactions to complete and will stop any new transaction from occurring. If the statement is taking some time to complete, check V$TRANSACTION and end any long-running transactions.

Step 6.  On the logical standby query, check the SWITCHOVER_STATUS column of V$DATABASE to see if the logical standby has recovered all of the information from the primary, including the marker that indicates that the switchover is occurring. Once the SWITCHOVER_STATUS column states TO PRIMARY, issue the statement to convert the logical standby into a primary database.

alter database commit to switchover to primary;

There is no need to bounce either the new primary or new logical standby database. Optionally, you can start the SQL Apply engine on the new logical standby.

---