25 Duplicating Databases: Advanced Topics
Advanced forms of database duplication include methods of specifying alternative names for duplicate database files.
25.1 Specifying Alternative Names for Duplicate Database Files
If the source database and duplicate database do not use the same names for database files, then you must choose an alternative naming strategy for the duplicate files.
Depending on whether the source and duplicate databases use Oracle Managed Files (OMF) or Oracle Automatic Storage Management (ASM), use one of the following strategies:
25.1.1 Specifying Non-OMF or Non-ASM Alternative Names for Duplicate Database Files
When the source and duplicate database either use different directory structures or use the same structure but you want to name the duplicate files differently, then you must specify how duplicate database files must be named.
Table 25-1 summarizes the formats that are available for naming each type of file.
Table 25-1 Substitution Variables for the SET NEWNAME Command
Variable | Description |
---|---|
|
Specifies the file name stripped of directory paths. For example, if a data file is named |
|
Specifies the absolute file number of the data file for which the new name is generated. For example, if data file 2 is duplicated, then |
|
Specifies the DBID. |
|
Specifies the tablespace name. |
|
Specifies the following format: |
Use one of the following techniques to provide alternate names for non-OMF or non-ASM duplicate database files:
25.1.1.1 Using the SET NEWNAME Command to Name File System Data Files and Temp Files
Use the SET NEWNAME
command before you execute the DUPLICATE
command to name duplicate data files.
RMAN supports the following commands, listed in order of precedence:
-
SET NEWNAME FOR DATAFILE
andSET NEWNAME FOR TEMPFILE
-
SET NEWNAME FOR TABLESPACE
-
SET NEWNAME FOR DATABASE
The order of precedence means that the SET NEWNAME FOR TABLESPACE
command specifies names for files that are not named by the SET NEWNAME FOR DATAFILE
and SET NEWNAME FOR TEMPFILE
commands, whereas the SET NEWNAME FOR DATABASE
command specifies names for files that are not named by the SET NEWNAME FOR TABLESPACE
, SET NEWNAME FOR DATAFILE
, or SET NEWNAME FOR TEMPFILE
commands.
When yo use the SET NEWNAME FOR DATAFILE
command, you can specify a full path as a literal, as in /oradata1/system01.dbf
. However, when you use the SET
command with the FOR DATABASE
or FOR TABLESPACE
options, you must use at least one of these substitution variables, described in Table 25-1: %b
, %f
, %U
(%I
and %N
are optional).
To use the SET NEWNAME
command to specify new file names:
Example 25-1 Duplicating Databases with the SET NEWNAME FOR DATAFILE Command
This example illustrates a script that specifies new names for data files 1 through 5 and temp file 1. The script does not set a new name for data file 6 because it is in the TOOLS
tablespace, which is excluded from the duplicate database.
-
DBID is 87650928
-
Database name is
PROD
Use the following command to duplicate the database:
RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/oradata1/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/oradata2/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/oradata3/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/oradata4/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/oradata5/users02.dbf';
SET NEWNAME FOR TEMPFILE 1 TO '/oradatat/temp01.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 25-2 Duplicating Databases with the SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TABLESPACE Commands
This example is a variation of Example 25-1 and uses a single SET NEWNAME
command to name all data files in the tablespace users
. After the example completes, the file names for tablespace users
are set to: /oradata4/users01.dbf
and /oradata5/users02.dbf
.
Use the following command to duplicate the database:
{
SET NEWNAME FOR TABLESPACE users TO '/oradata%f/%b';
SET NEWNAME FOR DATAFILE 1 TO '/oradata1/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/oradata2/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/oradata3/undotbs01.dbf';
SET NEWNAME FOR TEMPFILE 1 TO '/oradatat/temp01.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 25-3 Duplicating Database with the SET NEWNAME FOR DATABASE Command
This example is a variation of Example 25-1 and uses a single SET
command to name all data files in the database.
Use the following command to duplicate the database:
RUN
{
SET NEWNAME FOR DATABASE TO '/oradata/%U';
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;
}
The following table shows the results from this example.
Table 25-2 Results for the SET NEWNAME DATABASE Command
Before SET NEWNAME DATABASE | Tablespace Name | Data File Number | After SET NEWNAME DATABASE TO '/oradata/%U'; |
---|---|---|---|
|
|
1 |
|
|
|
2 |
|
|
|
3 |
|
|
|
4 |
|
|
|
5 |
|
|
|
1 |
|
See Also:
Oracle Database Backup and
Recovery Reference for details on substitution variables usable in SET NEWNAME
25.1.1.2 Using the CONFIGURE AUXNAME Command to Name File System Data Files and OMF or ASM Target Data Files
The CONFIGURE AUXNAME
command is an alternative to the SET NEWNAME
command. The difference is that after you configure the auxiliary name the first time, additional DUPLICATE
commands reuse the configured settings. In contrast, you must reissue the SET NEWNAME
command every time you execute the DUPLICATE
command.
To use the CONFIGURE AUXNAME
command to specify names for duplicate data files:
-
Issue a
CONFIGURE AUXNAME
command for each file to name in the duplicate database.For example, enter the following commands at the RMAN prompt to specify names for files data files 1 through 5:
CONFIGURE AUXNAME FOR DATAFILE 1 TO '/oradata1/system01.dbf'; CONFIGURE AUXNAME FOR DATAFILE 2 TO '/oradata2/sysaux01.dbf'; CONFIGURE AUXNAME FOR DATAFILE 3 TO '/oradata3/undotbs01.dbf'; CONFIGURE AUXNAME FOR DATAFILE 4 TO '/oradata4/users01.dbf'; CONFIGURE AUXNAME FOR DATAFILE 5 TO '/oradata5/users02.dbf';
-
Issue a
DUPLICATE
command.For example, enter the following command at the RMAN prompt:
SET NEWNAME FOR TEMPFILE 1 TO '/oradatat/temp01.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;
See Also:
Oracle Database Backup and
Recovery Reference for details on using CONFIGURE AUXNAME
25.1.2 Specifying OMF or ASM Alternative Names for Duplicate Database Files
You must specify how to name duplicate database files when the source database and duplicate database do not use the same names for database files.
The following sections discuss requirements and procedures for creating a duplicate database when some or all files of the duplicate database use OMF or ASM:
-
Using the DB_FILE_NAME_CONVERT Parameter to Generate Names for Non-OMF or ASM Data Files
-
Using the LOG_FILE_NAME_CONVERT Parameter to Generate Names for Non-OMF or ASM Log Files
See Also:
Oracle Automatic Storage Management Administrator's Guide for an introduction to ASM and OMF
25.1.2.1 Settings and Restrictions for OMF Initialization Parameters
When you create a duplicate database that uses OMF, you must set initialization parameters in the auxiliary instance. If you use the SPFILE
option of the DUPLICATE
command to name the files, then you can set initialization parameters in the SPFILE
option.
Table 25-3 describes the relevant parameters and recommended settings.
Table 25-3 Initialization Parameters for Oracle Managed Files
Initialization Parameter | Purpose | Recommendation |
---|---|---|
|
Specifies the default location for Oracle managed data files. This location is also the default location for Oracle managed control files and online logs if no |
Set this parameter to the location for OMF. Any database files that have no location specified are created in the directory specified by the |
|
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 online redo log files in multiple locations. |
|
Specifies the default location for the fast recovery area. The fast recovery area contains multiplexed copies of current control files and online redo log files. |
Set this parameter if you want a multiplexed copy of the control file and online redo log file in the recovery area. |
Table 25-4 lists the restrictions on setting other initialization parameters.
Table 25-4 Initialization Parameter Restrictions for Oracle Managed Files
Initialization Parameter | Purpose | Restriction |
---|---|---|
|
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 OMF format. When you use control files in an OMF format, Oracle recommends that you use a server parameter file at the duplicate database . |
|
Converts the file name of a new data file on the primary database to a file name on the duplicate database. |
Do not set this parameter. Omitting this parameter enables the database to generate valid Oracle managed file names for the duplicate data files. |
|
Converts the file name of a new log file on the primary database to the file name of a log file on the standby database. |
Do not set this parameter. Omitting this parameter allows the database to generate valid Oracle managed online redo log file names. To direct duplicate database online redo log files to Oracle managed storage, you can use the |
25.1.2.2 Setting Initialization Parameters for ASM
You must identify the initialization parameters that control the location where files are created and set these parameters to an ASM disk group.
The procedure for creating a duplicate database to an ASM location is similar to that of creating a duplicate database to OMF. For example, set the DB_CREATE_FILE_DEST
, DB_CREATE_ONLINE_DEST_n
, and CONTROL_FILES
parameters to +DISK1
.
25.1.2.3 Examples: Duplicating Databases to ASM
This section contains examples on duplicating databases to ASM.
Example 25-4 Duplicating a Database from a File System to ASM
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 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 data files 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 databases, run the following RMAN script to duplicate the database.
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 data files, online redo log files, and control files in ASM disk group +DISK1
.
Example 25-5 Duplicating a Database from ASM to ASM
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 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 data files in ASM disk group +DISK1
. You want to duplicate the target to database dupdb
on remote host host2
. You want to store the data files for dupdb
in ASM. Specifically, you want to store the data files 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 file names in +DISK2
are generated by ASM and do not match the original file names in disk group +DISK1
.
After connecting to the target, duplicate, and catalog databases, run the following RMAN script to duplicate the database.
DUPLICATE TARGET DATABASE
TO dupdb
FROM ACTIVE DATABASE
SPFILE PARAMETER_VALUE_CONVERT '+DISK1','+DISK2'
SET DB_RECOVERY_FILE_DEST_SIZE='750G';
When the DUPLICATE
command completes, the duplicate database is created, with data files, online redo logs, and control files in the larger ASM disk group +DISK2
.
25.1.2.4 Using the SET NEWNAME Command to Create OMF or ASM Files
To name Oracle managed data files, you can use the SET NEWNAME
command with the TO NEW
option instead of the TO 'filename'
option. RMAN creates the specified data files or temp files with OMF names in the location specified by the DB_CREATE_FILE_DEST
parameter.
To use the SET NEWNAME
command to specify names for OMF:
-
Set the
DB_CREATE_FILE_DEST
initialization parameter at the auxiliary instance to the desired location. -
Enclose the
DUPLICATE
command in aRUN
block and use theSET NEWNAME
command with theTO NEW
option for OMF.
Example 25-6 Duplicating Databases with the SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TABLESPACE Commands
This example illustrates a script that specifies literal names for data files 1-5. The only Oracle Managed Files in the source database are the data files in the users
tablespace. Therefore, the TO NEW
option is specified in the SET NEWNAME
command for these files.
RUN
{
SET NEWNAME FOR TABLESPACE users TO NEW;
SET NEWNAME FOR DATAFILE 3 TO NEW;
SET NEWNAME FOR DATAFILE 1 TO '/oradata1/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/oradata2/sysaux01.dbf';
SET NEWNAME FOR TEMPFILE 1 TO '/oradatat/temp01';
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 25-7 Using the SET NEWNAME Command to Create Files in an ASM Disk Group
This example uses the SET NEWNAME
command to direct individual data files, temp files, or tablespaces to a specified 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;
}
25.1.2.5 Using the DB_FILE_NAME_CONVERT Parameter to Generate Names for Non-OMF or ASM Data Files
In addition to using the SET NEWNAME
or CONFIGURE AUXNAME
commands, you can use the DB_FILE_NAME_CONVERT
parameter to transform target file names. You can either specify the DB_FILE_NAME_CONVERT
option in the DUPLICATE
command or you can set it in the initialization parameter file of the auxiliary instance.
For example, you can change the target file name from the /oracle/
directory to the /dup/oracle/
directory. The DB_FILE_NAME_CONVERT
parameter allows you to specify multiple conversion file name pairs, however, starting with Oracle Database 12c Release 2 (12.2), Oracle recommends that you do not exceed 99 pairs of file name strings. The DB_FILE_NAME_CONVERT
parameter can also be used to produce names for data files and temp files.
Restrictions of the DB_FILE_NAME_CONVERT Parameter
You cannot use the DB_FILE_NAME_CONVERT
option of the DUPLICATE
command to control generation of new names for files at the duplicate instance that are in the OMF format at the source database instance.
25.1.2.6 Using the LOG_FILE_NAME_CONVERT Parameter to Generate Names for Non-OMF or ASM Log Files
If the LOG_FILE
clause has been omitted and none of the OMF initialization parameters DB_CREATE_FILE_DEST
, DB_CREATE_ONLINE_DEST_n
, or DB_RECOVERY_FILE_DEST
are specified, then the LOG_FILE_NAME_CONVERT
parameter can transform target file names. This works in the same way that the DB_FILE_NAME_CONVERT
parameter does to transform target file names from log_*
to duplog_*
.
You can specify multiple conversion file name pairs with this parameter. When you specify the LOG_FILE_NAME_CONVERT
parameter, RMAN uses the REUSE
parameter when creating the online redo logs. If an online redo log file exists at the named location and is of the correct size, then it is reused for the duplication process.
Restrictions of the LOG_FILE_NAME_CONVERT Parameter
-
If you set OMF initialization parameters, do not specify the
LOG_FILE_NAME_CONVERT
parameter. -
The
LOG_FILE_NAME_CONVERT
parameter cannot be specified as a clause in theDUPLICATE
command, it can only be specified in the initialization parameter of the auxiliary instance. -
You cannot use the
LOG_FILE_NAME_CONVERT
initialization parameter to control generation of new names for files at the duplicate instance that are in the OMF format at the source database instance.
25.2 Making Disk Backups Accessible Without Shared Disk
You can use the shared disk technique to make backups available to the auxiliary instance. 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, unless the BACKUP LOCATION
option is used for the DUPLICATE
command without a target or recovery catalog connection.
Assume that you maintain two hosts, srchost
and dsthost
. The database on srchost
is srcdb
. The RMAN backups of srcdb
reside in the /dsk1/bkp
directory on host srchost
. The directory /dsk1/bkp
is in use on the destination host, so you intend to store backups in the /dsk2/dup
directory on the destination host.
To transfer the backups from the source host to the destination host:
-
Create a directory in the source host that has the same name as the directory on the destination host that contains the backups.
For example, if you intend to store the RMAN backups in the
/dsk2/dup
directory on the destination host, then create the/dsk2/dup
directory on the source host. -
On the source host, copy the backups to the directory created in the previous step, and then catalog the backups. You can use either of the following approaches:
- Connect RMAN to the source database as
TARGET
and use theBACKUP
command to back up the backups. For example, use theBACKUP 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. If you are duplicating a PDB, then use thePLUGGABLE DATABASE
syntax of theBACKUP COPY OF
command to copy only the backups of the PDB. - Use an operating system utility to copy the backups in the
/dsk1/bkp
directory on the source host to the/dsk2/dup
directory on the source host. Afterward, connect RMAN to the source database asTARGET
and use theCATALOG
command to update the source control file with the location of the manually transferred backups.
- Connect RMAN to the source database as
-
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 the
/dsk2/dup
directory on the source host to the/dsk2/dup
directory on the destination host.
/dsk2/dup
directory on the destination host and restore them.