RMAN Active Duplicate Database in Oracle from ASM to ASM

September 18, 2021
()

RMAN Active Duplicate Database in Oracle from ASM to ASM


In a recent article, we demonstrated about Step by Step Migrate Database from Non-ASM to ASM in Oracle click here to know more about it. In this article, we will demonstrate step by step RMAN Active Duplicate Database in Oracle from ASM to ASM with practical.

The active duplicate database features introduced in database version 11g. The Active duplicate database doesn’t require backup. Duplication of database performs over the network copying database files to the auxiliary instance from the live source database which should be either mount or open mode, the preferably open state with zero downtime. Below are the advantages and disadvantages of an active duplicate database.


Advantages

1. Backup is not required of the source database.
2. No downtime require applicable to archive enable on the database.
3. Eliminate manual efforts as post activities.


Disadvantages

1. High utilization of network traffic between Source and Auxiliary Host.
2. It may impact negative performance on the source database throughout entire duplicate activities.

Below are the environment details.

COMPONENTSSOURCEDESTINATION
INSTANCE TYPEHASHAS
DATABASELABDB05LABDBDEV
STORAGEASMASM
VERSION12.2.0.1.0 12.2.0.1.0
OSLinux 7Linux 7


1. 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 NAME, OPEN_MODE, DBID, CREATED FROM V$DATABASE;

NAME      OPEN_MODE                  DBID CREATED
--------- -------------------- ---------- ---------
LADB05    READ WRITE           3972956076 30-JUL-21 

SQL> @DATABASE_SIZE.sql

DATED     HOST_NAME            INSTANCE_NAME    VERSION           TOTAL_ALLOCATED_GB    USED_GB    FREE_GB
--------- -------------------- ---------------- ----------------- ------------------ ---------- ----------
14-AUG-21 DBsGuruN4.com        labdb05          12.2.0.1.0        127.682932         113.362694 14.320238

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /backup/arch/labdb05
Oldest online log sequence     448
Next log sequence to archive   450
Current log sequence           450


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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA1/oradata/labdb05/control01.ctl
+DATA1/oradata/labdb05/control02.ctl
+DATA1/oradata/labdb05/demo1_tbls_01.dbf
+DATA1/oradata/labdb05/demo1_tbls_02.dbf
+DATA1/oradata/labdb05/demo2_tbls_01.dbf
+DATA1/oradata/labdb05/demo1_tbls_03.dbf
+DATA1/oradata/labdb05/demo3_tbls_01.dbf
+DATA1/oradata/labdb05/demo3_tbls_02.dbf
+DATA1/oradata/labdb05/demo3_tbls_03.dbf
+DATA1/oradata/labdb05/demo4_tbls_01.dbf
+DATA1/oradata/labdb05/demo4_tbls_02.dbf
+DATA1/oradata/labdb05/demo4_tbls_03.dbf
+DATA1/oradata/labdb05/redo01.log
+DATA1/oradata/labdb05/redo02.log
+DATA1/oradata/labdb05/redo03.log
+DATA1/oradata/labdb05/sysaux01.dbf
+DATA1/oradata/labdb05/system01.dbf
+DATA1/oradata/labdb05/temp01.dbf
+DATA1/oradata/labdb05/undotbs01.dbf
+DATA1/oradata/labdb05/users01.dbf

20 rows selected.


3. Create a PFILE for Auxiliary Instance.

SQL> create pfile='/home/oracle/initlabdb05.ora' from spfile;

File created.


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 ~]$ 
[oracle@DBsGuruN4 dbs]$ orapwd file=orapwlabdb05 password=Passw0rd@5743 force=y entries=10
[oracle@DBsGuruN4 dbs]$ ls -lrt orapwlabdb5 
-rw-r----- 1 oracle dba 6144 Aug  14  05:25 orapwlabdb05 


5. Transfer PFILE and PASSWORD files to Auxiliary Host.

[oracle@DBsGuruN4 oracle]$ ls -lrt |tail initlabdb05.ora
-rw-r--r-- 1 oracle dba   1137 Aug  14 02:12 initlabdb05.ora
[oracle@DBsGuruN4 ~]$ scp initlabdb05.ora oracle@DBsGuruN5:/u01/app/oracle/product/12.2.0.1/db_1/dbs/initlabdbdev.ora
initlabdb05.ora                                                                                                        100% 1137   495.9KB/s   00:00
[oracle@DBsGuruN4 oracle]$ cd $ORACLE_HOME/dbs
[oracle@DBsGuruN4 dbs]$ scp orapwlabdb05 oracle@DBsGuruN5:/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapwlabdbdev
orapwlabdb05                                                                                                                100% 3584     1.3MB/s   00:00


Now login to Auxiliary Host and perform the below activities.

6. Validate available free size of destination ASM DISK GROUP/S. Make sure you have enough free space in DG as require to migrate the database. Click here to get the SQL command for How to check ASM diskgroup Size.

SQL> @Disk_Group_Size.sql
 
NAME                             TOTAL_GB    FREE_GB    USED_GB   FREE_PER
------------------------------ ---------- ---------- ---------- ----------
DATA1                           31657.9961 4369.72168 27288.2744 13.8029004
OCR                            8.44433594 7.89257813 .551757813 93.4659419
ORAARCH                        404.601563 404.313477 .288085938 99.9287976
REDO                           1511.53613 1446.42969 65.1064453 95.6926967


7. Create a directory in ASM.

[oracle@DBsGuruN5 ~]$ asmcmd
ASMCMD> cd DATA1/oradata
ASMCMD> mkdir labdbdev
ASMCMD> cd labdbdev
ASMCMD> pwd
+DATA1/oradata/labdbdev

Note: This is a completely optional step instead of it we can use DG +DATA1.


8. Create require Directories for Auxiliary Instance.

[oracle@DBsGuruN5 ~]$ mkdir -p /u01/app/oracle/admin/labdbdev/adump
[oracle@DBsGuruN5 ~]$ mkdir -p /backup/arch/labdbdev
[oracle@DBsGuruN5 ~]$ ls -ld /u01/app/oracle/admin/labdbdev/adump /backup/arch/labdbdev
drwxr-xr-x 2 oracle dba 6 Aug  14 02:47 /apps/oracle/product/admin/labdbdev/adump
drwxr-xr-x 2 oracle dba 6 Aug  14 02:48 /backup/arch/labdbdev


9. Prepare PFILE to start Auxiliary Instance. Replace require values according to Auxiliary instance and 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@DBsGuruN5 ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/
[oracle@DBsGuruN5 dbs]$ ls -lrt initlabdbdev.ora
-rw-r--r-- 1 oracle dba 1137 Aug  14 02:32 initlabdbdev.ora
[oracle@DBsGuruN5 dbs]$ cp initlabdbdev.ora initlabdbdev.ora.bkp
[oracle@DBsGuruN5 dbs]$ vi initlabdbdev.ora
[oracle@DBsGuruN5 dbs]$ ls -lrt initlabdbdev.ora
-rw-r--r-- 1 oracle dba 667 Aug  14 02:39 initlabdbdev.ora
[oracle@DBsGuruN5 dbs]$ cat initlabdbdev.ora
*.audit_file_dest='/u01/app/oracle/admin/labdbdev/adump'
*.audit_trail='db'
*.compatible='12.2.0.1.0'
*.control_files='+DATA1/oradata/labdbdev/control01.ctl','+DATA1/oradata/labdbdev/control02.ctl'
*.db_block_size=8192
*.db_name='labdbdev'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=labdbdevXDB)'
*.log_archive_dest='/backup/arch/labdbdev'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=1000
*.pga_aggregate_target=512m
*.processes=400
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=1G
*.sga_target=1G
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='+DATA1/oradata/labdb05','+DATA1/oradata/labdbdev'
*.log_file_name_convert='+DATA1/oradata/labdb05','+DATA1/oradata/labdbdev'


10. Add static service to the listener for Auxiliary Instance. Add below lines in the listener.ora file and reload the listener service.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = labdbdev)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(SID_NAME = labdbdev)
)
)

[oracle@DBsGuruN5 ~]$ cd $TNS_ADMIN
[oracle@DBsGuruN5 admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = labdbdev)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
      (SID_NAME = labdbdev)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN5.com )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

[oracle@DBsGuruN5 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-Aug-2021 02:59:04

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN5.com)(PORT=1521)))
The command completed successfully

[oracle@DBsGuruN5 admin]$ lsnrctl status| grep labdbdev
Service "labdbdev" has 1 instance(s).
  Instance "labdbdev", status UNKNOWN, has 1 handler(s) for this service...
[oracle@DBsGuruN5 admin]$


11. Add entry in oratab.

[oracle@DBsGuruN5 ~]$ cat /etc/oratab | grep labdbdev
labdbdev:/u01/app/oracle/product/12.2.0.1/db_1:N


12. Start Auxiliary Instance in NOMOUNT state.

[oracle@DBsGuruN5 admin]$ . oraenv
ORACLE_SID = [oracle] ? labdbdev
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN5 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 14 03:09:38 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>  startup nomount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8620128 bytes
Variable Size             486541216 bytes
Database Buffers          721420288 bytes
Redo Buffers                8155136 bytes
SQL>


13. Prepare TNS entries for both Source Database and Auxiliary Instance.

LABDB05 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN4.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = labdb05)
    )
  )

LABDBDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN5.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = labdbdev)
    )
  )


14. Add both TNS entries in both Source & Auxiliary Host into the file $ORACLE_HOME/network/admin/tnsnames.ora and validate it.

==On Source Host==
[oracle@DBsGuruN4 admin]$ tnsping LABDBDEV

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 14-Aug-2021 03:41:03

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN5.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = labdbdev)))
OK (10 msec)
[oracle@DBsGuruN4 ~]$ tnsping LABDB05

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 14-Aug-2021 03:41:19

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN4.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = labdb05)))
OK (10 msec)

==On Auxiliary Host==
[oracle@DBsGuruN5 admin]$ tnsping LABDBDEV

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 14-Aug-2021 03:43:57

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN5.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = labdbdev)))
OK (0 msec)
[oracle@DBsGuruN5 admin]$ tnsping LABDB05

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 14-Aug-2021 03:44:09

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DBsGuruN4.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = labdb05)))
OK (20 msec)
[oracle@DBsGuruN5 admin]$


15. Validate connectivity between Source database and Auxiliary Instance vice versa.

[oracle@DBsGuruN5 admin]$ sqlplus sys/Passw0rd@5743@LABDB05 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 14 03:46:50 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT NAME,HOST_NAME FROM V$DATABASE,V$INSTANCE;

NAME      HOST_NAME
--------- ----------------------------------------------------------------
LABDB05   DBsGuruN4.com

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

[oracle@DBsGuruN4 ~]$ sqlplus sys/Passw0rd@5743@LABDBDEV as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 14 03:50:16 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT INSTANCE_NAME, HOST_NAME FROM V$INSTANCE;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
labdbdev          DBsGuruN5.com

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


16. Connect to RMAN.

[oracle@DBsGuruN5 admin]$ rman

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Aug 14 03:58:10 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/Passw0rd@5743@LABDB05

connected to target database: LABDB05 (DBID=3972956076)

RMAN> connect auxiliary sys/Passw0rd@5743@LABDBDEV

connected to auxiliary database: LABDBDEV (not mounted)

RMAN>


17. Validate allocate target and auxiliary channels before moving to actual active duplication, this is an optional step.

RMAN> run{
allocate channel C1 type disk;
allocate channel C2 type disk;
allocate channel C3 type disk;
allocate channel C4 type disk;
allocate channel C5 type disk;
allocate auxiliary channel A1 device type disk;
allocate auxiliary channel A2 device type disk;
allocate auxiliary channel A3 device type disk;
allocate auxiliary channel A4 device type disk;
allocate auxiliary channel A5 device type disk;
release channel C1;
release channel C2;
release channel C3;
release channel C4;
release channel C5;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=504 device type=DISK

allocated channel: C2
channel C2: SID=746 device type=DISK

allocated channel: C3
channel C3: SID=879 device type=DISK

allocated channel: C4
channel C4: SID=14 device type=DISK

allocated channel: C5
channel C5: SID=255 device type=DISK

allocated channel: A1
channel A1: SID=619 device type=DISK

allocated channel: A2
channel A2: SID=5 device type=DISK

allocated channel: A3
channel A3: SID=128 device type=DISK

allocated channel: A4
channel A4: SID=253 device type=DISK

allocated channel: A5
channel A5: SID=374 device type=DISK

released channel: C1

released channel: C2

released channel: C3

released channel: C4

released channel: C5
released channel: A1
released channel: A2
released channel: A3
released channel: A4
released channel: A5

RMAN>


18. Perform Active Duplicate Database connected to RMAN session.

RMAN> connect target sys/Passw0rd@5743@LABDB05

connected to target database: LABDB05 (DBID=3972956076)

RMAN> connect auxiliary sys/Passw0rd@5743@LABDBDEV

connected to auxiliary database: LABDBDEV (not mounted)

RMAN>

RMAN> run{
allocate channel C1 type disk;
allocate channel C2 type disk;
allocate channel C3 type di2> sk;
allocate channel C4 type disk;
allocate channel C5 type disk;
allocate auxiliary channel A1 device type disk;
allocate auxiliary channel A2 device type disk;
allocate auxiliary channel A3 device type disk;
allocate auxiliary channel A4 device type disk;
allocate auxiliary channel A5 device type disk;
duplicate target database to LABDBDEV from active database nofilenamecheck;
release channel C1;
release channel C2;
release channel C3;
release channel C4;
release channel C5;
}3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18>

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=504 device type=DISK

allocated channel: C2
channel C2: SID=746 device type=DISK

allocated channel: C3
channel C3: SID=871 device type=DISK

allocated channel: C4
channel C4: SID=14 device type=DISK

allocated channel: C5
channel C5: SID=255 device type=DISK

allocated channel: A1
channel A1: SID=376 device type=DISK

allocated channel: A2
channel A2: SID=497 device type=DISK

allocated channel: A3
channel A3: SID=621 device type=DISK

allocated channel: A4
channel A4: SID=744 device type=DISK

allocated channel: A5
channel A5: SID=868 device type=DISK

Starting Duplicate Db at 14-Aug-21
current log archived

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    4294967296 bytes

Fixed Size                     8628936 bytes
Variable Size               3858761016 bytes
Database Buffers             419430400 bytes
Redo Buffers                   8146944 bytes
allocated channel: A1
channel A1: SID=253 device type=DISK
allocated channel: A2
channel A2: SID=376 device type=DISK
allocated channel: A3
channel A3: SID=497 device type=DISK
allocated channel: A4
channel A4: SID=623 device type=DISK
allocated channel: A5
channel A5: SID=743 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''LABDB05'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''labdbdev'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'LABDB05' primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''LABDB05'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''labdbdev'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    4294967296 bytes

Fixed Size                     8628936 bytes
Variable Size               3858761016 bytes
Database Buffers             419430400 bytes
Redo Buffers                   8146944 bytes
allocated channel: A1
channel A1: SID=253 device type=DISK
allocated channel: A2
channel A2: SID=376 device type=DISK
allocated channel: A3
channel A3: SID=497 device type=DISK
allocated channel: A4
channel A4: SID=623 device type=DISK
allocated channel: A5
channel A5: SID=743 device type=DISK

Starting restore at 14-Aug-21

channel A1: starting datafile backup set restore
channel A1: using network backup set from service LABDB05
channel A1: restoring control file
channel A1: restore complete, elapsed time: 00:00:05
output file name=+DATA1/oradata/labdbdev/control01.ctl
output file name=+DATA1/oradata/labdbdev/control02.ctl
Finished restore at 14-Aug-21

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "+DATA1/oradata/labdbdev/system01.dbf";
   set newname for datafile  2 to
 "+DATA1/oradata/labdbdev/demo4_tbls_02.dbf";
   set newname for datafile  3 to
 "+DATA1/oradata/labdbdev/sysaux01.dbf";
   set newname for datafile  4 to
 "+DATA1/oradata/labdbdev/undotbs01.dbf";
   set newname for datafile  5 to
 "+DATA1/oradata/labdbdev/demo1_tbls_01.dbf";
   set newname for datafile  7 to
 "+DATA1/oradata/labdbdev/users01.dbf";
   set newname for datafile  8 to
 "+DATA1/oradata/labdbdev/demo2_tbls_01.dbf";
   set newname for datafile  9 to
 "+DATA1/oradata/labdbdev/demo1_tbls_02.dbf";
   set newname for datafile  10 to
 "+DATA1/oradata/labdbdev/demo1_tbls_03.dbf";
   set newname for datafile  11 to
 "+DATA1/oradata/labdbdev/demo3_tbls_01.dbf";
   set newname for datafile  12 to
 "+DATA1/oradata/labdbdev/demo3_tbls_02.dbf";
   set newname for datafile  13 to
 "+DATA1/oradata/labdbdev/demo3_tbls_03.dbf";
   set newname for datafile  14 to
 "+DATA1/oradata/labdbdev/demo4_tbls_01.dbf";
   set newname for datafile  15 to
 "+DATA1/oradata/labdbdev/demo4_tbls_03.dbf";
   restore
   from  nonsparse   from service
 'LABDB05'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-Aug-21

channel A1: starting datafile backup set restore
channel A1: using network backup set from service LABDB05
channel A1: specifying datafile(s) to restore from backup set
channel A1: restoring datafile 00001 to +DATA1/oradata/labdbdev/system01.dbf
channel A2: starting datafile backup set restore
channel A2: using network backup set from service LABDB05
channel A2: specifying datafile(s) to restore from backup set
channel A2: restoring datafile 00002 to +DATA1/oradata/labdbdev/demo4_tbls_02.dbf
channel A3: starting datafile backup set restore
channel A3: using network backup set from service LABDB05
channel A3: specifying datafile(s) to restore from backup set
channel A3: restoring datafile 00003 to +DATA1/oradata/labdbdev/sysaux01.dbf
channel A4: starting datafile backup set restore
channel A4: using network backup set from service LABDB05
channel A4: specifying datafile(s) to restore from backup set
channel A4: restoring datafile 00004 to +DATA1/oradata/labdbdev/undotbs01.dbf
channel A5: starting datafile backup set restore
channel A5: using network backup set from service LABDB05
channel A5: specifying datafile(s) to restore from backup set
channel A5: restoring datafile 00005 to +DATA1/oradata/labdbdev/demo1_tbls_01.dbf
channel A1: restore complete, elapsed time: 00:00:14
channel A1: starting datafile backup set restore
channel A1: using network backup set from service LABDB05
channel A1: specifying datafile(s) to restore from backup set
channel A1: restoring datafile 00007 to +DATA1/oradata/labdbdev/users01.dbf
channel A1: restore complete, elapsed time: 00:00:19
channel A1: starting datafile backup set restore
channel A1: using network backup set from service LABDB05
channel A1: specifying datafile(s) to restore from backup set
channel A1: restoring datafile 00008 to +DATA1/oradata/labdbdev/demo2_tbls_01.dbf
channel A4: restore complete, elapsed time: 00:00:34
channel A4: starting datafile backup set restore
channel A4: using network backup set from service LABDB05
channel A4: specifying datafile(s) to restore from backup set
channel A4: restoring datafile 00009 to +DATA1/oradata/labdbdev/demo1_tbls_02.dbf
channel A3: restore complete, elapsed time: 00:01:45
channel A3: starting datafile backup set restore
channel A3: using network backup set from service LABDB05
channel A3: specifying datafile(s) to restore from backup set
channel A3: restoring datafile 00010 to +DATA1/oradata/labdbdev/demo1_tbls_03.dbf
channel A1: restore complete, elapsed time: 00:01:46
channel A1: starting datafile backup set restore
channel A1: using network backup set from service LABDB05
channel A1: specifying datafile(s) to restore from backup set
channel A1: restoring datafile 00011 to +DATA1/oradata/labdbdev/demo3_tbls_01.dbf
channel A2: restore complete, elapsed time: 00:02:45
channel A2: starting datafile backup set restore
channel A2: using network backup set from service LABDB05
channel A2: specifying datafile(s) to restore from backup set
channel A2: restoring datafile 00012 to +DATA1/oradata/labdbdev/demo3_tbls_02.dbf
channel A5: restore complete, elapsed time: 00:02:47
channel A5: starting datafile backup set restore
channel A5: using network backup set from service LABDB05
channel A5: specifying datafile(s) to restore from backup set
channel A5: restoring datafile 00013 to +DATA1/oradata/labdbdev/demo3_tbls_03.dbf
channel A2: restore complete, elapsed time: 00:02:08
channel A2: starting datafile backup set restore
channel A2: using network backup set from service LABDB05
channel A2: specifying datafile(s) to restore from backup set
channel A2: restoring datafile 00014 to +DATA1/oradata/labdbdev/demo4_tbls_01.dbf
channel A2: restore complete, elapsed time: 00:02:07
channel A2: starting datafile backup set restore
channel A2: using network backup set from service LABDB05
channel A2: specifying datafile(s) to restore from backup set
channel A2: restoring datafile 00015 to +DATA1/oradata/labdbdev/demo4_tbls_03.dbf
channel A4: restore complete, elapsed time: 00:06:49
channel A2: restore complete, elapsed time: 00:01:16
channel A3: restore complete, elapsed time: 00:06:40
channel A1: restore complete, elapsed time: 00:06:38
channel A5: restore complete, elapsed time: 00:06:31
Finished restore at 14-Aug-21

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'LABDB05'
           archivelog from scn  370915129;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 14-Aug-21

channel A1: starting archived log restore to default destination
channel A1: using network backup set from service LABDB05
channel A1: restoring archived log
archived log thread=1 sequence=452
channel A2: starting archived log restore to default destination
channel A2: using network backup set from service LABDB05
channel A2: restoring archived log
archived log thread=1 sequence=453
channel A1: restore complete, elapsed time: 00:00:01
channel A2: restore complete, elapsed time: 00:00:02
Finished restore at 14-Aug-21

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=1082694595 file name=+DATA1/oradata/labdbdev/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=1082694596 file name=+DATA1/oradata/labdbdev/demo4_tbls_02.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=1082694596 file name=+DATA1/oradata/labdbdev/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=1082694597 file name=+DATA1/oradata/labdbdev/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=1082694597 file name=+DATA1/oradata/labdbdev/demo1_tbls_01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=20 STAMP=1082694598 file name=+DATA1/oradata/labdbdev/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=21 STAMP=1082694598 file name=+DATA1/oradata/labdbdev/demo2_tbls_01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=22 STAMP=1082694599 file name=+DATA1/oradata/labdbdev/demo1_tbls_02.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=23 STAMP=1082694599 file name=+DATA1/oradata/labdbdev/demo1_tbls_03.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=24 STAMP=1082694600 file name=+DATA1/oradata/labdbdev/demo3_tbls_01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=25 STAMP=1082694601 file name=+DATA1/oradata/labdbdev/demo3_tbls_02.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=26 STAMP=1082694601 file name=+DATA1/oradata/labdbdev/demo3_tbls_03.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=27 STAMP=1082694602 file name=+DATA1/oradata/labdbdev/demo4_tbls_01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=28 STAMP=1082694603 file name=+DATA1/oradata/labdbdev/demo4_tbls_03.dbf

contents of Memory Script:
{
   set until scn  370919604;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-Aug-21

starting media recovery

archived log for thread 1 with sequence 452 is already on disk as file /backup/arch/labdbdev/1_452_1079300272.dbf
archived log for thread 1 with sequence 453 is already on disk as file /backup/arch/labdbdev/1_453_1079300272.dbf
archived log file name=/backup/arch/labdbdev/1_452_1079300272.dbf thread=1 sequence=452
archived log file name=/backup/arch/labdbdev/1_453_1079300272.dbf thread=1 sequence=453
media recovery complete, elapsed time: 00:00:01
Finished recover at 14-Aug-21
released channel: C1
released channel: C2
released channel: C3
released channel: C4
released channel: C5
released channel: A1
released channel: A2
released channel: A3
released channel: A4
released channel: A5
Oracle instance started

Total System Global Area    4294967296 bytes

Fixed Size                     8628936 bytes
Variable Size               3858761016 bytes
Database Buffers             419430400 bytes
Redo Buffers                   8146944 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''LABDBDEV'' 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 =  ''LABDBDEV'' 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    4294967296 bytes

Fixed Size                     8628936 bytes
Variable Size               3858761016 bytes
Database Buffers             419430400 bytes
Redo Buffers                   8146944 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "LABDBDEV" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+DATA1/oradata/labdbdev/redo01.log' ) SIZE 200 M  REUSE,
  GROUP   2 ( '+DATA1/oradata/labdbdev/redo02.log' ) SIZE 200 M  REUSE,
  GROUP   3 ( '+DATA1/oradata/labdbdev/redo03.log' ) SIZE 200 M  REUSE
 DATAFILE
  '+DATA1/oradata/labdbdev/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATA1/oradata/labdbdev/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA1/oradata/labdbdev/demo4_tbls_02.dbf",
 "+DATA1/oradata/labdbdev/sysaux01.dbf",
 "+DATA1/oradata/labdbdev/undotbs01.dbf",
 "+DATA1/oradata/labdbdev/demo1_tbls_01.dbf",
 "+DATA1/oradata/labdbdev/users01.dbf",
 "+DATA1/oradata/labdbdev/demo2_tbls_01.dbf",
 "+DATA1/oradata/labdbdev/demo1_tbls_02.dbf",
 "+DATA1/oradata/labdbdev/demo1_tbls_03.dbf",
 "+DATA1/oradata/labdbdev/demo3_tbls_01.dbf",
 "+DATA1/oradata/labdbdev/demo3_tbls_02.dbf",
 "+DATA1/oradata/labdbdev/demo3_tbls_03.dbf",
 "+DATA1/oradata/labdbdev/demo4_tbls_01.dbf",
 "+DATA1/oradata/labdbdev/demo4_tbls_03.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA1/oradata/labdbdev/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo4_tbls_02.dbf RECID=1 STAMP=1082694676
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/sysaux01.dbf RECID=2 STAMP=1082694677
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/undotbs01.dbf RECID=3 STAMP=1082694677
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo1_tbls_01.dbf RECID=4 STAMP=1082694677
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/users01.dbf RECID=5 STAMP=1082694677
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo2_tbls_01.dbf RECID=6 STAMP=1082694677
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo1_tbls_02.dbf RECID=7 STAMP=1082694678
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo1_tbls_03.dbf RECID=8 STAMP=1082694678
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo3_tbls_01.dbf RECID=9 STAMP=1082694678
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo3_tbls_02.dbf RECID=10 STAMP=1082694678
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo3_tbls_03.dbf RECID=11 STAMP=1082694679
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo4_tbls_01.dbf RECID=12 STAMP=1082694679
cataloged datafile copy
datafile copy file name=+DATA1/oradata/labdbdev/demo4_tbls_03.dbf RECID=13 STAMP=1082694679

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1082694676 file name=+DATA1/oradata/labdbdev/demo4_tbls_02.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1082694677 file name=+DATA1/oradata/labdbdev/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1082694677 file name=+DATA1/oradata/labdbdev/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1082694677 file name=+DATA1/oradata/labdbdev/demo1_tbls_01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=1082694677 file name=+DATA1/oradata/labdbdev/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=6 STAMP=1082694677 file name=+DATA1/oradata/labdbdev/demo2_tbls_01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=7 STAMP=1082694678 file name=+DATA1/oradata/labdbdev/demo1_tbls_02.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=8 STAMP=1082694678 file name=+DATA1/oradata/labdbdev/demo1_tbls_03.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=9 STAMP=1082694678 file name=+DATA1/oradata/labdbdev/demo3_tbls_01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=10 STAMP=1082694678 file name=+DATA1/oradata/labdbdev/demo3_tbls_02.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=11 STAMP=1082694679 file name=+DATA1/oradata/labdbdev/demo3_tbls_03.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=12 STAMP=1082694679 file name=+DATA1/oradata/labdbdev/demo4_tbls_01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=13 STAMP=1082694679 file name=+DATA1/oradata/labdbdev/demo4_tbls_03.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 14-Aug-21

release channel C1;
release channel A1;

RMAN>


Congratulation!!! We have completed successfully restore the database LABDBDEV using Active Duplicate in Oracle. Let’s follow the below steps for post validation on Auxiliary Host.

19. 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 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> SELECT NAME, HOST_NAME, OPEN_MODE, DBID, CREATED FROM V$DATABASE, V$INSTANCE;

NAME      HOST_NAME             OPEN_MODE                  DBID CREATED
--------- --------------------- -------------------- ---------- ---------
LABDB02   DBsGuruN5.com         READ WRITE           3972528048 14-AUG-21


20. 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
--------------------------------------------------------------------------------
+DATA1/oradata/labdbdev/control01.ctl
+DATA1/oradata/labdbdev/control02.ctl
+DATA1/oradata/labdbdev/demo2_tbls_01.dbf
+DATA1/oradata/labdbdev/demo1_tbls_01.dbf
+DATA1/oradata/labdbdev/demo1_tbls_02.dbf
+DATA1/oradata/labdbdev/demo1_tbls_03.dbf
+DATA1/oradata/labdbdev/demo3_tbls_01.dbf
+DATA1/oradata/labdbdev/demo3_tbls_02.dbf
+DATA1/oradata/labdbdev/demo3_tbls_03.dbf
+DATA1/oradata/labdbdev/demo4_tbls_01.dbf
+DATA1/oradata/labdbdev/demo4_tbls_02.dbf
+DATA1/oradata/labdbdev/demo4_tbls_03.dbf
+DATA1/oradata/labdbdev/redo01.log
+DATA1/oradata/labdbdev/redo02.log
+DATA1/oradata/labdbdev/redo03.log
+DATA1/oradata/labdbdev/sysaux01.dbf
+DATA1/oradata/labdbdev/system01.dbf
+DATA1/oradata/labdbdev/temp01.dbf
+DATA1/oradata/labdbdev/undotbs01.dbf
+DATA1/oradata/labdbdev/users01.dbf

20 rows selected.


21. Validate the path of SPFILE.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.2.0.1/db_1/
                                                 dbs/spfilelabdbdev.ora


22. Register database in a server control, applicable here since it’s HAS (ORACLE RESTART).

[oracle@DBsGuruN5 dbs]$ srvctl add database -d labdbdev -n labdbdev  -o /u01/app/oracle/product/12.2.0.1/db_1 -p /u01/app/oracle/product/12.2.0.1/db_1/dbs/spfilelabdbdev.ora -diskgroup DATA1
[oracle@DBsGuruN5 dbs]$ srvctl config database -d labdbdev
Database unique name: labdbdev
Database name: labdbdev
Oracle home: /u01/app/oracle/product/12.2.0.1/db_1
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.2.0.1/db_1/dbs/spfilelabdbdev.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA1
Services:
OSDBA group: dba
OSOPER group: dba
Database instance: labdbdev
[oracle@DBsGuruN5 dbs]$


23. Change the value of both convert parameters, SYS password, create PASSWORD file, etc. Again this is an optional step and follows standard requirements.

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.

[oracle@DBsGuruN5 dbs]$ srvctl start database -d labdbdev
[oracle@DBsGuruN5 dbs]$ srvctl status database -d labdbdev
Database is running.
[oracle@DBsGuruN5 dbs]$

SQL> alter user sys identified by Passw0rd@3745;

User altered.

SQL> create pfile from spfile;

File created.

[oracle@DBsGuruN5 dbs]$ orapwd file=orapwlabdbdev password=Passw0rd@3745 force=y entries=10
[oracle@DBsGuruN5 dbs]$ ls -lrt orapwlabdbdev
-rw-r----- 1 oracle dba 6144 Aug  14 05:25 orapwlabdbdev


24. 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 Clone PDB to another CDB using RMAN Active Duplicate in Oracle on New Host
Click here for Step by Step Migrate Database from Non-ASM to ASM 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?

<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 *