RMAN DUPLICATE DATABASE

Source Server - lnx133
Source database name: maze
Destination Server -lnx144

Goal: to clone database 'maze' on lnx144.

On source side:
lnx133

Step 1:
Source database 'maze' is in archivelog mode, ready to take hot backup.
Take a full backup of source database
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

SQL> Alter system switch logfile;



Step 2 : 
Ensure password file exists on the source database server (lnx133)

[oracle@lnx133 dbs]$ pwd
/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs
[oracle@lnx133 dbs]$ ls -ltr orapwmaze
-rw-r----- 1 oracle oinstall 1536 Apr 25 12:09 orapwmaze

Step3: 
Ensure service name is available in tnsnames.ora on target server (lnx144)

[oracle@lnx144 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11g/11.2.0.2/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

sourcemaze  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = maze)
    )
  )


[oracle@lnx144 admin]$ tnsping sourcemaze
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 25-APR-2013 16:01:58
Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/network/admin/sqlnet.ora


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

Step4:

Create pfile, this step matters if we are dealing with different directory structure. In this run directory structure is identical, so simply copying the pfile from source to targe t(lnx144), will serve the purpose.

Based on this you can start instance in nomount. (env variables are to be set).

Step 5:

SQL> startup nomount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1344616 bytes
Variable Size             327158680 bytes
Database Buffers           88080384 bytes
Redo Buffers                6086656 bytes

From source location copy the backup files (along with archivelog) to target server (at the same backup location on disk)



[oracle@lnx133 rman]$ scp H* 192.168.1.144:/u01/app/oracle/backup/rman/.
oracle@192.168.1.144's password:
Hotbkp_MAZE_4so7v8j8_1_1.bak                                                                           100%  264MB   8.8MB/s   00:30
Hotbkp_MAZE_4to7v8j9_1_1.bak                                                                           100%  251MB   6.8MB/s   00:37
Hotbkp_MAZE_4uo7v8j9_1_1.bak                                                                           100%  245MB   7.2MB/s   00:34
Hotbkp_MAZE_4vo7v8j9_1_1.bak                                                                           100%  217MB   7.5MB/s   00:29
Hotbkp_MAZE_50o7v94i_1_1.bak                                                                           100%  146MB   6.4MB/s   00:23
Hotbkp_MAZE_51o7v94j_1_1.bak                                                                           100%  197MB   6.2MB/s   00:32
Hotbkp_MAZE_52o7v94k_1_1.bak                                                                           100% 6656KB   6.5MB/s   00:01
Hotbkp_MAZE_53o7v94k_1_1.bak                                                                           100%   21MB   3.0MB/s   00:07
Hotbkp_MAZE_54o7v9ac_1_1.bak                                                                           100% 1168KB   1.1MB/s   00:00
Hotbkp_MAZE_55o7v9af_1_1.bak                                                                           100%  624KB 624.0KB/s   00:00
Hotbkp_MAZE_56o7v9em_1_1.bak                                                                           100% 6601KB   6.5MB/s   00:01
[oracle@lnx133 rman]$  scp ctl_n_spfile_c-1370075061-20130425-00 192.168.1.144:/u01/app/oracle/backup/rman/.
oracle@192.168.1.144's password:
ctl_n_spfile_c-1370075061-20130425-00                                                                  100% 9696KB   9.5MB/s   00:01

Step 6:
Required if you have a different directory structure 
(create same directories to have a consistent directory structure.

Step 7:
Environment is ready to start the duplicate database step.
[oracle@lnx144 dbs]$ rman target system/abc123@sourcemaze auxiliary /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 25 16:54:32 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MAZE (DBID=1370075061)
connected to auxiliary database: MAZE (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO maze
  SPFILE
  NOFILENAMECHECK;2> 3>

Starting Duplicate Db at 25-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=10 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=135 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 device type=DISK

contents of Memory Script:
{
   set until scn  19416113;
   restore clone spfile to  '/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs/spfilemaze.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs/spfilemaze.ora''";
}
executing Memory Script

executing command: SET until clause

Starting restore at 25-APR-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

WARNING: A restore time was estimated based on the supplied UNTIL SCN
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs/spfilemaze.ora
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/rman/ctl_n_spfile_c-1370075061-20130425-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/rman/ctl_n_spfile_c-1370075061-20130425-00 tag=TAG20130425T115818
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-APR-13

sql statement: alter system set spfile= ''/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs/spfilemaze.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''MAZE'' comment=
 ''duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''MAZE'' comment= ''duplicate'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                356518808 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6086656 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''MAZE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''MAZE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''MAZE'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''MAZE'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                356518808 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6086656 bytes

Starting restore at 25-APR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=10 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=134 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/rman/ctl_n_spfile_c-1370075061-20130425-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/rman/ctl_n_spfile_c-1370075061-20130425-00 tag=TAG20130425T115818
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata1/maze/control01.ctl
output file name=/u01/app/oracle/oradata2/maze/control02.ctl
Finished restore at 25-APR-13

database mounted
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
Using previous duplicated file /u01/app/oracle/oradata1/maze/system01.dbf for datafile 1 with checkpoint SCN of 19409868
Using previous duplicated file /u01/app/oracle/oradata1/maze/sysaux01.dbf for datafile 2 with checkpoint SCN of 19409867
Using previous duplicated file /u01/app/oracle/oradata1/maze/undotbs01.dbf for datafile 3 with checkpoint SCN of 19409869
Using previous duplicated file /u01/app/oracle/oradata2/maze/users01.dbf for datafile 4 with checkpoint SCN of 19409867
Using previous duplicated file /u01/app/oracle/oradata1/maze/example01.dbf for datafile 5 with checkpoint SCN of 19409870
Using previous duplicated file /u01/app/oracle/oradata1/maze/rcts01.dbf for datafile 6 with checkpoint SCN of 19410389
Using previous duplicated file /u01/app/oracle/oradata1/maze/undotbs02.dbf for datafile 7 with checkpoint SCN of 19410350
Using previous duplicated file /u01/app/oracle/oradata1/maze/appstbs_01.dbf for datafile 8 with checkpoint SCN of 19410350

contents of Memory Script:
{
   catalog clone datafilecopy  "/u01/app/oracle/oradata1/maze/system01.dbf",
 "/u01/app/oracle/oradata1/maze/sysaux01.dbf",
 "/u01/app/oracle/oradata1/maze/undotbs01.dbf",
 "/u01/app/oracle/oradata2/maze/users01.dbf",
 "/u01/app/oracle/oradata1/maze/example01.dbf",
 "/u01/app/oracle/oradata1/maze/rcts01.dbf",
 "/u01/app/oracle/oradata1/maze/undotbs02.dbf",
 "/u01/app/oracle/oradata1/maze/appstbs_01.dbf";
   switch clone datafile  1 to datafilecopy
 "/u01/app/oracle/oradata1/maze/system01.dbf";
   switch clone datafile  2 to datafilecopy
 "/u01/app/oracle/oradata1/maze/sysaux01.dbf";
   switch clone datafile  3 to datafilecopy
 "/u01/app/oracle/oradata1/maze/undotbs01.dbf";
   switch clone datafile  4 to datafilecopy
 "/u01/app/oracle/oradata2/maze/users01.dbf";
   switch clone datafile  5 to datafilecopy
 "/u01/app/oracle/oradata1/maze/example01.dbf";
   switch clone datafile  6 to datafilecopy
 "/u01/app/oracle/oradata1/maze/rcts01.dbf";
   switch clone datafile  7 to datafilecopy
 "/u01/app/oracle/oradata1/maze/undotbs02.dbf";
   switch clone datafile  8 to datafilecopy
 "/u01/app/oracle/oradata1/maze/appstbs_01.dbf";
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/system01.dbf RECID=11 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/sysaux01.dbf RECID=12 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/undotbs01.dbf RECID=13 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata2/maze/users01.dbf RECID=14 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/example01.dbf RECID=15 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/rcts01.dbf RECID=16 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/undotbs02.dbf RECID=17 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/appstbs_01.dbf RECID=18 STAMP=813689712

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/system01.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=813689712 file name=/u01/app/oracle/oradata2/maze/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/example01.dbf

datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/rcts01.dbf

datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/undotbs02.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/appstbs_01.dbf

contents of Memory Script:
{
   set until scn  19416113;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 25-APR-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 113 is already on disk as file /u01/archlogs/maze/1_113_811523932.arc
archived log for thread 1 with sequence 114 is already on disk as file /u01/archlogs/maze/1_114_811523932.arc
archived log file name=/u01/archlogs/maze/1_113_811523932.arc thread=1 sequence=113
archived log file name=/u01/archlogs/maze/1_114_811523932.arc thread=1 sequence=114
media recovery complete, elapsed time: 00:00:24
Finished recover at 25-APR-13
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                356518808 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6086656 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''MAZE'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''MAZE'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                356518808 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6086656 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "MAZE" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata1/maze/redo01.log', '/u01/app/oracle/oraredo2/maze/redo04.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oraredo2/maze/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oraredo2/maze/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata1/maze/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata1/maze/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata1/maze/sysaux01.dbf",
 "/u01/app/oracle/oradata1/maze/undotbs01.dbf",
 "/u01/app/oracle/oradata2/maze/users01.dbf",
 "/u01/app/oracle/oradata1/maze/example01.dbf",
 "/u01/app/oracle/oradata1/maze/rcts01.dbf",
 "/u01/app/oracle/oradata1/maze/undotbs02.dbf",
 "/u01/app/oracle/oradata1/maze/appstbs_01.dbf";
   switch clone datafile all;
   switch clone datafile  2 to datafilecopy
 "/u01/app/oracle/oradata1/maze/sysaux01.dbf";
   switch clone datafile  3 to datafilecopy
 "/u01/app/oracle/oradata1/maze/undotbs01.dbf";
   switch clone datafile  4 to datafilecopy
 "/u01/app/oracle/oradata2/maze/users01.dbf";
   switch clone datafile  5 to datafilecopy
 "/u01/app/oracle/oradata1/maze/example01.dbf";
   switch clone datafile  6 to datafilecopy
 "/u01/app/oracle/oradata1/maze/rcts01.dbf";
   switch clone datafile  7 to datafilecopy
 "/u01/app/oracle/oradata1/maze/undotbs02.dbf";
   switch clone datafile  8 to datafilecopy
 "/u01/app/oracle/oradata1/maze/appstbs_01.dbf";
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata1/maze/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/sysaux01.dbf RECID=1 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/undotbs01.dbf RECID=2 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata2/maze/users01.dbf RECID=3 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/example01.dbf RECID=4 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/rcts01.dbf RECID=5 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/undotbs02.dbf RECID=6 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/appstbs_01.dbf RECID=7 STAMP=813689760


datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=813689760 file name=/u01/app/oracle/oradata2/maze/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/example01.dbf

datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/rcts01.dbf

datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/undotbs02.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/appstbs_01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data

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

database opened
Finished Duplicate Db at 25-APR-13
RMAN>exit


SQL> select instance_name, host_name, status from v$instance;

INSTANCE_NAME    HOST_NAME                 STATUS
---------------- ------------------------- ------------
maze             lnx144                    OPEN

Your cloned database is ready to  use!!!