DBA Tips Archive for Oracle
No Title[an error occurred while processing this directive]
by Michael New, MichaelNew@earthlink.net, Gradation LLC
This article provides notes and instructions on how to activate a standby database to become the primary database in a standby database configuration. Note that this article was written for release Oracle7 through 8i. For instructions on how to activate a standby database in Oracle9i, please refer to the "Role Transitions" section in the appropriate article for your Data Guard configuration:
Data Guard Configuration Example - (Oracle 9i, Physical, Maximum Performance Mode)
The Standby Database feature of Oracle (Oracle7 and higher) allows the DBA to maintain a duplicate, or standby, copy of a database at a remote site to provide continued primary database availability in the event of failure. The standby database is created with a special copy of the control file from the primary database. The standby database is kept in close synchronization with the primary database by applying the primary database's archived log files to the standby database. It is therefore necessary to operate the primary database in Archivelog Mode to avail the benefit of a standby database.
A standby database is generally used where high availability of data is required. A standby database is one way of quickly providing access to data if the primary database fails and recovery will take longer than the desired time.
In the event of a disaster on the primary machine, you need to make the standby database active as soon as possible. In this example, it is assumed that the primary database server is not available and that the standby database needs to be activated as the primary database immediately. The standby database will need to be activated in its current state - no other redo from the primary database will be applied as it is assumed to be down The steps involved are:
If the standby database is in managed recovery mode, it will need to be cancelled and the instance shutdown using the IMMEDIATE option:
Startup the instance and then MOUNT the DB in standby mode:
If possible, archive the current redo logs on the primary database and transfer them (along with any remaining archived redo logs) to the standby machine. As I said in the begining of this article, it is assumed that the primary database and host is not available, so I will not go into the details of trying to recover any files on the primary host.
Apply the primary database's archived logs by recovering the standby database:
Cancel the recovery and activate the standby database. A CANCEL will need to be performed as this is Incomplete Recovery. In issuing the ALTER DATABASE ACTIVATE STANDBY DATABASE command, the standby bit in the control file is reset. This process takes the database back to nomount mode:
This process can take several minutes to complete, depending on the speed of the server.
Shutdown and restart the new primary database. This step ensures that all file headers are reset and clears all buffers. Remember that in the previous step when activating the standby database, that the command puts the database back into nomount mode:
Note that you cannot copy online redo logs from the primary to the standby in a failover scenario.
Shutdown the standby instance and backup if possible, then open the database for normal use. Users can now connect to the "new" primary database.
The following section will go into detail about how to build a graceful switchover when activating the standby database. This method is an alternative to the one discussed above: "Activating the Standby Database - (Immediate Method)". The major opposition to the "Activating the Standby Database - (Immediate Method)" is that it performs a "resetlogs" operation implicitly when opening the database.
The main advantage of a graceful switchover is that it avoids the resetlogs operation. This option is implicitly done by the ALTER DATABASE ACTIVATE STANDBY DATABASE command. By avoiding the resetlogs operation, the source database can resume its role as the standby database almost immediately with no data loss. Another feature to a graceful switchover is that it does not invalidate previous backups.
The prerequisites of initiating graceful switchovers are the following:
Discontinue client logons and complete the last set of transactions on the primary database. All clients should be logged out.
Shutdown the primary database:
Open the primary database in RESTRICTED mode:
Archive the current log on the primary database:
From the primary database, create a "create controlfile script" and send it to standby database:
The trace file created above contains the script which may need to be modified with the appropriate datafile and log file path names. The new "CREATE CONTROLFILE" script will then need to be shipped to the standby site.
Rename and transfer the "create controlfile script" to the standby server:
Shutdown the primary database using the IMMEDIATE or NORMAL option.
If your are NOT using managed recover mode, manually apply the last set of archived redo logs to the standby database and cancel any recovery.
If the standby database instance is still running, shut it down using the IMMEDIATE option. In my configuration, I have my standby database instance shutdown whenever canceling managed recovery mode:
Copy the online redo logs from the primary database to the standby machine. Make sure to place all online redos in their proper location. (i.e. /u03, /u04 and /u05)
From the standby database, drop all binary control files. (i.e. /u03, /u04 and /u05)
In the control file create script you copied from the primary database server to the standby database server, comment out the "Recover Database" and "Alter Database Open" commands located at the bottom of the script. Remember that in this article, we named the "create controlfile script" create_controlfile.sql.
Execute the create control file script on the standby database:
Perform Database Recovery on the standby database:
Note that the recover command only recovers online data files.
Archive all logs need archiving and perform a log switch.
Startup standby database as the new production (primary) database:
Create any tempfiles that may have been part of any TEMP tablespace:
Shutdown the new primary database instance and backup if possible, then open the new primary database for normal use. Users can now connect to the "new" primary database.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Wednesday, 28-Dec-2011 13:48:13 EST
Page Count: 54946