Previous to Oracle9i, the only method of switching the primary and standby database was a failover operation, which required the recreation of the primary database after the failover operation completed. Often in these cases, this recreated primary database would become the new standby database, and the old standby database would continue in its new role as the primary database. Because the failover required incomplete recovery (due to the use of the activate standby command), there was no going back once you switched over to the standby database. Also, in an environment with just one standby server, there was a risk to the system during the period that a new standby database was being created after a failover occurred.

In Oracle9i data guard, you have the primary database and one or more standby databases. There might be times that you will want to transfer the roles of a standby database and the primary database such that the primary becomes the standby and the standby becomes the primary at times other than an unplanned failure. Oracle supports this type of an operation in Oracle9i, using the role management feature. The actual process of changing the role of a given database is called a switchover operation. With a switchover operation, you do not need to recreate a new database as you likely would with a failover operation, because the data in both the primary and standby databases is not divergent.

Often the switchover method can be used to allow for planned hardware down time and maintenance. Also, switchover can be used to diagnose problems and try to deal with logical corruption of data in the primary database.

Switchover operations require planning, and can occur only if specific conditions are met. For a primary database to be switched over as a standby database, the primary must be either mounted or open. There must already be at least one standby database active in your current configuration. That standby database must either be mounted or in read-only mode, and accessible through the network. When you switch the primary database, it must be in ARCHIVELOG mode, and only one session can be active in the instance.

A standby database can be switched over to become a primary database when it is in either read-only mode or mounted. Before being able to complete the switch over, there can be only one active session in the standby instance. Finally, before initiating the switch over, the standby database must have been in managed recovery mode and the switch of the primary database to a standby configuration must be completed.

To facilitate the switchover, use the ALTER DATABASE COMMIT TO SWITCHOVER command. If you are switching a physical database to a standby database, then use the command alter database commit to switchover to physical standby. If you wish to switch a standby database and make it the primary database, issue the command alter database commit to switchover to physical primary.

Procedure for Performing a Swithover Operation

The steps below outline what commands must be issued to perform a switchover operation. In this examble, boston is initially the primary database and la is initially the standby database. la will become the primary database and boston will become the standby database.

Step 1: End Read or Update Activity on the Primary and Standby Databases.

Exclusive database access is required by the DBA before beginning a switchover operation. Ask users to log off the primary and stnadby databases or query the V$SESSION view to identify users that are connected to the database and close all open sessions except the SQL*Plus session from which you are going to issue the switchover command.

Step 2: Prepare the Primary Database for Switchover

On the primary database, boston, execute the following statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

This statement does the following:

  • Closes the primary database
  • Archives any unarchived log files and applies them to the standby database, la
  • Adds an end-of-redo marker to the header of the last log file being archived
  • Creates a backup of the current control file
  • Converst the current control file into a standby control file

Step 3: Shut Down and Start Up the Former Primary Instance Without Mounting the Database

Execute the following statement on boston:

SQL> SHUTDOWN NORMAL;
SQL> STARTUP NOMOUNT;

Step 4: Mount the Former Primary Database in the Standby Database Role

Execute the followint statement on boston:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Step 5: Prepare the Former Standby Database to Switch to the Primary Database Role

Execute the following statement on la:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

This statement does the following:

  • Makes sure the last log file has been received and applied through the end-of-redo marker
  • Closes the database if it has been opened for read-only transactions
  • Converts the standby control file to the current control file

Step 6: Shut Down the Database

Execute the following statement on la:

SQL> SHUTDOWN;

Step 7: Start Up the Database in the Primary Role

Execute the following statement on la:

SQL> STARTUP;

Step 8: Put the Standby Database in Managed Recovery Mode

Execute the following statement on the standby database, boston, to place it in managed recovery mode:

SQL> ALTER DATABASE RECOVDR MANAGED STANDBY DATABASE;

Step 9: Start Archiving Logs from the Primary Database to the Standby Database

Execute the following statement on the primary database, la:

SQL> ALTER SYSTEM ARCHIVE LOG START;
SQL> ALTER SYSTEM SWITCH LOGFILE;