Though we can the active duplication method, to clone a database from live running database. But sometime we may need to clone a database from a RMAN backup set. Below example will cover the steps for that.

STEPS:

1. Take full rman backup from source db

If you already have any existing latest valid full backup, then you can use that instead of taking fresh backup.

RMAN>  configure controlfile autobackup format for device type disk to '/archive/RMAN_BKP/%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archive/RMAN_BKP/%F';
new RMAN configuration parameters are successfully stored

RMAN> run
 {
 allocate channel c1 type disk format '/archive/RMAN_BKP/%I-%Y%M%D-%U';
 backup  as compressed backupset incremental level 0  check logical database plus archivelog;
 release channel c1 ;
 }



allocated channel: c1
channel c1: SID=23 device type=DISK

Starting backup at 19-SEP-21
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=457 RECID=453 STAMP=1083592832
input archived log thread=1 sequence=458 RECID=454 STAMP=1083632441
input archived log thread=1 sequence=459 RECID=455 STAMP=1083664917
input archived log thread=1 sequence=460 RECID=456 STAMP=1083665067
channel c1: starting piece 1 at 19-SEP-21
channel c1: finished piece 1 at 19-SEP-21
piece handle=/archive/RMAN_BKP/1307774653-20210919-0409eqlb_1_1 tag=TAG20210919T100427 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
Finished backup at 19-SEP-21

Starting backup at 19-SEP-21
channel c1: starting compressed incremental level 0 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/oradata/clsprod/SND_MAST_DATA_01.dbf
input datafile file number=00008 name=/oradata/clsprod/SND_TRANS_DATA_01.dbf
input datafile file number=00009 name=/oradata/clsprod/mbas_01.dbf
input datafile file number=00010 name=/oradata/clsprod/sm_ts_01.dbf
input datafile file number=00005 name=/oradata/clsprod/ts_prm_1.dbf
input datafile file number=00003 name=/oradata/clsprod/sysaux01.dbf
input datafile file number=00001 name=/oradata/clsprod/system01.dbf
input datafile file number=00004 name=/oradata/clsprod/undotbs01.dbf
input datafile file number=00007 name=/oradata/clsprod/users01.dbf
channel c1: starting piece 1 at 19-SEP-21


channel c1: finished piece 1 at 19-SEP-21
piece handle=/archive/RMAN_BKP/1307774653-20210919-0509eqlq_1_1 tag=TAG20210919T100442 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:45
Finished backup at 19-SEP-21

Starting backup at 19-SEP-21
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=461 RECID=457 STAMP=1083665247
channel c1: starting piece 1 at 19-SEP-21
channel c1: finished piece 1 at 19-SEP-21
piece handle=/archive/RMAN_BKP/1307774653-20210919-0609eqr0_1_1 tag=TAG20210919T100727 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-SEP-21

Starting Control File and SPFILE Autobackup at 19-SEP-21
piece handle=/archive/RMAN_BKP/c-1307774653-20210919-00 comment=NONE
Finished Control File and SPFILE Autobackup at 19-SEP-21

released channel: c1

2. Copy the backup pieces to the target host:If you are duplicating the database on a different host, then copy the backup pieces target  host.


[oracle@RMAN_BKP]$ ls -ltr
total 1120808
-rw-r----- 1 oracle oinstall 155184640 Sep 19 10:04 1307774653-20210919-0409eqlb_1_1
-rw-r----- 1 oracle oinstall 981581824 Sep 19 10:07 1307774653-20210919-0509eqlq_1_1
-rw-r----- 1 oracle oinstall    218624 Sep 19 10:07 1307774653-20210919-0609eqr0_1_1
-rw-r----- 1 oracle oinstall  10715136 Sep 19 10:07 c-1307774653-20210919-00

[oracle@RMAN_BKP]$ scp * oracle@target-host.com:/archive/RMAN_BKP/

3. Prepare pfile for the new db:

The new db_name is ckssdev.

cd $ORACLE_HOME/dbs

vi initckssdev.ora

*.audit_file_dest='/u01/app/oracle/admin/ckssdev/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ckssdev/control01.ctl','/u01/app/oracle/oradata/ckssdev/control02.ctl'
*.db_block_size=8192
*.db_name='ckssdev'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ckssdevXDB)'
*.log_archive_dest_1='LOCATION=/u01/app/arch/ckssdev'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=45174m
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=135520m
*.undo_tablespace='UNDOTBS1'
*.db_create_file_dest='/u01/app/oracle/oradata/ckssdev/'
*.db_create_online_log_dest_1='/u01/app/oracle/oradata/ckssdev/'

 

NOTE – db_create_file_dest – > In this location, all datafiles will be restored.

              db_create_online_log_dest_1 -> In this location, all online redolog files will be created/restored.

 

4. Create required directory structure


mkdir -p /u01/app/oracle/oradata/ckssdev/
mkdir -p /u01/app/arch/ckssdev
mkdir -p /u01/app/oracle/oradata/ckssdev/
mkdir -p /u01/app/oracle/admin/ckssdev/adump

5. Start the database in noMount stage:

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size                 19524688 bytes
Variable Size            2550136832 bytes
Database Buffers         1.8858E+10 bytes
Redo Buffers               47583232 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

6. Run duplicate command

 

SYNTAX – > DUPLICATE DATABASE TO <DB_NEW_NAME> BACKUP LOCATION ‘<<BACKUP_LOC>’ NOFILENAMECHECK;

 

[oracle@dbhost-2 dbs]$ rman auxiliary /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 1 14:11:55 2021
Version 19.3.0.0.0

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

connected to auxiliary database: ckssdev (not mounted)

RMAN> DUPLICATE DATABASE TO ckssdev
BACKUP LOCATION '/archive/RMAN_BKP/'
NOFILENAMECHECK;2> 3>

Starting Duplicate Db at 01-SEP-21
searching for database ID
found backup of database ID 2028559627

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area   21474835536 bytes

Fixed Size                    19524688 bytes
Variable Size               2550136832 bytes
Database Buffers           18857590784 bytes
Redo Buffers                  47583232 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''CLS21COM'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ckssdev'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/app/CLS_DONTDELETE/c-2028559627-20210901-00';
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area   21474835536 bytes

Fixed Size                    19524688 bytes
Variable Size               2550136832 bytes
Database Buffers           18857590784 bytes
Redo Buffers                  47583232 bytes

Starting restore at 01-SEP-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=997 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ckssdev/control01.ctl
output file name=/u01/app/oracle/oradata/ckssdev/control02.ctl
Finished restore at 01-SEP-21

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=997 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=1281 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=1423 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=1563 device type=DISK
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location

contents of Memory Script:
{
   set until scn  42773496;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME


Starting restore at 01-SEP-21
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_ecm_cbu_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_ecm_cbu__%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/CLS_DONTDELETE/2028559627-20210901-0307vpjo_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00002 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_2: restoring datafile 00009 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_cbu_%u_.dbf
channel ORA_AUX_DISK_2: restoring datafile 00010 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_cbu__%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u01/app/CLS_DONTDELETE/2028559627-20210901-0207vpjo_1_1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00003 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_3: restoring datafile 00005 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu_%u_.dbf
channel ORA_AUX_DISK_3: restoring datafile 00006 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu__%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /u01/app/CLS_DONTDELETE/2028559627-20210901-0407vpjo_1_1
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00004 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_4: restoring datafile 00007 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_ecm_ebu_%u_.dbf
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:03:06
channel ORA_AUX_DISK_4: piece handle=/u01/app/CLS_DONTDELETE/2028559627-20210901-0507vpjo_1_1 tag=TAG20210901T135815
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:03:46
Finished restore at 01-SEP-21

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=1082125032 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_system_jlyr2q12_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=1082125032 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_sysaux_jlyr2q0o_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1082125032 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_undotbs1_jlyr2q1k_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1082125032 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_users_jlyr2q24_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=1082125032 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu_jlyr2py1_.dbf
datafile 6 switched to datafile copy

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

executing command: SET until clause

Starting recover at 01-SEP-21
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

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=939
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/CLS_DONTDELETE/2028559627-20210901-0607vplp_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/CLS_DONTDELETE/2028559627-20210901-0607vplp_1_1 tag=TAG20210901T135921
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/arch/ckssdev/1_939_1075569611.dbf thread=1 sequence=939
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/arch/ckssdev/1_939_1075569611.dbf RECID=1 STAMP=1082125035
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-SEP-21
Oracle instance started

Total System Global Area   21474835536 bytes

Fixed Size                    19524688 bytes
Variable Size               2550136832 bytes
Database Buffers           18857590784 bytes
Redo Buffers                  47583232 bytes

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

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

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area   21474835536 bytes

Fixed Size                    19524688 bytes
Variable Size               2550136832 bytes
Database Buffers           18857590784 bytes
Redo Buffers                  47583232 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ckssdev" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP     1  SIZE 200 M ,
  GROUP     2  SIZE 200 M ,
  GROUP     3  SIZE 200 M
 DATAFILE
  '/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_system_jlyr2q12_.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_sysaux_jlyr2q0o_.dbf",
 "/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_undotbs1_jlyr2q1k_.dbf",
 "/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_users_jlyr2q24_.dbf",
 "/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu_jlyr2py1_.dbf",
 "/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu__jlyr2q08_.dbf",
 "/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_ecm_ebu_jlyr2pyn_.dbf",
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_sysaux_jlyr2q0o_.dbf RECID=1 STAMP=1082125074
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_undotbs1_jlyr2q1k_.dbf RECID=2 STAMP=1082125074
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_users_jlyr2q24_.dbf RECID=3 STAMP=1082125074
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu_jlyr2py1_.dbf RECID=4 STAMP=1082125074
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu__jlyr2q08_.dbf RECID=5 STAMP=1082125074
cataloged datafile copy

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1082125074 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_sysaux_jlyr2q0o_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1082125074 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_undotbs1_jlyr2q1k_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1082125074 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_users_jlyr2q24_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1082125074 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu_jlyr2py1_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=1082125074 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_CLS_ebu__jlyr2q08_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=1082125074 file name=/u01/app/oracle/oradata/ckssdev/ckssdev/datafile/o1_mf_ecm_ebu_jlyr2pyn_.dbf
datafile 8 switched to datafile copy


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

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 01-SEP-21

SEE ALSO: