DUPLICATE

Example 2-72 Duplicating from an Active Database to a Host with the Same Directory Structure

Assume that you want to create a test database from database prod1 on a new host. The new host has the same directory structure as the source host, so the files in the duplicate database can use the same names as the files in the source database. You want to create the database without using RMAN backups and allow prod1 to remain open during the duplication.

If prod1 uses a server parameter file, then you can create an initialization parameter file on the destination host that contains only the DB_NAME parameter set to an arbitrary value. Before starting the auxiliary instance you should create a password file that has the same SYSDBA password as the source database. Afterward, start the auxiliary instance using an spfile, if available. If you start the auxiliary instance with a pfile, then RMAN creates an spfile in the default location, possibly overwriting any spfile residing there.

By default, RMAN does not duplicate the password file when creating a duplicate database that is not a standby database. The PASSWORD FILE option specifies that RMAN should copy the password file to the destination host. If you want the duplicate database to contain all the passwords available on your source database, then use the PASSWORD FILE option.

You do not need to configure auxiliary channels because RMAN uses the normal channels configured on the source database to copy the database files. Start the RMAN client, connect to the source and auxiliary database instances, and duplicate the database as follows:

% rman
RMAN> CONNECT TARGET SYS@prod1

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

RMAN> CONNECT AUXILIARY SYS@dup1

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

RMAN> DUPLICATE TARGET DATABASE TO dup1
2> FROM ACTIVE DATABASE
3> NOFILENAMECHECK
4> PASSWORD FILE
5> SPFILE;

Example 2-73 Copying the Server Parameter File in Active Database Duplication

Assume that you want to create a standby database from database prod1 on a new host. The destination host has a different directory structure from the source host, so the standby database files are stored in /disk2 rather than /disk1. You want to create the standby database without using RMAN backups and let prod1 remain open during the duplication.

Your first step is to create a minimal initialization parameter file for the standby database and then start the standby instance. This parameter file is minimal because when you use the SPFILE option, RMAN copies the server parameter file to the new host and sets various parameters to the new values provided. The parameters used to start the standby instance are ignored.

Start the RMAN client, CONNECT to the source database as TARGET and connect to the auxiliary instance. Allocate multiple channels to the target instance and a channel to the auxiliary instance as shown here:

ALLOCATE CHANNEL tgt10 TYPE DISK; 
ALLOCATE CHANNEL tgt20 TYPE DISK; 
ALLOCATE CHANNEL tgt30 TYPE DISK; 
ALLOCATE CHANNEL tgt40 TYPE DISK; 
ALLOCATE AUXILIARY CHANNEL dup1 TYPE DISK;

You can then enter the following command:

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  PASSWORD FILE
  SPFILE 
    PARAMETER_VALUE_CONVERT '/disk1', '/disk2'
    SET DB_FILE_NAME_CONVERT '/disk1','/disk2'
    SET LOG_FILE_NAME_CONVERT '/disk1','/disk2'
    SET DB_UNIQUE_NAME 'dup1'
    SET SGA_MAX_SIZE 200M
    SET SGA_TARGET 125M;

Example 2-74 Duplicating a Database Without a Target Connection to a Host with the Same Directory Structure

Assume that you want to duplicate source database prod using backups and do not want to connect RMAN as TARGET to this database because it is shut down for maintenance. A description of the environment follows:

• The source and destination hosts have identical directory structures. The data file and online redo log names in the duplicate database must be identical to the names in the source database.

• The same number of online redo log files are used in the duplicate database.

• A recovery catalog is available. The source database name prod is unique in the recovery catalog.

• Auxiliary channels have been configured with the CONFIGURE CHANNEL command.

The following commands create a duplicate database named dupdb:

% rman 

RMAN> CONNECT CATALOG rman@catdb;
 
recovery catalog database Password: password
connected to recovery catalog database

RMAN> CONNECT AUXILIARY SYS@dupdb;

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

RMAN> DUPLICATE DATABASE 'PROD' TO 'DUPDB' NOFILENAMECHECK;

Assume a different scenario in which the database name prod is not unique in the recovery catalog. The following DUPLICATE command uses the DBID parameter to uniquely identify the source database:

RMAN> DUPLICATE DATABASE 'PROD' DBID 39525561 TO 'DUPDB' NOFILENAMECHECK;

Example 2-75 Setting New File Names in the DUPLICATE Command

Assume that you want to use tape backups to duplicate the source database prod on srchost to newdb on desthost.

In this scenario, the source database does not use a server parameter file. You create a text-based initialization parameter file on desthost and use it to start the database instance. Thus, backup-based duplication must use a target connection (see Table 2-6).

When executing DUPLICATE on desthost, you must use the PFILE parameter to specify the location of the initialization parameter file. You must use the RMAN client on the same host as the initialization parameter file for the duplicate database.

You do not want the tablespaces example and history to be included in the duplicate database, so you specify DUPLICATE ... SKIP TABLESPACE for these tablespaces. Also, you want the duplicate database to be in the state that the production database was in 24 hours ago, so you use DUPLICATE ... UNTIL TIME.

This example assumes that the data files of the source database are on srchost in directory /h1/oracle/dbs/trgt. You intend to duplicate the data files to the directory /h2/oracle/oradata/newdb, so you specify DUPLICATE ... DB_FILE_NAME_CONVERT to generate the names for the duplicate data files. You use DUPLICATE ... LOGFILE to specify names for the online redo log files in the duplicate database.

Start the RMAN client on desthost, CONNECT to the source database as TARGET, and connect to the auxiliary instance. You can then enter the following RUN command:

RUN
{  
  ALLOCATE AUXILIARY CHANNEL newdb DEVICE TYPE sbt; 
  DUPLICATE TARGET DATABASE TO newdb
    PFILE ?/dbs/initNEWDB.ora
    UNTIL TIME 'SYSDATE-1'  # specifies incomplete recovery
    SKIP TABLESPACE example, history   # skip desired tablespaces
    DB_FILE_NAME_CONVERT ('/h1/oracle/dbs/trgt/','/h2/oracle/oradata/newdb/')
    LOGFILE
      GROUP 1 ('/h2/oradata/newdb/redo01_1.f',
               '/h2/oradata/newdb/redo01_2.f') SIZE 4M,
      GROUP 2 ('/h2/oradata/newdb/redo02_1.f',
               '/h2/oradata/newdb/redo02_2.f') SIZE 4M,
      GROUP 3 ('/h2/oradata/newdb/redo03_1.f',
               '/h2/oradata/newdb/redo03_2.f') SIZE 4M REUSE;
}

Example 2-76 Using SET NEWNAME FOR DATABASE to Name Duplicate Files

In this scenario, you intend to use backup-based duplication without a target connection.

The source database prod contains eight data files spread out over multiple directories. The data files are not Oracle Managed Files. You want to duplicate the source database to dupdb on destination host desthost.

In this scenario, srchost and desthost have different directory structures. You want to store the data files in desthost in the /oradata1 subdirectory, so you use SET NEWNAME FOR DATABASE to specify the file names stripped of directory paths. For example, if a source data file has the name '/oradata/prod/financial.dbf', then %b results in 'financial.dbf'.

The source database does not use a server parameter file, so you cannot use the SPFILE technique to specify names for the duplicate data files. You decide to use the SET NEWNAME DATABASE command because you want all duplicate data files in the same directory on the destination host.

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 srchost and desthost 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 data files and archived redo log files in the source database, and you have manually copied them to desthost using an operating system utility. These backups and copies exist in the same location on desthost as they do in srchost.

You use an operating system utility to copy the initialization parameter file from srchost to an appropriate location in desthost. 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 data files 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.

The following sample script creates the duplicate database. A RUN command is necessary because you can only execute SET NEWNAME within RUN.

RUN
{
  SET NEWNAME FOR DATABASE TO '/oradata1/%b'; 
  DUPLICATE TARGET DATABASE TO dupdb
    LOGFILE
      GROUP 1 ('/duplogs/redo01a.log', 
               '/duplogs/redo01b.log') SIZE 4M REUSE,
      GROUP 2 ('/duplogs/redo02a.log',
               '/duplogs/redo02b.log') SIZE 4M REUSE;
}

Example 2-77 Using SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TABLESPACE to Name Duplicate Files

In this scenario, you intend to duplicate a database by using backup-based duplication.

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

The source database does not use a server parameter file, so you cannot use the SPFILE technique to specify names for the duplicate data files. You decide to use SET NEWNAME commands to specify the file names because the duplicate data files will be spread across several directories.

In this scenario, srchost and desthost have different directory structures. You want to store the data files in desthost in the /oradata1 through /oradata7 subdirectories. You want to place each data file in a different directory, except for the USERS tablespace, which contains two data files that you intend to duplicate to /oradata7.

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 srchost and desthost 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 data files and archived redo log files in the source database, and you have manually copied them to desthost using an operating system utility. These backups and copies exist in the same location on desthost as they do in srchost.

You use an operating system utility to copy the initialization parameter file from srchost to an appropriate location in desthost. 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 data files 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.

The following sample scripts creates the duplicate database. A RUN command is necessary because you can only execute SET NEWNAME within RUN.

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'; 
  # because the users command contains 2 datafiles, the following command
  # generates unique names for both datafiles, placing them in /oradata7
  SET NEWNAME FOR TABLESPACE users TO '/oradata7/users%b.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;
}

Example 2-78 Using SET NEWNAME FOR DATAFILE to Name Oracle-Managed Files

There are two ways to store specific data files or temp files in an Oracle-managed files destination that is independent of the locations of the rest of the database files.

1. Set the parameter DB_CREATE_FILE_DEST in the initialization parameter file of the auxiliary instance to the desired location.

2. Set the initialization parameters DB_CREATE_FILE_DEST and DB_FILE_NAME_CONVERT. At this point, you can use the SET NEWNAME command for those data files that you do not want to be converted by DB_FILE_NAME_CONVERT.

The specified data files or temp files are created with Oracle-managed file names in the location specified by DB_CREATE_FILE_DEST.

As shown in the following sample script, you can also use SET NEWNAME to direct individual data files or temp files to a specific 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;
}

Example 2-79 Using CONFIGURE AUXNAME to Name Duplicate Files

This section assumes the same circumstances described in Example 2-77, "Using SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TABLESPACE to Name Duplicate Files". This example is a variation that uses CONFIGURE AUXNAME instead of SET NEWNAME to specify the new data file names. These new file names are recorded in the control file and used every time you perform the duplication in the future.

This example 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 log files 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 data files 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 data files of the duplicate database. Run the following script 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;

Example 2-80 Creating a Standby Database with the Same Directory Structure

Assume that you want to use RMAN backups to create a standby database on a remote host with the same directory structure as the source host. The source database is called prod1 and is the primary database in the Data Guard environment.

First, start the RMAN client, CONNECT to the source database prod1 as TARGET, and connect to the auxiliary instance. You can then CONFIGURE the default device type to sbt for a standby database with the DB_UNIQUE_NAME of standby1:

CONFIGURE DEFAULT DEVICE TYPE sbt FOR DB_UNIQUE_NAME standby1;
CONFIGURE DEVICE TYPE sbt PARALLELISM 2 FOR DB_UNIQUE_NAME standby1;

Assume all backups needed to create the standby database are on tape. In the standby database initialization parameter file, you set DB_UNIQUE_NAME to standby1.

The default initialization parameter file location is in use on the standby database. After starting the standby instance NOMOUNT, you start the RMAN client, CONNECT to the source database as TARGET, and connect to the auxiliary instance and recovery catalog. You run the following DUPLICATE command, specifying the NOFILENAMECHECK option because the standby and primary data files and online redo log files have the same names:

DUPLICATE TARGET DATABASE FOR STANDBY
  NOFILENAMECHECK;

Example 2-81 Creating a Standby Database in OMF and ASM

Assume that you want to use RMAN backups to create a standby database on a host that uses OMF and ASM. The source database is called prod1 and is the primary database in the Data Guard environment.

First, start the RMAN client, CONNECT to database prod1 as TARGET, and connect to the recovery catalog. Run the following commands to CONFIGURE the default device type to sbt for a standby database that has the DB_UNIQUE_NAME of standby1 and the net service name sby1.

CONFIGURE CONNECT IDENTIFIER "sby1" FOR DB_UNIQUE_NAME standby1;
CONFIGURE DEFAULT DEVICE TYPE TO sbt FOR DB_UNIQUE_NAME standby1;
CONFIGURE DEVICE TYPE sbt PARALLELISM 2 FOR DB_UNIQUE_NAME standby1;

Assume all backups needed to create the standby database are stored on tape. You set the following parameters in the initialization parameter file for database standby1:

• Set DB_UNIQUE_NAME to the value standby1.

• Set DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST to the desired ASM disk groups on the standby host. For example, set DB_CREATE_FILE_DEST to +DATAFILE and DB_RECOVERY_FILE_DEST to +FLASH_REC_AREA.

Ensure that the standby instance is in NOMOUNT mode. Start the RMAN client, CONNECT to database prod1 as TARGET, connect to the standby1 instance as AUXILIARY, and connect to the recovery catalog. Enter the following command to create the standby database:

DUPLICATE TARGET DATABASE FOR STANDBY;

RMAN automatically generates new OMF/ASM data file names for the restored data files.

Example 2-82 Duplicating a Database Without Connection to Target Database and Recovery Catalog

In this example, all the necessary backups of data files, controlfile and archived logs of database prod can be accessed from the location: /net/prod/backups. This location is where only backups of database prod reside. The only connection is to the new instance as AUXILIARY.

Enter the following command to create a test database when there is no connection to the recovery catalog or target database:

DUPLICATE DATABASE TO 'TEST' BACKUP LOCATION '/net/prod/backups' NOFILENAMECHECK;

Example 2-83 Selecting a Specific Database When Duplicating Without Connection to Target Database and Recovery Catalog

In this example, /backups contains backups from several databases, including more than two databases with the name PROD. In this case, you must specify the DBNAME and the DBID of the database that you want to duplicate. The only connection is to the auxiliary instance.

DUPLICATE DATABASE 'PROD' dbid 8675309 to 'TEST'
  UNTIL TIME "to_date('11/01/2007', 'MM/DD/YYYY')"
  BACKUP LOCATION '/backups' NOFILENAMECHECK
  PFILE='?/dbs/inittest.ora' db_file_name_convert='prod','test';