Standby Database Concepts

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:

• Disaster protection
• Protection against data corruption
• Supplemental reporting

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
• Advantages and Disadvantages
• Compatibility and Operational Requirements
• Concepts and Terminology

Configuration Options:

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

Transferring archived redo logs to the standby site Applying archived redo logs to the standby database

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.

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

• You do not want to maintain a Net8 connection between the primary and standby sites, which is required for managed recovery.
• You want to create a time lag between the archiving of a log at the primary database and the application of the log to the standby database. A time lag protects against the transfer of corrupted or erroneous data from the primary database to the standby database.

Advantages and Disadvantages

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

• Maintain a standby database in a location that is geographically remote from the primary database, or maintain several standby databases in geographically diverse locations.
• Maintain the primary and standby databases on different disk drives of the same machine, so that if the primary database's drive fails, you can activate the standby database and resume normal operations.
• Implement a managed standby configuration, whereby a standby database automatically applies archived redo logs that are automatically shipped to the standby site by a primary database. In this way, changes to a primary database are regularly propagated to a standby database.
• Make a standby database the new primary database with minimal loss of time and data if the primary database is completely destroyed.
• Provide possible protection against erroneous batch jobs, user errors (for example, truncating the wrong table), or application corruptions on the primary database by not applying archived logs containing corrupt data to the standby database. You can then activate the uncorrupted standby database, making it the primary database.

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:

• An additional computer if you want to maximize disaster prevention by maintaining a standby database on a separate host
• Implementation and maintenance of a Net8 connection if you use the managed standby environment
• Additional system resources and extra storage space no matter which implementation you choose
• Administration of the standby database to mirror some structural operations (for example, adding a tablespace or datafile) performed on the primary database

Concepts and Terminology

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

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:

• The number of standby databases associated with a primary database
• The number of machines used by the databases
• The directory structures of the machines used by the databases
• The network configuration

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:

• Maintain it in manual recovery mode
• Maintain it in managed recovery mode
• Open it in read-only mode for queries

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

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

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.

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

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

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.

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\

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. 

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

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.

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:

• Creation of the standby database
• Shutdown of the standby database when the primary database is open
• A network failure that prevents archiving to the standby site

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.

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.

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.