Duplicate target database to backup location - ORACLEAGENT BLOG

1)Creating Directories

2)Verifying the RMAN backup database with control file

3)Creating pfile for clone database

4)Copying Password file for clone database

5)copy paste listener and tns for each other : prod & clone

6)Moving backup files to the directories which created for clone database

7)Connecting Rman and issue duplicate command

Target database: TEST

Cloned database: TESTDB

STEP – 1

Verifying the database name, Backups available in RMAN(Target Database)

SQL> select name,open_mode from v$database;

NAME            OPEN_MODE
--------- --------------------
TEST          READ WRITE

Verifying the database name, Backups available in RMAN(Target DB)

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;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=79 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 1 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

Step 2

Creating Pfile for TESTDB (Source DB)

*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TESTDB/control07.ctl','/u01/app/oracle/oradata/TESTDB/control08.ctl'
*.db_block_size=8192
*.db_name='testdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
#*.memory_target=1024m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#*.db_recovery_file_dest size=2g
#*.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'

Step 3

Copy target database password file to Source database

[oratest@oracle ~] cp orapwtest orapwtestdb

[oratest@oracle ~] mkdir testdb

Step 4

Copying the RMAN backup pieces and control file auto backup to desired location ‘TESTDB’(source DB)

[oratest@oracle backup]$ cp c-8581000-20210824 /u01/app/oracle/oradata/testdb
[oratest@oracle backup]$ cp c-581000-20210824-02
 /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

Step 5

Verifying Listener and tns entries in Source DB Entry.

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)

Step 6

Making the clone Database in nomount state to duplicate the Database(source db)

[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/test' 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.

Step 7

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

Tag Archives: Duplicate target database to backup location