RMAN Duplicating Databases and Transporting Data

Renaming Files in a Duplicate Database

db_file_name_convert and the log_file_name_convert

Once you specify the spfile parameter during the database duplication, you can provide your list of initialization parameters and the values for them through the parameter_value_convert clause and the set clause. It’s easiest to use the parameter_value_convert clause in the duplicate database command to specify all parameters that specify a directory path. You can specify all such directory-related parameter values through the parameter_value_convert parameter, except for the db_file_name_convert and log_file_name_convert parameters.

Here’s an example showing how to use the parameter_value_convert clause and the set clause to specify various initialization parameter values for the duplicate instance right when you issue the duplicate database command:


duplicate target database
to dupdb
from active database spfile
parameter_value_convert '/a01', '/a20'
set sga_max_size = 800m
set sga_target = 700m
set log_file_name_convert = '/a01','/a20',
db_file_name_convert '/a01','/a20';


RMAN> run
{
set newname for datafile 1 to '/u01/app/oracle/testdata/file1.dbf';
set newname for datafile 2 to '/u01/app/oracle/testdata/file2.dbf';
...
duplicate target database to newdb nofilenamecheck;
}

Creating a Duplicate Database on the Same Host


1. Back up the target database as follows:

RMAN> connect target /
RMAN> backup database plus archivelog;

You’ll use these backups as the source for the database duplication later. Of course, if you already have made backups of the source database, you can skip this step.

2. Use a dedicated listener configuration for RMAN by making the following additions to your listener.ora file:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =prod1)
)
(SID_DESC =
(GLOBAL_DBNAME = test1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =test1)
)
)
3. Add the following information to the tnsnames.ora file, located in the
$ORACLE_HOME/network/admin directory:
test1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))

)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)

4. Create the init.ora file for the new duplicate database, test1. During the database duplication process, RMAN will create the control files, the data files, and the redo log files for the duplicate database with the filename structure you provide through the db_file_name_convert and log_file_name_convert initialization parameters. The database duplication process will create a control file for the new duplicate database in the location specified by the control_files initialization parameter.

db_name = test1
db_block_size = 8192
compatible = 11.1.0.1.0
remote_login_passwordfile = exclusive
control_files = ('/u01/app/oracle/test1/control01.ctl',
'/u01/app/oracle/test1/control02.ctl')
db_file_name_convert = ('/u01/app/oracle/oradata/prod1',
'/u05/app/oracle/oradata/test1')
log_file_name_convert = ('/u01/app/oracle/oradata/prod1',
'/u05/app/oracle/oradata/test1')

5. Start the new auxiliary database (duplicate database) instance. You must start the
new instance in nomount mode since you don’t have a control file for this new
database yet.

$ export ORACLE_SID=test1
$ sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance

SQL> startup nomount pfile=$ORACLE_HOME/dbs/inittest1.ora
Oracle Instance started.
Total System Global Area 113246208 bytes
Fixed Size 1218004 bytes
Variable Size 58722860 bytes


6. Start RMAN, and connect to the target database after making sure you first set the
ORACLE_SID environmental variable to the source database, prod1. Note that the target
database can be mounted or open.

$ rman

Recovery Manager: Release 11.1.0.1.0 - Beta on Sat Jun 9 14:03:42 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target
connected to target database: prod1 (DBID=2561840016)

7. Connect to the duplicate database using the keyword auxiliary through a SQL*Net
connection:

RMAN> connect auxiliary sys/@test1
connected to auxiliary database: test1 (not mounted)

RMAN>
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes

SQL> exit

8. Issue the duplicate target database command to start the database duplication
process:

RMAN> duplicate target database to test1;

SQL> select parameter, value from nls_database_parameters
where parameter in
('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET'
,'NLS_NCHAR_CHARACTERSET');


Duplicating a Database Without Any RMAN Backups

1. Use a dedicated listener configuration for RMAN by making the following additions to
your listener.ora file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =prod1)
)
(SID_DESC =
(GLOBAL_DBNAME = test1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =test1)
)
)

2. Add the following information to the tnsnames.ora file, located in the
$ORACLE_HOME/network/admin directory:
test1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)

3. Create a password file for connecting remotely to the duplicate database with the
sysdba privilege. This is because when you perform active database duplication, the
target database instance must connect directly to the auxiliary database instance. Note
that the password file connection requires the same sysdba password as that of the
source database. Create the password file manually with the orapwd utility, as shown
here, with just a single password to start the auxiliary instance:

$ orapwd password= file=orapwtest1 entries=20

4. Start the auxiliary database (duplicate database) instance. You must start the new
instance in nomount mode since you don’t have a control file for this new database yet.
$ export ORACLE_SID=test1
$ sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance

SQL> startup nomount
Oracle Instance started.
Total System Global Area 113246208 bytes
Fixed Size 218004 bytes
Variable Size 58722860 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes
SQL> exit


$rman target sys/sammyy1@eleven

RMAN> connect auxiliary sys/sammyy1@auxdb
connected to auxiliary database: AUXDB (not mounted)

RMAN> duplicate target database
2> to auxdb
3> from active database
4> spfile
5> parameter_value_convert =
'/u01/app/oracle/eleven/eleven','/u01/app/oracle/eleven/auxdb'
6> set log_file_name_convert =
'/u05/app/oracle/eleven/eleven','/u05/app/oracle/eleven/auxdb'
7> db_file_name_convert =
'/u05/app/oracle/eleven/eleven','/u05/app/oracle/eleven/auxdb';

RMAN> duplicate database
to newdb
from active database
spfile
nofilenamecheck;

If you’d rather create a standby database instead of a duplicate database, all you have to do is replace the to auxdb part of the duplicate database command with the for standby clause, as shown here:

RMAN> duplicate target database
for standby
from active database
spfile

It’s easy to duplicate a non-ASM file based database to an ASM file system–based database. Here we’ll show you a simple example to demonstrate how to do this. First create an ASM disk group, named +DISK1. Here’s the database duplication command to create an ASM file system–based duplicate database:


RMAN> duplicate target database
to newdb
from active database
spfile
parameter_value_convert
'/u01/app/oracle/oradata/sourcedb/','+DISK1'
set db_create_file_dest = +DISK1;

Creating a Duplicate Database on a Remote Host with the
Same File Structure

Solution
Let’s call our primary database PROD, the duplicate database AUX, and the RMAN catalog
database CATDB (the catalog is purely optional). Here are the steps to duplicate the primary
database on a different server:

1. Back up the primary database. You must take a full backup and include all the archive logs as well as the control file.

[oracle@linux] rman target=/ catalog rman/rman@catdb

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}

2. Ensure that the source database backups are available for the duplication process. You can get a complete list of the necessary backups by running the list backup command, as shown here:

RMAN>list backup;


3. Create the necessary directories for the duplicate database, and then create the initialization
parameter file for the duplicate database, as shown in the following example:
audit_file_dest =/oradata/AUX/adump
background_dump_dest =/oradata/AUX/bdump
core_dump_dest =/oradata/AUX/cdump
user_dump_dest =/oradata/AUX/udump
db_name ="AUX"
instance_name =AUX
control_files =('/oradata/AUX/control01.ctl',
'/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl')


4. Start up the auxiliary instance in nomount mode:
$ export ORACLE_SID=AUX
$ sqlplus '/as sysdba'

SQL> startup nomount;

5. Check the Oracle Net connections to the primary database and the recovery catalog.
The production database can be open or in a mounted state. The catalog database, if
you’re using it, must be open. Make sure you can connect to the primary database on
server A from the target server. Make sure you can also connect to the RMAN catalog
from the target server..

$ sqlplus 'sys/oracle@PROD as sysdba'
$ rman catalog rman/rman@catdb # not mandatory

$ export ORACLE_SID=AUX
$ rman target sys/sys@PROD catalog rman/rman@catdb auxiliary /

RMAN> duplicate target database to AUX
nofilenamecheck;

SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
900 150 20.34375 1070.34375

Duplicating a Database with Several Directories

Problem
You want to duplicate a source database with data spread out over several directories.

Solution
If the source database files are spread over multiple directories, you must use the set newname parameter instead of the db_file_name_convert parameter to rename the files in the duplicate database.
The following example shows how to create a duplicate database when the target database uses several different directories:

RMAN> run
{set newname for datafile 1 to '/u01/app/oracle/testdata/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/testdata/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/testdata/data01.dbf';
set newname for datafile 4 to '/u01/app/oracle/testdata/index01.dbf';
set newname for datafile 5 to '/u01/app/oracle/testdata/undotbs01.dbf';
duplicate target database to newdb
logfile
group 1 ('/u01/app/oracle/testdata/logs/redo01a.log',
('/u01/app/oracle/testdata/logs/redo01b.log') size 10m reuse,
group 2 ('/u01/app/oracle/testdata/logs/redo02a.log',
('/u01/app/oracle/testdata/logs/redo02b.log') size 10m reuse;
}

Creating a Standby Database on a New Host

1. Perform a full backup of the primary database, including all the archive logs and the current control file.

2. Calculate the disk space necessary for the standby database by summing up the size of all the datafiles in the database.

3. Ensure there is enough space on the target host to accommodate the standby database by using an operating system command such as df –kh.

4. Back up the target database as shown here with the 'backup current controlfile for
standby format' command:

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
backup current controlfile for standby format '/backups/PROD/sb_t%t_s%s_p%p';
release channel d1;
}

5. Copy the backups from Host A to Host B to make them available to the standby creation process. Make sure you place the backups in identically named directories on
Host B. For example, if your source backup files are in /backups/PROD, those backup
files must be copied to the /backups/PROD directory on Host B. If you decide to place
them in a different directory, as explained at the beginning of this chapter, you must run the catalog command ("catalog start with ") to update the source
database control file with the location of the backups. For the catalog command to
work, make sure you have the different directory structure on the source database
as well. You must also move the archive log backups to the target server. If you’re performing a point-in-time recovery, you may need only some of the archived redo logs:

RMAN> list backup;
List of Backup Sets

6. Create an initialization file (init.ora) for the standby database in the $ORACLE_HOME/dbs directory. Use the primary database’s initialization file as the source for the initialization

parameter settings:
audit_file_dest =/apps/oracle/admin/PROD/adump
background_dump_dest =/apps/oracle/admin/PROD/bdump
core_dump_dest =/apps/oracle/admin/PROD/cdump
user_dump_dest =/apps/oracle/admin/PROD/udump
db_name ="PROD"
instance_name =PROD
# Set the following to the location of the standby clone control file.
control_files=('/u01/PROD/control01.ctl','/u02/PROD/control02.ctl',
'/u03/PROD/control03.ctl')
# Set the following for the from and to location for all data files / redo
# logs to be cloned. This is set if the location differs from Primary.
db_file_name_convert =("/u01/oradata/PROD","/u02/oradata/PROD")
log_file_name_convert =("/u01/oradata/PROD","/u02/oradata/PROD")
#Set the following to the same as the production target
undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 11.1.0.1.0
7. Run the following command on the standby database server to start the auxiliary
instance in nomount state:
$ export ORACLE_SID=AUX
$ sqlplus '/ as sysdba'
SQL> startup nomount;
8. Test the SQL*Net connections to the primary database and the RMAN catalog. The
production database must be open or mounted, and the recovery catalog must be
open. Run the following commands on the standby database server:
$ sqlplus 'sys/oracle@prod1 as sysdba'
$ sqlplus rman/rman@catdb
Test that you’re connecting to the correct SID/service_name and the hostname by executing
the tnsping command for both the prod and rman connections.
9. Connect to the production database (target) and the auxiliary instance, and run the
RMAN duplicate command for the standby database, as shown here (again, you’ll be
running these commands on the standby database server):
$ export ORACLE_SID=PROD
$ rman target sys/sys@PROD auxiliary /
RMAN> run {
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
duplicate target database for standby nofilenamecheck;
}

Duplicating a Database to a Past Point in Time

RMAN> connect target sys/@targdb
RMAN> connect auxiliary sys/RMAN> duplicate target database
to dupdb
spfile
nofilenamecheck
until time 'sysdate-1';

run
{
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk
set until time "to_date('July 16 2007 12:00:00','Mon DD YYYY HH24:MI:SS')";
duplicate target database to aux;
}

Skipping Tablespaces During Database Duplication

run
{
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
duplicate database to aux
skip tablespace users, tools;
}

Duplicating a Database with a Specific Backup Tag

ProblemYou want to specify a particular backup tag during the duplication of a database.

Solution

You can “force” RMAN to use a specific backup during a database duplication process by simply making other backups unavailable. Here are the steps to follow:
1. Use the list backup of database command to find out the primary key of the backup
set you plan to use in the duplication process:

RMAN> list backup of database;
2. Make all the backup sets except the one you choose unavailable to RMAN during the
database duplication process by using the following command for each of the backups
you want to make inaccessible to RMAN:

RMAN> change backupset unavailable;
3. Follow the steps in recipe 15-2 or recipe 15-3 to duplicate the source database.
4. Once the database duplication is finished, make all the backups available to RMAN
again by issuing the following command for each of the backup sets you made unavailable
prior to the database duplication:

RMAN> change backupset available;
All the backup sets are once again “available” for use by RMAN.

RMAN> connect target /
RMAN> connect catalog rman/cat@catdb
RMAN> connect auxiliary sys/sammyy1@dupdb
RMAN> run {
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/drsys01';
configure auxname for datafile 5 to '/oradata5/example01.dbf';
configure auxname for datafile 6 to '/oradata6/indx01.dbf';
configure auxname for datafile 7 to '/oradata7/users01.dbf';
}
Synchronize the duplicate database with the source database by periodically executing
the duplicate target database command to re-create the duplicate database. For example:

RMAN> connect target /
RMAN> connect catalog rman/cat@catdb
RMAN> connect auxiliary sys/sammyy1@dupdb
RMAN> duplicate target database to dupdb
logfile
group 1 ('/duplogs/redo01a.log',
'/duplogs/redo01b.log') size 200k reuse,
group 2 ('/duplogs/redo02a.log',
'/duplogs/redo02b.log') size 200k reuse;