DBA Tips Archive for Oracle

  


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC

Contents

Introduction

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.

Activating the Standby Database - (Immediate Method)

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:

  1. If the standby database is in managed recovery mode, it will need to be cancelled and the instance shutdown using the IMMEDIATE option:


    SQL> recover managed standby database cancel; Media recovery complete. SQL> shutdown immediate

  2. Startup the instance and then MOUNT the DB in standby mode:


    SQL> connect / as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 252777660 bytes Fixed Size 451772 bytes Variable Size 218103808 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes SQL> alter database mount standby database; Database altered.

  3. 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.

  4. Apply the primary database's archived logs by recovering the standby database:


    SQL> recover standby database until cancel;

  5. 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:


    SQL> alter database activate standby database; Database altered.

    This process can take several minutes to complete, depending on the speed of the server.

  6. 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:


    SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. -------------------------------------- SQL> startup open ORACLE instance started. Total System Global Area 252777660 bytes Fixed Size 451772 bytes Variable Size 218103808 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened.

    Note that you cannot copy online redo logs from the primary to the standby in a failover scenario.

  7. 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 standby bit in the controlfile is now set, so you can never go back - the standby is now your primary database. Since the redo log sequence was reset when the standby was opened, it is a good idea to take a full backup at this point.

Activating the Standby Database - (Graceful Switchover Method)

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.

 

A senior DBA or system administrator with a thorough understanding of Oracle standby database implementation and Oracle recovery mechanisms should only attempt this option on a production 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:

Graceful Switchover steps

  1. Discontinue client logons and complete the last set of transactions on the primary database. All clients should be logged out.

  2. Shutdown the primary database:


    SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.

  3. Open the primary database in RESTRICTED mode:


    SQL> startup restrict ORACLE instance started. Total System Global Area 252777660 bytes Fixed Size 451772 bytes Variable Size 218103808 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened.

  4. Archive the current log on the primary database:


    SQL> alter system archive log current; System altered.

  5. From the primary database, create a "create controlfile script" and send it to standby database:


    SQL> alter database backup controlfile to trace noresetlogs; Database altered.

    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:


    % cd ~oracle/admin/ORA817/udump % mv ora920_ora_16864.trc create_controlfile.sql % rcp create_controlfile.sql linux4:

  6. Shutdown the primary database using the IMMEDIATE or NORMAL option.


    SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.

     

    If the primary site crashed and is not accessible, these steps will still work if the online redo logs are still intact and accessible.

  7. 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.


    SQL> recover managed standby database cancel; Media recovery complete.

  8. 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:


    SQL> shutdown immediate ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or directory

  9. 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)


    % rcp /u03/app/oradata/ORA817/redo_g0*.log linux4:/u03/app/oradata/ORA817/ % rcp /u04/app/oradata/ORA817/redo_g0*.log linux4:/u04/app/oradata/ORA817/ % rcp /u05/app/oradata/ORA817/redo_g0*.log linux4:/u05/app/oradata/ORA817/

  10. From the standby database, drop all binary control files. (i.e. /u03, /u04 and /u05)


    % rm /u03/app/oradata/ORA817/control01.ctl % rm /u04/app/oradata/ORA817/control02.ctl % rm /u05/app/oradata/ORA817/control03.ctl

  11. 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.

  12. Execute the create control file script on the standby database:


    SQL> connect / as sysdba Connected to an idle instance. SQL> @create_controlfile.sql ORACLE instance started. Total System Global Area 252777660 bytes Fixed Size 451772 bytes Variable Size 218103808 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Control file created.

  13. Perform Database Recovery on the standby database:


    SQL> recover database Media recovery complete.

    Note that the recover command only recovers online data files.

  14. Archive all logs need archiving and perform a log switch.


    SQL> alter system archive log all; System altered.

  15. Startup standby database as the new production (primary) database:


    SQL> alter database open; Database altered.

  16. Create any tempfiles that may have been part of any TEMP tablespace:


    SQL> alter tablespace temp add tempfile 2 '/u06/app/oradata/ORA817/temp01.dbf' size 524288000 3 reuse autoextend on next 524288000 maxsize 1500M; Tablespace altered.

  17. 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.

About the Author

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-2017 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 jhunter@idevelopment.info.

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: 54475