Backup Based RMAN Duplicate Without Connecting To Target Database in Oracle

September 23, 2021
()

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:

COMPONENTSSOURCEDESTINATION (AUXILIARY)
INSTANCE TYPESTANDALONESTANDALONE
DATABASELABDB03LABDBDUP
STORAGE/oradata/LABDB03/oradata/LABDBDUP
VERSION19.12.0.0.019.3.0.0.0
OSLinux 8Linux 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

, , , , , ,



Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

🌐

<strong>Hello and welcome to DBsGuru,</strong>DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!<strong>Share Learn Grow!</strong>

Leave a Reply

Your email address will not be published. Required fields are marked *