Skip Headers
Oracle® Database Backup and Recovery User's Guide
11g Release 1 (11.1)

B28270-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

23 Duplicating a Database

This chapter describes how to use the DUPLICATE command to create a duplicate database. This chapter contains these topics:

Overview of RMAN Database Duplication

This section explains the basic concepts and tasks involved in database duplication.

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.

See Also:

Oracle Data Guard Concepts and Administration to learn how to create a standby database with the DUPLICATE command

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:

Creates a control file for the duplicate database

Restarts the auxiliary instance and mounts the duplicate control file

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 source database 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.

Opens the duplicate database with the RESETLOGS option

See Also:

The DUPLICATE entry in Oracle Database Backup and Recovery Reference for a complete list of which files are copied to the duplicate database

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:

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".

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".

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".

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".

Execute the DUPLICATE command.

This task is described in "Duplicating a Database".

Making Backups and Archived Logs Accessible to the Duplicate Instance

If you are using active database duplication, then skip this section and proceed to "Choosing a Strategy for Naming Duplicate Files".

RMAN uses the metadata in the duplicate control file to locate backups and archived logs needed for duplication. You must ensure that the auxiliary channels can access all datafile backups and archived redo logs required to restore and recover the duplicate database. Otherwise, the duplicate operation fails.

The database backup need not have been generated with BACKUP DATABASE. You can use a mix of full and incremental backups of individual datafiles. For example, suppose you have taken a full backup of datafiles 1, 2, and 3. Also, you have taken a level 0 and level 1 backup of datafiles 4, 5, 6. In this case, DUPLICATE can restore files 1, 2, and 3 from the full backup and files 4, 5, and 6 from the level 0 backup. RMAN can apply the incremental level 1 incremental backup to 4, 5, and 6 and apply archived redo logs to all six datafiles.

When using backup-based duplication, decide how to make database backups available to the auxiliary instance. Also, archived redo log files required to recover the duplicate database to the desired point in time must be accessible at the same path by the host where the duplicate database is to be created. The logs can be available either as backups (for instance, on a media manager) or as image copies (or the actual archived redo log files).

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.

Making Disk Backups Accessible Without Using Shared Disk

When NFS or shared disk is not an option, then the path that stores the backups must exist on both the source and destination hosts. Assume that you maintain two hosts, srchost and dsthost. The database on srchost is srcdb. The RMAN backups of srcdb reside in /dsk1/bkp on host srchost. The directory /dsk1/bkp is already in use on the destination host, so you intend to store backups in /dsk2/dup on the destination host. You must update the controlfile on the duplicate site with the new location of the dumpfiles.

Follow these steps to transfer the backups from the source host to the destination host:

  1. Create a new directory in the source host that has the same name as the directory on the destination host that will contain the backups.

    For example, if you intend to store the RMAN backups in /dsk2/dup on the destination host, then create /dsk2/dup on the source host.

    On the source host, copy the backups to the directory that you created in the previous step, and then catalog the backups. You can use either of the following techniques:

    • Connect RMAN to the source database as TARGET and use the BACKUP command to back up the backups, as explained in "Backing Up RMAN Backups". For example, use the BACKUP COPY OF DATABASE command to copy the backups in /dsk1/bkp on the source host to /dsk2/dup on the source host. In this case, RMAN automatically catalogs the backups in the new location.

  2. Use an operating system utility to copy the backups in /dsk1/bkp on the source host to /dsk2/dup on the source host. Afterward, connect RMAN to the source database as TARGET and use the CATALOG command to update the source database control file with the location of the manually transferred backups.

    Manually transfer the backups in the new directory on the source host to the identically named directory on the destination host.

    For example, use FTP to transfer the backups in /dsk2/dup on the source host to /dsk2/dup on the destination host.

As part of the duplication, RMAN will create a duplicate control file listing backups in the /dsk2/dup path. The auxiliary channel can then search for backups in /dsk2/dup on the destination host and restore them.

Making Disk Backups Accessible Using Shared Disk

Assume that there are two hosts, srchost and dsthost. The database on srchost is called srcdb. The backups of srcdb reside in /dsk1/bkp on host srchost. The directory /dsk1/bkp is already in use on the destination host, but the directory /dsk2/dup is not in use in either host.

Follow these steps to transfer the backups from the source host to the destination host:

  1. Create the new backup storage directory in either the source or destination host.

    For example, create backup directory /dsk2/dup on the destination host.

    Mount the directory created in the previous step on the other host, making sure that the directory and the mount point names are the same.

    For example, if you created /dsk2/dup on the destination host, then use NFS to mount this directory as /dsk2/dup on the source host.

    Make the backups available in the new location on the destination host. You can use either of the following techniques:

    • Connect RMAN to the source database as TARGET and use the BACKUP command to back up the backups, as explained in "Backing Up RMAN Backups". For example, use the BACKUP COPY OF DATABASE command to copy the backups in /dsk1/bkp on the source host to /dsk2/dup on the source host. In this case, RMAN automatically catalogs the backups in the new location.

  2. Use an operating system utility to transfer the backups to the new location. For example, FTP the backups from /dsk1/bkp on the source host to /dsk2/dup on the destination host, or use the cp command to copy the backups from /dsk1/bkp on the source host to /dsk2/dup on the source host. Afterward, connect RMAN to the source database as TARGET and use the CATALOG command to update the source database control file with the location of the manually transferred backups.

As part of the duplication, RMAN will create a duplicate control file listing backups in the /dsk2/dup path. The auxiliary channel can then search for backups in /dsk2/dup on the destination host and restore them.

See Also:

Oracle Database Backup and Recovery Reference for information about the BACKUP command

Choosing a Strategy for Naming Duplicate Files

When duplicating a database, RMAN generates names for the duplicate database files. If the destination host uses the same directory structure as the source host, then you can use the same names for the duplicate database files that you used for the source database files. In this case, you do not need to rename the duplicate files, but you do have to specify the NOFILENAMECHECK option on the DUPLICATE command.

If the hosts use different directory structures, or if they use the same structure but you want to name the duplicate files differently, then decide how to generate the names for the duplicate database files. Specifically, decide how to name the control files, datafiles, online redo log files, and tempfiles.

RMAN supports the following strategies for generating names for duplicate files:

This section describes the SPFILE strategy only. The following list shows DUPLICATE options that you can use to name duplicate files. Refer to Oracle Database Backup and Recovery Reference for complete syntax and semantics of the DUPLICATE command.

Example 23-1 shows a DUPLICATE command that uses the SPFILE clause to name duplicate files. The PARAMETER_VALUE_CONVERT option substitutes /disk2 for /disk1 in all initialization parameters that specify filenames (with the exception of DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT). The SET LOG_FILE_NAME_CONVERT clause substitutes /disk2 for /disk1 in the filenames of the online redo logs of the duplicate database. The DB_FILE_NAME_CONVERT option replaces /disk1 with /disk2 in the names of the duplicate datafiles and tempfiles.

Example 23-1 Using the SPFILE Clause to Name Duplicate Files

DUPLICATE TARGET DATABASE TO dup1
  FROM ACTIVE DATABASE
  DB_FILE_NAME_CONVERT '/disk1','/disk2'
  SPFILE 
    PARAMETER_VALUE_CONVERT '/disk1', '/disk2'
    SET LOG_FILE_NAME_CONVERT '/disk1','/disk2'
    SET SGA_MAX_SIZE '200M'
    SET SGA_TARGET '125M';

See Also:

Oracle Database Reference for information about the DB_FILE_NAME_CONVERT initialization parameter

Preparing the Auxiliary Instance

To prepare the auxiliary instance used by the DUPLICATE DATABASE command, perform the following tasks:

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.

Note:

A password file is not required for backup-based duplication. You can use operating system authentication for the auxiliary connection when duplicating to the same host as the source database.

When using the FROM ACTIVE DATABASE option, the source database instance, which is the database 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:

See Also:

Oracle Data Guard Concepts and Administration to learn how to create a standby database with the DUPLICATE command

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.

Table 23-1 Auxiliary Instance Initialization Parameters

Initialization Parameter Value Status

DB_NAME

The same name used in the DUPLICATE command.

The DB_NAME setting for the duplicate database must be unique among databases in its Oracle home.

Required

CONTROL_FILES

Control file locations.

See Also: "Naming Duplicate Control Files"

Required (unless you set parameters for Oracle-managed files)

DB_BLOCK_SIZE

The block size for the duplicate database.

This block size must match the block size of the source database. If the source database parameter file contains a value for the DB_BLOCK_SIZE initialization parameter, then you must specify the same value for the auxiliary instance. If no DB_BLOCK_SIZE is specified in the source database initialization parameter file, however, then do not specify DB_BLOCK_SIZE in the auxiliary instance.

See Also: "Naming Duplicate Files with Alternative Techniques"

Required if this initialization parameter is set in source database

DB_FILE_NAME_CONVERT

Pairs of strings for converting the names of datafiles and tempfiles. Note that you can also specify DB_FILE_NAME_CONVERT on the DUPLICATE command itself.

See Also: "Naming Duplicate Datafiles" and "Naming Duplicate Tempfiles"

Optional

LOG_FILE_NAME_CONVERT

Pairs of strings for naming online redo log files.

See Also: "Naming Duplicate Online Redo Log Files"

Optional

DB_CREATE_FILE_DEST

Location for Oracle-managed datafiles.

See Also: "Naming Duplicate Datafiles" and "Naming Duplicate Tempfiles"

Optional

DB_CREATE_ONLINE_LOG_DEST_n

Location for Oracle-managed online redo log files.

See Also: "Naming Duplicate Online Redo Log Files".

Optional

DB_RECOVERY_FILE_DEST

Location for flash recovery area.

See Also: "Naming Duplicate Files with Alternative Techniques"

Optional


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.

Example 23-2 Sample Initialization Parameter File for the Auxiliary Instance

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 database instance with SYSOPER privileges. Start the instance in NOMOUNT mode, specifying the PFILE parameter if necessary:

SQL> STARTUP NOMOUNT

Note:

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.

Starting and Configuring RMAN Before Duplication

Before executing the DUPLICATE DATABASE command, perform the following tasks:

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:

Start the RMAN client.

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

% rman

At the RMAN prompt, run 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@prod     # source database

target database Password: password
connected to target database: PROD (DBID=39525561)

RMAN> CONNECT AUXILIARY SYS@dupdb   # duplicate database instance

auxiliary database Password: password
connected to auxiliary database: DUPDB (not mounted)

RMAN> CONNECT CATALOG rman@catdb     # recovery catalog database

recovery catalog database Password: password
connected to 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 database instance. Note that it is the channel on the auxiliary instance, not the source database 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.

See Also:

Oracle Database Backup and Recovery Reference for information about the CONFIGURE command

Duplicating a Database

This section describes the most basic procedure to create a duplicate database. The procedure depends on how your databases and hosts are configured. This section contains the following topics:

See Also:

Oracle Database Backup and Recovery Reference for the complete set of options and clauses that you can specify on the DUPLICATE command

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:

Follow the steps in "Preparing the Auxiliary Instance".

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

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

Run 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 (Active)

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.

Example 23-4 Duplicating a Database to a Past Point in Time (Backup Based)

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:

Follow the steps in "Preparing the Auxiliary Instance".

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

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

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

RMAN> CONNECT TARGET SYS@prod

target database Password: password
connected to target database: PROD1 (DBID=39525561)

CONNECT AUXILIARY SYS@dupdb

auxiliary database Password: password
connected to auxiliary database: DUPDB (not mounted)

Run 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 (Active)

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.

Caution:

Do not use the NOFILENAMECHECK option when duplicating to the same Oracle home as the primary database. If you do, then the DUPLICATE command may overwrite the datafiles of the source database.

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):

See Also:

Oracle Database Storage Administrator's Guide for an introduction to ASM

Setting Initialization Parameters for Oracle Managed Files

When creating a duplicate database that uses Oracle-managed files, you must set initialization parameters in the auxiliary instance. If you use the SPFILE clause of DUPLICATE to name the files, then you can set initialization parameters in the SPFILE clause. The following table describes the relevant initialization parameters and recommended settings.

Table 23-2 Initialization Parameters for Oracle Managed Files

Initialization Parameter Purpose Recommendation

DB_CREATE_FILE_DEST

Specifies the default location for Oracle-managed datafiles. This location is also the default location for Oracle-managed control files and online logs if none of the DB_CREATE_ONLINE_LOG_DEST initialization parameters are specified.

Set this parameter to the location for the Oracle-managed files. Any database files for which no other location is specified are created in DB_CREATE_FILE_DEST by DUPLICATE. You can override the default for specific files using SET NEWNAME, as described in "Using SET NEWNAME to Name Oracle-Managed Files".

DB_CREATE_ONLINE_LOG_DEST_n

Specifies the default location for Oracle-managed control files and online redo logs. If multiple parameters are set, then one control file and one online redo log is created in each location.

Set these parameters (_1, _2, and so on) only if you want to multiplex the control files and redo logs in multiple locations.

DB_RECOVERY_FILE_DEST

Specifies the default location for the flash recovery area. The flash recovery area contains multiplexed copies of current control files and online redo logs.

Set this parameter if you want a multiplexed copy of the control file and online redo log file in the recovery area.

CONTROL_FILES

Specifies one or more names of control files, separated by commas.

Do not set this parameter if you want the duplicate database control files in an Oracle Managed Files format. Oracle recommends that you use a server parameter file at the duplicate database when using control files in an OMF format.

DB_FILE_NAME_CONVERT

Converts the filename of a new datafile on the primary database to a filename on the duplicate database.

Do not set this parameter. Omitting this parameter enables the database to generate valid Oracle-managed filenames for the duplicate datafiles.

LOG_FILE_NAME_CONVERT

Converts the filename of a new log file on the primary database to the filename of a log file on the standby database.

Do not set this parameter. Omitting this parameter enables the database to generate valid Oracle-managed online redo log filenames.

To direct duplicate database online redo log files to Oracle-managed storage, you can use the DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST or DB_CREATE_ONLINE_LOG_DEST_n initialization parameters to identify an Oracle-managed location for the online logs.


Setting Initialization Parameters for ASM

The procedure for creating a duplicate database to an ASM location is similar to the procedure described in "Setting Initialization Parameters for Oracle Managed Files". The difference is that you must set the initialization parameters in the auxiliary instance that control the location where files are created at the duplicate to the ASM disk group. For example, set DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_DEST_n, and CONTROL_FILES.

Duplicating a Database from a File System to ASM: Example

In this example, you use active database duplication. If the source database uses a server parameter file (or a backup is available), then you can just create a temporary initialization parameter file on the destination host and set only the DB_NAME parameter.

Assume that the source database prod is on host1 and stores its datafiles in a non-ASM file system. The control files for prod are located in /oracle/oradata/prod/. You want to duplicate the source database to database dupdb on remote host host2. You want to store the duplicate database files in ASM disk group +DISK1.

After connecting RMAN to the target, duplicate, and recovery catalog database, run the RMAN script shown in Example 23-6 to duplicate the database.

Example 23-6 Duplicating from a File System to ASM (Active)

DUPLICATE TARGET DATABASE
  TO dupdb
  FROM ACTIVE DATABASE
  SPFILE 
    PARAMETER_VALUE_CONVERT '/oracle/oradata/prod/', '+DISK1'
    SET DB_CREATE_FILE_DEST +DISK1;

When the DUPLICATE command completes, the duplicate database is created, with datafiles, online redo logs, and control files in ASM disk group +DISK1.

Duplicating a Database from ASM to ASM: Example

In this example, you use active database duplication. If the source database uses a server parameter file (or a backup is available), then you can just create a temporary initialization parameter file on the destination host and set only the DB_NAME parameter.

Assume that the source database prod is on host1 and stores its datafiles in ASM disk group +DISK1. You want to duplicate the target to database dupdb on remote host host2. You want to store the datafiles for dupdb in ASM. Specifically, you want to store the datafiles and control files in disk group +DISK2.

In the DUPLICATE command, set PARAMETER_VALUE_CONVERT to convert all directory locations from +DISK1 to +DISK2. The new filenames in +DISK2 are generated by ASM and do not match the original filenames in disk group +DISK1.

After connecting to the target, duplicate, and catalog databases, run the RMAN script shown in Example 23-7 to duplicate the database.

Example 23-7 Duplicating from ASM to ASM (Active)

DUPLICATE TARGET DATABASE
  TO dupdb
  FROM ACTIVE DATABASE
  SPFILE PARAMETER_VALUE_CONVERT '+DISK1','+DISK2'
     SET DB_FILE_NAME_CONVERT    '+DISK1','+DISK2'
     SET LOG_FILE_NAME_CONVERT   '+DISK1','+DISK2';

When the DUPLICATE command completes, the duplicate database is created, with datafiles, online redo logs, and control files in ASM disk group +DISK2.

Naming Duplicate Files with Alternative Techniques

This section assumes that you do not want to use the SPFILE clause technique for naming duplicate files, or you want to supplement the SPFILE technique with other naming techniques.

The following sections explain how to name the files in the duplicate database:

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.

See Also:

The "Semantics" section in the CREATE CONTROLFILE entry in Oracle Database SQL Language Reference

Naming Duplicate Online Redo Log Files

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.

Specify the LOGFILE clause of DUPLICATE command.

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

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 source database 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.

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 source database files. The rules of precedence among these parameters are the same used by the SQL statement ALTER DATABASE ADD LOGFILE.

Do none of the preceding steps.

Makes the duplicate filenames the same as the filenames from the source database. 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.

Caution:

  • If the source database and duplicate database are in the same host, then do not use the name of an online redo log currently in use by the source database.

  • If the duplicate database is in a different host and NOFILENAMECHECK is not used, then do not use the name of an online redo log file currently used by any database on the destination host.

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:

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

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

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 source database instance. See Oracle Database Backup and Recovery Reference for details on this restriction.

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 source database 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 .

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:

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

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

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 source database instance. See Oracle Database Backup and Recovery Reference for details on this restriction.

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.

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

RMAN Duplication Scenarios

This section presents several representative scenarios for using RMAN DUPLICATE:

Duplicating a Subset of the Source Database 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

When you specify SKIP READONLY on the DUPLICATE command, RMAN does not duplicate the datafiles of read-only tablespaces. Example 23-8 is a variation of Example 23-3 except with read-only tablespace excluded.

Example 23-8 Excluding Read-Only Tablespaces

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

Excluding OFFLINE NORMAL Tablespaces

When tablespaces are taken offline with the OFFLINE NORMAL option before a DUPLICATE operation, RMAN does not duplicate their datafiles and issues DROP TABLESPACE for these tablespaces on the duplicate database. Thus, you do not need to specify special syntax to exclude these tablespaces. After duplication, all datafiles and tablespaces are online.

Note:

RMAN duplicates tablespaces that are taken offline with the IMMEDIATE option because they require recovery. As with online tablespaces, RMAN requires a valid backup for these tablespaces when you use the backup-based duplication technique.

Including and Excluding Specified Tablespaces

You can use the SKIP TABLESPACE parameter to exclude specified tablespaces from the duplicate database. Note that you cannot exclude the SYSTEM and SYSAUX tablespaces, undo tablespaces, and tablespaces with rollback segments. You can use the TABLESPACE parameter to specify which tablespaces should be included in the specified database. Unlike SKIP TABLESPACE, which specifies which tablespaces should be excluded from the duplicate database, this option specified which tablespaces should be included and then skips the remaining tablespaces.

Example 23-9 is a variation of Example 23-3 except with the tools tablespace excluded.

Example 23-9 Excluding Specified Tablespaces

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

Example 23-10 is a variation of Example 23-3 except with the users tablespace included and all other tablespaces excluded, with the exception of the SYSTEM and SYSAUX tablespaces and tablespaces with undo or rollback segments.

Example 23-10 Including Specified Tablespaces

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 is to create a temporary instance and use the DUPLICATE command. In this way, you avoid interfering with the production system.

Assume that you start RMAN and connect to a target and catalog database. Example 23-11 makes an archival backup on a temporary disk with the tag TESTDB.

Example 23-11 Creating a Temporary Archival Backup

BACKUP DATABASE 
  FORMAT '/disk1/oraclebck/%U' 
  TAG TESTDB 
  KEEP UNTIL 'SYSDATE+1' 
  RESTORE POINT TESTDB06;

Example 23-11 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.

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:

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

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

LIST RESTORE POINT;

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

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 UNTIL 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 log 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.

Example 23-12 Duplicating with SET NEWNAME

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;
}

Using SET NEWNAME to Name Oracle-Managed Files

To store specific datafiles or tempfiles in an Oracle-managed files destination that is independent of the locations of the rest of the database files, use the following steps:

  • Set the DB_CREATE_FILE_DEST initialization parameter at the auxiliary instance to the desired location

  • Enclose the DUPLICATE command in a RUN block and use SET NEWNAME FOR DATAFILE ... TO NEW and SET NEWNAME FOR TEMPFILE ... TO NEW

The specified datafiles or tempfiles are created with Oracle-managed file names in the location specified by DB_CREATE_FILE_DEST.

As shown in Example 23-13, you can also use SET NEWNAME to direct individual datafiles or tempfiles to a specific ASM disk group.

Example 23-13 Using SET NEWNAME to Create Files in an ASM Disk Group

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;
}

See also:

Oracle Database Backup and Recovery Reference for details on using SET NEWNAME

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.

Example 23-14 Using CONFIGURE AUXNAME to Generate Database Filenames

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.

Example 23-15 Reduplicating the Database

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;