Perform Backup Based RMAN DUPLICATE Without Connecting To Target
Database
Target less Duplicate
Clone/Duplicate Database without connecting to target using RMAN Backup
Clone/Duplicate Database without connecting to target using RMAN Backup
Purposes
Using of this document is the following fictitious environment is used as
an example to describe the procedure:
If you are
performing a backup based RMAN duplicate and using with or without recovery
catalog as well, it is not required to connect to the source database as TARGET
in RMAN.
This technique is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions. In duplication without a TARGET connection, the source database is unaffected by the duplication
This technique is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions. In duplication without a TARGET connection, the source database is unaffected by the duplication
1.
For this RMAN duplication, we will connect RMAN to
the auxiliary instance using RMAN backup or recovery catalog database and run
duplicate command similar duplication or cloning.
2.
Here we have to make sure we can only use SET UNTIL TIME
clause for RMAN backup based duplication.
3.
We can use this technique for both disk as well as
tape (SBT) backups.
4.
Usage of recovery catalog is mandatory for
tape and disk based backup. However if
you do not have a recovery catalog; you can still perform RMAN DUPLICATE
without connecting to the target database but ONLY for disk backups. This can be
achieved via BACKUP LOCATION clause of DUPLICATE command.
Here in these scenarios I am demonstrating BACKUP LOCATION clause of
DUPLICATE without using recovery catalog.
Procedure
SOURCE
·
Target Database: DB_NAME: NCTEST
·
Target Server : vm215.ora.com
DESTINATION
·
Auxiliary Database: DB_NAME: CCBTEST
·
Destination Server : vm216.ora.com
Ø On
Source database I am taking RMAN Backup.
RMAN> backup INCREMENTAL level 0 tag
'LEV-0-JUN2020' DATABASE PLUS ARCHIVELOG TAG 'LEV-0-ARCH--JUN2020' ;
…………….
…………….
…………….
Starting backup at 24-JUN-20
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
input archived log thread=1 sequence=117 RECID=130 STAMP=1043937822
input archived log thread=1 sequence=118 RECID=131 STAMP=1043937895
…………….
…………….
…………….
input datafile file number=00005
name=/u01/app/oracle/oradata/NCTEST/example01.dbf
input datafile file number=00003
name=/u01/app/oracle/oradata/NCTEST/sysaux01.dbf
input datafile file number=00001
name=/u01/app/oracle/oradata/NCTEST/system01.dbf
input datafile file number=00004
name=/u01/app/oracle/oradata/NCTEST/undotbs01.dbf
input datafile file number=00006
name=/u01/app/oracle/oradata/NCTEST/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-JUN-20
channel ORA_DISK_1: finished piece 1 at 24-JUN-20
piece
handle=/u01/app/oracle/fast_recovery_area/NCTEST/backupset/2020_06_24/o1_mf_nnnd0_LEV_0_JUN2020_hh6d886q_.bkp
tag=LEV-0-JUN2020 comment=NONE
……
……
……
piece
handle=/u01/app/oracle/fast_recovery_area/NCTEST/autobackup/2020_06_24/o1_mf_s_1043943970_hh6dbbr6_.bkp
comment=NONE
Finished Control File and SPFILE Autobackup at 24-JUN-20
Ø Copy the
Backup to destination server (vm216)
/u01/app/oracle/fast_recovery_area/NCTEST/backupset/2020_06_24
[oracle@vm215
2020_06_24]$ ls -ltrah
-rw-r-----. 1 oracle
oinstall 1.1G Jun 24 16:24 o1_mf_annnn_LEV_0_ARCH__JUN2020_hh6d7rpj_.bkp
-rw-r-----. 1 oracle
oinstall 1.7G Jun 24 16:25 o1_mf_nnnd0_LEV_0_JUN2020_hh6d886q_.bkp
-rw-r-----. 1 oracle
oinstall 11K Jun 24 16:26
o1_mf_annnn_LEV_0_ARCH__JUN2020_hh6db9gk_.bkp
/u01/app/oracle/fast_recovery_area/NCTEST/backupset/2020_06_25
[oracle@vm215
2020_06_25]$ ls -ltrah
total 1.3G
-rw-r-----. 1 oracle
oinstall 1.2G Jun 25 07:34 o1_mf_annnn_LEVEL_1_NCTEST_ARCH_hh81k40l_.bkp
-rw-r-----. 1 oracle
oinstall 92M Jun 25 07:35
o1_mf_nnnd1_LEVEL_1_NCTEST_DB_hh81l02o_.bkp
drwxr-x---. 2 oracle
oinstall 4.0K Jun 25 07:35 .
-rw-r-----. 1 oracle
oinstall 92K Jun 25 07:35
o1_mf_annnn_LEVEL_1_NCTEST_ARCH_hh81mdlj_.bkp
Ø Create
Pfile and password file on
[oracle@vm216
script]$ cd $ORACLE_HOME/dbs
[oracle@vm216
dbs]$ vi initCCBTEST.ora
[oracle@vm216 dbs]$
[oracle@vm216
dbs]$ more initCCBTEST.ora
CCBTEST.__data_transfer_cache_size=0
CCBTEST.__db_cache_size=377487360
CCBTEST.__java_pool_size=4194304
CCBTEST.__large_pool_size=8388608
CCBTEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
CCBTEST.__pga_aggregate_target=209715200
CCBTEST.__sga_target=629145600
CCBTEST.__shared_io_pool_size=8388608
CCBTEST.__shared_pool_size=222298112
CCBTEST.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/CCBTEST/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/CCBTEST/control01.ctl','/u01/app/oracle/fast_recovery_area/CCBTEST/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CCBTEST'
*.db_file_name_convert='/u01/app/oracle/oradata/NCTEST','/u01/app/oracle/oradata/CCBTEST'
*.log_file_name_convert='/u01/app/oracle/oradata/NCTEST','/u01/app/oracle/oradata/CCBTEST'
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=12560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CCBTESTXDB)'
*.log_archive_format='CCBTEST_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=700
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=600m
*.undo_tablespace='UNDOTBS1'
Ø Startup
database using this parameter file
[oracle@vm216
~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 22 21:11:05 2020
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>
startup force nomount
ORACLE instance started.
Total System Global Area 629145600
bytes
Fixed Size 2927528
bytes
Variable Size 515900504
bytes
Database Buffers 104857600
bytes
Redo Buffers 5459968
bytes
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release
12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
[oracle@vm216
script]$ cd $ORACLE_HOME/dbs
[oracle@vm216 dbs]$
[oracle@vm216
dbs]$ ls -ltrah orapwCCBTEST
-rw-r-----. 1 oracle
oinstall 7.5K Jun 22 20:32 orapwCCBTEST
[oracle@vm216 dbs]$
Ø Create
script for duplicate database...
[oracle@vm216
script]$ ls -l copy_CCBTEST_dup.sh
-rwxr-xr-x. 1 oracle
oinstall 400 Jun 25 07:51 copy_CCBTEST_dup.sh
[oracle@vm216
script]$ more copy_CCBTEST_dup.sh
export
CURRDATE=`date +%m_%d_%Y_%H-%M-%S`
export
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
$ORACLE_HOME/bin/rman
<< EOF > /home/oracle/script/CCBTEST_copy_Duplicate_$CURRDATE.log
connect auxiliary
sys/oracle@CCBTEST;
run{
DUPLICATE DATABASE TO CCBTEST BACKUP LOCATION '/tmp/backup/2020_06_24' nofilenamecheck
until time "TO_DATE('24-JUN-2020 18:23:30','DD-MON-YYYY
HH24:MI:SS')";
}
EOF
!
/tmp/backup/2020_06_24 : is
the backup location where I have copy the source RMAN backup here…
Ø Start the
duplication duplicate database...
[oracle@vm216
script]$ nohup ./copy_CCBTEST_dup.sh &
[oracle@vm216
script]$ jobs
[1]+ Running nohup ./copy_CCBTEST_dup.sh
&
[oracle@vm216
script]$ tail -f
/home/oracle/script/CCBTEST_copy_Duplicate_06_25_2020_07-51-26.log
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jun 25 07:51:26
2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to auxiliary database: CCBTEST (not mounted)
RMAN> 2> 3>
Starting Duplicate Db at 25-JUN-20
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
629145600 bytes
Fixed Size
2927528 bytes
Variable Size
520094808 bytes
Database Buffers
100663296 bytes
Redo Buffers
5459968 bytes
contents of Memory Script:
{
sql clone "alter system
set db_name =
''NCTEST'' comment=
''Modified by RMAN duplicate''
scope=spfile";
sql clone "alter system
set db_unique_name =
''CCBTEST'' comment=
''Modified by RMAN duplicate''
scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary
controlfile from
'/tmp/backup/2020_06_24/o1_mf_s_1043943970_hh6dbbr6_.bkp';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set
db_name = ''NCTEST'' comment=
''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set
db_unique_name = ''CCBTEST''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area
629145600 bytes
Fixed Size
2927528 bytes
Variable Size
520094808 bytes
Database Buffers
100663296 bytes
Redo Buffers 5459968 bytes
Starting restore at 25-JUN-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=543 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/CCBTEST/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/CCBTEST/control02.ctl
Finished restore at 25-JUN-20
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=543 device type=DISK
contents of Memory Script:
{
set until scn 2801030;
set newname for datafile 1 to
"/u01/app/oracle/oradata/CCBTEST/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/CCBTEST/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/CCBTEST/undotbs01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/CCBTEST/example01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/CCBTEST/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 25-JUN-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/app/oracle/oradata/CCBTEST/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/u01/app/oracle/oradata/CCBTEST/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/CCBTEST/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to
/u01/app/oracle/oradata/CCBTEST/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to
/u01/app/oracle/oradata/CCBTEST/users01.dbf
channel ORA_AUX_DISK_1: reading
from backup piece /tmp/backup/2020_06_24/o1_mf_nnnd0_LEV_0_JUN2020_hh6d886q_.bkp
channel ORA_AUX_DISK_1: piece
handle=/tmp/backup/2020_06_24/o1_mf_nnnd0_LEV_0_JUN2020_hh6d886q_.bkp
tag=LEV-0-JUN2020
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 25-JUN-20
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1043999649 file
name=/u01/app/oracle/oradata/CCBTEST/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1043999649 file
name=/u01/app/oracle/oradata/CCBTEST/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1043999649 file
name=/u01/app/oracle/oradata/CCBTEST/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1043999649 file
name=/u01/app/oracle/oradata/CCBTEST/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=1043999649 file
name=/u01/app/oracle/oradata/CCBTEST/users01.dbf
contents of Memory Script:
{
set until time "to_date('JUN 24 2020 18:23:30', 'MON DD
YYYY HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25-JUN-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=121
channel ORA_AUX_DISK_1: reading from backup piece
/tmp/backup/2020_06_24/o1_mf_annnn_LEV_0_ARCH__JUN2020_hh6db9gk_.bkp
channel ORA_AUX_DISK_1: piece
handle=/tmp/backup/2020_06_24/o1_mf_annnn_LEV_0_ARCH__JUN2020_hh6db9gk_.bkp
tag=LEV-0-ARCH--JUN2020
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/CCBTEST/archivelog/2020_06_25/o1_mf_1_121_hh82pc48_.arc
thread=1 sequence=121
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/CCBTEST/archivelog/2020_06_25/o1_mf_1_121_hh82pc48_.arc
RECID=1 STAMP=1043999651
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=122
channel ORA_AUX_DISK_1: reading from backup piece
/tmp/backup/2020_06_24/o1_mf_annnn_LEVEL_1_NCTEST_ARCH_hh81k40l_.bkp
channel ORA_AUX_DISK_1: piece
handle=/tmp/backup/2020_06_24/o1_mf_annnn_LEVEL_1_NCTEST_ARCH_hh81k40l_.bkp
tag=LEVEL-1-NCTEST-ARCH
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file
name=/u01/app/oracle/fast_recovery_area/CCBTEST/archivelog/2020_06_25/o1_mf_1_122_hh82pds9_.arc
thread=1 sequence=122
channel clone_default: deleting archived log(s)
archived log file
name=/u01/app/oracle/fast_recovery_area/CCBTEST/archivelog/2020_06_25/o1_mf_1_122_hh82pds9_.arc
RECID=2 STAMP=1043999654
media recovery complete, elapsed time: 00:00:05
Finished recover at 25-JUN-20
Oracle instance started
Total System Global Area
629145600 bytes
Fixed Size
2927528 bytes
Variable Size
520094808 bytes
Database Buffers
100663296 bytes
Redo Buffers
5459968 bytes
contents of Memory Script:
{
sql clone "alter system
set db_name =
''CCBTEST'' 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 = ''CCBTEST'' 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
629145600 bytes
Fixed Size
2927528 bytes
Variable Size
520094808 bytes
Database Buffers
100663296 bytes
Redo Buffers
5459968 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CCBTEST"
RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/CCBTEST/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 (
'/u01/app/oracle/oradata/CCBTEST/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 (
'/u01/app/oracle/oradata/CCBTEST/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/CCBTEST/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/CCBTEST/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy
"/u01/app/oracle/oradata/CCBTEST/sysaux01.dbf",
"/u01/app/oracle/oradata/CCBTEST/undotbs01.dbf",
"/u01/app/oracle/oradata/CCBTEST/example01.dbf",
"/u01/app/oracle/oradata/CCBTEST/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/CCBTEST/temp01.dbf in
control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CCBTEST/sysaux01.dbf
RECID=1 STAMP=1043999685
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CCBTEST/undotbs01.dbf
RECID=2 STAMP=1043999685
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CCBTEST/example01.dbf
RECID=3 STAMP=1043999685
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CCBTEST/users01.dbf
RECID=4 STAMP=1043999685
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1043999685 file
name=/u01/app/oracle/oradata/CCBTEST/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1043999685 file
name=/u01/app/oracle/oradata/CCBTEST/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=1043999685 file
name=/u01/app/oracle/oradata/CCBTEST/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=1043999685 file
name=/u01/app/oracle/oradata/CCBTEST/users01.dbf
contents of Memory Script:
{
Alter clone database open
resetlogs;
}
executing Memory Script
database opened
Cannot remove created server parameter file
Finished Duplicate Db at 25-JUN-20
RMAN>
Recovery Manager complete.
^C
[1]+ Done nohup ./copy_CCBTEST_dup.sh
Ø Validate
the database after successful duplication.
[oracle@vm216 script]$ ps -ef |
grep pmon
oracle 6131
1 0 07:54 ? 00:00:00 ora_pmon_CCBTEST
oracle 6457 5010 0
07:55 pts/0 00:00:00 grep pmon
[oracle@vm216 script]$ . oraenv
ORACLE_SID = [oracle] ? CCBTEST
The Oracle base has been set to /u01/app/oracle
[oracle@vm216 ~]$ sqlplus / as
sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 25 07:56:10 2020
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> @open
INST_ID DB_NAME INSTANCE_NAME VERSION
HOST_NAME OPEN_MODE
----------
---------- -------------- --------- --------------- ------------
1 CCBTEST CCBTEST 12.1.0.2.0 vm216.ora.com READ WRITE
no rows selected
SQL> select name from v$datafile
;
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/CCBTEST/system01.dbf
/u01/app/oracle/oradata/CCBTEST/sysaux01.dbf
/u01/app/oracle/oradata/CCBTEST/undotbs01.dbf
/u01/app/oracle/oradata/CCBTEST/example01.dbf
/u01/app/oracle/oradata/CCBTEST/users01.dbf
SQL> show parameter pfile
NAME TYPE
VALUE
---------------
----------- ------------------------------
spfile string
/u01/app/oracle/product/12.1.0
.2/dbhome_1/dbs/spfileCCBTEST.
ora
SQL>
Conclusion
So there is number of ways to duplicate or clone the
database depending upon you business/project requirements. Here we don’t want to engage our live
environment. Also it’s up to you what
best hands-on and suits to you..