Tuesday, April 19, 2016

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
2> SPFILE
3> set control_files='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'
4> set DB_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'
5> set LOG_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'
6> BACKUP LOCATION '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW'
7> ;

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






Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert


Post a Comment