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:

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.

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:

• Production database is shutdown normal or shutdown immediate.
• There is no loss of any archive logs that haven't been (yet) applied to the standby database.
• All archive logs will be applied to the standby database.
• Standby database is shutdown normal or shutdown immediate.
• Source database's online redo logs are available and intact.
• Target database is still intact (datafiles, control files are present) and has the same resetlogs version as the source database.
• Loss of unrecoverable transactions (e.g. direct loader unrecoverable) is acceptable.
• All steps have been validated and tested in a test environment first.
• Experienced DBAs with in-depth knowledge of database and standby recovery are managing the environment.

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.