How to Clone an Oracle Database using the RMAN DUPLICATE

One of the most important tasks that a DBA must know and understand is database cloning. Database cloning will be required for different reasons such as creating replicas for your development team or you want to stress test your database and you need a copy of it. We will see in this article one of the many ways we can clone an Oracle database using RMAN DUPLICATE. You can use the RMAN DUPLICATE command to create a duplicate database from backups of the target database(pri database) while retaining the original target database. The duplicate database can be identical to the target database or contain only a subset of the tablespaces in the target database. The target site and the duplicate site can be on separate hosts or on the same host. Just as a reminder remember that a duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. In the image bellow is a description of the process and the components that are required to create a stb using duplicate command. Let's see the list of tasks we have to go thrum in order to successfully stb our Oracle database using duplicate command

1-Install the Oracle database Software on the new host(if you haven't done yet)
2-Create the Target Database(on any host as long as the hosts can communicate to each other)
3-Create the listener.ora and tnsnames.ora files for the new Target Database(stb)
4-Create entries in the tnsnames.ora files on both server(pri and stb)
5-Create an Oracle Password File for the Auxiliary Instance
6-Establish Oracle Net Connectivity to the Auxiliary Instance
7-Create a full backup on the pri database
8-Create the rename script
8-Put the stb database in nomount mode
9-Connect to RMAN
10-Run the DUPLICATE command/script(datafile rename and other parameters are treated at this point)

1-Install the Oracle database Software on the new host(if you haven't done yet) If you haven't done it yet and you have problems doing it follow this howto article on how to install Oracle Database Software. 2-Create the Target Database(on any host as long as the hosts can communicate to each other) In this task you will create a new database from scratch. This will receive our duplicate database. You can create the database using DBCA(which i recommend). 3-Create the listener.ora and tnsnames.ora files for the new Target Database(stb) We will need to make sure the the new stb database will be available for connections. So for this you need to add specific entries in the listener.ora file and also in the tnsnames.ora file. Normally the network configurations are located in the $ORACLE_HOME/network/admin or in my case /oracle/app/product/11.2.0/dbhome_1/network/admin ; the listener.ora and tnsnames.ora can be create by hand or using the NETMGR tool provided by Oracle. Here is an example of listener entry for the stb database:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server.domain)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
   (SID_LIST =
       (SID_DESC =
          (ORACLE_HOME= /oracle/app/product/11.2.0/dbhome_1)
          (SID_NAME = stb)
          (GLOBAL_DBNAME= stb)
       )
    )

Here is an example of listener entry for the stb database:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY)(PORT = 1521))
    )
  )
--add this to so you can log in with RMAN when your stb db is in nomount mode.
--this is a static listener entry
SID_LIST_LISTENER =
   (SID_LIST =
       (SID_DESC =
          (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)
          (SID_NAME = stb)
          (GLOBAL_DBNAME= stb)
       )
    )

Start you listener and make sure you can access your database using tns alias. Here is an example how you can do this: Start listener

[oracle@STANDBY admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 14-SEP-2014 09:06:51

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/STANDBY/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                14-SEP-2014 09:06:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/STANDBY/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))
Services Summary...
Service "stb" has 1 instance(s).
  Instance "stb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

4-Create entries in the tnsnames.ora files on both server(pri and stb) Test connection using TNS alias Before doing so you need to create tns aliases for both your database instances(pri and stb). In both hosts network directories make sure you create the tnsnames.ora file and edit the file by adding the following entry. I will use my tns aliases as an example.

STB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stb)
    )
  )

PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pri)
    )
  )

Make sure your hosts can recognize each other by name. If not add the necessary entry into you /etc/hosts file. Test that you can ping both instances.

[oracle@STANDBY admin]$ tnsping stb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-SEP-2014 09:07:25

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stb)))
OK (0 msec)

5-Create an Oracle Password File for the Auxiliary Instance When we create duplicate via RMAN we need to create a orapw file in the stb instance. What i use to do in order to avoid problems i make sue that the sys user on my stb instance will have the same password as the sys user in the pri instance. Let's get to work then:

sql:alter user sys identified by "your pri password";

Once the stb sys password was alter create a new password file.

# Go to 
[oracle@STANDBY admin]$ cd $ORACLE_HOME/dbs
[oracle@STANDBY dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@STANDBY dbs]$ ll orapwstb
-rw-r----- 1 oracle oinstall 1536 Sep 14 08:52 orapwstb
# Remove the old orapwstb
[oracle@STANDBY dbs]$ rm -f orapwstb
# Re-create the orapwd file
[oracle@STANDBY dbs]$ orapwd file=orapwstb password=******* ignorecase=y
[oracle@STANDBY dbs]$ ll orapwstb
-rw-r----- 1 oracle oinstall 1536 Sep 14 09:59 orapwstb

6-Establish/Check Oracle Net Connectivity to the Auxiliary Instance You need to do this from both server(primary and standby)

--Test on standby host
[oracle@STANDBY dbs]$ rman target sys/*******@pri auxiliary sys/*******@stb

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Sep 14 10:03:08 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRI (DBID=788900126)
connected to auxiliary database: STB (DBID=783068347)

RMAN

--Test on primary host

[oracle@primary dbs]$ rman target sys/*******@pri auxiliary sys/*******@stb

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Sep 14 10:04:22 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRI (DBID=788900126)
connected to auxiliary database: STB (DBID=783068347)

7-Create a full backup on the pri database.

$ rman target /

RMAN CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN BACKUP DATABASE PLUS ARCHIVELOG

8-Create the rename script. This is needed because the hosts have different mount points and you need to alter the location. We will use this two scripts to create the rename rman script. Run the scripts on the pri instance. Script to rename datafile to point to the new mount/point location.

 select 'set newname for datafile '||file#||' to ''/new/mount/point'|| substr(name,instr(name,'/',-1))||''';' as remane_dbf from v$datafile;

REMANE_DBF
--------------------------------------------------------------------------------
set newname for datafile 1 to '/new/mount/point/system01.dbf';
set newname for datafile 2 to '/new/mount/point/sysaux01.dbf';
set newname for datafile 3 to '/new/mount/point/undotbs01.dbf';
set newname for datafile 4 to '/new/mount/point/users01.dbf';

Script to rename temp files to point to the new mount/point location.

 select 'set newname for tempfile '||file#||' to ''/new/mount/point'|| substr(name,instr(name,'/',-1))||''';' as remane_tmp from v$tempfile;
REMANE_TMP
--------------------------------------------------------------------------------
set newname for tempfile 1 to '/new/mount/point/temp01.dbf';

Create the RMAN script that will be used.

run {
allocate channel P1 device type disk;
allocate auxiliary channel AUX1 device type disk;
set newname for datafile 1 to '/new/mount/point/system01.dbf';
set newname for datafile 2 to '/new/mount/point/sysaux01.dbf';
set newname for datafile 3 to '/new/mount/point/undotbs01.dbf';
set newname for datafile 4 to '/new/mount/point/users01.dbf';
set newname for tempfile 1 to '/new/mount/point/temp01.dbf';
duplicate target database to stb from active database;
}

9-Put the stb database in nomount mode In order to run the RMAN duplicate commnad you need to have your stb instance in nomount mode.

 startup force nomount;
ORACLE instance started.

Total System Global Area  221294592 bytes
Fixed Size                  2212048 bytes
Variable Size             104861488 bytes
Database Buffers          109051904 bytes
Redo Buffers                5169152 bytes

Connect to RMAN(connect on the stb host)

[oracle@STANDBY dbs]$ rman target sys/*******@pri auxiliary sys/*******@stb

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Sep 14 10:23:23 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRI (DBID=788900126)
connected to auxiliary database: STB (not mounted)

RMAN

10-Run the DUPLICATE command/script(datafile rename and other parameters are treated at this point)

RMAN run {
allocate channel P1 device type disk;
allocate auxiliary channel AUX1 device type disk;
set newname for datafile 1 to '/u01/app/oracle/oradata/stb/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/stb/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/stb/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/stb/users01.dbf';
set newname for tempfile 1 to '/u01/app/oracle/oradata/stb/temp01.dbf';
duplicate target data base to STB from active database;
}
.........
.........
.........
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=858249753 file name=/u01/app/oracle/oradata/stb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=858249754 file name=/u01/app/oracle/oradata/stb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=858249754 file name=/u01/app/oracle/oradata/stb/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 14-SEP-14
released channel: P1
released channel: AUX1

RMAN

Done !

Search Loading... Please wait

Subscribe to our Newsletter

Buy me a coffee

Join our Youtube Channel

A Rock Solid, Responsive CSS Framework built to work on all devices big, small and in-between