Posts Tagged ‘Duplicate Database from RMAN Backup’

Duplicate Database from RMAN Backup – Oracle

April 10th, 2022, posted in Oracle Queries
Share

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

Share