Backup based Cloning a database using RMAN

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