Duplicate Database from RMAN Backup
Seven Steps for Duplicate Database :
Step 1) RMNA Backup (Prerequisites)
Step 2) Create Password File
Step 3) Create Directory Structure
Step 4) Create Pfile
Step 5) Start Database in nomount Stage
Step 6) Run Duplicate Database Command
Step 7) Verify Duplicate Database
Step 1 RMAN Full database backup required (Prerequisites)
If database is in non archivelog mode then Shutdown the database and open in mount stage then take database backup i.e.RMAN > backup database;
If database is in archivelog mode use command i.e. RMAN > backup database plus archivelog;
Backup Database using following command
Backup Database
-bash-4.1$ rman Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 11 12:19:23 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN> connect target sys/sysdba123@orcl connected to target database: ORCL (DBID=1611723298) RMAN> RMAN> configure controlfile autobackup on; using target database control file instead of recovery catalog new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> backup database plus archivelog;
Step 2 Create a password file for the duplicate instance.
Copy Source database password file and rename it to new database password file
Go to directory: /mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/
$ cp orapworcl orapwduporcl
Step 3 Create directory structure for Duplicate Database
Login with user db1212
Following directories need to be created
– DupOrcl
– Oradata
– Fast_recovery_area
– Diag
– Adump
Following are sample commands
-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl -bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/adump -bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/oradata -bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl -bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/fast_recovery_area -bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/diag -bash-4.1$
Step 4 Create Pfile with few parameters.
Login with db1212
Create Pfile with following parameters
Go to directory: /mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/
Parameters as below for initduporcl.ora,
*.audit_file_dest='/mnt/devops_0/DupDB/DupOrcl/adump' *.audit_trail='db' *.compatible='12.1.0.2.0' *.db_block_size=8192 *.db_create_file_dest='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl' *.db_name='duporcl' *.db_recovery_file_dest='/mnt/devops_0/DupDB/DupOrcl/fast_recovery_area' *.db_recovery_file_dest_size=4560m *.diagnostic_dest='/mnt/devops_0/DupDB/DupOrcl/diag' *.undo_tablespace='UNDOTBS1'
Step 5 Start Database in nomount stage.
-bash-4.1$ export ORACLE_SID=duporcl -bash-4.1$ -bash-4.1$ echo $ORACLE_SID duporcl -bash-4.1$ SQL> startup nomount ORACLE instance started. Total System Global Area 222298112 bytes Fixed Size 2922760 bytes Variable Size 163579640 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes SQL> SQL> exit
Step 6 Run Duplicate Database command.
$ rman auxiliary / $ DUPLICATE DATABASE TO duporcl SPFILE set control_files='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl' set DB_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/' set LOG_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/' BACKUP LOCATION '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW' ;
Following is the log for above command for future references.
-bash-4.1$ rman auxiliary / Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 11 12:04:18 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to auxiliary database: DUPORCL (not mounted) RMAN> DUPLICATE DATABASE TO duporcl SPFILE set control_files='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl' set DB_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/' set LOG_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/' BACKUP LOCATION '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW' ; Starting Duplicate Db at 11-MAR-16 contents of Memory Script: { restore clone spfile to '/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora' from '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp'; sql clone "alter system set spfile= ''/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora''"; } executing Memory Script Starting restore at 11-MAR-16 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=13 device type=DISK channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 11-MAR-16 sql statement: alter system set spfile= ''/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora'' contents of Memory Script: { sql clone "alter system set db_name = ''DUPORCL'' comment= ''duplicate'' scope=spfile"; sql clone "alter system set control_files = ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'' comment= '''' scope=spfile"; sql clone "alter system set db_file_name_convert = ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment= '''' scope=spfile"; sql clone "alter system set LOG_FILE_NAME_CONVERT = ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set db_name = ''DUPORCL'' comment= ''duplicate'' scope=spfile sql statement: alter system set control_files = ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'' comment= '''' scope=spfile sql statement: alter system set db_file_name_convert = ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment= '''' scope=spfile sql statement: alter system set LOG_FILE_NAME_CONVERT = ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 713031784 bytes Database Buffers 352321536 bytes Redo Buffers 5455872 bytes contents of Memory Script: { sql clone "alter system set db_name = ''ORCLNEW'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''DUPORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone primary controlfile from '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp'; alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''ORCLNEW'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''DUPORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 713031784 bytes Database Buffers 352321536 bytes Redo Buffers 5455872 bytes Starting restore at 11-MAR-16 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=12 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=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl Finished restore at 11-MAR-16 database mounted released channel: ORA_AUX_DISK_1 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=12 device type=DISK contents of Memory Script: { set until scn 1804148; set newname for datafile 1 to "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf"; set newname for datafile 3 to "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf"; set newname for datafile 4 to "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf"; set newname for datafile 5 to "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf"; set newname for datafile 6 to "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf"; 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 executing command: SET NEWNAME Starting restore at 11-MAR-16 using channel ORA_AUX_DISK_1 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 /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf channel ORA_AUX_DISK_1: restoring datafile 00005 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf channel ORA_AUX_DISK_1: restoring datafile 00006 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf channel ORA_AUX_DISK_1: reading from backup piece /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_nnndf_TAG20160310T121557_cg2615wf_.bkp channel ORA_AUX_DISK_1: piece handle=/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_nnndf_TAG20160310T121557_cg2615wf_.bkp tag=TAG20160310T121557 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05 Finished restore at 11-MAR-16 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=6 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=7 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=8 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=9 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf datafile 6 switched to datafile copy input datafile copy RECID=10 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf contents of Memory Script: { set until scn 1804148; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 11-MAR-16 using channel ORA_AUX_DISK_1 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=11 channel ORA_AUX_DISK_1: reading from backup piece /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_annnn_TAG20160310T121703_cg2637go_.bkp channel ORA_AUX_DISK_1: piece handle=/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_annnn_TAG20160310T121703_cg2637go_.bkp tag=TAG20160310T121703 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=/mnt/devops_0/OrclNew/Archives/1_11_906113933.dbf thread=1 sequence=11 channel clone_default: deleting archived log(s) archived log file name=/mnt/devops_0/OrclNew/Archives/1_11_906113933.dbf RECID=1 STAMP=906206849 media recovery complete, elapsed time: 00:00:01 Finished recover at 11-MAR-16 Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 713031784 bytes Database Buffers 352321536 bytes Redo Buffers 5455872 bytes contents of Memory Script: { sql clone "alter system set db_name = ''DUPORCL'' 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 = ''DUPORCL'' 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 1073741824 bytes Fixed Size 2932632 bytes Variable Size 713031784 bytes Database Buffers 352321536 bytes Redo Buffers 5455872 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPORCL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo01.log' ) SIZE 50 M REUSE, GROUP 2 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo02.log' ) SIZE 50 M REUSE, GROUP 3 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo03.log' ) SIZE 50 M REUSE DATAFILE '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf' CHARACTER SET AL32UTF8 contents of Memory Script: { set newname for tempfile 1 to "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf", "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf", "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf", "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/temp01.dbf in control file cataloged datafile copy datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf RECID=1 STAMP=906206886 cataloged datafile copy datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf RECID=2 STAMP=906206886 cataloged datafile copy datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf RECID=3 STAMP=906206886 cataloged datafile copy datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf RECID=4 STAMP=906206886 datafile 3 switched to datafile copy input datafile copy RECID=1 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=2 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=3 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf datafile 6 switched to datafile copy input datafile copy RECID=4 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 11-MAR-16
Step 7 Check Duplicate Database Status
-bash-4.1$ ps -ef | grep pmon
db1212 8550 1 0 Mar09 ? 00:00:12 ora_pmon_orcl12c
db1212 32132 1 0 12:07 ? 00:00:00 ora_pmon_duporcl
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 11 12:09:06 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ——————–
DUPORCL READ WRITE