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.
| COMPONENTS | SOURCE | DESTINATION |
| INSTANCE TYPE | HAS | HAS |
| DATABASE | LABDB05 | LABDBDEV |
| STORAGE | ASM | ASM |
| VERSION | 12.2.0.1.0 | 12.2.0.1.0 |
| OS | Linux 7 | Linux 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
- Solution for ORA-01516: nonexistent log file, data file, or temporary file in Oracle
- Solution for RMAN-05541: no archived logs found in target database in Oracle
- Steps to Clone Oracle Database from Windows to Oracle Linux
- Clone PDB to another CDB using RMAN Active Duplicate in Oracle on New Host
- RMAN Incremental Level 1 Differential and Cumulative Database Backup Scripts in Oracle
