F Creating a Standby Database with Recovery Manager

F.1.1 About Standby Database Preparation Using RMAN

You can use either manual methods or the Recovery Manager DUPLICATE command to create a standby database from backups of your primary database. Before you perform the creation procedure, you must prepare the standby instance. You can use RMAN to do the preparation tasks described in Table F-1.

In addition to the RMAN tasks described in Table F-1, you must perform the following tasks to set up your standby database:

• Set all necessary initialization parameters on the primary database.

• Create an initialization parameter file for the standby database and configure all necessary parameters.

• Set up and configure Oracle Net, as required, to connect to the standby instance.

• Start the standby instance without mounting the control file.

See Chapter 3 for a complete discussion of physical standby database preparation, including initialization parameter settings. You must perform all necessary preparation tasks described in these chapters before RMAN can successfully create the standby database files and mount the standby database.

F.1.2 Creating the Standby Control File with RMAN

You can create the standby control file using either the RMAN BACKUP or COPY commands by performing the following steps:

Step 1   Connect to the primary database.

Connect to the primary database and, if desired, the recovery catalog database. For example, enter:

% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb

Step 2   Create the standby control file.

Use either of the following commands to create the standby control file. The only difference between BACKUP and COPY commands is that the file format of the backup file is different.

• Using the BACKUP command

  Mount the primary database and create the standby control file with the BACKUP CURRENT CONTROLFILE FOR STANDBY command. The following example uses a configured channel to create the standby control file. Then open the database, archive all unarchived redo log files, and back up any log files that have not yet been backed up at least once:

  STARTUP MOUNT
  BACKUP CURRENT CONTROLFILE FOR STANDBY;
  SQL> ALTER DATABASE OPEN;
  SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';  # so backup is consistent and recoverable
  BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
  

• Using the COPY command

  Copy the current primary control file. Specify the FOR STANDBY option of the COPY CURRENT CONTROLFILE command to make a copy of the current control file that is usable as a standby control file. For example:

  COPY CURRENT CONTROLFILE FOR STANDBY TO '/tmp/sby_control01.ctl';
  

Step 1   List the backup sets or image copies.

If desired, issue a LIST command to see a listing of the backup sets and pieces, or issue a LIST COPY command to see a listing of the image copies.

F.1.3 Naming the Standby Database Datafiles When Using RMAN

A standby database can reside either on the same host as the primary database or on a different host. The following table illustrates the implications for renaming the standby database datafiles depending on if the directory structures on the hosts are the same or different.

When the directory structures are different for the primary and standby hosts, you have these options for naming the standby datafiles:

• Configuring the standby database initialization parameter DB_FILE_NAME_CONVERT

• Use the DB_FILE_NAME_CONVERT option of the RMAN DUPLICATE command

• By using the RMAN CONFIGURE AUXNAME or SET NEWNAME command when creating the standby database

When the directory structures are the same for the primary and standby hosts, then you have these naming options:

• Leaving the standby file names the same as the primary file names (that is, not setting DB_FILE_NAME_CONVERT or issuing a CONFIGURE AUXNAME or SET NEWNAME command) and specifying the NOFILENAMECHECK option of the DUPLICATE command

• By using the DB_FILE_NAME_CONVERT parameter, or the CONFIGURE AUXNAME or SET NEWNAME commands to rename the standby datafiles

Note that when you use DB_FILE_NAME_CONVERT, the format is as follows:

DB_FILE_NAME_CONVERT = 'oldstring1', 'newstring1', 'oldstring2', 'newstring2', ...

For example, you can specify the DB_FILE_NAME_CONVERT initialization parameter as follows:

DB_FILE_NAME_CONVERT = '/dbs/t1/', '/dbs/t1/s_', '/dbs/t2/', '/dbs/t2/s_'

Because you can specify datafile file names in the standby control file in multiple ways, a method for prioritizing settings is necessary. Table F-2 specifies the hierarchy for the naming of datafiles in the standby database.

See Oracle Database Reference for more information about how to use DB_FILE_NAME_CONVERT to name files on a standby database.

F.1.4 Naming the Standby Database Log Files When Using RMAN

Redo log files are not created on the standby database by RMAN. However, as described in Chapter 3, log files can be created by other actions that you perform on the standby database. After the log files are created, they are maintained and archived according to the normal rules for log files.

The only option when naming the redo log files on the standby database is the file names for the log files, as specified in the standby control file. If the log file names on the standby must be different from the primary file names, then one option is to specify file names for the redo logs by setting LOG_FILE_NAME_CONVERT in the standby initialization parameter file.

Note these restrictions when specifying file names for the redo log files on the standby database:

• You must use the LOG_FILE_NAME_CONVERT parameter to name the redo log files if the primary and standby databases use different naming conventions for the log files.

• You cannot use the SET NEWNAME or CONFIGURE AUXNAME commands to rename the redo log files.

• You cannot use the LOGFILE clause of the DUPLICATE command to specify file names for the redo log files.

• If you want the redo log file names on the standby database to be the same as the primary redo log file names, then you must specify the NOFILENAMECHECK clause of the DUPLICATE command. Otherwise, RMAN signals an error even if the standby database is created in a different host.

F.2.1 RMAN Standby Creation Without Recovery

By default, RMAN does not recover the standby database after creating it. If you do not specify the DORECOVER option of the DUPLICATE command, then RMAN automates these steps of the standby creation procedure during duplication:

1. RMAN establishes connections both to the primary and standby databases, and the recovery catalog (if used).

2. RMAN queries the repository, which is either the primary control file or the recovery catalog, to identify the backups of primary database datafiles and the standby control file.

3. If you use a media manager, then RMAN contacts the media manager on the standby host to request the backup data.

4. RMAN restores the standby control file to the standby host, thereby creating the standby control file.

5. RMAN restores the primary datafile backups and copies to the standby host, thereby creating the standby database datafiles.

6. RMAN leaves the standby database mounted, but does not place the standby database in manual or managed recovery mode. RMAN disconnects and does not perform media recovery of the standby database. Note that you should not register the standby database in the recovery catalog.

F.2.2 RMAN Standby Creation with Recovery

If you do specify the DORECOVER option of the DUPLICATE command, then RMAN performs the same Steps 1-5 in Section F.2.1. Instead of Step 6, it performs these steps:

1. After all data is restored, RMAN begins media recovery. If recovery requires archived redo log files, and if the log files are not already on disk, RMAN attempts to restore it from backups.

2. RMAN recovers the standby database to the specified time, system change number (SCN), or log file sequence number, or to the latest archived redo log file generated if none of the preceding are specified.

3. RMAN leaves the standby database mounted after media recovery is complete, but does not place the standby database in manual or managed recovery mode. Note that you should not register the standby database in the recovery catalog.

   Note:

   After RMAN creates the standby database, you must resolve any gap sequence before placing it in manual or managed recovery mode, or opening it in read-only mode. Section 5.8 discusses gap sequence resolution in detail.

If you want RMAN to recover the standby database after creating it, then the standby control file must be usable for the desired recovery. Thus, these conditions must be met:

• The end recovery time of the standby database must be greater than or equal to the checkpoint SCN of the standby control file.

• An archived redo log file containing the checkpoint SCN of the standby control file must be available at the standby site for recovery.

One way to ensure these conditions are met is to issue the ALTER SYSTEM ARCHIVE LOG CURRENT statement after creating the standby control file. This statement archives the online redo log files of the primary database. Then, either back up the most recent archived redo log file with RMAN or move the archived redo log file to the standby site.

See Oracle Database Backup and Recovery Reference for the list of DUPLICATE restrictions for creating a standby database with RMAN.

F.3.1 Setting Up a Standby Database When Files Are Not Oracle Managed Files

Perform the following steps to set up a standby database that does not use OMF:

1. Use an operating system utility to copy the SPFILE (or the initialization parameter file) from the target host to the standby host. Set all required parameters in the standby database's initialization parameter file, as described in Section 3.2.3.

   For example, if creating the standby database on a separate host with a different directory structure, edit these initialization parameters:

   • Those that end with _DEST and _PATH and specify a path name

   • DB_FILE_NAME_CONVERT so that it captures all the target datafiles and converts them appropriately, for example, from tbs_* to sbytbs_*

   • LOG_FILE_NAME_CONVERT so that it captures all the redo log files and converts them appropriately, for example, log_* to sbylog_*

   For example, the following are sample parameter settings in the standby database initialization parameter file:

   STANDBY_ARCHIVE_DEST = /fs3/arc_dest/
   LOG_ARCHIVE_FORMAT = log%d_%t_%s_%r.arc
   DB_FILE_NAME_CONVERT = '/oracle', '/fs3/oracle', '/dbf', '/fs3/oracle'
   LOG_FILE_NAME_CONVERT = '/oracle', '/fs3/oracle'
   

2. Use SQL*Plus to start the standby instance without mounting it. For example, enter the following to connect to sbdb1 as SYS (who has SYSDBA privileges) and start the database:

   SQL> CONNECT SYS/sys_pwd@sbdb1 AS SYSDBA
   SQL> STARTUP NOMOUNT PFILE=initSBDB1.ora
   

3. Use SQL*Plus to mount or open the primary database if it is not already mounted or open. For example, enter the following to connect to prod1 as SYS and open the database:

   SQL> CONNECT SYS/sys_pwd@prod1 AS SYSDBA
   SQL> STARTUP PFILE=initPROD1.ora
   

   Ensure the recovery catalog database is open. For example, enter the following to connect to catdb as SYS and open the recovery catalog database:

   SQL> CONNECT SYS/oracle@catdb AS SYSDBA
   SQL> STARTUP PFILE=initCATDB.ora
   

4. The standby instance must be accessible through Oracle Net. Before proceeding, use SQL*Plus to ensure you can establish a connection to the standby instance. Note that you must connect to the standby instance with SYSDBA privileges, so a password file must exist.

5. Connect to the target database, the standby instance, and (if you use one) the recovery catalog database. Note that you specify the primary database with the TARGET keyword and the standby instance with the AUXILIARY keyword.

   In the following example, connection is established without a recovery catalog by using operating system authentication:

   % rman TARGET / AUXILIARY SYS/sys_pwd@sbdb1
   

F.3.2 Setting Up a Standby Database When All Files Are Oracle Managed Files

If the standby database uses OMF for all files, you must set the following parameters on the standby database or auxiliary instance before you perform the RMAN duplication. Oracle recommends using a server parameter file (SPFILE) when the database control files are OMF files. Otherwise, at end of the following procedure, be sure to update the CONTROL_FILE initialization parameter with the value of the control file names column in V$CONTROLFILE view.

Required initialization parameter changes:

1. Set up a different DB_NAME parameter for duplicate databases and a different DB_UNIQUE_NAME parameter for standby databases

2. Do not set the LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT parameters

3. Do not set the CONTROL_FILE parameter (if the database control files are to be OMF files)

4. Set the DB_CREATE_FILE_DEST parameter (all datafiles, control file and online redo log will be created in this destination)

5. Set the STANDBY_FILE_MANAGEMENT=AUTO parameter (for standby database duplication only)

You can use the ALTER SYSTEM RESET statement to clear initialization parameters in the SPFILE for steps 2 and 3. For example:

ALTER SYSTEM RESET CONTROL_FILE SCOPE=SPFILE SID='*'

Optional initialization parameter changes:

Set the DB_CREATE_FILE_DEST parameter to create one copy of the control file and one member of the online redo log. To create more than one control file and online redo log file, set the DB_CREATE_ONLINE_LOG_DEST_n (where n can be an integer from 1 to 5) parameter, depending on the number of multiplexed copies you require. The control file and online redo log files are not created in DB_CREATE_FILE_DEST when one DB_CREATE_ONLINE_LOG_DEST_n parameter is set. For example, if you set DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2, then you will get two copies of the control file and online redo log file on each destination.

F.3.3 Setting Up a Standby Databases When a Subset of Files Are Oracle Managed Files

If only some of the primary database files are OMF files, or if there are datafiles spread across more than one disk group, you may want to create a duplicate database with exactly the same structure.

For example, fast datafiles may reside in +'FAST'disk group and slow datafiles may reside in '+SLOW' disk group. After duplicating the database, you would like to have its datafiles reside in '+FAST2' disk group and its slow datafiles reside in '+SLOW2' disk group. To set up the standby database, configure the LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT parameters as ('+FAST', '+FAST2', '+SLOW', '+SLOW2'). If you want the database to be in the same disk group but with a different name, then change the middle part of the name. For example, use ('/boston/', '/sfo/') where boston is the DB_UNIQUE_NAME of the primary database and sfo is the DB_UNIQUE_NAME of the duplicate database.Use the following steps to set initialization parameters on the standby database or auxiliary instance before performing the RMAN duplication.

Required initialization parameter changes:

1. Set up a different DB_NAME parameter for duplicate database and a different DB_UNIQUE_NAME for standby database

2. Set up the LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT parameters to convert file names

3. Modify the CONTROL_FILE parameter to specify new control files

F.4.1 Creating the Standby Database Without Performing Recovery

To create the standby database without performing recovery, do not specify the DORECOVER option on the DUPLICATE command. By default, RMAN leaves the standby database mounted and does not recover it.

To create a standby database without performing recovery:

1. Follow the steps in Section F.3, "Setting Up the Standby Database". Make sure to set all necessary parameters in the standby initialization parameter file.

2. Follow these steps during duplication to create but not recover the standby datafiles:

   1. If automatic channels are not configured, then allocate at least one auxiliary channel. This channel performs the work of duplication.

   2. Specify NOFILENAMECHECK in the DUPLICATE command. The NOFILENAMECHECK option is required when the standby and primary datafiles and log files have the same names. Otherwise, RMAN returns an error.

   For example, run the following command to create the standby database:

   DUPLICATE TARGET DATABASE FOR STANDBY 
     NOFILENAMECHECK;
   

F.4.2 Creating the Standby Database and Performing Recovery

To create the standby database and perform recovery, specify the DORECOVER option on the DUPLICATE command.

To create a standby database and perform recovery:

1. Follow the steps in Section F.3. Make sure to set all necessary parameters in the standby initialization parameter file.

2. Follow these steps to restore and recover the standby datafiles:

   1. Ensure the end recovery time is greater than or equal to the checkpoint SCN of the standby control file and that a log file containing the checkpoint SCN is available for recovery.

   2. If desired, issue a SET command to specify the end time, SCN, or log sequence number for incomplete recovery.

   3. If automatic channels are not configured, then manually allocate at least one auxiliary channel.

   4. Specify the NOFILENAMECHECK parameter in the DUPLICATE command, and use the DORECOVER option.

   For example, enter the following at the RMAN prompt to use a configured channel to create the standby database:

   # If desired, issue a LIST command to determine the SCN of the standby control file.
   # The SCN to which you recover must be greater than or equal to the standby control 
   # file SCN.
   LIST BACKUP OF CONTROLFILE;
   LIST COPY OF CONTROLFILE;
   
   RUN
   {
     # If desired, issue a SET command to terminate recovery at a specified point. 
     # SET UNTIL SCN 143508;
     DUPLICATE TARGET DATABASE FOR STANDBY 
       NOFILENAMECHECK
       DORECOVER;
   }
   

   RMAN uses all incremental backups, archived redo log file backups, and archived redo log files to perform incomplete recovery. The standby database is left mounted.

F.5.1 Naming Standby Database Files with DB_FILE_NAME_CONVERT

In this procedure, you use the DB_FILE_NAME_CONVERT parameter to name the standby datafiles and the LOG_FILE_NAME_CONVERT parameter to name the redo log files on the standby database. See Section 3.1.4 for examples of how to use the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters to name standby database files.

F.5.2 Naming Standby Database Files with SET NEWNAME

In this procedure, you use SET NEWNAME commands to name the standby datafiles.

F.5.3 Naming Standby Database Files with CONFIGURE AUXNAME

In this procedure, you use CONFIGURE AUXNAME commands to name the standby datafiles.

F.7.1 Overview

The main restriction when using RMAN image copies to create the standby datafiles is that the image copy file names for datafiles and archived redo log files on the primary and standby hosts must be the same. For example, assume that datafile 1 is named /oracle/dbs/df1.f on the primary host. If you use the RMAN COPY command to copy this datafile to /data/df1.f, then this image copy must exist on the standby host with the same file name of /data/df1.f. Otherwise, RMAN cannot locate the metadata for the standby image copy in its repository.

You have two main ways of populating the standby host with the image copies:

• Transferring them manually with ftp or some other utility

• Mounting the standby directory structure on the primary host with a network file system (NFS)

When you use the NFS method, you can create a directory on the primary host that maps to a directory on the standby host. If you use this method, then the NFS mount point on both machines must have the same directory name. For example, you can map /data on the primary host to /data on the standby host, but you cannot map /data on the primary host to /dir on the standby host (unless you use functionality such as symbolic links in UNIX or logical drives on Windows).

The file name of the image copy on the standby host must be the same as the file name of the image copy on the primary host. Nevertheless, you can specify a different path name for the standby datafile by using SET NEWNAME commands or the DB_FILE_NAME_CONVERT initialization parameter.

For example, although the image copy of datafile 1 is named /data/df1.f on the standby host, you can specify the path name /oracle/sb/df1.f in the standby control file by using initialization parameters or RMAN commands. Note that you do not manually rename the physical image copy. When you run the DUPLICATE command, RMAN restores the image copy /data/df1.f and creates the standby datafile 1 as /oracle/sb/df1.f based on the information in the initialization parameters or RMAN commands.

Table F-3 illustrates two scenarios for using NFS to create a standby database with one datafile.

Table F-3 assumes that the standby directory structure is mounted on the primary host, and that the mount point is /data on both hosts. Because the primary host mounts the standby host directory structure, when you create the image copy /data/df1.f on the primary host, you are actually creating the image copy /data/df1.f on the standby host.

In the first scenario, you name the standby datafiles with the same file names as the image copies. This case is the simplest because you do not need to use RMAN at all to create the standby database. First, set the DB_FILE_NAME_CONVERT parameter in the standby initialization parameter file to convert the primary datafile file name /oracle/dbs/df1.f to the standby file name /data/df1.f. Then, copy the files to the standby host, and mount the standby database.

In the second scenario, you use different file names for the standby datafiles and the image copies. To create this standby database, run the DUPLICATE command. The DUPLICATE command restores the image copy of datafile 1 and renames it according to either the SET NEWNAME commands or the DB_FILE_NAME_CONVERT initialization parameter.

F.7.2 When Copies and Datafiles Use the Same Names

This procedure assumes that you are using the same file names for the standby datafiles and the image copies of the primary datafiles.

To create a standby database when the copies and standby datafiles have the same names:

1. After connecting to the primary database, and if desired, the recovery catalog database, mount but do not open the primary database and ensure the database was closed cleanly before mounting. For example, enter:

   RMAN> STARTUP MOUNT PFILE=init.ora;
   

2. Make sure that you set DB_FILE_NAME_CONVERT in the standby initialization parameter file so that standby datafile file names are translated from the primary datafile file names. For example:

   DB_FILE_NAME_CONVERT = '/oracle/dbs', '/dsk2/oracle'
   

3. Copy all of the datafiles and the standby control file. For example, enter:

   COPY 
     DATAFILE 1 TO '/dsk2/oracle/df_1.f',
     DATAFILE 2 TO '/dsk2/oracle/df_2.f',
     DATAFILE 3 TO '/dsk2/oracle/df_3.f',
     DATAFILE 4 to '/dsk2/oracle/df_4.f',
     DATAFILE 5 TO '/dsk2/oracle/df_5.f',
     DATAFILE 6 TO '/dsk2/oracle/df_6.f',
     DATAFILE 7 TO '/dsk2/oracle/df_7.f',
     DATAFILE 8 to '/dsk2/oracle/df_8.f',
     DATAFILE 9 TO '/dsk2/oracle/df_9.f',
     DATAFILE 10 TO '/dsk2/oracle/df_10.f',
     DATAFILE 11 TO '/dsk2/oracle/df_11.f',
     DATAFILE 12 to '/dsk2/oracle/df_12.f',
     CURRENT CONTROLFILE FOR STANDBY TO '/dsk2/oracle/cf.f';
   

4. Start the standby instance and mount the standby control file. For example, start SQL*Plus and enter:

   SQL> STARTUP NOMOUNT PFILE=/dsk2/oracle/dbs/initSTANDBY1.ora
   SQL> ALTER DATABASE MOUNT;
   

F.7.3 When Copies and Datafiles Use Different Names

This procedure assumes that you use different file names for the standby datafiles and the image copies of the primary datafiles.