2
Creating the Standby Database Environment

This chapter explains creating the standby database portion of the Data Guard environment. It includes the following topics:

• Considerations for Creating the Standby Database Environment

• Creating a Standby Database: Basic Tasks

• Creating the Standby Database Files

• Creating the Standby Initialization Parameter File

2.1 Considerations for Creating the Standby Database Environment

A standby database environment includes a primary database with one or more associated standby databases. This section describes the main factors affecting the Data Guard configuration:

• Number of Standby Databases

• Typical Modes for a Standby Database in the Data Guard Environment

• Method of Archiving Redo Logs to the Standby Site

• Method of Applying Redo Logs on the Standby Site

• Location and Directory Structure of Primary and Standby Sites

• Advantages of Using Multiple Standby Databases

2.1.1 Number of Standby Databases

Although a standby database can be synchronized with one and only one primary database, a single primary database can support up to nine standby databases. These standby databases are separate and independent, and can reside on multiple systems or on a single system.

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

2.1.2 Typical Modes for a Standby Database in the Data Guard Environment

Typically, you create a standby database for one or more of the following reasons:

• Protection against the total destruction of the primary database

• Protection against application corruption of the primary database

• Supplemental reporting of data contained in the primary database

For maximum protection against data loss or corruption, maintain the standby database in managed recovery mode in a Data Guard environment. In this setup, the primary database archives logs to the standby site, and the standby database automatically applies these logs.

Note: You may need to manually copy the archived logs to the standby database. To learn why and how to avoid the need to, see Section 4.5.

To use the standby database for reporting purposes, open it in read-only mode in a Data Guard environment. Log apply services cannot apply archived redo logs to the standby database when it is in this mode, but you can still execute queries on the database. The primary database continues to archive to the standby site so long as the standby instance is started.

You can easily change between managed recovery mode and read-only mode. In most implementations of a Data Guard environment, you perform this change at various times to either:

• Update a standby database used primarily for reporting

• Check that data is correctly applied to a database that is used primarily for disaster protection

  See Also: Section 4.3 to learn how to initiate managed recovery mode. See Section 4.7 to learn how to open a standby database in read-only mode.

2.1.3 Method of Archiving Redo Logs to the Standby Site

One crucial aspect of any Data Guard environment is archiving the redo logs from the primary site to the standby site. You have two options for archiving the redo logs:

• Configure the primary database to archive automatically to the standby site using log transport services

• Archive the logs manually using operating system commands

When a primary database archives to a standby site, log transport services automatically archive the online redo logs through Oracle Net to a directory on the standby site. As redo logs are generated on the primary database, log transport services automatically archive them and log apply services apply them to each standby database. This allows standby databases to remain synchronized with the primary database. Log transport services help minimize the latency between the primary and standby databases by continuously capturing the modifications from the archived redo logs.

See Also: Chapter 3, "Log Transport Services" and Chapter 4, "Log Apply Services"

2.1.3.1 Independence of Automatic Archival and Managed Recovery

The primary database can continue to archive to the standby site even if the standby database is not in managed recovery mode, but only if the standby instance is mounted. The mechanism for recovery of a standby database is independent of the mechanism for automatic archival of 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.

2.1.3.2 Optional Manual Copy of Archived Redo Logs

Even if you configure the primary database to archive automatically to the standby site, you can still copy the completed archived redo logs manually if the occasion requires it, but only if you have specified the standby site as an optional destination.

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

See Also: Section 3.3.1

2.1.3.3 Functions of Log Transport Services

Log transport services provide the following functions:

• Control of different log archiving mechanisms

• Automatic log archiving

• Error handling and reporting

• Automatic archive gap detection and retrieval of lost logs

• Guaranteed no-data-loss data protection

• Selectable log transport modes that balance data protection needs with availability and performance

• Increased DBA and operator productivity (by eliminating the need for operator intervention)

• Ability to fetch archived logs from any number of servers, which reduces the risk that log archiving will stop because of data communications failures, out-of-space conditions, or other reasons

• Ability to configure a standby database to receive but not apply archived logs, which avoids most of the storage and processing expense of another fully configured standby database

2.1.4 Method of Applying Redo Logs on the Standby Site

The log apply services component of the Data Guard environment is responsible for maintaining the standby database in either a managed recovery mode or in open read-only mode. It coordinates activities with the log transport services.

2.1.4.1 Functions of Log Apply Services

Log apply services provide the following functions:

• Automatic application of archived logs

• Automatic archive gap recovery

• Enhanced monitoring capability

• Automatic delayed application of archived logs

• Partial archived log recovery (recovery from primary database failure)

2.1.5 Location and Directory Structure of Primary and Standby Sites

One crucial aspect of the Data Guard environment is the number and configuration of the systems involved. Of particular importance are whether:

• The primary and standby databases reside on the same site or on different sites

• The primary and standby sites have identical or different directory structures

2.1.5.1 Number and Location of Standby Databases

You can locate a standby database:

• On a different site in the same data center

• On a different site in a different data center, but in the same metropolitan area

• On a different site in a data center in a geographically remote location, for example, on a different continent

• On the same site as the primary database

The location of sites involved in the Data Guard environment has obvious implications for a disaster recovery strategy. For example, if the primary site in a data center is destroyed, then you cannot perform failover to a standby database unless it resides on a different site, 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 system in a remote location.

See Also: Section 6.5 for a disaster recovery scenario

2.1.5.2 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. If you have a standby database on the same site as the primary database, you must use a different directory structure; otherwise, the standby database attempts to overwrite the primary database files.

Use the same path names for the standby files if possible. This option eliminates the need to set filename conversion parameters. Nevertheless, if you need to use a site with a different directory structure or place the standby and primary databases on the same site, you can do so with a minimum of extra administration.

2.1.5.3 Configuration Options

The three basic configuration options are illustrated in Figure 2-1. These include:

• A standby database on the same site as the primary database that uses a different directory structure than the primary site (Standby1)

• A standby database on a separate site that uses the same directory structure as the primary site (Standby2)

• A standby database on a separate site that uses a different directory structure than the primary site (Standby3)

Figure 2-1 Possible Standby Configurations

Text description of the illustration sbr81097.gif

Table 2-1 describes possible configurations of primary and standby databases and the consequences of each.

Table 2-1  Primary and Standby Database Configurations

Standby Site	Directory Structure	Consequences
Same as primary site	Different than primary site (required)	You must set the LOCK_NAME_SPACE initialization parameter. You must rename primary database datafiles in the standby database control file. You can either manually rename the datafiles (see Section B.4) or set up the DB_FILE_NAME_CONVERT initialization parameter on the standby database to automatically rename the datafiles (see Section 4.6). Some operating systems do not permit two instances with the same name to run on the same system. Refer to your platform-specific documentation for more information. The standby database does not protect against disaster.
Separate site	Same as primary site	You do not need to rename primary database filenames in the standby database 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 site	Different than primary site	You must rename primary database datafiles in the standby database control file. You can either manually rename the datafile (see Section B.4) or set up the DB_FILE_NAME_CONVERT initialization parameter on the standby database to automatically rename the datafiles (see Section 4.6). Using separate physical media for your databases safeguards your primary data.

2.1.6 Advantages of Using Multiple Standby Databases

A given standby database must use only one configuration. However, 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 site as the primary database, another standby database on a separate site in the same data center, and a third standby database on a separate site on the other side of the world. You can run each standby database in managed 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 fail over to the standby database that has not yet become corrupted.

2.2 Creating a Standby Database: Basic Tasks

Setting up a standby database for managed recovery requires you to perform a series of different tasks. After you have completed the preparation and initiated managed recovery, the standby database automatically and continuously applies redo logs as they are received from the primary database. Figure 2-2 shows creation of a standby database.

Figure 2-2 Standby Database Creation

Text description of the illustration sbr81101.gif

Table 2-2 summarizes the basic tasks for setting up a standby database and synchronizing it so that it is ready to begin managed recovery.

Note: You can do these steps automatically with the Create Configuration Wizard shipped with the Oracle9i Data Guard Manager.

Table 2-2  Task List: Preparing for Managed Recovery

Step	Task	See ...
1	Either make a new backup of the primary database datafiles or access an old backup.	Section 2.3.2
2	Ensure the primary database is in ARCHIVELOG mode.	Section 2.3.3
3	Connect to the primary database and create the standby control file.	Section 2.3.3
4	Copy the backup datafiles and control file from the primary site to the standby site.	Section 2.3.4
5	Set the initialization parameters for the primary database.	Section 3.5.3
6	Create the standby initialization parameter file and set the initialization parameters for the standby database. Depending on your configuration, you may need to set filename conversion parameters.	Section 3.5.1 and Section 4.6
7	Start the standby instance and mount the standby database.	Section 4.3.1
8	Create standby redo log files, if necessary.	Section 3.6.3.4
9	Manually change the names of the primary datafiles and redo logs in the standby control file for all files not automatically renamed using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT in step 7. If step 7 renamed all files, skip this step.	Section B.4
10	Manually enable initialization parameter changes on the primary database so that it can initiate archival to the standby site.	Section 3.5 and Section 4.6
11	Use the Oracle Net Manager to configure a listener on the standby database. If you plan to manage this standby database using the Data Guard broker, you must configure the listener to use the TCP/IP protocol and statically register the standby database service using its SID.	Oracle9i Net Services Administrator's Guide for information about configuring and administering the listener
12	Use the Oracle Net Manager to create a net service name that the standby database can use to connect to the primary database. The net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and SID that you specified when you configured the listener on the primary database site. If you are unsure what values to use for these parameters, run the Oracle Net Manager on the primary database site to display the listener configuration.	Oracle9i Net Services Administrator's Guide for information about configuring naming methods
13	Use the Oracle Net Manager to create a net service name that the primary database can use to connect to the standby database. The net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and SID that you specified when you configured the listener on the standby database site. If you are unsure what values to use for these parameters, run the Oracle Net Manager on the standby database site to display the listener configuration.	Oracle9i Net Services Administrator's Guide for information about configuring naming methods

2.3 Creating the Standby Database Files

You can create a standby database on the same site as your primary database or on a separate site. If you create your standby database on the same site, follow the creation procedure carefully when creating the standby database files so that you do not overwrite files on the primary database.

The creation of the standby database files occurs in four stages:

1. Using Backups for Standby Creation

2. Creating the Standby Datafiles

3. Creating the Standby Control File

4. Copying Files to the Standby Site

2.3.1 Using Backups for Standby Creation

Each standby database must be created from a backup of the primary database.

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. Figure 2-3 shows that 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. So long as you have the archived redo logs required to perform complete recovery of a backup, it can serve as the basis for a standby database.

Figure 2-3 Creating Standby Databases Using Different Backups

Text description of the illustration sbr81104.gif

2.3.2 Creating the Standby Datafiles

First, make backups of your primary database datafiles. You create the standby datafiles from these backups.

You can use any backup of the primary database so long as you have archived redo logs to completely recover the database. The backup can be old or new, consistent or inconsistent. Hot backups (or open backups) allow you to keep the database open while performing the backup. Nevertheless, you may prefer to make a new closed, consistent backup to prevent the application of a large number of archived redo logs.

To make a consistent, whole database backup to serve as the basis for the standby database:

1. Start a SQL session on your primary database and query the V$DATAFILE fixed view to obtain a list of the primary datafiles. For example, enter:

   SQL> SELECT name FROM v$datafile;
   NAME 
   ----------------------------------------------------------------------------
   /oracle/dbs/tbs_01.dbf
   /oracle/dbs/tbs_02.dbf
   /oracle/dbs/tbs_03.dbf
   /oracle/dbs2/tbs_11.dbf 
   /oracle/dbs2/tbs_12.dbf
   /oracle/dbs3/tbs_21.dbf
   /oracle/dbs3/tbs_22.dbf 
   7 rows selected.
   

2. Shut down the primary database cleanly:

   SQL> SHUTDOWN;
   

3. Make a consistent backup of the datafiles from your primary database using the Recovery Manager utility (RMAN) or an operating system utility.

4. Reopen the primary database. For example, enter:

   SQL> STARTUP pfile=initPRIMARY1.ora;
   

   See Also: Oracle9i User-Managed Backup and Recovery Guide to learn how to make operating system backups and Oracle9i Recovery Manager Reference for more information on the STARTUP statement

2.3.3 Creating the Standby Control File

After you have created the backups that will be used as the standby datafiles, you can create the standby database control file. The control file must be created after the latest time stamp for the backup datafiles.

Note: You cannot use a single control file for both the primary and standby databases. The standby instance is independent from the primary instance and so requires exclusive possession of its database files.

To create the standby database control file:

1. Ensure that the primary database is in ARCHIVELOG mode and that archiving is enabled. Either issue the SQL*Plus ARCHIVE LOG LIST command or query the V$DATABASE view. Take the following steps:
   1. Start and mount the primary database without opening it. For example:

      SQL> STARTUP MOUNT PFILE=initPRIMARY1.ora
      

   2. Issue the SQL*Plus ARCHIVE LOG LIST command to determine if the database is in ARCHIVELOG mode. For example:

      SQL> ARCHIVE LOG LIST;
      Database log mode              No Archive Mode
      Automatic archival             Disabled
      Archive destination            /oracle/dbs/arch
      Oldest online log sequence     0
      Current log sequence           1
      

   3. If the database is not in ARCHIVELOG mode, as shown in step b, issue the following command to place the database in ARCHIVELOG mode:

      SQL> ALTER DATABASE ARCHIVELOG;
      

   4. You can issue the SQL*Plus ARCHIVE LOG LIST command again to verify the database has been placed in ARCHIVELOG mode. For example:

      SQL> ARCHIVE LOG LIST;
      Database log mode              Archive Mode
      Automatic archival             Disabled
      Archive destination            /oracle/dbs/arch
      Oldest online log sequence     0
      Next log sequence to archive   1
      Current log sequence           1
      

   To enable the automatic archival of the online redo logs, you must set LOG_ARCHIVE_START=true in the initialization parameter file. However, this does not have to be done before you create the standby control file.

   See Also: SQL*Plus User's Guide and Reference for additional information on the ARCHIVE LOG LIST command and Oracle9i Database Administrator's Guide for additional information on the ALTER DATABASE ARCHIVELOG statement and the LOG_ARCHIVE_START initialization parameter

2. Connect to the primary database and create the control file for your standby database. For example, to create the standby control file as /oracle/dbs/stbycf.ctl on the primary site, enter the following:

   SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/dbs/stbycf.ctl';
   

   The filename for the created standby control file must be different from the filename of the current control file of the primary database. You can also use RMAN to create the standby database control file.

   See Also: Oracle9i SQL Reference for additional information on the ALTER DATABASE statement and Oracle9i Recovery Manager User's Guide for additional information on RMAN

2.3.4 Copying Files to the Standby Site

After you have successfully created the standby datafiles and control file, copy the files to the standby site using an operating system utility.

If the standby database is on	Then you
A separate site with the same directory structure as the primary database	Can use the same path names for the standby files as the primary files. In this way, you do not have to rename the primary datafiles in the standby control file.
The same site as the primary database, or the standby database is on a separate site with a different directory structure	Must rename the primary datafiles in the standby control file after copying them to the standby site. You can: Set the filename conversion initialization parameters. See Section 3.5.1 and Section 4.6. Rename the files manually using ALTER DATABASE statements. See Section B.4. Use a combination of conversion parameters and manual renames.

To copy datafiles and the control file to the standby site:

Copy the created control file and datafile backups to the standby site using operating system commands or utilities. Use an appropriate method for copying binary files.

1. Copy the control file.

2. Copy the backup datafiles.

3. Copy all available archived redo logs to the standby site.

4. Copy the online redo logs. This is recommended for switchover and failover operations.

   See Also: Section 5.3.4 and Section 5.4.7.2

Do not copy temporary tablespaces. See Section 4.8.2.1 for more information about creating temporary tablespaces.

2.4 Creating the Standby Initialization Parameter File

Once you have configured the primary database initialization parameter file, you can duplicate the file for use by the standby database. The procedure for creating the standby initialization parameter file is as follows:

1. Copy the initialization parameter file for the primary database using an operating system utility.

2. Edit the initialization parameter file for use by the standby database.

3. Transfer the initialization parameter file to the standby site using an appropriate operating system utility.

   See Also: Section 3.5.1, Section 4.6, and Section 6.2.6