Graceful Failover and Failback Procedures in Non-Data Guard Environments

by Brian Keating
Database Specialists, Inc.

About Database Specialists, Inc.
Database Specialists, Inc. provides remote DBA services and onsite database support for your mission critical Oracle systems. Since 1995, we have been providing Oracle database consulting in Solaris, HP-UX, Linux, AIX, and Windows environments. We are DBAs, speakers, educators, and authors. Our team is continually recognized by Oracle, at national conferences and by leading trade publications. Learn more about our remote DBA, database tuning, and consulting services. Or, call us at 415-344-0500 or 888-648-0500.

Introduction

This paper describes the procedures to perform “graceful” failovers and failbacks of hot standby databases in Oracle environments that are not using Data Guard. A “graceful” failover is one that does not require databases to be opened with the RESETLOGS option—and as a result, graceful failbacks do not require the primary database to be rebuilt. (That is, they do not require the standby database’s datafiles to be copied to the primary server.) Graceful failovers and failbacks are also known as “switchovers” and “switchbacks”.

In order to avoid confusion, the original primary database will be referred to as database “abc”, and the original standby database will be referred to as database “xyz”. In other words, before any of these procedures are run, the primary database is “abc” and the standby database is “xyz”.

The procedures in this document are generally based on the information contained in Metalink document 90817.1.

Graceful Failover Procedure

  1. Shut down database abc and database xyz with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.

  2. Copy the unsent archived redo logs, all of the online redo logs, and all of the control files from abc’s host to xyz’s host. Make sure that you copy all of these files into their pre-existing locations on xyz’s host. For example, if a control file called “control01.ctl” exists in the /u01 directory on abc’s host; but it exists in the /u04 directory on xyz’s host, then copy that file from /u01 on abc’s host to /u04 on xyz’s host.

  3. Start up database xyz with STARTUP MOUNT.

  4. If necessary, rename the datafiles and online redo logs in database xyz to reflect those files’ locations on xyz’s host with ALTER DATABASE RENAME FILE commands. This will be necessary if any datafiles or online redo logs have different filenames or locations on xyz’s host than on abc’s host.

  5. In database xyz, execute the command RECOVER DATABASE. If any archived redo logs are needed for recovery, then you will be prompted for the next log. In that case, type in AUTO at that prompt. When the instance finishes applying all of the changes contained in the archived redo logs and the online redo logs, the message “Media recovery complete” will be displayed.

  6. Open database xyz by executing the ALTER DATABASE OPEN; command. The xyz database is now open in read-write mode; so at this point the xyz database is ready to be used as the new primary database.

  7. In database xyz execute the following command, substituting an appropriate filename with full path:

            ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
  8. Copy the standby controlfile that you just created to abc’s host. Then, on that host, overwrite all of the existing controlfiles for the abc database with this standby controlfile. In other words, copy the standby controlfile into each of the existing controlfile’s directories; and then rename the standby controlfiles to match the name of the existing controlfiles.

  9. Start up the abc database with the following commands:

            STARTUP NOMOUNT
            ALTER DATABASE MOUNT STANDBY DATABASE;
  10. If necessary, rename the datafiles and online redo logs in database abc to reflect those files’ locations on abc’s host with ALTER DATABASE RENAME FILE commands. This will be necessary if any datafiles or online redo logs have different filenames or locations on abc’s host than on xyz’s host. At this point, the abc database is configured as the new standby database, and is ready to apply archived redo logs from the xyz database.

Graceful Failback Procedure

(These steps are basically identical to the failover procedure above—the only difference is that you are reversing the roles of the abc and xyz databases.)

  1. Shut down database abc and database xyz with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.

  2. Copy the unsent archived redo logs, all of the online redo logs, and all of the control files from xyz’s host to abc’s host. Make sure that you copy all of these files into their pre-existing locations on abc’s host. For example, if a control file called “control01.ctl” exists in the /u01 directory on abc’s host; but it exists in the /u04 directory on xyz’s host, then copy that file from /u04 on xyz’s host to /u01 on abc’s host

  3. Start up database abc with STARTUP MOUNT.

  4. If necessary, rename the datafiles and online redo logs in database abc to reflect those files’ locations on abc’s host with ALTER DATABASE RENAME FILE commands. This will be necessary if any datafiles or online redo logs have different filenames or locations on abc’s host than on xyz’s host.

  5. In database abc, execute the command RECOVER DATABASE. If any archived redo logs are needed for recovery, then you will be prompted for the next log. In that case, type in AUTO at that prompt. When the instance finishes applying all of the changes contained in the archived redo logs and the online redo logs, the message “Media recovery complete” will be displayed.

  6. Open database abc by executing the ALTER DATABASE OPEN; command. The abc database is now open in read-write mode; so at this point the abc database is ready to be used as the new primary database.

  7. In database abc execute the following command, substituting an appropriate filename with full path:

            ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
  8. Copy the standby controlfile that you just created to xyz’s host. Then, on that host, overwrite all of the existing controlfiles for the xyz database with this standby controlfile. In other words, copy the standby controlfile into each of the existing controlfile’s directories; and then rename the standby controlfiles to match the name of the existing controlfiles.

  9. Start up the xyz database with the following commands:

            STARTUP NOMOUNT
            ALTER DATABASE MOUNT STANDBY DATABASE;
  10. If necessary, rename the datafiles and online redo logs in database xyz to reflect those files’ locations on xyz’s host with ALTER DATABASE RENAME FILE commands. This will be necessary if any datafiles or online redo logs have different filenames or locations on xyz’s host than on abc’s host. At this point, the xyz database is configured as the new standby database, and is ready to apply archived redo logs from the abc database.

Requirements and Notes

This section describes the requirements that must be met in order to use these procedures; along with some notes.

First, the primary database’s host must be accessible. In order to use these procedures, the control files and online redo logs from the primary database need to be copied from the primary database’s host to the standby database’s host. Therefore, these procedures can not be used if the primary database’s host is inaccessible—because that would prevent those files from being copied to the standby server. (It may be possible to configure a geographic disk mirroring utility between the primary and standby hosts in order to be able to eliminate this requirement; but I have not seen any successful implementation of geographic disk mirroring in Oracle environments.)

Next, the primary and standby databases must be shut down with normal or immediate priority. If the databases (particularly the primary database) are shut down with SHUTDOWN ABORT, then it might not be possible to fully recover the standby database—and that would prevent these procedures from being usable.

These procedures include converting the original primary database into a standby database during graceful failover (after the original standby database has been converted into a primary database). It should be pointed out that it is not required to convert the primary into a standby—it is still possible to fail back to the original primary, even if that database was not converted into a standby. However, if you do not convert the original primary database into a standby, then you must ensure that that database never gets opened until you are performing the failback procedure. This is because the act of opening a primary database generates some redo in that database—and that redo will cause the primary database’s datafiles to become permanently out of sync with the redo in the standby database. So, if you open up the original primary database without converting it to a standby, you will not be able to use these procedures; you will have to completely rebuild the primary database from a copy of the standby database’s datafiles.

Thirdly, these procedures specify to copy the control files from the primary database to the standby database. An alternative to doing this is to rebuild the standby’s control files from the output of an ALTER DATABASE BACKUP CONTROLFILE TO TRACE command. Note, however, that if you wish to rebuild the standby’s control files, then you must ensure that the CREATE CONTROLFILE statement contains the NORESETLOGS option. If you change that option to specify RESETLOGS, then you will not be able to open the database with a regular ALTER DATABASE OPEN. Instead, you will be forced to specify RESETLOGS when opening the database, and, of course, the whole purpose of these procedures is to avoid opening the database with RESETLOGS.

Finally, there is an “idiosyncrasy” with these procedures when they are used in conjunction with locally-managed temporary tablespaces. To be specific, if these procedures are used with a database that contains a locally-managed temporary tablespace, and if the tempfiles of that tablespace were not added to the hot standby database before the standby was converted into the new primary database, then that locally managed temporary tablespace will have to be completely dropped and re-created in the new primary database in order to use the tempfiles in question.

The reason for this is that these procedures specify to copy the control files from the primary database to the standby database. Therefore, if you try to add the tempfiles to the standby database’s temporary tablespace after converting it to the new primary, you will receive a “file is already part of database” error message – because the control files (which were copied from the original primary) will already contain the information about those tempfiles. Also, if the new primary instance tries to use any of the tempfiles (such as for a sorting operation) then a “cannot identify/lock data file xxx” error message will be displayed – because the tempfile in question does not actually exist on the new primary’s host. As mentioned above, one solution to this issue is to simply drop and re-create the locally managed temporary tablespace completely, after converting the standby database into a primary database. An alternate solution is to manually ensure that all tempfiles have been added to the standby before converting the standby into the new primary.

About the Author

Brian Keating, OCP, has been an Oracle DBA and Unix system administrator for over nine years. He is currently a consultant with Database Specialists, Inc., a consulting group specializing in business solutions based on Oracle technology. You can contact Brian Keating at bkeating@dbspecialists.com.

Still Looking for Help on this Subject?

Get a Consultation
We would be happy to talk with you about our services and how our senior-level database team might help you. Call Database Specialists at 415-344-0500 or 888-648-0500 or fill out a free consultation request form.
Complimentary Newsletter
If you'd like to receive our complimentary monthly newsletter with database tips and new white paper announcements, sign up for The Specialist.


Copyright © 2005 Database Specialists, Inc. http://www.dbspecialists.com