Source(RAC db) – Troy (troy1,troy2) @ol6-112-rac1.localdomain, ol6-112-rac2.localdomain
Target (Single instance) – dup5 @oel6-node02.localdomain
Here we are making sure that we don’t have any backup of database before starting this demo
[oracle@ol6-112-rac1 ~]$ env|grep ORA
ORACLE_UNQNAME=TROY
ORACLE_SID=TROY1
ORACLE_HOSTNAME=ol6-112-rac1.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
[oracle@ol6-112-rac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 15 19:25:02 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TROY (DBID=4201044407)
RMAN> list backup summary;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN>
Create a temp pfile for new database dup5
[oracle@oel6-node02 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@oel6-node02 dbs]$ cat initdup5.ora
*.db_name='dup5'
*.db_block_size=8192
*.remote_login_passwordfile='exclusive'
*.db_create_file_dest='/u01/app/oracle/oradata/'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5G
*.compatible='11.2.0.0.0'
Add dup5 entry oratab
[oracle@oel6-node02 dbs]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
track:/u01/app/oracle/product/11.2.0/db_1:Y
dup5:/u01/app/oracle/product/11.2.0/db_1:Y
Start dup5 in nomount stage
[oracle@oel6-node02 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 15 19:30:36 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initdup5.ora
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
SQL>
Add troy and dup5 tns entry in ol6-112-rac1 and oel6-node02 tnsnames.ora
Note that on ol6-112-rac1 you need to add tns entry in tnsnames.ora located in DB home not in grid home. Also TROY entry with scan name was already created during RAC setup so no need to put additional entry for troy in ol6-112-rac1.
[oracle@ol6-112-rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/network/admin
[oracle@ol6-112-rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TROY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TROY.localdomain)
)
)
DUP5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel6-node02.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dup5)
)
)
On oel6-node02 tnsnames.ora is located in db home as there is no grid installed. Here we are pointing to node1 i.e ol6-112-rac1 and not the scan.
[oracle@oel6-node02 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TROY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-rac1.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TROY.localdomain)
)
)
DUP5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel6-node02.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dup5)
)
)
Create dup5 password file on oel6-node02
[oracle@oel6-node02 dbs]$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdup5 password=oracle entries=10Add static entry for dup5 in listener.ora and reload the listener on oel6-node02
[oracle@oel6-node02 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dup5)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = dup5)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel6-node02.localdomain)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@oel6-node02 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-MAY-2020 19:50:38
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel6-node02.localdomain)(PORT=1521)))
The command completed successfully
Verify services (TROY.localdomain,dup5) are listed in listener at respective hosts. Dup5 service will be in unknown state which is fine as database is still in nomount state.
[oracle@oel6-node02 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-MAY-2020 19:51:51
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel6-node02.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 15-MAY-2020 15:58:53
Uptime 0 days 3 hr. 52 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oel6-node02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel6-node02.localdomain)(PORT=1521)))
Services Summary...
Service "dup5" has 2 instance(s).
Instance "dup5", status UNKNOWN, has 1 handler(s) for this service...
Instance "dup5", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@ol6-112-rac1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-MAY-2020 19:52:21
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 14-MAY-2020 17:45:06
Uptime 1 days 2 hr. 7 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0.3/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol6-112-rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.101)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.103)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "TROY.localdomain" has 1 instance(s).
Instance "TROY1", status READY, has 1 handler(s) for this service...
Service "TROYXDB.localdomain" has 1 instance(s).
Instance "TROY1", status READY, has 1 handler(s) for this service...
The command completed successfully
/etc/hosts file
Ensure that /etc/hosts file contain the entry for the hosts ol6-112-rac1 and oel6-node02 on both servers.
[oracle@oel6-node02 admin]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.189.102 oel6-node02.localdomain oel6-node02
192.168.189.103 oel6-node03.localdomain oel6-node03
192.168.189.111 oel6-rac01.localdomain oel6-rac01
#192.168.189.121 ol6-112-rac1 ol6-112-rac1.localdomain
192.168.43.101 ol6-112-rac1 ol6-112-rac1.localdomain
[oracle@ol6-112-rac1 admin]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
# Public
192.168.43.101 ol6-112-rac1.localdomain ol6-112-rac1
192.168.43.102 ol6-112-rac2.localdomain ol6-112-rac2
# Private
192.168.190.201 ol6-112-rac1-priv.localdomain ol6-112-rac1-priv
192.168.190.202 ol6-112-rac2-priv.localdomain ol6-112-rac2-priv
# Virtual
192.168.43.103 ol6-112-rac1-vip.localdomain ol6-112-rac1-vip
192.168.43.104 ol6-112-rac2-vip.localdomain ol6-112-rac2-vip
# SCAN
192.168.43.105 ol6-112-scan.localdomain ol6-112-scan
192.168.43.106 ol6-112-scan.localdomain ol6-112-scan
192.168.43.107 ol6-112-scan.localdomain ol6-112-scan
#for duplicate testing
192.168.189.102 oel6-node02.localdomain oel6-node02
Now run the duplicate
[oracle@oel6-node02 admin]$ . oraenv
ORACLE_SID = [track] ? dup5
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel6-node02 admin]$ rman TARGET sys/oracle@TROY AUXILIARY sys/oracle@dup5
Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 15 19:58:19 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TROY (DBID=4201044407)
connected to auxiliary database: DUP5 (not mounted)
RMAN> DUPLICATE DATABASE TO DUP5 FROM ACTIVE DATABASE;
Starting Duplicate Db at 15-MAY-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
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 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8tl_.ctl'', ''/u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8tv_.ctl'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''TROY'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUP5'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl';
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8vf_.ctl' from
'/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl';
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl'', ''/u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8vf_.ctl'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8tl_.ctl'', ''/u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8tv_.ctl'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''TROY'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUP5'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
Starting backup at 15-MAY-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 instance=TROY1 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_TROY1.f tag=TAG20200515T195904 RECID=4 STAMP=1040500745
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-MAY-20
Starting restore at 15-MAY-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 15-MAY-20
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl'', ''/u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8vf_.ctl'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
database mounted
contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
datafile 6 auxiliary format new
;
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
Starting backup at 15-MAY-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/troy/datafile/system.256.1040416307
output file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_system_0pv09i0s_.dbf tag=TAG20200515T195924
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/troy/datafile/sysaux.257.1040416309
output file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_sysaux_0qv09i1c_.dbf tag=TAG20200515T195924
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/troy/datafile/example.264.1040416383
output file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_example_0rv09i1r_.dbf tag=TAG20200515T195924
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/troy/datafile/undotbs1.258.1040416309
output file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs1_0sv09i22_.dbf tag=TAG20200515T195924
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/troy/datafile/undotbs2.265.1040416497
output file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs2_0tv09i25_.dbf tag=TAG20200515T195924
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/troy/datafile/users.259.1040416309
output file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_users_0uv09i28_.dbf tag=TAG20200515T195924
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-MAY-20
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "+DATA/troy/archivelog/2020_05_15/thread_2_seq_8.279.1040493429" auxiliary format
"/u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_8_%u_.arc" archivelog like
"+DATA/troy/archivelog/2020_05_15/thread_1_seq_17.280.1040500815" auxiliary format
"/u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_1_17_%u_.arc" archivelog like
"+DATA/troy/archivelog/2020_05_15/thread_2_seq_9.281.1040500817" auxiliary format
"/u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_9_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 15-MAY-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=8 RECID=11 STAMP=1040493431
output file name=/u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_8_0vv09i2l_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=17 RECID=12 STAMP=1040500816
output file name=/u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_1_17_10v09i2n_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=9 RECID=13 STAMP=1040500816
output file name=/u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_9_11v09i2o_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 15-MAY-20
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/DUP5/autobackup/2020_05_05/o1_mf_s_1039619521_hc2f7dv2_.bkp
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx0kxl7_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx1so2p_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx186kj_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hc2dyh51_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx10cov_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcwvyz07_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8tv_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx0d42c_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcwz071m_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx2llv7_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx186l3_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx0d433_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx1so3d_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx10co8_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcwvyz10_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx0kxlz_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcwz070l_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_8_0nv09ars_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_7_0lv09arp_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_1_hcx2oxxl_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_9_11v09i2o_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_8_0vv09i2l_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_1_16_0mv09arr_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_1_17_10v09i2n_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_09/o1_mf_1_1_hcdko37r_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_10/o1_mf_1_3_hcgwhq3p_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_10/o1_mf_1_2_hcgq4yod_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_05/o1_mf_1_5_hc2f5oq8_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_05/o1_mf_1_6_hc2f5ps2_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx1so3d_.ctl
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_8_0nv09ars_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_7_0lv09arp_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_1_hcx2oxxl_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_9_11v09i2o_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_8_0vv09i2l_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_1_16_0mv09arr_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_1_17_10v09i2n_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_09/o1_mf_1_1_hcdko37r_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_10/o1_mf_1_3_hcgwhq3p_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_10/o1_mf_1_2_hcgq4yod_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_05/o1_mf_1_5_hc2f5oq8_.arc
File Name: /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_05/o1_mf_1_6_hc2f5ps2_.arc
List of Files Which Where Not Cataloged
=======================================
File Name: /u01/app/oracle/fast_recovery_area/DUP5/autobackup/2020_05_05/o1_mf_s_1039619521_hc2f7dv2_.bkp
RMAN-07518: Reason: Foreign database file DBID: 2987427433 Database Name: DUP5
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx0kxl7_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx1so2p_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx186kj_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hc2dyh51_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx10cov_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcwvyz07_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8tv_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx0d42c_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcwz071m_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx2llv7_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx186l3_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx0d433_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx10co8_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcwvyz10_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx0kxlz_.ctl
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcwz070l_.ctl
RMAN-07517: Reason: The file header is corrupted
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1040500828 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_system_0pv09i0s_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1040500828 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_sysaux_0qv09i1c_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1040500828 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs1_0sv09i22_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1040500828 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_users_0uv09i28_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1040500828 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_example_0rv09i1r_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=1040500828 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs2_0tv09i25_.dbf
contents of Memory Script:
{
set until scn 1528982;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 15-MAY-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
starting media recovery
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_1_17_10v09i2n_.arc
archived log for thread 2 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_9_11v09i2o_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_1_17_10v09i2n_.arc thread=1 sequence=17
archived log file name=/u01/app/oracle/fast_recovery_area/DUP5/archivelog/2020_05_15/o1_mf_2_9_11v09i2o_.arc thread=2 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-MAY-20
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUP5'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DUP5'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP5" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M
DATAFILE
'/u01/app/oracle/oradata/DUP5/datafile/o1_mf_system_0pv09i0s_.dbf'
CHARACTER SET AL32UTF8
sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE 'i2'
GROUP 3 SIZE 50 M ,
GROUP 4 SIZE 50 M
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/DUP5/datafile/o1_mf_sysaux_0qv09i1c_.dbf",
"/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs1_0sv09i22_.dbf",
"/u01/app/oracle/oradata/DUP5/datafile/o1_mf_users_0uv09i28_.dbf",
"/u01/app/oracle/oradata/DUP5/datafile/o1_mf_example_0rv09i1r_.dbf",
"/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs2_0tv09i25_.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/DUP5/datafile/o1_mf_temp_%u_.tmp in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_sysaux_0qv09i1c_.dbf RECID=1 STAMP=1040500847
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs1_0sv09i22_.dbf RECID=2 STAMP=1040500847
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_users_0uv09i28_.dbf RECID=3 STAMP=1040500847
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_example_0rv09i1r_.dbf RECID=4 STAMP=1040500847
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs2_0tv09i25_.dbf RECID=5 STAMP=1040500847
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1040500847 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_sysaux_0qv09i1c_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1040500847 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs1_0sv09i22_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1040500847 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_users_0uv09i28_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1040500847 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_example_0rv09i1r_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=1040500847 file name=/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs2_0tv09i25_.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 15-MAY-20
RMAN>
Create the spfile
SQL> sho parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfiledup5.ora
SQL> !ls -ltr /u01/app/oracle/product/11.2.0/db_1/dbs/spfiledup5.ora
ls: cannot access /u01/app/oracle/product/11.2.0/db_1/dbs/spfiledup5.ora: No such file or directory
it is not yet present. So we need to create it.
Take a note of control file as we need to add it in our pfile.
SQL> select name from V$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl
/u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8vf_.ctl
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !vi /u01/app/oracle/product/11.2.0//db_1/dbs/initdup5.ora
SQL> !vi /u01/app/oracle/product/11.2.0//db_1/dbs/initdup5.ora
SQL> !cat /u01/app/oracle/product/11.2.0//db_1/dbs/initdup5.ora
*.db_name='dup5'
*.db_block_size=8192
*.remote_login_passwordfile='exclusive'
*.db_create_file_dest='/u01/app/oracle/oradata/'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5G
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl', '/u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8vf_.ctl'
SQL> !ls -ltr /u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl
-rw-r-----. 1 oracle oinstall 18825216 May 15 20:04 /u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8vf_.ctl
-rw-r-----. 1 oracle oinstall 18825216 May 15 20:04 /u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8vf_.ctl
SQL> create spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledup5.ora' from pfile='/u01/app/oracle/product/11.2.0//db_1/dbs/initdup5.ora';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
Database mounted.
Database opened.
Verify the database and datafile
SQL> col HOST_NAME for a30
SQL> select name,open_mode,host_name from v$database, v$instance;
NAME OPEN_MODE HOST_NAME
--------- -------------------- ------------------------------
DUP5 READ WRITE oel6-node02.localdomain
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUP5/datafile/o1_mf_system_0pv09i0s_.dbf
/u01/app/oracle/oradata/DUP5/datafile/o1_mf_sysaux_0qv09i1c_.dbf
/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs1_0sv09i22_.dbf
/u01/app/oracle/oradata/DUP5/datafile/o1_mf_users_0uv09i28_.dbf
/u01/app/oracle/oradata/DUP5/datafile/o1_mf_example_0rv09i1r_.dbf
/u01/app/oracle/oradata/DUP5/datafile/o1_mf_undotbs2_0tv09i25_.dbf
6 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUP5/onlinelog/o1_mf_2_hcx9ws4x_.log
/u01/app/oracle/fast_recovery_area/DUP5/onlinelog/o1_mf_2_hcx9ws6q_.log
/u01/app/oracle/oradata/DUP5/onlinelog/o1_mf_1_hcx9wqrn_.log
/u01/app/oracle/fast_recovery_area/DUP5/onlinelog/o1_mf_1_hcx9wqyq_.log
/u01/app/oracle/oradata/DUP5/onlinelog/o1_mf_3_hcx9wnkc_.log
/u01/app/oracle/fast_recovery_area/DUP5/onlinelog/o1_mf_3_hcx9wnlw_.log
/u01/app/oracle/oradata/DUP5/onlinelog/o1_mf_4_hcx9wnn3_.log
/u01/app/oracle/fast_recovery_area/DUP5/onlinelog/o1_mf_4_hcx9wovt_.log
8 rows selected.
SQL> select name from V$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DUP5/controlfile/o1_mf_hcx9s8v7_.ctl
/u01/app/oracle/fast_recovery_area/DUP5/controlfile/o1_mf_hcx9s8vf_.ctl
SQL>
DBATracker 