Oracle8i Backup and Recovery Guide
Release 8.1.5
A67773-01

Library
Product
Contents
Index

Prev Next

16
Managing a Standby Database

This chapter describes how to maintain a standby database. It includes the following topics:

Planning a Standby Database

When developing your backup and recovery strategy, consider whether to maintain a standby database. You can use a standby database to maintain a duplicate copy of your production database.

In the event that all media are destroyed at your production site, the standby database can replace the destroyed or damaged database. For maximum disaster protection, place the datafiles, redo log files, and control files of your primary and standby databases on separate physical media in separate geographical areas.

You can also locate the production and standby databases in the same data center or even on the same machine. This configuration is useful if you use the standby in read-only mode for reporting purposes.

This section contains the following topics:

Standby Database Advantages

A standby database can be a powerful tool for both disaster prevention and reporting. You can:

Standby Database Requirements

Note the following requirements for maintaining a standby database:

Creating a Standby Database

You can create a standby database on the same host as your production database or on a remote host. If you create your standby on the same host, follow the procedure carefully so that you do not overwrite important files.

To create a standby database:

  1. Create a standby init.ora file by copying the production init.ora file. Configure the standby initialization parameters using the considerations described in "Configuring Initialization Parameters".

  2. Start a SQL*Plus session on your primary database and issue a SELECT on V$DATAFILE to obtain a list of datafiles. For example, enter: 

    SELECT name FROM v$datafile;
NAME 
--------------------------------------------------------------------------------
/oracle/dbs/tbs_01.f
/oracle/dbs/tbs_02.f
/oracle/dbs/tbs_03.f
/oracle/dbs/tbs_11.f 
/oracle/dbs/tbs_12.f
/oracle/dbs/tbs_21.f
/oracle/dbs/tbs_22.f 
7 rows selected.
  3. Shut down your primary database cleanly: 

    SHUTDOWN IMMEDIATE
  4. Make a consistent backup of the datafiles from your primary database using an O/S utility.

  5. Open the database: 

    STARTUP
  6. Connect to the production database and create the control file for your standby database. For example, enter the following (where filename is the fully specified pathname): 

    ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
  7. Archive the current online redo logs of the primary database: 

    ALTER SYSTEM ARCHIVE LOG CURRENT;

    This operation ensures consistency among the datafiles, the control file, and the redo log files.

  8. Transfer the standby database control file, archived log files, and backed up datafiles to the standby site using operating system commands or utilities. Use an appropriate method if transferring binary files (see "Maintaining a Standby Database in Recovery Mode"). For example, enter: 

    % cp /oracle/dbs/*.f /standby/oracle/dbs/*.f
  9. If your standby database is on a remote host with the same filesystem as your primary database, you can use the same filenames for the standby database as the primary database. If not, use the filename conversion initialization parameters described in "Converting Filenames for Datafiles and Archived Redo Logs".

See Also: For ALTER DATABASE and ALTER SYSTEM syntax, see Oracle8i SQL Reference.

Choosing the Standby Database Mode

Oracle allows you to perform one of three mutually exclusive operations on your standby database. You can mount the standby database and then:

To protect against disaster, keep your standby database in recovery mode, which means that you cannot query or open it for any purpose other than to initiate disaster recovery. You have the option of placing the database in either manual recovery mode, in which you must continually transfer and apply archived redo logs to the standby database, or managed recovery mode, in which the procedure is automated.

You can also open your standby database in read-only mode. This option allows you to query the database and even store data in temporary tablespaces without affecting the datafiles or redo logs. If you need to return to recovery mode, you can do so at any time.

Note:

Opening a standby database in read-only mode requires you to set the COMPATIBLE parameter to 8.1 or higher.  

Once you activate your standby database, it ceases to become a standby database and functions as a production database. You cannot return it to standby recovery mode unless you re-create it as another standby database.

WARNING:

Activating a standby database resets the online logs of the standby database. Hence, after activation, the logs from your standby database and production database are incompatible.  

Maintaining a Standby Database in Recovery Mode

This section describes how to maintain your standby database in recovery mode, which allows you to restore and recover the database in case of disaster. Depending on whether you want archived logs to be applied manually or automatically, you can choose between manual recovery mode and managed recovery mode. You can also control how Oracle manages obsolete online logfiles and filename conversion.

This section contains the followings topics:

Placing the Standby in Manual Recovery Mode

Once you have started and mounted your database, you can place it in manual recovery mode. To keep your standby database current, you must manually apply archived redo logs from your target database to your standby database. For details about various media recovery options, e.g., recovering a database to a non-current date, see "Performing Incomplete Media Recovery".

To place the standby database in manual recovery mode:

  1. Configure the initialization parameters for the standby site. See "Maintaining a Standby Database in Recovery Mode" and "Configuring Initialization Parameters".

  2. Use SQL*Plus to start the Oracle instance at the standby database. For example, enter: 

    STARTUP NOMOUNT
  3. Mount the standby database: 

    ALTER DATABASE MOUNT STANDBY DATABASE;
  4. Transfer the archived redo logs to the desired location on the standby host. Use an appropriate operating system utility for transferring binary data.

  5. Place the standby database in recovery mode, optionally specifying the FROM 'location' option. If you omit this parameter, Oracle assumes the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST_n (where n is an integer from 1 to 5) or LOG_ARCHIVE_DEST initialization parameter. For example, enter: 

    RECOVER STANDBY DATABASE # uses archiving location for logs specified in init.ora
RECOVER FROM '/logs' STANDBY DATABASE # specifies non-default location

    Note:

    As Oracle generates archived redo logs, continually transfer and apply them to the standby database to keep the standby current.  

Placing the Standby Database in Managed Recovery Mode

When you operate your production database in managed recovery mode, you can automate archiving to either a local or remote host. Oracle keeps the standby database synchronized with the primary database by waiting for archived logs from the primary and then automatically applying them to the standby. This feature eliminates the need for you to interactively provide the recovery process with the filenames of the archived logs.

Figure 16-1 Transmitting and Applying Archived Redo Logs to a Standby Database


When placing the database in managed recovery mode, use the TIMEOUT option of the RECOVER statement to specify an optional timeout interval. In this case, the managed recovery operation waits the specified number of minutes for Oracle to write the requested archived log entry to the standby control file's directory.

If Oracle times out because it cannot find the required next log entry in the standby control file, the system issues an appropriate message and exits managed recovery mode. By default the managed recovery operation waits indefinitely for a requested archived redo log; it terminates only through user intervention, a shutdown, or crash.

Cancel the operation at any time by issuing RECOVER MANAGED STANDBY DATABASE CANCEL with or without the IMMEDIATE option. RECOVER MANAGED STANDBY DATABASE CANCEL waits for the managed recovery operation to finish with the current redo log file before terminating the recovery operation.

If you use the CANCEL statement with the IMMEDIATE option, Oracle stops the managed recovery operation either before reading another block from the redo log file or before opening the next redo log file--whichever comes first.

If Oracle terminates recovery before opening the next redo log, then CANCEL IMMEDIATE is equivalent to CANCEL. If Oracle terminates recovery while processing a log, then CANCEL IMMEDIATE leaves the database in an inconsistent state. Note that Oracle does not allow a database to be opened in an inconsistent state.

To place the standby database in managed recovery mode:

  1. Use SQL*Plus to start the standby database without mounting it, specifying a parameter file if necessary: 

    STARTUP NOMOUNT pfile=initSTANDBY.ora
  2. Mount the database: 

    ALTER DATABASE MOUNT STANDBY DATABASE;
  3. Put the standby database in managed recovery mode: 

    RECOVER MANAGED STANDBY DATABASE

    If you wish to use the optional time-out option, add TIMEOUT integer to the command syntax, as in the following: 

    RECOVER MANAGED STANDBY DATABASE TIMEOUT 60

    Note:

    Do not use FROM with the MANAGED option.  

Transmitting Archived Redo Logs to a Standby Database

For the background archiver processes to archive to a standby location, the following must be true:

Each ARCn process creates a corresponding RFS for each standby destination. For example, if three ARCn processes are archiving to two standby databases, then Oracle establishes six RFS connections.

The STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT parameters in the standby init.ora file determine the filenames for the archived redo logs at the standby site. These filenames are stored in the standby database control file; access them using the V$ARCHIVED_LOG dynamic performance view.

This section contains these topics:

See Also: To learn how to manage archived redo logs, see the chapter on archived redo logs in the Oracle8i Administrator's Guide. For more information about Oracle networking options, see the Net8 Administrator's Guide. For an overview of the ARCn archiver process, see Oracle8i Concepts.

Specifying Archive Destinations in the Primary Parameter File

Specify the number of locations for your primary database archived logs by setting the following initialization parameters:

Parameter   Host   Example  

LOG_ARCHIVE_DEST_n (where n is an integer from 1 to 5)  

Remote or local  

LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/arc/'

LOG_ARCHIVE_DEST_2 = 'SERVICE = standby1'  

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST  

Local only  

LOG_ARCHIVE_DEST = /oracle/arc

LOG_ARCHIVE_DUPLEX_DEST = /bak  

When you maintain a standby database, use the LOG_ARCHIVE_DEST_n parameter to specify from one to five different destinations for archival. Each numerically-suffixed parameter uniquely identifies an individual destination, e.g., LOG_ARCHIVE_DEST_1, LOG_ARCHIVE_DEST_2, etc.

Alternatively, you can use LOG_ARCHIVE_DEST in optional conjunction with LOG_ARCHIVE_DUPLEX_DEST to specify up to two locations. Note that you cannot use LOG_ARCHIVE_DEST in conjunction with LOG_ARCHIVE_DEST_n.

Specify the location using these keywords:

Keyword   Indicates   Example  

LOCATION  

A local filesystem location.  

LOG_ARCHIVE_DEST_1= 'LOCATION=/arc/'  

SERVICE  

Remote archival via Net8 service name.  

LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'  

If you use the LOCATION keyword, specify a valid pathname for your operating system. If you specify SERVICE, Oracle translates the net service name through the tnsnames.ora file to a connect descriptor. The descriptor contains the information necessary for connecting to the remote database. Note that the service name must have an associated database SID, so that Oracle correctly updates the log history of the control file for the standby database.

See Also: For a detailed account of LOG_ARCHIVE_DEST_n and the archiving process, see the chapter on archived redo logs in the Oracle8i Administrator's Guide. For information about STANDBY_ARCHIVE_DEST, SERVICE_NAME, or other related initialization parameters, see the Oracle8i Reference. For information about the tnsnames.ora file or network configuration parameters, see the Net8 Administrator's Guide.

Specifying Mandatory and Optional Destinations

Using the LOG_ARCHIVE_DEST_n parameter, you can specify whether a destination has the attributes OPTIONAL (default) or MANDATORY. For example, you can set the parameter as follows: 

LOG_ARCHIVE_DEST_3 = 'SERVICE=standby1 MANDATORY'

The LOG_ARCHIVE_MIN_SUCCEED_DEST=n parameter uses all MANDATORY destinations plus some number of OPTIONAL non-standby destinations to determine whether LGWR can over-write the online redo log.

See Also: For a detailed account of the OPTIONAL and MANDATORY keywords, see the chapter on archived redo logs in the Oracle8i Administrator's Guide.

Enabling Archive Destination States

The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 5) parameter identifies the status of the specified destination. The destination parameters can have two values: ENABLE and DEFER. ENABLE indicates that Oracle can use the destination, whereas DEFER indicates that it should not.

For example, you can set the parameter as follows: 

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

See Also: For a detailed account of the archive destination states, see the chapter on archived redo logs in the Oracle8i Administrator's Guide. For a description of the LOG_ARCHIVE_DEST_STATE_n parameter, see the Oracle8i Reference.

Re-Archiving to a Failed Destination

Use the REOPEN attribute of the LOG_ARCHIVE_DEST_n parameter to determine whether and when ARCn attempts to re-archive to a failed destination following an error. REOPEN applies to all errors, not just OPEN errors.

REOPEN=n sets the minimum number of seconds before ARCn should try to reopen a failed destination. If you specify REOPEN, it has a default value 300 seconds. If you do not specify REOPEN, it has the value of 0, which is the same as turning off the option. If you do not specify the REOPEN keyword, ARCn will never reopen a destination following an error.

You cannot use REOPEN to specify a limit on the number of attempts to reconnect and transfer archived logs. The REOPEN attempt either succeeds or fails, in which case the REOPEN information is reset.

For example, you can set the parameter as follows to specify a reopen time of 60 seconds: 

LOG_ARCHIVE_DEST_2 = 'SERVICE=standby2 OPTIONAL REOPEN=60'

Note:

Archived logs that are not transmitted while communication is lost must be shipped manually.  

See Also: For a detailed account of how to use the REOPEN option, see the chapter on archived redo logs in the Oracle8i Administrator's Guide.

Specifying Archived Log Filenames for the Standby Database

The standby RFS process uses the STANDBY_ARCHIVE_DEST parameter in the standby's init.ora file to determine the directory location for the archived redo logs. Oracle uses this value in conjunction with LOG_ARCHIVE_FORMAT to generate the archived log filenames on the standby host.

Parameter   Indicates   Example  

STANDBY_ARCHIVE_DEST  

Directory in which to place archived logs.  

STANDBY_ARCHIVE_DEST= /arc_dest  

LOG_ARCHIVE_FORMAT  

Format for archived redo log filename.  

LOG_ARCHIVE_FORMAT = "log%s.arc"  

Oracle stores the fully qualified filenames in the standby control file. Managed recovery uses this information to perform the recovery operation. Access this information via V$ARCHIVED_LOG: 

SQL> SELECT name FROM v$archived_log;
NAME                                                                            
--------------------------------------------------------------------------------
/arc_dest/log771.arc                                       
/arc_dest/log772.arc                                       
/arc_dest/log773.arc                                       
/arc_dest/log774.arc                                       
/arc_dest/log775.arc
Specifying Filenames with the RECOVER Command

With the exception of RECOVER MANAGED STANDBY DATABASE, the RECOVER STANDBY DATABASE commands rely on one of the following to provide the location of the archived files:

If you run the database in managed recovery mode, then you must issue RECOVER STANDBY DATABASE if the following situations occur:

Issuing RECOVER STANDBY DATABASE in these circumstances requires you to use the LOG_ARCHIVE_DEST parameter to locate the necessary archived redo log. For a standby database in managed recovery mode, Oracle recommends setting STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same value. In this way, the various types of recovery operations can access the same set of archived redo logs.

Specifying Archive Destinations: Example

This example assumes the following:

INIT.ORA Settings for PROD1

Following are sample settings for the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 parameters in the primary database PROD1's init.ora file: 

# This example specifies net service name "standby1", makes archiving mandatory, and  
# enables the destination. 

# A REOPEN value of 5 indicates that if the LOG_ARCHIVE_DEST_1 location
# encounters an error during archival of a redo log file, Oracle will remain inactive 
# until the archival of a redo file is about to begin and 5 seconds has elapsed. At that 
# time, Oracle re-attempts the archival to LOG_ARCHIVE_DEST_1. 
  
# If Oracle encounters an error when archiving to a destination, that destination 
# is inactive for the duration of the archival of the current redo log file. 
# The destination may be reactivated (based on the REOPEN attribute) at the start 
# of the archival of another redo log.  

LOG_ARCHIVE_DEST_1 = 'SERVICE=standby1 MANDATORY REOPEN=5' 
LOG_ARCHIVE_DEST_STATE_1 = ENABLE

# Specifies net service name "standby2", makes archiving optional, and specifies that 
# Oracle should re-try archiving after 5 seconds should an error occur. The destination 
# is enabled.

LOG_ARCHIVE_DEST_2 = 'SERVICE=standby2 OPTIONAL REOPEN=5' 
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
TNSNAMES.ORA Settings

Following are settings in the tnsnames.ora file for the standby databases STANDBY1 and STANDBY2 in the above example: 

# The standby1 standby database is on the same node as the primary.
standby1 = (DESCRIPTION= 
             (ADDRESS= 
                (PROTOCOL=ipc) 
                (KEY=stby)) 
             (CONNECT_DATA= 
                (SID=stby1) 
                (SERVER=DEDICATED))) 
 
# The standby2 standby database is on a different node from the primary.
standby2 = (DESCRIPTION= 
             (ADDRESS= 
                (PROTOCOL=tcp) 
                (HOST=remote2) 
                (PORT=1512) 
             )
             (CONNECT_DATA= 
                (SID=stby2) 
                (GLOBAL_NAME=standby2) 
                (SERVER=DEDICATED)))
LISTENER.ORA Settings

Following are the settings in the listener.ora files for the standby databases STANDBY1 and STANDBY2: 

# The listener settings for standby1 on host local
LISTENER = (ADDRESS_LIST=  
   (ADDRESS= 
   (PROTOCOL=ipc) 
   (KEY=stby1)))   
 
SID_LIST_LISTENER = (SID_LIST=  
 (SID_DESC=(SID_NAME=stby1)(ORACLE_HOME=/oracle))  
  
# The listener settings for standy2 on the remote host remote2 
LISTENER = (ADDRESS_LIST=  
 (ADDRESS= 
   (PROTOCOL=tcp) 
   (KEY=stby2) 
   (HOST=remote2) 
   (PORT=1512))) 
 
SID_LIST_LISTENER = (SID_LIST= 
 (SID_DESC=(SID_NAME=stby2)(ORACLE_HOME=/oracle))
INIT.ORA Settings for STANDBY1 and STANDBY2

Following are settings in the init.ora files for the standby databases STANDBY1 and STANDBY2 in the above example. These settings determine the filenames on the standby database for the archived redo logs: 

# The init.ora values for the standby1 database, which is on the same host as the primary
STANDBY_ARCHIVE_DEST = /oracle/standby/arc 
LOG_ARCHIVE_DEST = /oracle/standby/arc 
LOG_ARCHIVE_FORMAT = log%s.arc

# The init.ora values for the standby2 database, which is on host remote2
STANDBY_ARCHIVE_DEST = /oracle/standby/arc
LOG_ARCHIVE_DEST = /oracle/standby/arc  
LOG_ARCHIVE_FORMAT = log%s.arc

Maintaining the Standby Database in Recovery Mode

When running a standby database, be mindful of the various maintenance issues that can arise. If possible, research the solutions to possible problems before placing the standby in recovery mode.

This section contains the following topics:

Converting Filenames for Datafiles and Archived Redo Logs

Set the following initialization parameters so that your standby database converts files from your primary database control file. If your primary and standby databases occupy the same node, these parameters allow you to distinguish the filenames for the standby and primary databases. Note that if you do not set the LOCK_NAME_SPACE parameters differently for same-node systems using OPS, you will receive an ORA-1102 error.

Table 16-1 Filename Conversion
Parameter  Function 

DB_FILE_NAME_CONVERT  

Transforms primary database datafile filenames to standby datafile filenames, e.g., from tbs_* to standbytbs_*.  

LOG_FILE_NAME_CONVERT  

Transforms primary database redo log filenames to standby redo log filenames, e.g., from log_* to standbylog_*.  

LOCK_NAME_SPACE  

Specifies the name space that the distributed lock manager (DLM) uses to generate lock names. You may need to set this value in OPS configurations if there is a standby database with the same database name on the same cluster.  

Use DB_FILE_NAME_CONVERT to convert the filename of a new datafile on the primary database to a filename on the standby database; use LOG_FILE_NAME_CONVERT to convert the filename of a new redo log on the primary database to a filename on the standby database. Adding a datafile or log to the primary database necessitates adding a corresponding file to the standby database.

When the standby database is updated, this parameter is used to convert the datafile name on the primary database to the a datafile name on the standby database. The file must exist and be writable on the standby database or the recovery process will halt with an error.

The DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters must have two strings. The first string is a sequence of characters to be looked for in a primary database filename. If that sequence of characters is matched, it is replaced by the second string to construct the standby database filename.

Figure 16-2 shows how the filename conversion parameters work.

Figure 16-2 Setting Filename Conversion Parameters


If you execute the following statements, then the conversion parameters will not apply to the affected files:

See Also: To learn how to add datafiles to the standby database, see "Adding Datafiles".

Determining the Most Recently Applied Archived Redo Log

Use either of these methods for determining when the most recent archived redo log was applied to the standby database:

Clearing Online Redo Logs

You can clear standby database online redo logs to optimize performance by issuing the ALTER DATABASE CLEAR LOGFILE GROUP integer command. If you prefer not to perform this operation during maintenance, the online logs will be cleared automatically during activation.

Making Backups of a Standby Database

If needed, you can back up your standby database--but not while the database is in manual or managed recovery mode. You must take the standby database out of managed recovery mode, take the backups, then resume managed recovery. You can make the backups when the database is shut down or when it is in read-only mode.

Depending on the size of your database, this procedure may be time-consuming, which means that the primary database may have to wait to archive its logs because the standby database is down. Consequently, the best solution is to follow one of the procedures below.

To back up tablespaces on a standby database when it is shut down:

  1. Start a SQL*Plus session on the standby database and take the database out of managed recovery mode: 

    RECOVER MANAGED STANDBY DATABASE CANCEL
  2. Shut down the database: 

    SHUTDOWN IMMEDIATE
  3. Take cold backups of some tablespaces using O/S utilities. Minimize the time that the database is down. For example, to back up datafiles tbs11.f, tbs12.f, and tbs13.f in tablespace TBS_1 on UNIX you might enter: 

    % cp /disk1/oracle/dbs/tbs11.f /disk2/backup/tbs11.bk
% cp /disk1/oracle/dbs/tbs12.f /disk2/backup/tbs12.bk
% cp /disk1/oracle/dbs/tbs12.f /disk2/backup/tbs13.bk
  4. Use SQL*Plus to start the Oracle instance at the standby database without mounting it, specifying a parameter file if necessary: 

    STARTUP NOMOUNT pfile = initSTANDBY.ora
  5. Mount the database: 

    ALTER DATABASE MOUNT STANDBY DATABASE;
  6. Recover the database: 

    RECOVER MANAGED STANDBY DATABASE

    If you wish to use the optional time-out option, add TIMEOUT integer to the command syntax as in the following: 

    RECOVER MANAGED STANDBY DATABASE TIMEOUT 60
  7. Repeat the above steps until you have backed up each tablespace in the database.

To back up tablespaces on a standby database when it is in read-only mode:

Note that you must back up the primary database control file, not the standby database control file.

  1. Start a SQL*Plus session on the standby database and take the database out of managed recovery mode: 

    RECOVER MANAGED STANDBY DATABASE CANCEL
  2. Open the database in read-only mode: 

    ALTER DATABASE OPEN READ ONLY;
  3. Take backups of some tablespaces using O/S utilities. You should not back up the standby control file.

    Minimize the time that the database is down. For example, to back up datafiles tbs11.f, tbs12.f, and tbs13.f in tablespace TBS_1 on UNIX you might enter: 

    % cp /disk1/oracle/dbs/tbs11.f /disk2/backup/tbs11.bk
% cp /disk1/oracle/dbs/tbs12.f /disk2/backup/tbs12.bk
% cp /disk1/oracle/dbs/tbs12.f /disk2/backup/tbs13.bk
  4. Terminate all active user sessions on the standby database.

  5. Issue the following statement: 

    RECOVER MANAGED STANDBY DATABASE # you can also set the TIMEOUT option
  6. Back up the control file on the primary database using an O/S utility.

  7. Repeat the above steps until you have backed up each tablespace in the database.

Opening a Standby Database in Read-Only Mode

The read-only mode allows users to query an open database, thereby eliminating the potential for online data modifications. This functionality enables you to use your standby database as a temporary reporting database. Temporary tablespaces allow you to add tempfile entries in read-only mode for the purposes of making queries. Adding and modifying tempfiles will not generate redo entries.

If you maintain your standby database primarily for disaster prevention, you should not rely too heavily on your standby database as a source of information. If a disaster does occur, you will be forced to activate it quickly and hence immediately cease all user activity. Furthermore, using a standby database for queries makes it unavailable for managed recovery. At some point, you will need to run a recovery operation against the standby to resynchronize it with the primary. This action limits the standby's role as a disaster recovery database.

If you need both disaster prevention and a standby available for queries, you can maintain multiple standby databases, some read-only and some in managed recovery mode. You will need to resynchronize the read-only database, but the recovery mode databases give you protection against disaster.

See Also: For more information about using tempfiles and temporary tablespaces, see the Oracle8i Administrator's Guide.

To open the standby database in read-only mode when the database is shut down:

  1. Use SQL*Plus to start the Oracle instance for the standby database without mounting it: 

    STARTUP NOMOUNT pfile=initSTANDBY.ora
  2. Mount the standby database: 

    ALTER DATABASE MOUNT STANDBY DATABASE;
  3. Open the database in read-only mode: 

    ALTER DATABASE OPEN READ ONLY;
To open the standby database in read-only mode when in manual recovery mode:

  1. Cancel the recovery by entering the following (terminate the flow of archived redo logs to get the prompt): 

    RECOVER CANCEL
  2. Open the database in read-only mode: 

    ALTER DATABASE OPEN READ ONLY;
To open the standby database in read-only mode when in managed recovery mode:

  1. Start a SQL*Plus session and execute the following: 

    RECOVER MANAGED STANDBY DATABASE CANCEL
  2. Open the database in read-only mode: 

    ALTER DATABASE OPEN READ ONLY;
To move standby database from read-only mode back to managed recovery mode:

  1. Terminate all active user sessions on the standby database.

  2. Issue the following statement: 

    RECOVER MANAGED STANDBY DATABASE # you can also set the TIMEOUT option
To move standby database from read-only mode back to manual recovery mode:

  1. Terminate all active user sessions on the standby database.

  2. Issue the following statement: 

    RECOVER STANDBY DATABASE # you can also set the TIMEOUT option

Activating a Standby Database

You should not activate the standby database unless it is an emergency. Once activated, the standby database becomes a normal production database and loses its standby status.

Depending on the nature of the disaster, you may not have access to your primary database files. If you do have access, you should do the following if possible:

  1. Archive your primary database online redo logs: 

    ALTER SYSTEM ARCHIVE LOG CURRENT;
  2. Transfer them to your standby site.

  3. Apply them before activating your standby database.

Following this procedure rolls forward your standby database to the time immediately before the failure of your primary database. You can apply any redo log other than the current redo log to the standby database. If you have lost your non-current online redo logs and they have not been archived, then activate the standby database without recovering the transactions from the unarchived redo logs of the primary database.

After you activate your standby database, reset the online redo logs. Note that the redo logs from the standby database and primary database are now incompatible. Also, the standby database is not mounted when activated; therefore, you are unable to look at tables and views immediately after activation.

To activate a standby database:

  1. Ensure that your standby database is mounted in EXCLUSIVE mode.

  2. Activate the standby database: 

    ALTER DATABASE ACTIVATE STANDBY DATABASE;
  3. Shut down your standby instances: 

    SHUTDOWN IMMEDIATE
  4. As soon as possible, back up your new production database. At this point, the former standby database is now your production database. This task, while not required, is a recommended safety measure because you cannot recover changes made after activation without a backup.

  5. Start the new production instance in read-write or read-only mode: 

    STARTUP MOUNT
ALTER DATABASE READ ONLY; # opens the database in read-only mode
ALTER DATABASE READ WRITE; # opens the database in read-write mode

    Note:

    After you activate your standby database, you lose all transactions from unarchived logs at your original production database.  

Altering the Physical Structure of the Primary Database

The following sections describe the effects of primary database structural alterations on a standby database.

This section contains the following topics:

Adding Datafiles

Adding a datafile to your primary database generates redo data that, when applied at your standby database, automatically adds the datafile name to the standby control file. If the standby database locates the new file with the new filename, the recovery process continues. If the standby database is unable to locate the new datafile, recovery terminates.

If the recovery process stops, perform the procedure below. Note that if you do not want the new datafile in the standby database, you can take it offline using the following syntax: 

ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;
To add a tablespace or datafile to the primary database and transmit to the standby:

  1. Create a tablespace on the primary database as usual. For example, to create new datafile t_db2.f in tablespace TBS_2 issue; 

    CREATE TABLESPACE tbs_2 DATAFILE 't_db2.f' SIZE 2M;
  2. Copy the newly created empty datafile to the standby site. For example, if the databases are on the same host, you might enter: 

    % cp t_db2.f /private1/stby/t_db2.f
  3. Start the standby instance without mounting it. For example, enter: 

    STARTUP NOMOUNT pfile=/private1/stby/initSTANDBY.ora
  4. Mount the standby database, then place it in managed recovery mode: 

    ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER MANAGED STANDBY DATABASE
  5. Switch redo logs on the primary to initiate redo archival to standby database: 

    ALTER SYSTEM SWITCH LOGFILE;
  6. After all archived redo logs have been applied, cancel managed recovery: 

    RECOVER MANAGED STANDBY DATABASE CANCEL

    Applying CREATE TABLESPACE redo adds the new filename to the standby control file. The following alert.log entry is generated; 

    WARNING! Recovering data file 2 from a fuzzy file. If not the current file it might
be an online backup taken without entering the begin backup command. 
Successfully added datafile 2 to media recovery 
Datafile #2: '/private1/stby/t_db2.f'
  7. Create the datafile on the standby database. For example, issue; 

    ALTER DATABASE CREATE DATAFILE '/private1/stby/t_db2.f' AS '/private1/stby/t_db2.f';
  8. Place the standby in managed recovery mode: 

    RECOVER MANAGED STANDBY DATABASE

Continue normal processing on the primary database. The primary and standby databases are now synchronized.

See Also: For more information on offline datafile alterations, see "Taking Datafiles in the Standby Database Offline".

Renaming Datafiles

Datafile renames on your primary database do not take effect at the standby database until the standby database control file is refreshed. To keep the datafiles at your primary and standby databases synchronized when you rename primary database datafiles, perform analogous operations on the standby database.

Altering Redo Logs

You can add redo log file groups or members to the primary database without affecting your standby database. Similarly, you can drop log file groups or members from the primary database without affecting your standby database. Enabling and disabling of threads at the primary database has no effect on the standby database.

You may want to keep the online redo log configuration the same at the primary and standby databases. If so, when you enable a log file thread with the ALTER DATABASE ENABLE THREAD statement at the primary database, create a new control file for your standby database before activating it. See "Refreshing the Standby Database Control File" for procedures.

If you clear log files at the primary database by issuing the ALTER DATABASE CLEAR UNARCHIVED LOGFILE statement, or open the primary database using the RESETLOGS option, you invalidate the standby database. Because the standby database recovery process will not have the archived logs it requires to continue, you will need to re-create the standby database.

Altering Control Files

If you use the CREATE CONTROLFILE command at the primary database to perform any of the following operations, you may invalidate the standby database's control file:

If you have invalidated the standby database's control file, re-create it using the procedures in "Refreshing the Standby Database Control File".

Using the CREATE CONTROLFILE command with the RESETLOGS option on your primary database will force the next open of the primary database to reset the online logs, thereby invalidating the standby database.

Configuring Initialization Parameters

Most initialization parameters at your primary and standby databases should be identical. Specific initialization parameters such as CONTROL_FILES and DB_FILE_NAME_CONVERT should be changed. Differences in other initialization parameters may cause performance degradation at the standby database, and in some cases, bring standby database operations to a halt.

The following initialization parameters play a key role in the standby database recovery process:

Table 16-2 Configuring Standby Initialization Parameters
Parameter  Guideline 

COMPATIBLE  

This parameter must be the same at the primary and standby databases. If it is not, you may not be able to apply the logs from your primary database to your standby database. You must set the COMPATIBLE parameter to 8.1 or higher if you want to open your standby database in read-only mode.  

DB_FILES  

MAXDATAFILES must be the same at both databases so that you allow the same number of files at the standby as you allow at the primary database.  

CONTROL_FILES  

This parameter must be different between the primary and standby databases. The names of the control files that you list in this parameter for the standby database must exist at the standby database.  

DB_FILE_NAME_CONVERT  

Set when you want to make your standby datafile filenames distinguishable from your primary database filenames. For more information, see "Converting Filenames for Datafiles and Archived Redo Logs".  

LOG_FILE_NAME_CONVERT  

Set when you want to make your standby log filenames distinguishable from your primary database log filenames. For more information on this parameter see "Converting Filenames for Datafiles and Archived Redo Logs".  

STANDBY_ARCHIVE_DEST  

This parameter is used solely by the standby RFS process to determine the directory in which to place the archived logs. Oracle uses this value along with LOG_ARCHIVE_FORMAT to generate the log filename for the standby site. Oracle stores the fully qualified filenames in the standby control file (query V$ARCHIVED_LOG for this data). Managed recovery uses this information to drive the recovery operation.

The RECOVER STANDBY DATABASE commands (excluding the MANAGED option) rely on either LOG_ARCHIVE_DEST to provide the location of the archived files or a user-entered filename. If a log is missing at the standby site, i.e., the RFS has not recorded its name in the standby control file, and the managed recovery operation fails, you must issue RECOVER STANDBY DATABASE. This statement requires you to use the LOG_ARCHIVE_DEST parameter to locate the archived log.

For a managed standby database, set the parameters STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same value. If manual recovery is required, copy the missing archived log to the same location as all the other archived logs, run the manual standby recovery operation, and place the standby back into managed recovery mode.  

See Also: For more information on initialization parameters, see the Oracle8i Reference.

Taking Datafiles in the Standby Database Offline

You can take standby database datafiles offline as a means to support a subset of your primary database's datafiles. For example, you may decide that it is undesirable to recover the primary database's temporary tablespaces on the standby database.

Take the datafiles offline using the following statement on the standby database: 

ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;

If you execute this statement, then the tablespace containing the offline files must be dropped after opening the standby database.

Performing Direct Path Operations

When you perform a direct load originating from any of the following, the performance improvement applies only to the primary database (there is no corresponding recovery process performance improvement on the standby database):

The standby database recovery process still sequentially reads and applies the redo information generated by the unrecoverable direct load.

Primary database processes using the UNRECOVERABLE option are not propagated to the standby database because these processes do not appear in the archived redo logs. To propagate such processes to your standby database, perform any one of the following tasks:

If you perform an UNRECOVERABLE operation at the primary database and then attempt to recover at the standby database, you will not receive error messages during recovery; instead, such error messages appear in the standby database alert log. Check the standby database alert log periodically.

If you attempt to read a block at the standby site that was loaded with the UNRECOVERABLE option, the following error message is displayed: 

26040, 00000, "Data block was loaded using the NOLOGGING option\n" 
//* Cause: Trying to access data in block that was loaded without  
//*        redo generation using the NOLOGGING/UNRECOVERABLE option 
//* Action: Drop the object containing the block.

See Also: For more details, see "Taking Datafiles in the Standby Database Offline".

Note:

Blocks loaded using the UNRECOVERABLE option will be marked logically corrupt during recovery at the standby site. Querying these data blocks will result in an error message.  

Determining Whether a Backup is Required After UNRECOVERABLE Operations

If you have performed UNRECOVERABLE operations on your primary database, use the V$DATAFILE view to determine the SCN or time at which Oracle generated the most recent invalidation redo data.

Issue the following SQL command to determine whether you need to perform another backup: 

SELECT unrecoverable_change#, to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss') 
FROM v$datafile;

If the query reports an unrecoverable time for a datafile that is more recent than the time when the datafile was last backed up, then make another backup of the datafile in question.

See Also: For more information about the V$DATAFILE view, see the Oracle8i Reference.

Refreshing the Standby Database Control File

The following steps describe how to refresh, or create a copy, of changes you have made to the primary database control file. Refresh the control file after making major structural changes to the primary database such as adding or dropping files.

To refresh the standby database control file:

  1. Start a SQL*Plus session on the standby instance and issue the CANCEL command on the standby database to halt its recovery process. 

    RECOVER CANCEL  # for manual recovery mode
RECOVER MANAGED STANDBY DATABASE CANCEL   # for managed recovery mode
  2. Shut down the standby instances: 

    SHUTDOWN IMMEDIATE
  3. Start a SQL*Plus session on the production instance and create the control file for the standby database: 

    ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
  4. Archive the current online redo log of your primary database: 

    ALTER SYSTEM ARCHIVE LOG CURRENT;
  5. Transfer the standby control file and archived log files to the standby site using an O/S utility appropriate for binary files.

  6. Connect to the standby instance and mount (but do not open) the standby database: 

    ALTER DATABASE MOUNT STANDBY DATABASE;
  7. Restart the recovery process on the standby database: 

    RECOVER STANDBY DATABASE  # recovers using location for logs in init.ora
RECOVER FROM 'location' STANDBY DATABASE # recovers using specified location

Using a Standby Database in an OPS Configuration

You can use a standby database in conjunction with the Oracle multi-threaded server option. The following table describes the possible legal and illegal combinations of nodes in the primary and standby databases:

  Single-Instance Standby   Multi-Instance Standby  
Single-Instance Primary  

Yes  

No  
Multi-Instance Primary  

Yes  

Yes  

In each scenario, each node of the primary database transmits its own thread of archived redo logs to the standby database. For example, Figure 16-3 illustrates an OPS database with two nodes transmitting redo logs to a single-instance standby:

Figure 16-3 Transmitting Archived Logs from a Multi-Instance Primary Database


In this case, node 1 of the primary database transmits logs 1,3,5,7,9 while node 2 transmits logs 2,4,6,8,10. If the standby database is in managed recovery mode, it automatically determines the correct order in which to apply the archived redo logs.

If both your primary and standby databases are in an OPS configuration, and the standby database is in managed recovery mode, then a single node of the standby database applies all sets of logs transmitted by the primary nodes. In this case, the standby nodes that are not applying redo cannot be in read-only mode while managed recovery is in progress; in most cases, the non-recovery nodes should be shut down, although they can also be mounted.

See Also: For information about configuring a database for OPS, see the Oracle8i Parallel Server Setup and Configuration Guide.


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index