Backup Based RMAN Duplicate Without Connecting To Target Database in Oracle
This article is a continuation of a recent article where we have demonstrated RMAN Active Duplicate Database in Oracle from ASM to ASM click here to go through it. In this article, we will perform a Backup Based RMAN Duplicate Without Connecting To Target Database in Oracle.
RMAN Duplicate Database requires valid Backup of Source database and doesn’t require to make a connection to Source Database from Auxiliary while connected to RMAN hence Duplicate Database is a win-win situation for intermittent disruptions of the network where it doesn’t require target connection and works over backup.
Below are the environment details:
COMPONENTS | SOURCE | DESTINATION (AUXILIARY) |
INSTANCE TYPE | STANDALONE | STANDALONE |
DATABASE | LABDB03 | LABDBDUP |
STORAGE | /oradata/LABDB03 | /oradata/LABDBDUP |
VERSION | 19.12.0.0.0 | 19.3.0.0.0 |
OS | Linux 8 | Linux 8 |
Follow the below steps to clone/migrate the database on a new server with Auxiliary Instance LABDBDUP.
1. Validate the database size, DBID, creation timestamp, archive enable, etc. Click here to get the SQL command to check the size of DATABASE.
[oracle@DBsGuruN ~]$ . oraenv
ORACLE_SID = [oracle] ? labdb03
The Oracle base has been set to /u01/app/oracle
[oracle@DBsGuruN ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 21 16:55:39 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> SELECT BANNER FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> SELECT NAME, OPEN_MODE, DBID, CREATED FROM V$DATABASE;
NAME OPEN_MODE DBID CREATED
--------- -------------------- ---------- ---------
LABDB03 READ WRITE 323245928 18-AUG-21
SQL> @DATABASE_SIZE.sql
DATED HOST_NAME INSTANCE_NAME VERSION TOTAL_ALLOCATED_GB USED_GB FREE_GB
--------- -------------------- ---------------- ----------------- ------------------ ---------- ----------
21-SEP-21 DBsGuruN.dbsguru.com labdb03 19.0.0.0.0 4.22167969 2.20599365 2.01080322
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
2. Capture all physical files. Click here to get the SQL command for How to find all physicals files in Oracle.
SQL> @Physical_Files.sql
NAME
------------------------------------------------------------
/oradata/LABDB03/control01.ctl
/oradata/LABDB03/control02.ctl
/oradata/LABDB03/demo_tbls01.dbf
/oradata/LABDB03/redo01.log
/oradata/LABDB03/redo02.log
/oradata/LABDB03/redo03.log
/oradata/LABDB03/sysaux01.dbf
/oradata/LABDB03/system01.dbf
/oradata/LABDB03/temp01.dbf
/oradata/LABDB03/undotbs01.dbf
/oradata/LABDB03/users01.dbf
11 rows selected.
3. Create a PFILE for Auxiliary Instance.
SQL> create pfile='/home/oracle/initlabdb03.ora' from spfile;
File created.
[oracle@DBsGuruN ~]$ ls -lrt /home/oracle/initlabdb03.ora
-rw-r--r--. 1 oracle oinstall 1114 Sep 21 17:04 /home/oracle/initlabdb03.ora
4. Create a password file for Auxiliary Instance if it’s not already available on the Source Database and make sure created with the correct SYS’s password.
[oracle@DBsGuruN4 dbs]$ orapwd file=orapwlabdb03 password=Passw0rd@5743 force=y entries=10
[oracle@DBsGuruN4 dbs]$ ls -lrt orapwlabdb03
-rw-r----- 1 oracle dba 6144 Sep 21 17:05 orapwlabdb03
5. Backup of the source database.
5.1: Create a directory for RMAN backup.
[oracle@DBsGuruN ~]$ mkdir -p /oradata/LABDB03/backup
[oracle@DBsGuruN ~]$ ls -ld /oradata/LABDB03/backup
drwxr-xr-x. 2 oracle oinstall 6 Sep 21 17:07 /oradata/LABDB03/backup
5.2: Execute RMAN FULL DATABASE plus ARCHIVE backup.
[oracle@DBsGuruN ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 21 17:08:01 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: LABDB03 (DBID=323245928)
RMAN> run
{
ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT '/oradata/LABDB03/backup/labdb03_%U';
ALLOCATE CHANNEL CH2 DEVICE TYPE DISK FORMAT '/oradata/LABDB03/backup/labdb03_%U';
ALLOCATE CHANNEL CH3 DEVICE TYPE DISK FORMAT '/oradata/LABDB03/backup/labdb03_%U';
BACKUP tag 'DUPLICATE_DB' FORCE AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP CURRENT CONTROLFILE TAG 'DUPLICATE_CTL' FORMAT '/oradata/LABDB03/backup/labdb03ctl_%u';
BACKUP SPFILE TAG 'DUPLICATE_SPFILE' FORMAT '/oradata/LABDB03/backup/labdb03SPF_%U';
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
using target database control file instead of recovery catalog
allocated channel: CH1
channel CH1: SID=467 device type=DISK
allocated channel: CH2
channel CH2: SID=461 device type=DISK
allocated channel: CH3
channel CH3: SID=455 device type=DISK
Starting backup at 21-SEP-21
current log archived
channel CH1: starting compressed archived log backup set
channel CH1: specifying archived log(s) in backup set
input archived log thread=1 sequence=33 RECID=2 STAMP=1083863466
channel CH1: starting piece 1 at 21-SEP-21
channel CH2: starting compressed archived log backup set
channel CH2: specifying archived log(s) in backup set
input archived log thread=1 sequence=32 RECID=1 STAMP=1083861992
channel CH2: starting piece 1 at 21-SEP-21
channel CH1: finished piece 1 at 21-SEP-21
piece handle=/oradata/LABDB03/backup/labdb03_0209ksdb_2_1_1 tag=DUPLICATE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
channel CH2: finished piece 1 at 21-SEP-21
piece handle=/oradata/LABDB03/backup/labdb03_0309ksdb_3_1_1 tag=DUPLICATE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-21
Starting backup at 21-SEP-21
channel CH1: starting compressed full datafile backup set
channel CH1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata/LABDB03/demo_tbls01.dbf
channel CH1: starting piece 1 at 21-SEP-21
channel CH2: starting compressed full datafile backup set
channel CH2: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/LABDB03/sysaux01.dbf
input datafile file number=00004 name=/oradata/LABDB03/undotbs01.dbf
channel CH2: starting piece 1 at 21-SEP-21
channel CH3: starting compressed full datafile backup set
channel CH3: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/LABDB03/system01.dbf
input datafile file number=00007 name=/oradata/LABDB03/users01.dbf
channel CH3: starting piece 1 at 21-SEP-21
channel CH1: finished piece 1 at 21-SEP-21
piece handle=/oradata/LABDB03/backup/labdb03_0409ksdc_4_1_1 tag=DUPLICATE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
channel CH2: finished piece 1 at 21-SEP-21
piece handle=/oradata/LABDB03/backup/labdb03_0509ksdc_5_1_1 tag=DUPLICATE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:00:55
channel CH3: finished piece 1 at 21-SEP-21
piece handle=/oradata/LABDB03/backup/labdb03_0609ksdc_6_1_1 tag=DUPLICATE_DB comment=NONE
channel CH3: backup set complete, elapsed time: 00:01:05
Finished backup at 21-SEP-21
Starting backup at 21-SEP-21
current log archived
channel CH1: starting compressed archived log backup set
channel CH1: specifying archived log(s) in backup set
input archived log thread=1 sequence=34 RECID=3 STAMP=1083863533
channel CH1: starting piece 1 at 21-SEP-21
channel CH1: finished piece 1 at 21-SEP-21
piece handle=/oradata/LABDB03/backup/labdb03_0709ksfe_7_1_1 tag=DUPLICATE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-21
Starting backup at 21-SEP-21
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current control file in backup set
channel CH1: starting piece 1 at 21-SEP-21
channel CH1: finished piece 1 at 21-SEP-21
piece handle=/oradata/LABDB03/backup/labdb03ctl_0809ksff tag=DUPLICATE_CTL comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-21
Starting backup at 21-SEP-21
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel CH1: starting piece 1 at 21-SEP-21
channel CH1: finished piece 1 at 21-SEP-21
piece handle=/oradata/LABDB03/backup/labdb03SPF_0909ksfh_9_1_1 tag=DUPLICATE_SPFILE comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-21
Starting Control File and SPFILE Autobackup at 21-SEP-21
piece handle=/oradata/LABDB03/FRA/LABDB03/autobackup/2021_09_21/o1_mf_s_1083863538_jnmk8tqc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-SEP-21
released channel: CH1
released channel: CH2
released channel: CH3
RMAN> exit
Recovery Manager complete.
5.3: Validate the RMAN backup piece.
[oracle@DBsGuruN LABDB03]$ cd /oradata/LABDB03/backup/
[oracle@DBsGuruN backup]$ ls -lrt
total 434492
-rw-r-----. 1 oracle oinstall 4827136 Sep 21 17:11 labdb03_0309ksdb_3_1_1
-rw-r-----. 1 oracle oinstall 6681600 Sep 21 17:11 labdb03_0209ksdb_2_1_1
-rw-r-----. 1 oracle oinstall 1073152 Sep 21 17:11 labdb03_0409ksdc_4_1_1
-rw-r-----. 1 oracle oinstall 103145472 Sep 21 17:11 labdb03_0509ksdc_5_1_1
-rw-r-----. 1 oracle oinstall 318373888 Sep 21 17:12 labdb03_0609ksdc_6_1_1
-rw-r-----. 1 oracle oinstall 13312 Sep 21 17:12 labdb03_0709ksfe_7_1_1
-rw-r-----. 1 oracle oinstall 10682368 Sep 21 17:12 labdb03ctl_0809ksff
-rw-r-----. 1 oracle oinstall 114688 Sep 21 17:12 labdb03SPF_0909ksfh_9_1_1
6. Transfer PFILE, PASSWORDFILE, and RMAN Backup files to Auxiliary Host.
[oracle@DBsGuruN backup]$ cd /oradata/LABDB03/
[oracle@DBsGuruN LABDB03]$ scp -r backup/ oracle@DBsGuruN3:/oradata/LABDB03/
oracle@dbsgurun3's password:
labdb03_0209ksdb_2_1_1 100% 6525KB 65.4MB/s 00:00
labdb03_0309ksdb_3_1_1 100% 4714KB 69.8MB/s 00:00
labdb03_0409ksdc_4_1_1 100% 1048KB 22.7MB/s 00:00
labdb03_0609ksdc_6_1_1 100% 304MB 50.6MB/s 00:06
labdb03_0509ksdc_5_1_1 100% 98MB 49.5MB/s 00:01
labdb03_0709ksfe_7_1_1 100% 13KB 6.5MB/s 00:00
labdb03ctl_0809ksff 100% 10MB 62.5MB/s 00:00
labdb03SPF_0909ksfh_9_1_1 100% 112KB 18.8MB/s 00:00
[oracle@DBsGuruN LABDB03]$
[oracle@DBsGuruN LABDB03]$ cd /home/oracle
oracle@DBsGuruN ~]$ scp initlabdb03.ora oracle@DBsGuruN3:/home/oracle/initlabdbdup.ora
oracle@dbsgurun3's password:
initlabdb03.ora 100% 1114 1.2MB/s 00:00
[oracle@DBsGuruN ~]$ cd $ORACLE_HOME/dbs
[oracle@DBsGuruN dbs]$ ls -lrt orapwlabdb03
-rw-r-----. 1 oracle oinstall 2048 Sep 21 17:05 orapwlabdb03
[oracle@DBsGuruN dbs]$ scp orapwlabdb03 oracle@DBsGuruN3:/u01/app/oracle/product/19.0.0/db_1/dbs/orapwlabdbdup
oracle@dbsgurun3's password:
orapwlabdb03 100% 2048 2.2MB/s 00:00
[oracle@DBsGuruN dbs]$
NOTE: We are creating the clone database on a new server, we will have to move the backups from the Source Host to the Auxiliary Host (destination) in exactly the same location where it was created on the source server hence transferred the dedicated backup folder. This step is needed only for disk backups. If you take backups to tape, this step is not applicable
Now login to Auxiliary Host and perform the below activities.
7. Validate transferred files PFILE, PASSWORD FILE, and RMAN Backup.
[oracle@DBsGuruN3 ~]$ ls -lrt /home/oracle/initlabdbdup.ora
-rw-r--r--. 1 oracle oinstall 1114 Sep 21 17:33 /home/oracle/initlabdbdup.ora
[oracle@DBsGuruN3 ~]$ ls -lrt /u01/app/oracle/product/19.0.0/db_1/dbs/orapwlabdbdup
-rw-r-----. 1 oracle oinstall 2048 Sep 21 17:35 /u01/app/oracle/product/19.0.0/db_1/dbs/orapwlabdbdup
[oracle@DBsGuruN3 ~]$
[oracle@DBsGuruN3 ~]$ cd /oradata/LABDB03/backup/
[oracle@DBsGuruN3 backup]$ ls -lrt
total 434492
-rw-r-----. 1 oracle oinstall 6681600 Sep 21 17:49 labdb03_0209ksdb_2_1_1
-rw-r-----. 1 oracle oinstall 4827136 Sep 21 17:49 labdb03_0309ksdb_3_1_1
-rw-r-----. 1 oracle oinstall 1073152 Sep 21 17:49 labdb03_0409ksdc_4_1_1
-rw-r-----. 1 oracle oinstall 318373888 Sep 21 17:49 labdb03_0609ksdc_6_1_1
-rw-r-----. 1 oracle oinstall 103145472 Sep 21 17:49 labdb03_0509ksdc_5_1_1
-rw-r-----. 1 oracle oinstall 13312 Sep 21 17:49 labdb03_0709ksfe_7_1_1
-rw-r-----. 1 oracle oinstall 10682368 Sep 21 17:49 labdb03ctl_0809ksff
-rw-r-----. 1 oracle oinstall 114688 Sep 21 17:49 labdb03SPF_0909ksfh_9_1_1
8. Create require Directories for Auxiliary Instance.
[oracle@DBsGuruN3 backup]$ mkdir -p /u01/app/oracle/admin/labdbdup/adump
[oracle@DBsGuruN3 backup]$ mkdir -p /oradata/LABDBDUP/FRA
[oracle@DBsGuruN3 backup]$ ls -ld /u01/app/oracle/admin/labdbdup/adump /oradata/LABDBDUP/FRA
drwxr-xr-x. 2 oracle oinstall 6 Sep 21 17:56 /oradata/LABDBDUP/FRA
drwxr-xr-x. 2 oracle oinstall 6 Sep 21 17:56 /u01/app/oracle/admin/labdbdup/adump
9. Prepare PFILE to start Auxiliary Instance. Replace require values according to Auxiliary instance and add below two parameters in the same format.
*.db_file_name_convert= ‘Source Database Datafiles path’,’Auxiliary Instance Datafiles path’
*.log_file_name_convert= ‘Source Database Redolog File path’,’Auxiliary Instanse Redolog File path‘
[oracle@DBsGuruN3 backup]$ cat /home/oracle/initlabdbdup.ora
*.audit_file_dest='/u01/app/oracle/admin/labdbdup/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oradata/LABDBDUP/control01.ctl','/oradata/LABDBDUP/control02.ctl'
*.db_block_size=8192
*.db_name='labdbdup'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='/oradata/LABDBDUP/FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=labdbdupXDB)'
*.local_listener='LISTENER_LABDBDUP'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=100m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=900m
*.undo_tablespace='UNDOTBS1'
*.log_file_name_convert='/oradata/LABDB03','/oradata/LABDBDUP'
*.db_file_name_convert='/oradata/LABDB03','/oradata/LABDBDUP'
[oracle@DBsGuruN3 backup]$
10. Add entry in oratab.
[oracle@DBsGuruN3 backup]$ cat /etc/oratab | grep labdbdup
labdbdup:/u01/app/oracle/product/19.0.0/db_1:N
11. Start Auxiliary Instance in NOMOUNT state.
[oracle@DBsGuruN3 backup]$ . oraenv
ORACLE_SID = [oracle] ? labdbdup
The Oracle base has been set to /u01/app/oracle
[oracle@DBsGuruN3 backup]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 21 18:09:14 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/initlabdbdup.ora'
ORACLE instance started.
Total System Global Area 943716976 bytes
Fixed Size 8903280 bytes
Variable Size 255852544 bytes
Database Buffers 671088640 bytes
Redo Buffers 7872512 bytes
SQL>
12. Perform Active Duplicate Database connected to RMAN session.
[oracle@DBsGuruN3 ~]$ rman
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 21 18:13:11 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect auxiliary /
connected to auxiliary database: LABDBDUP (not mounted)
RMAN> run {
allocate auxiliary channel A1 type disk;
allocate auxiliary channel A2 type disk;
allocate auxiliary channel A3 type disk;
duplicate target database to labdbdup backup location '/oradata/LABDB03/backup' nofilenamecheck;
}2> 3> 4> 5> 6>
allocated channel: A1
channel A1: SID=165 device type=DISK
allocated channel: A2
channel A2: SID=244 device type=DISK
allocated channel: A3
channel A3: SID=323 device type=DISK
Starting Duplicate Db at 21-SEP-21
searching for database ID
found backup of database ID 323245928
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 943716976 bytes
Fixed Size 8903280 bytes
Variable Size 255852544 bytes
Database Buffers 671088640 bytes
Redo Buffers 7872512 bytes
allocated channel: A1
channel A1: SID=84 device type=DISK
allocated channel: A2
channel A2: SID=165 device type=DISK
allocated channel: A3
channel A3: SID=245 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''LABDB03'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''labdbdup'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/oradata/LABDB03/backup/labdb03ctl_0809ksff';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''LABDB03'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''labdbdup'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 943716976 bytes
Fixed Size 8903280 bytes
Variable Size 255852544 bytes
Database Buffers 671088640 bytes
Redo Buffers 7872512 bytes
allocated channel: A1
channel A1: SID=84 device type=DISK
allocated channel: A2
channel A2: SID=165 device type=DISK
allocated channel: A3
channel A3: SID=245 device type=DISK
Starting restore at 21-SEP-21
channel A2: skipped, AUTOBACKUP already found
channel A3: skipped, AUTOBACKUP already found
channel A1: restoring control file
channel A1: restore complete, elapsed time: 00:00:08
output file name=/oradata/LABDBDUP/control01.ctl
output file name=/oradata/LABDBDUP/control02.ctl
Finished restore at 21-SEP-21
database mounted
contents of Memory Script:
{
set until scn 2309318;
set newname for datafile 1 to
"/oradata/LABDBDUP/system01.dbf";
set newname for datafile 3 to
"/oradata/LABDBDUP/sysaux01.dbf";
set newname for datafile 4 to
"/oradata/LABDBDUP/undotbs01.dbf";
set newname for datafile 5 to
"/oradata/LABDBDUP/demo_tbls01.dbf";
set newname for datafile 7 to
"/oradata/LABDBDUP/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 21-SEP-21
channel A1: starting datafile backup set restore
channel A1: specifying datafile(s) to restore from backup set
channel A1: restoring datafile 00001 to /oradata/LABDBDUP/system01.dbf
channel A1: restoring datafile 00007 to /oradata/LABDBDUP/users01.dbf
channel A1: reading from backup piece /oradata/LABDB03/backup/labdb03_0609ksdc_6_1_1
channel A2: starting datafile backup set restore
channel A2: specifying datafile(s) to restore from backup set
channel A2: restoring datafile 00003 to /oradata/LABDBDUP/sysaux01.dbf
channel A2: restoring datafile 00004 to /oradata/LABDBDUP/undotbs01.dbf
channel A2: reading from backup piece /oradata/LABDB03/backup/labdb03_0509ksdc_5_1_1
channel A3: starting datafile backup set restore
channel A3: specifying datafile(s) to restore from backup set
channel A3: restoring datafile 00005 to /oradata/LABDBDUP/demo_tbls01.dbf
channel A3: reading from backup piece /oradata/LABDB03/backup/labdb03_0409ksdc_4_1_1
channel A1: piece handle=/oradata/LABDB03/backup/labdb03_0609ksdc_6_1_1 tag=DUPLICATE_DB
channel A1: restored backup piece 1
channel A1: restore complete, elapsed time: 00:00:55
channel A2: piece handle=/oradata/LABDB03/backup/labdb03_0509ksdc_5_1_1 tag=DUPLICATE_DB
channel A2: restored backup piece 1
channel A2: restore complete, elapsed time: 00:00:55
channel A3: piece handle=/oradata/LABDB03/backup/labdb03_0409ksdc_4_1_1 tag=DUPLICATE_DB
channel A3: restored backup piece 1
channel A3: restore complete, elapsed time: 00:01:05
Finished restore at 21-SEP-21
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1083867599 file name=/oradata/LABDBDUP/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1083867599 file name=/oradata/LABDBDUP/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1083867599 file name=/oradata/LABDBDUP/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1083867599 file name=/oradata/LABDBDUP/demo_tbls01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=1083867599 file name=/oradata/LABDBDUP/users01.dbf
contents of Memory Script:
{
set until scn 2309318;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 21-SEP-21
starting media recovery
channel A1: starting archived log restore to default destination
channel A1: restoring archived log
archived log thread=1 sequence=34
channel A1: reading from backup piece /oradata/LABDB03/backup/labdb03_0709ksfe_7_1_1
channel A1: piece handle=/oradata/LABDB03/backup/labdb03_0709ksfe_7_1_1 tag=DUPLICATE_DB
channel A1: restored backup piece 1
channel A1: restore complete, elapsed time: 00:00:01
archived log file name=/oradata/LABDBDUP/FRA/LABDBDUP/archivelog/2021_09_21/o1_mf_1_34_jnmo7s3m_.arc thread=1 sequence=34
channel clone_default: deleting archived log(s)
archived log file name=/oradata/LABDBDUP/FRA/LABDBDUP/archivelog/2021_09_21/o1_mf_1_34_jnmo7s3m_.arc RECID=1 STAMP=1083867601
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-SEP-21
released channel: A1
released channel: A2
released channel: A3
Oracle instance started
Total System Global Area 943716976 bytes
Fixed Size 8903280 bytes
Variable Size 255852544 bytes
Database Buffers 671088640 bytes
Redo Buffers 7872512 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''LABDBDUP'' 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 = ''LABDBDUP'' 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 943716976 bytes
Fixed Size 8903280 bytes
Variable Size 255852544 bytes
Database Buffers 671088640 bytes
Redo Buffers 7872512 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "LABDBDUP" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/oradata/LABDBDUP/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/oradata/LABDBDUP/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/oradata/LABDBDUP/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/oradata/LABDBDUP/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradata/LABDBDUP/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/oradata/LABDBDUP/sysaux01.dbf",
"/oradata/LABDBDUP/undotbs01.dbf",
"/oradata/LABDBDUP/demo_tbls01.dbf",
"/oradata/LABDBDUP/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/LABDBDUP/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/oradata/LABDBDUP/sysaux01.dbf RECID=1 STAMP=1083867625
cataloged datafile copy
datafile copy file name=/oradata/LABDBDUP/undotbs01.dbf RECID=2 STAMP=1083867625
cataloged datafile copy
datafile copy file name=/oradata/LABDBDUP/demo_tbls01.dbf RECID=3 STAMP=1083867625
cataloged datafile copy
datafile copy file name=/oradata/LABDBDUP/users01.dbf RECID=4 STAMP=1083867625
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1083867625 file name=/oradata/LABDBDUP/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1083867625 file name=/oradata/LABDBDUP/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=1083867625 file name=/oradata/LABDBDUP/demo_tbls01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1083867625 file name=/oradata/LABDBDUP/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 21-SEP-21
RMAN> exit;
Recovery Manager complete.
[oracle@DBsGuruN3 ~]$
Congratulation!!! We have completed successfully restore the database LABDBDUP using a Backup based Duplicate in Oracle. Let’s follow the below steps for post validation on Auxiliary Host.
13. Validate the database size, DBID, creation timestamp, archive enable, etc. Click here to get the SQL command to check the size of DATABASE.
SQL> SELECT BANNER FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> SELECT NAME, HOST_NAME, OPEN_MODE, DBID, CREATED FROM V$DATABASE, V$INSTANCE;
NAME HOST_NAME OPEN_MODE DBID CREATED
--------- ---------------------------------------------------------------- -------------------- ---------- ---------
LABDBDUP DBsGuruN3.labdomain READ WRITE 4236123496 21-SEP-21
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
14. Capture all physical files and should be the same as the SOURCE database. Click here to get the SQL command for How to find all physicals files in Oracle.
SQL> @Physical_Files.sql
NAME
------------------------------------------------------------
/oradata/LABDBDUP/control01.ctl
/oradata/LABDBDUP/control02.ctl
/oradata/LABDBDUP/demo_tbls01.dbf
/oradata/LABDBDUP/redo01.log
/oradata/LABDBDUP/redo02.log
/oradata/LABDBDUP/redo03.log
/oradata/LABDBDUP/sysaux01.dbf
/oradata/LABDBDUP/system01.dbf
/oradata/LABDBDUP/temp01.dbf
/oradata/LABDBDUP/undotbs01.dbf
/oradata/LABDBDUP/users01.dbf
11 rows selected.
15. Change the value of both convert parameters, SYS password, create PASSWORD file, etc. Again this is an optional step and follows standard requirements.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/spfilelabdbdup.ora
SQL> alter system reset db_file_name_convert scope = spfile;
System altered.
SQL> alter system reset log_file_name_convert scope=spfile;
System altered.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 943716976 bytes
Fixed Size 8903280 bytes
Variable Size 255852544 bytes
Database Buffers 671088640 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> create pfile from spfile;
File created.
SQL> alter user sys identified by Passw0rd#1438;
User altered.
[oracle@DBsGuruN3 dbs]$ orapwd file=orapwlabdbdup password=Passw0rd#1438 force=y entries=10
[oracle@DBsGuruN3 dbs]$ ls -lrt orapwlabdbdup
-rw-r-----. 1 oracle oinstall 6144 Sep 21 19:09 orapwlabdbdup
16. Validate service in LISTENER.
[oracle@DBsGuruN3 dbs]$ lsnrctl status | grep -i labdbdup
Service "labdbdup" has 1 instance(s).
Instance "labdbdup", status READY, has 1 handler(s) for this service...
Service "labdbdupXDB" has 1 instance(s).
Instance "labdbdup", status READY, has 1 handler(s) for this service...
17. Finally add the target in OEM, add/enable jobs in DBMS_scheduler/cronjobs, handover to the dependent team for checkout, etc as applicable if any. Click here to know about Target Blackouts ENABLE/DISABLE in OEM 13c.
Click here for Offline Restore or Clone Database on the same server or rename database in Oracle
Click here for How to Rename Database and DBID using DBNEWID in Oracle
Click here for How to Rename Database using CONTROLFILE in Oracle
This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
Related Articles
- Solution for ORA-01516: nonexistent log file, data file, or temporary file in Oracle
- Solution for RMAN-05541: no archived logs found in target database in Oracle
- Steps to Clone Oracle Database from Windows to Oracle Linux
- Clone PDB to another CDB using RMAN Active Duplicate in Oracle on New Host
- RMAN Incremental Level 1 Differential and Cumulative Database Backup Scripts in Oracle