23 Duplicating a Database

Purpose of Database Duplication

The goal of database duplication is the creation of a duplicate database, which is a separate database that contains all or a subset of the data in the source database. A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:

• Test backup and recovery procedures

• Test an upgrade to a new release of Oracle Database

• Test the effect of applications on database performance

• Generate reports

• Export data such as a table that was inadvertently dropped from the production database, and then import it back into the production database

For example, you can duplicate the production database on host1 to host2, and then use the duplicate database on host2 to practice restoring and recovering this database while the production database on host1 operates as usual.

If you copy a database by means of operating system utilities rather than with DUPLICATE, then the DBID of the copied database remains the same as the original database. To register the copy database in the same recovery catalog with the original, you must change the DBID with the DBNEWID utility (refer to Oracle Database Utilities). In contrast, the DUPLICATE command automatically assigns the copied database a different DBID so that it can be registered in the same recovery catalog as the database from which it was copied.

A duplicate database serves a different purpose from a physical standby database, although both are created with the DUPLICATE command. A standby database is a copy of the primary database that you update continually with archived logs from the primary database. If the primary database is inaccessible, then you can perform failover to the standby database and make it the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options.

Basic Concepts of Database Duplication

You create a duplicate database by using the RMAN DUPLICATE command. The duplicate database is the copied database, whereas the source database is the database that you are copying. The duplicate database has a different DBID from the source database and functions entirely independently.

The source host is the computer that hosts the source database, whereas the destination host is the computer that hosts the duplicate database. The source host and destination host can be the same or different. The source database instance is the Oracle instance associated with the source database. The instance associated with the duplicate database is called the auxiliary instance.

A duplicate database can include the same contents as the source database or contain only a subset of the tablespaces in the source database. For example, you can use the TABLESPACE option to duplicate only specified tablespaces, or the SKIP READONLY option to exclude read-only tablespaces from the duplicate database.

You can use either of the following techniques: active database duplication or backup-based duplication. Active database duplication copies the live source database over the network to the duplicate database instance, whereas backup-based duplication uses pre-existing RMAN backups and copies.

The principal work of the duplication is performed by an auxiliary channel. This channel corresponds to a server session on the auxiliary instance on the destination host. As part of the duplicating operation, RMAN automates the following steps:

1. Creates a control file for the duplicate database

2. Restarts the auxiliary instance and mounts the duplicate control file

3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs

   RMAN must perform incomplete recovery because the online redo log files in the target are not backed up and cannot be applied to the duplicate database. The farthest that RMAN can go in recovery of the duplicate database is the most recent redo log archived by the source database.

4. Opens the duplicate database with the RESETLOGS option

Basic Steps of Database Duplication

Before duplicating a database you must meet a number of prerequisites. For example, the source and duplicate databases must be on the same platform. Review the "Prerequisites" section of the DUPLICATE command entry in the Oracle Database Backup and Recovery Reference for a complete list.

To duplicate a database:

1. If you use backup-based duplication, then make database backups and archived redo logs available to the auxiliary instance on the destination host. If you use active database duplication, then skip this step.

   This task is described in "Making Backups and Archived Logs Accessible to the Duplicate Instance".

2. Decide how to provide names for the duplicate control files, datafiles, online redo logs, and tempfiles. If you are duplicating to a different host that uses the same directory structure as the source host, and if you want to name the duplicate files the same as the source database files, then skip to the next step.

   This task is described in "Choosing a Strategy for Naming Duplicate Files".

3. Create an initialization parameter file for use by the auxiliary instance on the destination host, and then start the auxiliary instance.

   This task is described in "Preparing the Auxiliary Instance".

4. Start RMAN, connect as TARGET to the source database, as AUXILIARY to the instance of the duplicate database, and optionally as CATALOG to the recovery catalog database. If necessary configure channels for use by the DUPLICATE command.

   This task is described in "Starting and Configuring RMAN Before Duplication".

5. Execute the DUPLICATE command.

   This task is described in "Duplicating a Database".

Making SBT Backups Accessible to the Duplicate Instance

When using SBT backups, make the tapes with the backups accessible to the destination host. If media management software is installed on the destination host, then you can physically move the tape to a drive attached to the remote host or use a network-accessible tape server. Depending on the media manager, you typically must inform the remote media management software about the existence of the tapes.

Making Disk Backups Accessible to the Duplicate Instance

When using disk backups, the simplest scenario is when the file system of the source host and destination hosts are identical. For example, assume that the backups of the source database are stored in /dsk1/bkp. In this case, you can make disk backups accessible to the destination host in either of the following ways:

• Manually transfer the backups and copies from the source host to the destination host to an identical path. For example, if the backups are in /dsk1/bkp on the source host, then FTP them to /dsk1/bkp on the destination host.

• Use NFS or shared disks and make sure that the same path is accessible in the destination host. For example, use NFS to mount /dsk1/bkp on the destination host and use /dsk1/bkp as the mount point name.

A more complicated scenario occurs when you cannot use the same directory name on the destination host as you use on the source host. As explained in the following sections, the technique for making backups accessible in this scenario depends on whether you use shared disk.

Step 1: Create an Oracle Password File for the Auxiliary Instance

A password file is required for the auxiliary instance only if one of the following conditions is true:

• You use the RMAN client on a host other than the destination host.

• You duplicate from an active database.

When using the FROM ACTIVE DATABASE option, the source database instance, which is the instance to which RMAN is connected as TARGET, connects directly to the auxiliary database instance. This connection requires a password file with the same SYSDBA password. You can create the password file manually, making sure to use the same SYSDBA password as the source database. You may want to create the password file with a single password so you can start the auxiliary instance and enable the source database to connect to it.

Additionally, you can specify the PASSWORD FILE option on the DUPLICATE command. In this case, RMAN copies the source database password file to the destination host and overwrites any existing password file for the auxiliary instance. This technique is useful if the source database password file has multiple passwords that you want to make available on the duplicate database.

If you are creating a standby database with active database duplication, then RMAN always copies the password file to the standby host. You do not need to specify the PASSWORD FILE option. RMAN overwrites any existing password file for the auxiliary instance.

To create a password file:

• Follow the instructions in Oracle Database Administrator's Guide to create a password file. Note that the types of filenames allowed for password file are operating system-specific.

Step 2: Establish Oracle Net Connectivity to the Auxiliary Instance

The auxiliary instance must be available through Oracle Net if either of the following conditions is met:

• You use the RMAN client on a host other than the destination host.

• You duplicate from an active database.

When duplicating from an active database, you must first have connected as SYSDBA to the auxiliary instance by means of a net service name. This net service name must also be available on the source database instance. The source database instance, to which RMAN is connected as TARGET, uses this net service name to connect directly to the auxiliary database instance.

Step 3: Create an Initialization Parameter File for the Auxiliary Instance

Create a text-based initialization parameter file for the auxiliary instance.

If you are using the SPFILE technique for naming duplicate files described in "Choosing a Strategy for Naming Duplicate Files", then the only necessary parameter is DB_NAME, which you can set to an arbitrary value. You do not need to set parameters such as DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT in the initialization parameter file because you can set these and all other parameters in the DUPLICATE command itself.

If you are not using the SPFILE technique, then you need to set initialization parameters to set in the initialization parameter file. Table 23-1 describes these initialization parameters.

If necessary, set other initialization parameters, including the parameters that allow you to connect as SYSDBA through Oracle Net, as needed.

When duplicating to the same host or to a new host with a different file system, pay attention to all initialization parameters specifying path names. Verify that all paths are accessible on the host where the database is being duplicated.

Example 23-2 shows sample settings for a sample initialization parameter file.

DB_NAME=dupdb
CONTROL_FILES=(/dup/oracle/oradata/prod/control01.ctl,
               /dup/oracle/oradata/prod/control02.ctl)
DB_FILE_NAME_CONVERT=(/oracle/oradata/prod/,/dup/oracle/oradata/prod/)
LOG_FILE_NAME_CONVERT=(/oracle/oradata/prod/redo,/dup/oracle/oradata/prod/redo)

Step 4: Start the Auxiliary Instance with SQL*Plus

Before beginning RMAN duplication, use SQL*Plus to connect to the auxiliary instance and start it in NOMOUNT mode (specifying the PFILE parameter if necessary). In this example, oracle is the password for the user with SYSDBA authority and aux is the net service name for the auxiliary instance:

CONNECT SYS/password@aux AS SYSDBA
-- start instance with the server parameter file
STARTUP NOMOUNT

Because the auxiliary instance does not yet have a control file, you can only start the instance in NOMOUNT mode. Do not create a control file or try to mount or open the auxiliary instance.

RMAN shuts down and restarts the auxiliary instance as part of the duplication. Thus, it is a good idea to have a server-side initialization parameter file for the auxiliary instance in the default location on the destination host.

If you use the SPFILE clause of the DUPLICATE command, then RMAN copies or restores the source database server parameter file to the default location for the auxiliary instance on the destination host. If you do not use the SPFILE clause, then either copy the server parameter file to the destination host or specify a text-based initialization parameter file with the PFILE parameter on the DUPLICATE command. Note that the text-based initialization parameter file for the auxiliary instance must reside on the same host as the RMAN client used to perform the duplication.

Step 1: Start RMAN and Connect to the Database Instances

Start RMAN and connect to the source database as TARGET, the duplicate database instance as AUXILIARY, and, if applicable, the recovery catalog database. You can start the RMAN client on any host so long as it can connect to all of the database instances. If the auxiliary instance requires a text-based initialization parameter file, then this file must exist on the same host that runs the RMAN client application.

To start RMAN and connect to the target and auxiliary instances:

1. Start the RMAN client.

   For example, enter the following command at the operating system prompt:

   % rman
   

2. At the RMAN prompt, execute CONNECT commands for each database instance.

   In this example, a connection is established to three database instances, all through the use of net service names:

   RMAN> CONNECT TARGET SYS/password@prod     # source database
   RMAN> CONNECT AUXILIARY SYS/password@aux   # duplicate database instance
   RMAN> CONNECT CATALOG rman/password@catdb     # recovery catalog database
   

Step 2: Mount or Open the Source Database

Before beginning RMAN duplication, mount or open the source database it if it is not already mounted or open. If the source database is open, then archiving must be enabled. If the source database is not open, and if it is not a standby database, then it must have been shut down consistently.

Step 3: Configure RMAN Channels for Use in the Duplication

If necessary for backup-based duplication, configure channels to be used on the auxiliary instance. Note that it is the channel on the auxiliary instance, not the target instance, that restores backups.

RMAN can use the same channel configurations set on the source database for duplication even if they do not specify the AUXILIARY option. If the auxiliary channels need special parameters (for example, to point to a different media manager), however, then you can configure an automatic channel with the AUXILIARY option of the CONFIGURE command.

In backup-based duplication, the channel type (DISK or sbt) of the channel must match the media where the backups of the source database are located. If the backups reside on disk, then the more channels you allocate, the faster the duplication will be. For tape backups, limit the number of channels to the number of devices available.

In active database duplication, you do not have to change your source database channel configuration or configure AUXILIARY channels. However, you may want to increase the parallelism setting of your source database disk channels so that RMAN copies files over the network in parallel.

Duplicating a Database to a Remote Host with the Same Directory Structure

The simplest case is to use active database duplication to duplicate the database to a different host and use the same directory structure. If the source database uses a server parameter file (or a backup is available), then you can create a temporary initialization parameter file on the destination host and set only the DB_NAME parameter. You do not have to set additional parameters for specifying duplicate database filenames or transfer backups to the destination host.

To duplicate a database to a remote host with the same directory structure:

1. Follow the steps in "Preparing the Auxiliary Instance".

   In this example, the initialization parameter file contains only DB_NAME set to an arbitrary value.

2. Follow the steps in "Starting and Configuring RMAN Before Duplication".

   In this example, the database is open. RMAN has automatic channels already configured. You connect to the database instances as follows:

   CONNECT TARGET SYS/password@prod
   CONNECT AUXILIARY SYS/password@dupdb
   

3. Execute the DUPLICATE command.

   Example 23-3 illustrates how to use DUPLICATE for active duplication. This example requires the NOFILENAMECHECK option because the source database files have the same names as the duplicate database files.

   Example 23-3 Duplicating to a Host with the Same Directory Structure

   DUPLICATE TARGET DATABASE
     TO dupdb
     FROM ACTIVE DATABASE
     SPFILE
     NOFILENAMECHECK;
   

   RMAN automatically copies the server parameter file to the destination host, starts the auxiliary instance with the server parameter file, copies all necessary database files and archived redo logs over the network to the destination host, and recovers the database. Finally, RMAN opens the database with the RESETLOGS option to create the online redo logs.

Assume a slightly different case in which you want to recover the duplicate database to one week ago in order to view the data in the source database as it appeared at that time. Example 23-4 uses backup-based duplication to create a duplicate of the source database as it appeared one week ago. The UNTIL clause is not supported in active database duplication.

CONNECT TARGET SYS/password@prod      # source database
CONNECT AUXILIARY SYS/password@dupdb  # duplicate database instance
DUPLICATE TARGET DATABASE 
  TO dupdb 
  PASSWORD FILE
  SPFILE
  NOFILENAMECHECK
  UNTIL TIME 'SYSDATE-7';

Note that the command in Example 23-4 specifies the PASSWORD FILE option to indicate that RMAN should duplicate the password file from the source database.

Duplicating a Database to a Remote Host with a Different Directory Structure

If you create the duplicate database on a host with a different directory structure, then you must use some technique to generate filenames for the duplicate database datafiles. The simplest technique is to use active database duplication and to use the SPFILE clause to rename files as explained in "Choosing a Strategy for Naming Duplicate Files". If the source database uses a server parameter file (or a backup is available), then you can create a temporary initialization parameter file on the destination host and set only the DB_NAME parameter.

Assume that the source database datafiles reside in /oracle/oradata/prod/, and you want to duplicate them to /scratch/oracle/oradata/dupdb/. All of the source database online redo logs reside in /oracle/oradata/prod/redo/, and you want to duplicate them to /scratch/oracle/oradata/dupdb/redo/.

To duplicate a database on a remote host with a different directory structure:

1. Follow the steps in "Preparing the Auxiliary Instance".

   In this example, you create a temporary initialization parameter file with DB_NAME as the only parameter. You can set this parameter to any arbitrary value.

2. Follow the steps in "Starting and Configuring RMAN Before Duplication".

   For this example, assume that the database is open and you have automatic channels configured. You connect to the instances as follows:

   CONNECT TARGET SYS/password@prod
   CONNECT AUXILIARY SYS/password@dupdb
   

3. Execute the DUPLICATE command.

   Example 23-5 specifies names for the duplicate database files with the SPFILE clause in conjunction with the DB_FILE_NAME_CONVERT parameter. Note that the PARAMETER_FILE_CONVERT parameter does not affect LOG_FILE_NAME_CONVERT or DB_FILE_NAME_CONVERT, which is why these two parameters must be set separately.

   Example 23-5 Duplicating to a Host with a Different Directory Structure

   DUPLICATE TARGET DATABASE
     TO dupdb
     FROM ACTIVE DATABASE   
     DB_FILE_NAME_CONVERT '/oracle/oradata/prod/','/scratch/oracle/oradata/dupdb/'
     SPFILE 
       PARAMETER_VALUE_CONVERT '/oracle/oradata/prod/', 
                               '/scratch/oracle/oradata/dupdb/'
       SET SGA_MAX_SIZE '300M'
       SET SGA_TARGET '250M'
       SET LOG_FILE_NAME_CONVERT '/oracle/oradata/prod/redo/', 
                                 '/scratch/oracle/oradata/dupdb/redo/';
   

   RMAN automatically copies the server parameter file to the destination host and updates the server parameter file on the destination host based on values provided in PARAMETER_VALUE_CONVERT and SET. RMAN then starts the auxiliary instance with the server parameter file, copies all necessary database files and archived redo logs over the network to the destination host, and recovers the database. Finally, RMAN opens the database with the RESETLOGS option to create the online redo logs.

Creating a Duplicate Database on the Local Host

When creating a duplicate database on the same host as the source database, follow the same procedure as for duplicating to a remote host with a different directory structure as described in "Duplicating a Database to a Remote Host with a Different Directory Structure".

You can duplicate the database to the same Oracle home as the source database, but you must use a different database name from the source database, and convert the filenames by means of the same methods used for conversion on a separate host.

Duplicating a Database with Oracle Managed Files or Automatic Storage Management

The following sections discuss requirements for creating a duplicate database when some or all files of the duplicate database are Oracle-managed files or use Automatic Storage Management (ASM):

• Setting Initialization Parameters for Oracle Managed Files

• Setting Initialization Parameters for ASM

CONNECT TARGET SYS/password@prod;
CONNECT CATALOG rman/password@catdb;
CONNECT AUXILIARY SYS/password@dupdb;
DUPLICATE TARGET DATABASE
  TO dupdb
  FROM ACTIVE DATABASE
  SPFILE 
    PARAMETER_VALUE_CONVERT '/oracle/oradata/prod/', '+DISK1'
    SET DB_CREATE_FILE_DEST +DISK1;

CONNECT TARGET SYS/password@prod;
CONNECT CATALOG rman/password@catdb;
CONNECT AUXILIARY SYS/password@dupdb;
DUPLICATE TARGET DATABASE
  TO dupdb
  FROM ACTIVE DATABASE
  SPFILE PARAMETER_VALUE_CONVERT '+DISK1','+DISK2';

Naming Duplicate Control Files

When specifying names for the duplicate database control files, you must set initialization parameters in the auxiliary initialization parameter file. You can specify filenames in the CONTROL_FILES initialization parameter or use other parameters to specify the location for Oracle-managed files.

The rules of precedence for determining the names for the duplicate database control files are the same used by the SQL statement CREATE CONTROLFILE. When choosing names for the duplicate control files, make sure you set the parameters in the initialization parameter file of the auxiliary database correctly; otherwise, you could overwrite the control files of the source database.

Naming Duplicate Online Redo Logs

RMAN needs new names for the online redo log files of the duplicate database. You can either specify the names explicitly in the DUPLICATE command, or you can let RMAN generate them.

1. Specify the LOGFILE clause of DUPLICATE command.

   This option creates online redo logs in the duplicate database as specified.

2. Set the LOG_FILE_NAME_CONVERT initialization parameter.

   This parameter creates the duplicate log file names by using string substitution in the names of the target files, for example, from log_* to duplog_*. Note that you can specify multiple conversion pairs.

   For details on the use of LOG_FILE_NAME_CONVERT with Oracle-managed files, see "Setting Initialization Parameters for Oracle Managed Files".

   RMAN uses the REUSE parameter when creating the online redo logs. If an online redo log file already exists at the named location and is of the correct size, then it is reused for the duplicate.

3. Set one of the Oracle Managed Files initialization parameters DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_DEST_n, or DB_RECOVERY_FILE_DEST.

   This parameter creates the duplicate log file names by using string substitution in the names of the target files. The rules of precedence among these parameters are the same used by the SQL statement ALTER DATABASE ADD LOGFILE.

4. Do none of the preceding steps.

   Makes the duplicate filenames the same as the filenames from the target. You must specify the NOFILENAMECHECK option when using this technique. The duplicate database should be in a different host so that the online redo logs of the duplicate do not conflict with the originals.

Rules higher in the order of precedence override rules lower in the list. For example, if you specify both the LOGFILE clause and the LOG_FILE_NAME_CONVERT parameter, then RMAN uses the LOGFILE clause.

Naming Duplicate Datafiles

There are several means of specifying new names to be used for the datafiles of your duplicate database. Listed in order of precedence, they are:

1. Use the RMAN command SET NEWNAME FOR DATAFILE within a RUN block that encloses both the SET NEWNAME commands and the DUPLICATE command.

2. Use the RMAN command CONFIGURE AUXNAME to specify new names for existing datafiles. Run the CONFIGURE AUXNAME command before the DUPLICATE command.

3. Execute DUPLICATE ... DB_FILE_NAME_CONVERT to specify a rule for converting filenames for any datafiles not renamed with SET NEWNAME or CONFIGURE AUXNAME.

   Note:

   You cannot use the DB_FILE_NAME_CONVERT clause of the DUPLICATE command to control generation of new names for files at the duplicate instance which are Oracle Managed Files (OMF) at the target instance. See Oracle Database Backup and Recovery Reference for details on this restriction.

4. Set the DB_FILE_NAME_CONVERT initialization parameter.

   Note:

   You cannot use the DB_FILE_NAME_CONVERT initialization parameter to control generation of new names for files at the duplicate instance which are Oracle Managed Files (OMF) at the target instance. It is subject to the same semantics and limitations as the DB_FILE_NAME_CONVERT parameter to the DUPLICATE command. It See Oracle Database Backup and Recovery Reference for details .

5. Set the DB_CREATE_FILE_DEST initialization parameter to create Oracle Managed Files datafiles at the specified location.

If you do not use any of the preceding options, then the duplicate database reuses the original datafile locations from the source database.

Naming Duplicate Tempfiles

RMAN re-creates datafiles for temporary tablespaces as part of the process of duplicating a database. There are several means of specifying locations for duplicate database tempfiles. Listed in order of precedence, they are:

1. Use the SET NEWNAME FOR TEMPFILE command within a RUN block that encloses both the SET NEWNAME commands and the DUPLICATE command.

2. Specify the DB_FILE_NAME_CONVERT clause to the DUPLICATE command to specify a rule for converting tempfiles not renamed with SET NEWNAME or CONFIGURE AUXNAME.

   Note:

   You cannot use the DB_FILE_NAME_CONVERT clause to control generation of new names for files at the duplicate instance which are Oracle Managed Files (OMF) at the target instance. See Oracle Database Backup and Recovery Reference for details on this restriction.

3. Set the DB_FILE_NAME_CONVERT initialization parameter.

   Note:

   The DB_FILE_NAME_CONVERT initialization parameter is subject to the same semantics and limitations as the DB_FILE_NAME_CONVERT parameter to the DUPLICATE command.See Oracle Database Backup and Recovery Reference for details.

4. Set the DB_CREATE_FILE_DEST initialization parameter to create Oracle Managed Files tempfiles.

Duplicating a Subset of the Target Tablespaces

It is not always necessary to duplicate all tablespaces of a database. For example, you may plan to generate reports at the duplicate that require only a subset of tablespaces from your source database. The DUPLICATE DATABASE command has options that enable you to skip read-only tablespaces or tablespaces that are currently OFFLINE NORMAL. You can also use the TABLESPACE clause to specify which tablespaces to include in the duplicate database. This section contains the following topics:

• Excluding Read-Only Tablespaces

• Excluding OFFLINE NORMAL Tablespaces

• Including and Excluding Specified Tablespaces

DUPLICATE TARGET DATABASE 
  TO dupdb
  FROM ACTIVE DATABASE
  SKIP READONLY
  NOFILENAMECHECK;

DUPLICATE TARGET DATABASE 
  TO dupdb
  FROM ACTIVE DATABASE
  SKIP TABLESPACE tools
  NOFILENAMECHECK;

DUPLICATE TARGET DATABASE 
  TO dupdb
  FROM ACTIVE DATABASE
  TABLESPACE users
  NOFILENAMECHECK;

Using DUPLICATE to Restore an Archival Backup

As explained in "Making Database Backups for Long-Term Storage", you can make an archival backup that is all-inclusive in the sense that every file needed to restore and recover the database is included. The recommended technique for restoring an archival backup for testing purposes is to create a temporary instance and use the DUPLICATE command. In this way, you avoid interfering with the production system.

Example 23-11 makes an archival backup on a temporary disk with the tag TESTDB. The example creates a restore point, which is a label for the time to which the backup should be recovered, that exists both in the recovery catalog and in the backup control file. Note that archived logs are only backed up if this is an online backup. Archived logs are not needed for offline backups and as such are not backed up.

CONNECT TARGET SYS/password@prod
CONNECT CATALOG rman/password@mycat; 
BACKUP DATABASE 
  FORMAT '/disk1/oraclebck/%U' 
  TAG TESTDB 
  KEEP UNTIL 'SYSDATE+1' 
  RESTORE POINT TESTDB06;

The procedure for restoring the backup created in Example 23-11 is the same as for "Duplicating a Database". The only additional requirement is that in the DUPLICATE command you must specify the restore point that was created with the archival backup.

To restore an archival backup:

1. Connect RMAN to the source database as TARGET, the duplicate database instance as AUXILIARY, and recovery catalog.

   CONNECT TARGET SYS/password@prod
   CONNECT AUXILIARY /
   CONNECT CATALOG rman/password@mycat
   

2. Run the LIST RESTORE POINT to display the available restore points (see "Listing Restore Points" for instructions).

   LIST RESTORE POINT;
   

3. Follow all the steps for duplicating database up to (but not including) the issuing of the DUPLICATE command itself.

4. Execute the DUPLICATE command, specifying the restore point of the archival backup that you intend to restore.

   The following example assumes that you have created an auxiliary instance and are running the RMAN client on the test host. The USING RESTORE POINT clause specifies TESTDB06, which is the restore point created in Example 23-11.

   DUPLICATE TARGET DATABASE 
     TO mytest
     UNTIL RESTORE POINT TESTDB06
     DB_FILE_NAME_CONVERT '/prod/oracledb/','/test/oracledb'
     PFILE 'test/oracledb/init.ora';
   

   The preceding DUPLICATE command restores the whole database and renames it to mytest. The DUPLICATE command does not restore the original control file, but instead creates a new control file. Thus, you can only specify DUPLICATE ... UNTIL RESTORE POINT if RMAN is connected to a catalog or connected to the source database when the restore point still exists in the control file.

Using SET NEWNAME to Name Duplicate Files

In this scenario, you create a duplicate database by using backup-based duplication. The source database does not use a server parameter file, so you cannot use the SPFILE technique to specify names for the duplicate datafiles. You decide to use SET NEWNAME commands to specify the filenames because the duplicate datafiles will be spread out across several directories.

Assume that the source database prod is on host1 and contains eight datafiles, which are spread out over multiple directories. You want to duplicate the source database to database dupdb on remote host host2. The dupdb database should exclude tablespace tools, but keep all of the other tablespaces.

In this scenario, host1 and host2 have different directory structures. You want to store the datafiles in host2 in the /oradata1 through /oradata7 subdirectories. Although eight datafile exist in the source database, you only need to specify seven locations for the target datafiles because you are excluding the tools tablespace. You want to create two online redo logs groups, each with two members of size 200 KB, in the directory /duplogs on the destination host. Assume that host1 and host2 cannot mount each other's file systems by any means such as NFS.

You have disk copies or backup sets stored on disk for all the datafiles and archived redo logs in the source database, and you have manually copied them to host2 by means of an operating system utility. These backups and copies exist in the same location on host2 as they do in host1.

You use an operating system utility to copy the initialization parameter file from host1 to an appropriate location in host2. You have reset all initialization parameters that end in _DEST and specify a path name. You do not set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT because you are specifying names for datafiles and online logs in the RUN command itself. The auxiliary instance uses a server-side initialization parameter file in the default location so the PFILE parameter is not necessary on the DUPLICATE command.

Example 23-12 illustrates a script that creates the duplicate database. Note that a RUN command is necessary because you can only execute SET NEWNAME within RUN. You do not set a new name for datafile 7 because it is in the tools tablespace, which you are excluding from the duplicate database.

RUN
{
  SET NEWNAME FOR DATAFILE 1 TO '/oradata1/system01.dbf'; 
  SET NEWNAME FOR DATAFILE 2 TO '/oradata2/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/oradata3/cwmlite01.dbf'; 
  SET NEWNAME FOR DATAFILE 4 TO '/oradata4/drsys01'; 
  SET NEWNAME FOR DATAFILE 5 TO '/oradata5/example01.dbf'; 
  SET NEWNAME FOR DATAFILE 6 TO '/oradata6/indx01.dbf';
  # Do not set a newname for datafile 7, because it is in the tools tablespace,
  # and you are excluding tools from the duplicate database.
  SET NEWNAME FOR DATAFILE 8 TO '/oradata7/users01.dbf'; 
  DUPLICATE TARGET DATABASE TO dupdb
    SKIP TABLESPACE tools
    LOGFILE
      GROUP 1 ('/duplogs/redo01a.log', 
               '/duplogs/redo01b.log') SIZE 4M REUSE, 
      GROUP 2 ('/duplogs/redo02a.log', 
               '/duplogs/redo02b.log') SIZE 4M REUSE;
}

RUN
{
   SET NEWNAME FOR DATAFILE 1 TO "+dgroup1";
   SET NEWNAME FOR DATAFILE 2 TO "+dgroup2";
   .
   .
   .
   DUPLICATE TARGET DATABASE 
     TO dupdb
     FROM ACTIVE DATABASE
     SPFILE SET DB_CREATE_FILE_DEST +dgroup3;
}

Using CONFIGURE AUXNAME to Name Duplicate Files

This section assumes the same circumstances described in "Using SET NEWNAME to Name Duplicate Files". Example 23-14 is a variation of Example 23-12 that uses CONFIGURE AUXNAME to specify the new datafile names. These new filenames are recorded in the control file and used every time you perform the duplication in the future.

Example 23-14 also uses automatic channels and a client-side initialization parameter file for the database duplication, and uses the LOGFILE clause to specify names and sizes for the online redo logs. In this case the RUN command is not necessary because you are not using SET NEWNAME.

CONFIGURE AUXNAME FOR DATAFILE 1 TO '/oradata1/system01.dbf'; 
CONFIGURE AUXNAME FOR DATAFILE 2 TO '/oradata2/undotbs01.dbf';
CONFIGURE AUXNAME FOR DATAFILE 3 TO '/oradata3/cwmlite01.dbf'; 
CONFIGURE AUXNAME FOR DATAFILE 4 TO '/oradata4/drsys01'; 
CONFIGURE AUXNAME FOR DATAFILE 5 TO '/oradata5/example01.dbf'; 
CONFIGURE AUXNAME FOR DATAFILE 6 TO '/oradata6/indx01.dbf';

DUPLICATE TARGET DATABASE
  TO dupdb
  SKIP TABLESPACE tools
  LOGFILE
    GROUP 1 ('/duplogs/redo01a.log',
             '/duplogs/redo01b.log') SIZE 4M REUSE,
    GROUP 2 ('/duplogs/redo02a.log',
             '/duplogs/redo02b.log') SIZE 4M REUSE;

RMAN uses all incremental backups, archived redo log backups, and archived redo logs to perform incomplete recovery and then opens the database with the RESETLOGS option to create the online redo logs.

After the duplication is complete, you can clear the configured auxiliary names for the datafiles in the duplicate database, so that they are not overwritten by future operations. For example, enter the following commands:

CONFIGURE AUXNAME FOR DATAFILE 1 CLEAR; 
CONFIGURE AUXNAME FOR DATAFILE 2 CLEAR; 
CONFIGURE AUXNAME FOR DATAFILE 3 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 4 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 5 CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 6 CLEAR;

Alternatively, you may want to periodically synchronize a duplicate database with the original database that was duplicated. In this case, you can run the DUPLICATE command again, essentially re-creating the duplicate database. This technique requires making complete copies of the datafiles of the duplicate database. Run the script in Example 23-15 whenever you want to synchronize the duplicate with the source database. For example, you might run the script daily or weekly.

DUPLICATE TARGET DATABASE TO dupdb
SKIP TABLESPACE tools
  LOGFILE
    GROUP 1 ('/duplogs/redo01a.log',
             '/duplogs/redo01b.log') SIZE 4M REUSE,
    GROUP 2 ('/duplogs/redo02a.log',
             '/duplogs/redo02b.log') SIZE 4M REUSE;