RMAN BACKUP BASED DUPLICATION:
High-Level Steps
Creating Directories
Verifying the RMAN backup database with the control file
Creating pfile for clone database
Copying Password file for clone database
copy paste listener and tns for each other: prod & clone
Moving backup files to the directories created for the clone database
Connecting Rman and issue the duplicate command
Target database : TEST
Cloned database: TESTDB
Verifying the database name, Backups available in RMAN
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ WRITE
RMAN> SHOW CONTROLFILE AUTOBACKUP;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
RMAN> CROSSCHECK BACKUP OF DATABASE;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0106mhr1_1_1 RECID=1
STAMP=1080772449
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/090798lu_1_1 RECID=9
STAMP=1081385662
Crosschecked 2 objects
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.19G DISK 00:00:44 16-AUG-21
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20210816T223409
Piece Name: /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0106mhr1_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2351179 16-AUG-21 NO /u01/app/oracle/oradata/TEST/system01.dbf
3 Full 2351179 16-AUG-21 NO /u01/app/oracle/oradata/TEST/sysaux01.dbf
4 Full 2351179 16-AUG-21 NO /u01/app/oracle/oradata/TEST/undotbs01.dbf
7 Full 2351179 16-AUG-21 NO /u01/app/oracle/oradata/TEST/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 1.23G DISK 00:00:51 24-AUG-21
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20210824T005422
Piece Name: /u01/app/oracle/oradata/TEST/backup/090798lu_1_1
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2769124 24-AUG-21 NO /u01/app/oracle/oradata/TEST/system01.dbf
3 Full 2769124 24-AUG-21 NO /u01/app/oracle/oradata/TEST/sysaux01.dbf
4 Full 2769124 24-AUG-21 NO /u01/app/oracle/oradata/TEST/undotbs01.dbf
7 Full 2769124 24-AUG-21 NO /u01/app/oracle/oradata/TEST/users01.dbf
Creating Pfile for TESTDB
[oratest@oracle dbs]$ vi inittestdb.ora
*.db_name='testdb
*.control_files='/u01/app/oracle/oradata/TESTDB/control04.ctl','/u01/app/oracle/oradata/TESTDB/control05.ctl'
#*.db_recovery_file_dest size=1g
#*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_file_name_convert='/u01/app/oracle/oradata/TEST/datafile','/u01/app/oracle/oradata/TESTDB'
*.log_file_name_convert='/u01/app/oracle/oracle/oradata/TEST/onlinelog','/u01/app/oracle/oradata/TESTDB'
Copy Target Database Password File to Auxiliary Database
[oratest@oracle dbs]$ cp orapwtest orapwtestdb
[oratest@oracle oradata]$ mkdir testdb
[oratest@oracle oradata]$ ls
data01.dbf ORCL TEST testdb
Copying the RMAN backup pieces and control file auto backup to Desired location ‘TESTDB’
[oratest@oracle backup]$ cp c-2378581000-20210824/u01/app/oracle/oradata/testdb
[oratest@oracle backup]$ cp c-2378581002021082402/u01/app/oracle/oradata/testdb
[oratest@oracle backup]$ cp 070798lh_1_1 /u01/app/oracle/oradata/testdb
[oratest@oracle backup]$ cp 090798lu_1_1 /u01/app/oracle/oradata/testdb
[oratest@oracle testdb]$ ls -l
total 1319440
-rw-rw----. 1 oratest oratest 10682368 Aug 24 01:01 070798lh_1_1
-rw-rw----. 1 oratest oratest 1318993920 Aug 24 01:02 090798lu_1_1
-rw-rw----. 1 oratest oratest 10715136 Aug 24 01:01 c-2378581000-20210824-01
-rw-rw----. 1 oratest oratest 10715136 Aug 24 01:01 c-2378581000-20210824-02
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
[oratest@oracle dbs]$ export ORACLE_SID=testdb
[oratest@oracle dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 24 22:33:36 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='$ORACLE_HOME/dbs/inittestdb.ora' nomount;
ORACLE instance started.
Total System Global Area 281017392 bytes
Fixed Size 8895536 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
SQL> exit
[oratest@oracle dbs]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 24 22:54:08 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)
RMAN> exit
Verifying Listener and Tns Entries
TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
(UR = A)
)
)
[oratest@oracle admin]$ rman target sys/oracle@test
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 24 22:55:01 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2378581000)
RMAN> connect auxiliary sys/oracle@testdb
connected to auxiliary database: TESTDB (not mounted)
Making the clone Database in nomount state to Duplicate the Database
[oratest@oracle dbs]$ export ORACLE_SID=testdb
[oratest@oracle dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 24 23:09:52 2021
Version 19.3.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.3.0.0.0
SQL> select status from v$instance;
STATUS
------------
STARTED
[oratest@oracle dbs]$ rman auxiliary /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 24 23:11:06 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: TESTDB (not mounted)
RMAN> duplicate target database to testdb backup location
'/u01/app/oracle/oradata/testdb' nofilenamecheck;
Starting Duplicate Db at 24-AUG-21
searching for database ID
found backup of database ID 2378581000
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 281017392 bytes
Fixed Size 8895536 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''TEST'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''testdb'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u01/app/oracle/oradata/testdb/c-2378581000-20210824-04';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''testdb'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 281017392 bytes
Fixed Size 8895536 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Starting restore at 24-AUG-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=424 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/TESTDB/control07.ctl
output file name=/u01/app/oracle/oradata/TESTDB/control08.ctl
Finished restore at 24-AUG-21
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=424 device type=DISK
RMAN-05158: WARNING: auxiliary (bctfile) file name /u01/app/oracle/oradata/TEST/changetracking/o1_mf_jl7qx7bp_.chg conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/oradata/TEST/redo01.log conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/oradata/TEST/redo02.log conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (logfile) file name /u01/app/oracle/oradata/TEST/redo03.log conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/TEST/system01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/TEST/sysaux01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/TEST/undotbs01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/TEST/users01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/TEST/temp01.dbf conflicts with a file used by the target database
contents of Memory Script:
{
set until scn 2841711;
set newname for datafile 1 to
"/u01/app/oracle/oradata/TEST/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/TEST/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/TEST/undotbs01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/TEST/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 24-AUG-21
using channel ORA_AUX_DISK_1
database opened
Cannot remove created server parameter file
Finished Duplicate Db at 24-AUG-21
RMAN> exit
Recovery Manager complete.
Now the database has been successfully cloned we can verify the Database
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTDB READ WRITE
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
