Backup-Based Duplication Database with RMAN

Backup-Based Duplication Database with RMAN

RMAN creates the duplicate database by using pre-existing RMAN backups and copies. RMAN can perform backup-based duplication with or without either of the following connections:

* Target
* Recovery catalog

How RMAN duplicates a database:

As a part of the duplicating operation, RMAN automates the following steps:-

1) Creates a default server parameter file for the auxiliary instance.
2) Mounts the restored or copied backup control file from the active database.
3) Uses the RMAN repository to select the backups for restoring the data files to the auxiliary instance.
4) Restores and copies the duplicate data files and recovers them with incremental backups and archived redo log files to a noncurrent point in time.
5) Shuts down and restarts the database instance in NOMOUNT mode.
6) Creates a new control file, which then creates and stores the new DBID in the data files.
7) Opens the duplicate database with the RESETLOGS option and creates the online redo log for the new database.

Actual Steps:

1) Create a backup of the source database:

$ rman target /

RMAN> backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u01/backup/full%u' ;
RMAN> backup current controlfile format '/u01/backup/ctl%u';
RMAN> backup tag ORCL_ARCHIVE format '/u01/backup/%d_%T_%s_%p_ARCHIVE' archivelog all;
RMAN> backup spfile format '/u01/backup/spfile%u';

2) Transfer the backup from source to target:

scp -r * rac2:/u01/backup

3) Create a password file for the duplicate instance or source and target side.

cd $ORACLE_HOME/dbs
orapwd file=orapw$ORACLE_SID password=oracle force=y

Notes: The Oracle orapwd command line utility assists the DBA with granting SYSDBA and SYSOPER privileges to other users.

4) Prepare pfile and add only two parameters:

vi initQTEST.ora

db_name=QTEST
audit_file_dest=/u01/app/oracle/admin/QPROD/adump/

5) Startup the database in nomount with a pfile.

SQL> startup nomount pfile='initQTEST.ora';
ORACLE instance started.
Total System Global Area  238034944 bytes
Fixed Size                  2227136 bytes
Variable Size             180356160 bytes
Database Buffers           50331648 bytes
Redo Buffers                5120000 bytes
SQL> host

5) Connect to rman with auxiliary command.

[oracle@rac2 dbs]$ rman auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 3 00:22:39 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: QTEST (not mounted)

RMAN> DUPLICATE DATABASE TO QTEST
SPFILE set control_files='/u01/QTEST/c1.ctl' set db_file_name_convert='/u09/QPROD','/u01/QTEST' set log_file_name_convert='/u09/QPROD','/u01/QTEST'
BACKUP LOCATION '/u01/backup';

RMAN Duplicate Output:

Starting Duplicate Db at 03-MAR-20
contents of Memory Script:
{
   restore clone spfile to  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileQTEST.ora' from
 '/u01/backup/spfile1iuq48k6';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileQTEST.ora''";
}
executing Memory Script
Starting restore at 03-MAR-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/spfile1iuq48k6
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 03-MAR-20

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileQTEST.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''QTEST'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u01/QTEST/c1.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u09/QPROD'', ''/u01/QTEST'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u09/QPROD'', ''/u01/QTEST'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''QTEST'' comment= ''duplicate'' scope=spfile
sql statement: alter system set  control_files =  ''/u01/QTEST/c1.ctl'' comment= '''' scope=spfile
sql statement: alter system set  db_file_name_convert =  ''/u09/QPROD'', ''/u01/QTEST'' comment= '''' scope=spfile
sql statement: alter system set  log_file_name_convert =  ''/u09/QPROD'', ''/u01/QTEST'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    2689060864 bytes
Fixed Size                     2231392 bytes
Variable Size                587203488 bytes
Database Buffers            2097152000 bytes
Redo Buffers                   2473984 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''QPROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''QTEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/backup/ctl1fuq48c8';
   alter clone database mount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''QPROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set  db_unique_name =  ''QTEST'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area    2689060864 bytes
Fixed Size                     2231392 bytes
Variable Size                587203488 bytes
Database Buffers            2097152000 bytes
Redo Buffers                   2473984 bytes

Starting restore at 03-MAR-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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/QTEST/c1.ctl
Finished restore at 03-MAR-20

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   set until scn  1082431;
   set newname for datafile  1 to
 "/u01/QTEST/system01.dbf";
   set newname for datafile  2 to
 "/u01/QTEST/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/QTEST/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/QTEST/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
Starting restore at 03-MAR-20
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 /u01/QTEST/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/QTEST/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/QTEST/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/QTEST/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/full1euq48b5
channel ORA_AUX_DISK_1: piece handle=/u01/backup/full1euq48b5 tag=ORCL_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 03-MAR-20
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1034036763 file name=/u01/QTEST/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1034036763 file name=/u01/QTEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1034036763 file name=/u01/QTEST/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1034036763 file name=/u01/QTEST/users01.dbf
contents of Memory Script:
{
   set until scn  1082431;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 03-MAR-20
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=9
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/QPROD_20200303_48_1_ARCHIVE
channel ORA_AUX_DISK_1: piece handle=/u01/backup/QPROD_20200303_48_1_ARCHIVE tag=ORCL_ARCHIVE
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/oracle/fast_recovery_area/QTEST/archivelog/2020_03_03/o1_mf_1_9_h5top40c_.arc thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/QTEST/archivelog/2020_03_03/o1_mf_1_9_h5top40c_.arc RECID=1 STAMP=1034036764
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-MAR-20
Oracle instance started
Total System Global Area    2689060864 bytes
Fixed Size                     2231392 bytes
Variable Size                587203488 bytes
Database Buffers            2097152000 bytes
Redo Buffers                   2473984 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''QTEST'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  db_name =  ''QTEST'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    2689060864 bytes
Fixed Size                     2231392 bytes
Variable Size                587203488 bytes
Database Buffers            2097152000 bytes
Redo Buffers                   2473984 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "QTEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/QTEST/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/QTEST/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/QTEST/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/QTEST/system01.dbf'
 CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/QTEST/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/QTEST/sysaux01.dbf",
 "/u01/QTEST/undotbs01.dbf",
 "/u01/QTEST/users01.dbf";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/QTEST/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/QTEST/sysaux01.dbf RECID=1 STAMP=1034036771
cataloged datafile copy
datafile copy file name=/u01/QTEST/undotbs01.dbf RECID=2 STAMP=1034036771
cataloged datafile copy
datafile copy file name=/u01/QTEST/users01.dbf RECID=3 STAMP=1034036771

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1034036771 file name=/u01/QTEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1034036771 file name=/u01/QTEST/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1034036771 file name=/u01/QTEST/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 03-MAR-20