How to clone a Oracle Database using RMAN

Cloning of Database includes below mentioned steps :-

1. Take a fresh backup of your OLD database of which you want to make a clone.

Refer to this article for taking RMAN consistent or Inconsistent backups :-
How to backup of Oracle Database using RMAN

2. Create a pfile for OLD database.

SQL> create pfile='/home/cognos/pfileorcl.ora' from spfile;

File created.

3. PFB contents of OLD database pfile . 

Modify the parameters in green colour of this pfile according to the New Database server.

*.audit_file_dest='/u04/app/cognos/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='+AMIT_R1/orcl/controlfile/current.261.790789011','+AMIT_R1/orcl/controlfile/current.260.790789011'
*.db_block_size=8192
*.db_create_file_dest='+AMIT_R1'
*.db_domain='com'
*.db_name='orcl'
*.db_recovery_file_dest='+AMIT_R1'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u04/app/cognos'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.java_pool_size=64m
*.log_archive_dest_1='LOCATION=/backup/orcl/'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=1026870912
*.sga_target=1026870912
*.undo_tablespace='UNDOTBS1'

4. Move Backup pieces and pfile to new database server.

5. Use the above pfile to startup in nomount mode the NEW database. 
 
SQL> startup nomount pfile='/home/cognos/pfileorcl.ora'
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
SQL>

6. Restore control files of OLD database to new host database.

RMAN> restore controlfile from '/backup/BKPSETS_20OCT12_CONTROLFILE_0sno8cm4_1_1';

Starting restore at 20-OCT-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output file name=+EXAMPLE/orcl/controlfile/current.267.797196775
output file name=+EXAMPLE/orcl/controlfile/current.268.797196777
Finished restore at 20-OCT-12


7. Mount your New Database.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

8. Catalog all Backup pieces of OLD database server to new database server.

RMAN> catalog backuppiece '/backup/BKPSETS_20OCT12_0ino8cf8_1_1','/backup/BKPSETS_20OCT12_0lno8cfa_1_1','/backup/BKPSETS_20OCT12_0kno8cf8_1_1','/backup/BKPSETS_20OCT12_0rno8cm2_1_1','/backup/BKPSETS_20OCT12_0sno8cm4_1_1';

cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0ino8cf8_1_1 RECID=29 STAMP=797197934
cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0lno8cfa_1_1 RECID=30 STAMP=797197935
cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0kno8cf8_1_1 RECID=31 STAMP=797197935
cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0rno8cm2_1_1 RECID=32 STAMP=797197935
cataloged backup piece
backup piece handle=/backup/BKPSETS_20OCT12_0sno8cm4_1_1 RECID=33 STAMP=797197935

 9. Run the restore command to restore the datafiles.

TO run restore command you need the details of the datafiles of OLD database.
Login into the OLD database and get below mentioned details:-

SQL> Select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- --------------------------------------------------------------------------------                  
         6 +AMIT_R1/orcl/datafile/amit.271.795743985
         5 +AMIT_R1/orcl/datafile/example.269.790789095
         4 +AMIT_R1/orcl/datafile/users.259.790788813
         3 +AMIT_R1/orcl/datafile/undotbs1.258.790788813
         2 +AMIT_R1/orcl/datafile/sysaux.257.790788811
         1 +AMIT_R1/orcl/datafile/system.256.790788811

6 rows selected.


Now run the resore command in NEW database host in RMAN prompt :-

RMAN> run
2> {
3> set newname for datafile 1 to '+EXAMPLE';
4> set newname for datafile 2 to '+EXAMPLE';
5> set newname for datafile 4 to '+EXAMPLE';
6> set newname for datafile 3 to '+EXAMPLE';
7> set newname for datafile 5 to '+EXAMPLE';
8> set newname for datafile 6 to '+EXAMPLE';
9> restore database;
10> switch datafile all;
11> }

The Command will give you below mentioned output:-

RMAN> run
 {
set newname for datafile 1 to '+EXAMPLE';
 set newname for datafile 2 to '+EXAMPLE';
 set newname for datafile 4 to '+EXAMPLE';
 set newname for datafile 3 to '+EXAMPLE';
 set newname for datafile 5 to '+EXAMPLE';
 set newname for datafile 6 to '+EXAMPLE';
 restore database;
 switch datafile all;
  }2> 3> 4> 5> 6> 7> 8> 9> 10> 11>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to +EXAMPLE
channel ORA_DISK_1: restoring datafile 00004 to +EXAMPLE
channel ORA_DISK_1: reading from backup piece +AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.275.797192689
channel ORA_DISK_1: piece handle=+AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.275.797192689 tag=TAG20121020T182444
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +EXAMPLE
channel ORA_DISK_1: restoring datafile 00006 to +EXAMPLE
channel ORA_DISK_1: reading from backup piece +AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.276.797192687
channel ORA_DISK_1: piece handle=+AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.276.797192687 tag=TAG20121020T182444
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +EXAMPLE
channel ORA_DISK_1: restoring datafile 00005 to +EXAMPLE
channel ORA_DISK_1: reading from backup piece +AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.274.797192711
channel ORA_DISK_1: piece handle=+AMIT_R1/orcl/backupset/2012_10_20/nnndf0_tag20121020t182444_0.274.797192711 tag=TAG20121020T182444
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 20-OCT-12

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=797201985 file name=+EXAMPLE/orcl/datafile/system.260.797201879
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=797201985 file name=+EXAMPLE/orcl/datafile/sysaux.269.797201773
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=797201985 file name=+EXAMPLE/orcl/datafile/undotbs1.268.797201697
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=797201986 file name=+EXAMPLE/orcl/datafile/users.267.797201699
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=797201986 file name=+EXAMPLE/orcl/datafile/example.261.797201879
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=797201986 file name=+EXAMPLE/orcl/datafile/amit.256.797201773 

10. Recover the database

RMAN> recover database;

Starting recover at 20-OCT-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file +AMIT_R1/orcl/onlinelog/group_2.264.790789023
archived log for thread 1 with sequence 9 is already on disk as file +AMIT_R1/orcl/onlinelog/group_3.266.790789027
archived log for thread 1 with sequence 10 is already on disk as file +AMIT_R1/orcl/onlinelog/group_1.262.790789015
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /backup/BKPSETS_20OCT12_0rno8cm2_1_1
channel ORA_DISK_1: piece handle=/backup/BKPSETS_20OCT12_0rno8cm2_1_1 tag=TAG20121020T182818
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/backup/orcl/1_7_796679647.dbf thread=1 sequence=7
archived log file name=+AMIT_R1/orcl/onlinelog/group_2.264.790789023 thread=1 sequence=8
archived log file name=+AMIT_R1/orcl/onlinelog/group_3.266.790789027 thread=1 sequence=9
archived log file name=+AMIT_R1/orcl/onlinelog/group_1.262.790789015 thread=1 sequence=10
media recovery complete, elapsed time: 00:00:26
Finished recover at 20-OCT-12

Exit from RMAN and login with SQLPLUS

11. Rename the ONLINE redo logs file

 SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------      

+AMIT_R1/orcl/onlinelog/group_3.266.790789027
+AMIT_R1/orcl/onlinelog/group_3.267.790789029
+AMIT_R1/orcl/onlinelog/group_2.264.790789023
+AMIT_R1/orcl/onlinelog/group_2.265.790789025
+AMIT_R1/orcl/onlinelog/group_1.262.790789015
+AMIT_R1/orcl/onlinelog/group_1.263.790789019

6 rows selected.

Rename all logfiles :-

SQL> alter database rename file '+AMIT_R1/orcl/onlinelog/group_3.266.790789027' to '+EXAMPLE/fingate/redo03a.log';
Database altered.

SQL> alter database rename file '+AMIT_R1/orcl/onlinelog/group_1.262.790789015' to '+EXAMPLE/fingate/redo01.log';
Database altered.

SQL> alter database rename file '+AMIT_R1/orcl/onlinelog/group_1.263.790789019' to '+EXAMPLE/fingate/redo01a.log';
Database altered.
.
.
.
like this reaname all logfiles .

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------        
+EXAMPLE/fingate/redo01.log+EXAMPLE/fingate/redo01a.log+EXAMPLE/fingate/redo03a.log+EXAMPLE/fingate/redo03.log+EXAMPLE/fingate/redo4.log+EXAMPLE/fingate/redo4a.log

6 rows selected.

12. Open your clone database

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME
---------
ORCL

13. Rename Temp tablespace.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------  
+AMIT_R1/orcl/tempfile/temp.268.790789087

SQL> create temporary tablespace temp1 tempfile '+EXAMPLE';

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------             
+EXAMPLE/orcl/tempfile/temp1.270.797205367

Cloning of ORCL database on a NEW HOST completed.

I Hope this article helped you.

Regards,
Amit Rath