Oracle8i Standby Database Concepts and Administration
Release 2 (8.1.6)

Part Number A76995-01

Library

Product

Contents

Index

Go to previous page Go to next page

1
Standby Database Concepts

This chapter explains the nature and function of a standby database. It includes the following topics:

What Is a Standby Database?

A standby database is a database replica created from a backup of a primary database. By applying archived redo logs from the primary database to the standby database, you can keep the two databases synchronized.

A standby database has the following main purposes:

If the primary database is destroyed or its data becomes corrupted, you can perform a failover to the standby database, in which case the standby database becomes the new primary database. You can also open a standby database with the read-only option, thereby allowing it to function as an independent reporting database.

This section contains the following topics:

Configuration Options

You can set up a standby database in several different ways, depending on the method for:

For example, Oracle's managed standby environment allows the primary database to automatically archive redo logs to the standby database site so long as the standby instance is started. If you implement a non-managed standby environment, you must transfer the logs manually.

If the standby database is in managed recovery mode, the standby database automatically applies logs received from the primary database. You can also apply logs manually to the standby database by placing it in manual recovery mode. At any time you can open the standby database in read-only mode for reporting purposes.

The following table explains the possible configurations depending on the environment that you choose:

Environment  Method of Transfer  Standby Database Modes  Network Requirements 

Managed 

Automatic (or manual if necessary) 

Managed recovery, manual recovery, or read-only 

Net8 

Non-Managed 

Manual only 

Manual recovery or read-only 

None 

Most database administrators (DBAs) choose a managed recovery environment. You may prefer a non-managed environment if:

Advantages and Disadvantages

A standby database can be a powerful tool for both disaster prevention and supplementary reporting. For example, you can:

While a standby database can be a tremendous benefit in your backup and recovery strategy, it involves costs as well. For example, a standby database requires:

Compatibility and Operational Requirements

Note the following requirements for maintaining a standby database:

Concepts and Terminology

Familiarize yourself with the following terms, which are used throughout the subsequent chapters:


activation

See failover.


failover

The operation of turning a standby database into a normally functioning primary database. This operation is also called standby database activation. Note that after a failover, you cannot switch the standby database back so that it becomes a standby database again.


gap sequence

A sequence of archived redo logs that must be manually applied to a standby database before it can be placed in managed recovery mode.

managed recovery mode

A standby database mode initiated by entering the following SQL*Plus statement:

RECOVER MANAGED STANDBY DATABASE;

When a standby database runs in managed recovery mode, it automatically applies redo logs received from the primary database.

managed standby environment

A configuration in which a primary database automatically archives redo logs to a standby site. If the standby database is in managed recovery mode, it automatically applies the logs received from the primary database to the standby database. Note that in a managed standby environment, a standby site continues to receive archived logs even if the standby database is not in managed recovery mode.

manual recovery mode

A standby database mode initiated by issuing the following SQL*Plus statement:

RECOVER STANDBY DATABASE;

This mode allows you to recover a standby database manually.

non-managed standby environment

Any environment in which the primary database does not automatically archive redo logs to the standby site. In this environment, you must manually transfer archived logs to the standby site and manually apply them.

primary database

A database used to create a standby database. Every standby database is associated with one and only one primary database. A single primary database can, however, support multiple standby databases.

primary site

The location of the primary database. Note that the primary and standby sites can be on separate hosts or on the same host.


read-only mode

A standby database mode initiated by issuing the following SQL statement:

ALTER DATABASE OPEN READ ONLY;

This mode allows you to query the standby database, but not to make changes to it.

standby database

A database replica created using a backup of your primary database. A standby database has its own initialization parameter file, control file, and datafiles.


standby database environment

The physical configuration of the primary and standby databases. The environment depends on many factors, including:

standby site

The location of the standby database. The standby site can be on the same host as the primary database or on a separate host.

Standby Database Modes

You can perform any of the following mutually exclusive operations on a standby database:

Although you cannot run the standby database in more than one mode at the same time, you can switch back and forth between the modes at will. For example, you can run in managed recovery mode, then open read-only, then switch to manual recovery, then return to managed recovery, as shown in Figure 1-1.

Figure 1-1 Switching Between Modes


Text description of sbr81102.gif follows.

Text description of the illustration sbr81102.gif.

Manual Recovery Mode

You have the option of placing the database in manual recovery mode, in which case you must continually and manually transfer and apply archived redo logs to the standby database to keep it synchronized with the primary database.

To perform manual recovery, connect to the standby database using SQL*Plus and issue the RECOVER STANDBY DATABASE statement. Figure 1-2 shows an example of a database in manual recovery mode.

Figure 1-2 Standby Database in Manual Recovery Mode


Text description of sbr81098.gif follows.

Text description of the illustration sbr81098.gif.

Manual recovery mode is useful in environments in which you do not want to connect the primary and standby databases through Net8. Also, if for some reason the primary database is unable to automatically transfer archived redo logs to a standby database in a managed recovery environment, you may need to perform manual recovery to update the standby database.

See Also:

To learn how to recover a standby database manually, see Placing the Standby Database in Manual Recovery Mode

Managed Recovery Mode

You can place the standby database in managed recovery mode, in which case the standby database automatically applies archived redo logs as it receives them from the primary database. To initiate managed recovery, connect to the standby database using SQL*Plus and issue the RECOVER MANAGED STANDBY DATABASE statement.

The principal advantage of running a database in managed recovery mode is that you do not have to transfer or apply archived redo logs manually: Oracle automates the procedure. For example, Figure 1-3 illustrates a case in which a primary database in San Francisco transmits archived redo logs to a standby site in Boston, where the standby database automatically applies them.

Figure 1-3 Automatic Updating of a Standby Database


Text description of sbr81091.gif follows.

Text description of the illustration sbr81091.gif.

See Also:

To learn how to run the standby database in managed recovery mode, see Placing the Standby Database in Managed Recovery Mode

Read-Only Mode

You can also open your standby database in read-only mode after terminating manual or managed recovery. You can then query the database and even store data in temporary tablespaces (so long as they already exist in the standby database) without affecting the datafiles or redo logs. You can return the standby database to manual or managed recovery mode at any time, without having to shut it down. Figure 1-4 shows a standby database in read-only mode.

Figure 1-4 Standby Database in Read-Only Mode


Text description of sbr81099.gif follows.

Text description of the illustration sbr81099.gif.

In a managed standby environment, the standby site continues to receive redo logs archived by the primary database and the control file continues to be updated with their records. Consequently, archiving continues to the standby site even though the standby database does not perform recovery while in read-only mode.

A read-only standby database is useful when you want to decrease the number of queries to the primary database. For example, if specific tablespaces in a primary database change infrequently but are accessed frequently, you can direct those queries to the standby database so the primary database does not become overloaded with read requests.

See Also:

To learn how to open a standby database in read-only mode, see Opening a Standby Database in Read-Only Mode

Failover to a Standby Database

Performing a failover to a standby database, also known as activation, occurs when you issue the following SQL statement:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

You can issue this statement only when the standby database is mounted.

Figure 1-5 depicts a failover operation from a primary database in San Francisco to a standby database in managed recovery mode in Boston.

Figure 1-5 Failover to a Standby Database


Text description of sbr81092.gif follows.

Text description of the illustration sbr81092.gif.

After you activate the standby database, it ceases to be a standby database and becomes a fully functional primary database. At this point, you can open the database in read/write or read-only mode and make changes or issue queries as usual.


CAUTION:

Activating a standby database is a permanent operation. You cannot undo the activation and return the database to its former role as a standby database. 


See Also:

To learn how to perform failover to a standby database, see Activating a Standby Database

Consequences of Failover

Failover permanently transforms a standby database into a primary database. Because standby activation is a unidirectional operation, you cannot return the new primary database to any of the standby modes. In other words, you cannot perform a failover and then undo it.


CAUTION:

Activating a standby database resets the online logs of the standby database. After activation, the archived logs from the standby database and the primary database are incompatible. 


Another consequence of failover is that any other standby databases that were supporting the original primary database are now invalid as standby databases to the new, activated primary database. For example, assume primary database A supports standby databases B and C, as illustrated in Figure 1-6.

Figure 1-6 Primary Database with Multiple Standby Databases


Text description of sbr81100.gif follows.

Text description of the illustration sbr81100.gif.

If you perform a failover from A to B, then C does not function as standby database to the newly activated B. Because B's redo logs are reset, you cannot apply archived redo logs from B to C.

In some situations, maintaining multiple standby databases can lessen the repercussions of a failover. For example, assume the preceding scenario, with standby databases B and C supporting primary database A. The following events occur:

  1. A's machine suffers a media failure.

  2. You activate standby database B. Users now access B as the primary database.

  3. You quickly fix the media problem on A's machine.

  4. You shut down B, then restart A.

  5. Users now access A as the primary database again. C continues to function as a standby database for A, while B is invalidated.

One consequence of this scenario is that any changes made to B while it briefly served as the primary database cannot be applied through archived redo logs. Note that you can generate a report of these changes by using the LogMiner utility and then reenter the changes manually into database A.

Testing the Standby Database Without Performing Failover

Because failover to a standby database destroys its standby functionality, perform this operation only when absolutely necessary. If you want to test the standby database, do not activate it--open it in read-only mode instead. By opening in read-only mode, you can query the standby database to ensure that it is correctly updating the datafiles with the redo logs received from the primary database.

Re-Creating the Original Primary Database After Failover

If you activate a standby database and then solve the problem at the original primary site that necessitated the failover operation, you have the option of re-creating the primary database on the original primary site. Perform the following steps, assuming the original primary site was on node A and the activated standby site is on node B:

  1. Make a consistent backup of the activated standby database on node B.

  2. Restore the backup created on node B to node A.

  3. Shut down the activated standby database on node B.

  4. Open the restored database on A. It is now the primary database.

  5. Make a backup of the database on node A.

  6. Use the backup of A to re-create the standby database on node B.

    See Also:

    To learn how to re-create a standby database, see Scenario 8: Re-Creating a Standby Database

Standby Database Life Cycle

Most standby database implementations use managed recovery. The life cycle of a standby database intended for managed recovery is illustrated in Figure 1-7.

Figure 1-7 Standby Database Life Cycle


Text description of sbr81103.gif follows.

Text description of the illustration sbr81103.gif.

The four stages illustrated in this diagram are:

  1. Standby Database Creation

  2. Manual Recovery Using Logs in the Gap Sequence

  3. Managed Recovery and Read-Only Access Cycle

  4. Failover to the Standby Database

Standby Database Creation

In this stage, you must construct the standby database from backups of the primary database control files and datafiles, and then prepare it for managed recovery. This stage, illustrated in Figure 1-8, involves the following basic steps:

  1. Make a backup of the primary datafiles (or access a previous backup) and create the standby control file.

  2. Transfer the standby datafiles and control file to the standby site.

  3. Configure Net8 so that you can connect to the standby service name.

  4. Configure the primary and standby initialization parameter files.

  5. Initiate automatic archiving on the primary site.

  6. Start the standby instance without mounting it.

Figure 1-8 Standby Database Creation


Text description of sbr81101.gif follows.

Text description of the illustration sbr81101.gif.

See Also:

This procedure is explained in complete detail in Chapter 2. For additional information, see Scenario 1: Creating a Standby Database on the Same Host and Scenario 2: Creating a Standby Database on a Remote Host

Manual Recovery Using Logs in the Gap Sequence

A gap sequence is created whenever you are unable to apply the next archived redo log generated by the primary database to the standby database. For example, the primary database archives log 100 to the standby site, but the standby control file has no knowledge of any logs after log sequence 89, because the standby control file was created when the most recent log archived by the primary database was 89. The gap sequence in this case spans logs 90 to 99.

To be able to begin managed recovery, you must first manually apply logs in the gap sequence to the standby database. After you have performed this manual recovery, you can issue the RECOVER MANAGED STANDBY DATABASE statement, at which point Oracle applies subsequent logs to the standby database automatically.

Typical Causes of Gap Sequences

Most commonly, gap sequences occur in the following situations:

In the first two situations, the primary database can archive redo logs to the standby site, but the standby database control file is unaware of logs archived while it was not mounted. Whenever the primary database archives to the primary site but the standby control file does not contain records of logs that are necessary for recovery of the database, a gap sequence is created.

In the third situation, the primary database continues to archive locally, but is prevented from archiving to the standby site by a network failure. Archived logs accumulate at the primary site, but the standby control file does not know about them. Consequently, you must transfer the accumulated logs manually and then apply them in a manual recovery operation before managed recovery can begin.

The Gap Sequence Cycle

Because a gap sequence can occur whenever the primary database is archiving logs that the standby control file is not informed about, you can occasionally go through a gap sequence cycle. This cycle occurs whenever you must exit either managed recovery mode or read-only mode to perform manual recovery using logs in a gap sequence. After you have completed manual recovery of all logs in the sequence, you can return to managed recovery or read-only mode.

See Also:

Detailed procedures for resolving the gap sequence problem are described in Resolving a Gap Sequence Before Initiating Managed Recovery. Various scenarios in Chapter 5 also describe the procedure for resolving gap sequences. 

Managed Recovery and Read-Only Access Cycle

In most scenarios, you run the database primarily in managed recovery mode or primarily in read-only mode. During managed recovery, the standby site receives logs from the primary database and the standby recovery process applies them automatically. In read-only mode, the standby site receives logs from the primary database, but the standby recovery process does not apply them.

You can easily switch between managed recovery mode and read-only mode. Because the standby control file continues to be updated about incoming logs when it is in read-only mode, you do not have to perform manual recovery before returning to managed recovery mode.

See Also:

Detailed procedures for performing managed recovery are in Placing the Standby Database in Managed Recovery Mode. Detailed procedures for opening the standby database in read-only mode are in Opening a Standby Database in Read-Only Mode

Failover to the Standby Database

You can activate the standby database at any time, so long as the database is mounted. Typically, you perform a failover operation while the standby database is running in managed recovery mode. It can occur, however, that the primary database fails while you are performing manual recovery using logs in the gap sequence. In this case, you can activate the standby database even though it does not contain the latest changes made to the primary database.

After failover, you can back up the new primary database and start the standby life cycle over again by creating a new standby database.

See Also:

Detailed procedures for performing failover are described in Activating a Standby Database

Configuration of the Standby Database Environment

A standby database environment refers to the physical configuration of a primary database with one or more associated standby databases. This section describes the main factors affecting configuration of the standby database environment:

Number of Standby Databases

Although a standby database can be synchronized with one and only one primary database, a single primary database can support a theoretically unlimited number of standby databases. These standby databases are separate and independent, and can reside on multiple machines or on a single machine.

Using Backups for Standby Creation

Each standby database must be created from a backup of the primary database. This backup can be either consistent or inconsistent, open or closed.

You can also use a single backup of the primary database to create an unlimited number of standby databases, although the various standby databases in the environment do not have to be created from the same backup. For example, you can create one standby database from a backup of the primary database taken on January 20 and create another standby database from the backup taken on June 20 (see Figure 1-9). So long as you have the archived redo logs required to perform complete recovery of a backup, then it can serve as the basis for a standby database.

Figure 1-9 Creating Standby Databases Using Different Backups


Text description of sbr81104.gif follows.

Text description of the illustration sbr81104.gif.

Limits Imposed by Automatic Archival to Standby Sites

In a managed standby environment, the primary database can automatically archive to a maximum of four standby sites. Consequently, you can simultaneously run a maximum of four standby databases in managed recovery mode in any given standby database environment.

Although automatic archival limits the number of standby databases that you can maintain in managed recovery mode, it does not limit the number of standby databases that you can maintain in manual recovery mode. For example, you can configure the primary database to archive automatically to four standby sites, then use operating system commands to copy archived redo logs to five additional standby sites. Consequently, there is no theoretical limit to the number of standby databases that can support a given primary database.

Method of Transferring Archived Redo Logs to the Standby Site

One crucial aspect of any standby database environment is the transfer of archived redo logs from the primary site to the standby site. You have two options for transferring the logs:

If you archive automatically to a standby site, then you are operating in a managed recovery environment.

Managed Recovery Environment

If you choose to implement a managed recovery environment, then you must connect the primary and standby databases through Net8. To configure the primary initialization parameter file for automatic archival to a standby site, you must specify a service name. To specify a service name, you must configure network files such as tnsnames.ora, listener.ora, and possibly names.ora.

When a primary database archives to a standby service, Oracle automatically transfers the archived redo logs through Net8 to a directory on the standby site. As the primary database archives each log, Oracle automatically transfers the new log to the standby site.

Independence of Automatic Archival and Managed Recovery

Note that the primary database can continue to archive to the standby site even if the standby database is not in standby recovery mode, but only if the standby instance is started. The mechanism for recovery of a standby database is independent of the mechanism for automatic transfer of archived redo logs to the standby site. Consequently, you can take a standby database out of managed recovery mode and temporarily place it in read-only mode. While the standby database is in read-only mode, archived redo logs continue to accumulate at the standby site.

Optional Manual Transfer of Archived Redo Logs

Even if you configure the primary database to archive automatically to the standby site, you can still transfer the logs manually if the occasion requires it, but only if you have specified the standby site as an optional destination (see Specifying Mandatory and Optional Archive Destinations).

For example, assume that a problem with the Net8 configuration prevents the transfer of archived logs to the standby site. The primary database continues to archive locally, so you can transfer the logs manually using operating system commands, then perform manual recovery at the standby site to synchronize the standby database.

Non-Managed Recovery Environment

In a non-managed recovery environment, you do not configure the primary database to archive automatically to the standby site and so must transfer the archived redo logs by hand. Because the primary database is not archiving automatically to the standby site, you do not need to maintain a Net8 connection. For example, you can use an operating system utility such as UNIX cp or ftp to transfer the archived redo logs to the standby site, connect to the standby database using operating system authentication, then recover the database manually (see Figure 1-10).

Figure 1-10 Non-Managed Recovery Environment


Text description of sbr81105.gif follows.

Text description of the illustration sbr81105.gif.


Note:

Managed and non-managed recovery environments are not mutually exclusive. For example, you can configure the primary database to archive automatically to one standby location through Net8, then manually transfer archived logs from the primary site to a different, non-network standby site. 


Location and Directory Structure of Primary and Standby Sites

One crucial aspect of the standby database environment is the number and configuration of the machines involved. Of particular importance are whether:

Number and Location of Machines

Just as there is no theoretical limit to the number of standby databases associated with a given primary database, there is no theoretical limit to the number of machines on which the standby databases reside. For example, you can locate a standby database:

The location of hosts involved in the standby database environment has obvious implications for a disaster recovery strategy. For example, if the primary host in a data center is destroyed, then you cannot perform failover to a standby database unless it resides on a different host, which may or may not be in the same data center. In a worst case scenario, if the data center is completely destroyed, then you cannot perform a failover to a standby database unless the standby database is located on a different machine in a remote location.

See Also:

Scenario 5: Deciding Which Standby Database to Fail Over to in a Multiple Standby Database Configuration addresses the location of the primary and standby databases. 

Directory Structure of Standby Sites

The directory structure of the various standby sites is important because it determines the path names for the standby datafiles and redo logs. Note that a standby site on the same host as the primary database necessarily uses a different directory structure; otherwise, the standby database attempts to overwrite the primary database files.

In general, use the same path names for the standby files if at all possible. This option prevents you from having to set filename conversion parameters or manually rename standby files. Nevertheless, if you need to use a host with a different directory structure or place the standby and primary databases on the same host, you can do so with a minimum of extra administration.

Configuration Options

You have three basic configuration options, which are illustrated in Figure 1-11:

Figure 1-11 Possible Standby Configurations


Text description of sbr81097.gif follows.

Text description of the illustration sbr81097.gif.

The following table describes possible configurations of primary and standby databases and the consequences of each:

Standby Host  Directory Structure  Consequences 

Same as primary host 

Differs from primary host (necessarily) 

  • You must set the LOCK_NAME_SPACE parameter.

  • You must rename primary database filenames in the standby database control file (see Renaming Primary Filenames in the Standby Control File).

  • Some operating systems do not permit two instances with the same name to run on the same machine. Refer to your platform-specific documentation for more information.

  • The standby database does not protect against disaster.

 

Separate 

Same as primary host 

  • You do not need to rename primary database filenames in the standby control file, although you can still do so if you want a new naming scheme (for example, to spread the files among different disks).

  • Using separate physical media for your databases safeguards your primary data.

 

Separate 

Differs from primary host 

 

Although these configurations are mutually exclusive, that is, a given standby database must use only one configuration, you can run multiple standby databases simultaneously for a given primary database. Consequently, you can implement any combination of configurations. For example, you can maintain one standby database on the same host as the primary database, another standby database on a separate host in the same data center, and a third standby database on a separate host on the other side of the world. You can run each standby in managed recovery mode, manual recovery mode, or read-only mode.

Besides increasing disaster protection, maintaining more than one standby database gives you more flexibility at the standby database management level. For example, you can shut down one standby database to upgrade hardware while maintaining disaster protection by running other standby databases. Also, you can delay the application of archived redo logs to one standby database but not to another, so that if the primary database is corrupted, you can activate the standby database that has not yet become corrupted.

A standby database provides excellent protection against user errors, for example, accidentally truncating a table. By delaying the application of an archived redo log to the standby database, you can open the standby database in read-only mode and export the missing data. You can then import the missing data back into the primary database. When the correct data is in the primary database, you can continue to recover the standby database. This procedure propagates both the user error and the correction of the user error, so you can avoid activating the standby database.

Standby Database Maintenance

Although a managed standby environment is mostly automated, it is not completely automated. In various situations, you may need to perform status checks or maintenance on the standby database. The most common maintenance operations are:

Checking the Status of Archived Redo Logs

To ensure that the standby database is functioning properly, you may want to see which logs have been archived to the standby site and which logs have been applied to the standby database. You have these options:

Backing Up the 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, make 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.

See Also:

For more information about making backups, see Backing Up the Standby Database

Responding to Physical Changes in the Primary Database

Changes to the primary database structure always affect a standby database. In cases such as the following, the standby database is updated automatically through applied redo:

In cases such as the following, however, you must perform maintenance on the standby database:

In cases such as the following, you must re-create the standby database entirely:

Standby Database Statements

Several different SQL and SQL*Plus statements use a STANDBY keyword to specify operations on a standby database. Other SQL statements do not include standby-specific syntax, but are useful for performing operations on a standby database. Table 1-1 describes relevant statements.

Table 1-1  Standby Database Statements
Statement  Description 
ALTER DATABASE ACTIVATE STANDBY 
DATABASE
 

Activates the standby database, which effectively destroys the standby functionality of the database. See Activating a Standby Database

ALTER DATABASE CREATE STANDBY 
CONTROLFILE AS 'filename'
 

Creates a standby control file. Issue this statement at the primary database. See Creating the Standby Control File

ALTER DATABASE MOUNT STANDBY DATABASE
 

Mounts a standby database. 

ALTER DATABASE OPEN READ ONLY
 

Opens the standby database in read-only mode. See Opening a Standby Database in Read-Only Mode

RECOVER [FROM '/dir'] STANDBY DATABASE
 

Initiates manual recovery of the standby database. You can direct Oracle to search a nondefault directory for the archived redo logs. See Placing the Standby Database in Manual Recovery Mode

RECOVER MANAGED STANDBY DATABASE 
[TIMEOUT integer]
 

Initiates managed recovery of the standby database. Use the TIMEOUT option to cause Oracle to exit recovery after waiting integer minutes for the log to arrive. See Placing the Standby Database in Managed Recovery Mode

RECOVER MANAGED STANDBY DATABASE CANCEL 

Causes Oracle to wait for managed recovery to finish writing the current log before canceling recovery. See Canceling Managed Recovery

RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE 

Cancels managed recovery either before reading another block from the redo log or before opening the next redo log file, whichever comes first. See Canceling Managed Recovery

STARTUP NOMOUNT pfile='initSID.ora

Starts the standby instance without mounting the control file. You must execute this statement before mounting the standby database. 


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index