Duplicating an DATABASE on the same host with asm as storage.
SOURCE DB [ORCL] => AUXILIARY DB [ORCL_DUP]
VERSION: 11.2.0.3
METHOD: BACKUP - SOURCE IMAGE COPY.
#####LAYOUT:
##SOURCE
ORCL [DATAFILE + LOGFILE + CONTROFILE ] reside on +DATA
ORCL [LOGFILE ] reside on +FRA
BACKUP LOCATION reside on +BKP1
##TARGET
ORCL_DUP [DATAFILE + LOGFILE + CONTROFILE ] reside on +DATA
ORCL_DUP [LOGFILE + CONTROFILE ] reside on +FRA
Backup Strategy: Using Image copies
#####STORAGE REVIEW
asmcmd ls -lt
SOURCE DB [ORCL] => AUXILIARY DB [ORCL_DUP]
VERSION: 11.2.0.3
METHOD: BACKUP - SOURCE IMAGE COPY.
#####LAYOUT:
##SOURCE
ORCL [DATAFILE + LOGFILE + CONTROFILE ] reside on +DATA
ORCL [LOGFILE ] reside on +FRA
BACKUP LOCATION reside on +BKP1
##TARGET
ORCL_DUP [DATAFILE + LOGFILE + CONTROFILE ] reside on +DATA
ORCL_DUP [LOGFILE + CONTROFILE ] reside on +FRA
Backup Strategy: Using Image copies
#####STORAGE REVIEW
asmcmd ls -lt
State
Type Rebal Name
MOUNTED
EXTERN N BKP1/
MOUNTED
EXTERN N CLONE_DATA/
MOUNTED
EXTERN N CLONE_LOG/
MOUNTED
EXTERN N DATA/
MOUNTED
EXTERN N FRA/
######SET THE ENVIRONMENT
[oracle@OEL61B ~]$ . oraenv
ORACLE_SID = [ORCL] ? ORCL
cat > get_list.sql
SET LINESIZE 168
SET SERVEROUTPUT OFF
SET PAGES 500
COL MEMBER FOR A60
COL NAME FOR A60
COL FILE_NAME FOR A60
COL VALUE FOR A60
COL GROUP# for 99
SET HEADING OFF
PROMPT DB FILE
SELECT NAME FROM V$DATAFILE;
PROMPT CONTROLFILE
SELECT NAME FROM V$CONTROLFILE;
PROMPT LOGFILE
SELECT GROUP#,MEMBER FROM V$LOGFILE ORDER BY 1;
PROMPT BDUMP ,UDUMP ,CDUMP ,ADUMP
SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME IN ('background_dump_dest','user_dump_dest','core_dump_dest','audit_file_dest');
EXIT
PRESS CTRL+D
####FILE LAYOUT OF THE DATABASE ORCL
sqlplus -s '/as sysdba' @get_list.sql
DB FILE
+DATA/orcl/datafile/system.258.812679447
+DATA/orcl/datafile/sysaux.259.812679447
+DATA/orcl/datafile/undotbs1.270.812679447
+DATA/orcl/datafile/users.257.812679447
CONTROLFILE
+DATA/orcl/controlfile/current.260.804527889
LOGFILE
1 +FRA/orcl/onlinelog/group_1.272.812702221
1 +DATA/orcl/onlinelog/group_1.279.812702217
2 +DATA/orcl/onlinelog/group_2.278.812702225
2 +FRA/orcl/onlinelog/group_2.271.812702229
3 +DATA/orcl/onlinelog/group_3.277.812702233
3 +FRA/orcl/onlinelog/group_3.292.812702237
4 +FRA/orcl/onlinelog/group_4.286.812702249
4 +DATA/orcl/onlinelog/group_4.275.812702243
5 +FRA/orcl/onlinelog/group_5.285.812702269
5 +DATA/orcl/onlinelog/group_5.274.812702257
6 +FRA/orcl/onlinelog/group_6.284.812702287
6 +DATA/orcl/onlinelog/group_6.273.812702279
7 +DATA/orcl/onlinelog/group_7.280.812702295
7 +FRA/orcl/onlinelog/group_7.283.812702303
8 +FRA/orcl/onlinelog/group_8.281.812702321
8 +DATA/orcl/onlinelog/group_8.271.812702313
16 rows selected.
BDUMP ,UDUMP ,CDUMP ,ADUMP
background_dump_dest /u01/app/oracle/diag/rdbms/orcl/ORCL/trace
user_dump_dest /u01/app/oracle/diag/rdbms/orcl/ORCL/trace
core_dump_dest /u01/app/oracle/diag/rdbms/orcl/ORCL/cdump
audit_file_dest /u01/app/oracle/admin/ORCL/adump
####NOW Lets get our TARGET DATABASE ORCLDUP ENV READY
CREATE THE PFILE for the AUXILARY DATABASE (ORCL_DUP)
[oracle@OEL61B ~]$ sqlplus '/as sysdba'
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL_DUP.ora' from spfile;
File created.
SQL> exit
####ORIGINAL PARAMETERS:
[oracle@OEL61B dbs]$ cat initORCL_DUP.ora
ORCL.__db_cache_size=285212672
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=12582912
ORCL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=188743680
ORCL.__sga_target=557842432
ORCL.__shared_io_pool_size=0
ORCL.__shared_pool_size=243269632
ORCL.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/orcl/controlfile/current.260.804527889'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+FRA'
*.db_domain=''
*.db_name='ORCL'
*.db_recovery_file_dest='+BKP1'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.open_cursors=300
*.pga_aggregate_target=185597952
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names=''
*.sga_target=557842432
*.undo_tablespace='UNDOTBS1'
####MODIFIED PARAMETERS :
[oracle@OEL61B dbs]$ cat initORCL_DUP.ora
*.db_name='ORCL_DUP'
*.db_domain=''
*.db_recovery_file_dest='+CLONE_LOG'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL_DUPXDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names=''
*.pga_aggregate_target=185597952
*.sga_target=557842432
*.undo_tablespace='UNDOTBS1'
*.audit_file_dest='/u01/app/oracle/admin/ORCL_DUP/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files=('+CLONE_DATA','+CLONE_LOG')
*.db_block_size=8192
*.db_file_name_convert=('+DATA','+CLONE_DATA')
*.log_file_name_convert=('+DATA','+CLONE_LOG','+FRA','+CLONE_LOG')
###CREATE THE PASWORD FILE
pwd :
/u01/app/oracle/product/11.2.0/db_1/dbs
orapwd file=orapwORCL_DUP entries=5 force=y
Enter password for SYS: ******
##APPEND THE BELOW ENTRY IN /etc/oratab
ORCL_DUP:/u01/app/oracle/product/11.2.0/db_1:N
####CREATE DIRECTORIES
mkdir -p /u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/trace \
/u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/trace \
/u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/cdump\
/u01/app/oracle/admin/ORCL_DUP/adump \
####BACKUP UP our PRIMARY DATABASE - METHOD IMAGE COPY:
rman target / catalog rcowner/rcowner@rcat
RMAN> backup as copy database tag 'ORCL_DUP' format '+BKP1/%d/datafile_%f.dbf';
Starting backup at 20-APR-13
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=71 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=10 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=138 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.258.812679447
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.259.812679447
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.270.812679447
channel ORA_DISK_4: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.257.812679447
output file name=+BKP1/orcl/datafile_4.dbf tag=ORCL_DUP RECID=30 STAMP=813200447
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:55
output file name=+BKP1/orcl/datafile_3.dbf tag=ORCL_DUP RECID=31 STAMP=813200462
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:15
output file name=+BKP1/orcl/datafile_2.dbf tag=ORCL_DUP RECID=32 STAMP=813200581
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:03:05
output file name=+BKP1/orcl/datafile_1.dbf tag=ORCL_DUP RECID=33 STAMP=813200593
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:15
Finished backup at 20-APR-13
Starting Control File and SPFILE Autobackup at 20-APR-13
piece handle=+BKP1/orcl/autobackup/2013_04_20/s_813200595.301.813200597 comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-13
RMAN> sql 'alter system archive log current';
sql statement: alter system archive log current
RMAN>
####STARTUP THE INSTANCE IN NOMOUNT
[oracle@OEL61B dbs]$ sqlplus '/as sysdba'
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL_DUP.ora';
ORACLE instance started.
Total System Global Area 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 171968512 bytes
Database Buffers 373293056 bytes
Redo Buffers 7847936 bytes
####ADD THE BELOW TNS ENTRY to the tnsnames.ora file under the ORACLE USER
ORCL_DUP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_DUP)
)
)
###MODIFY THE LISTENER ENTRY TO THE BELOW under the GRID USER for STATIC REGISTRATION.
###LOGIN as GRID USER
. oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/grid
$ netmgr
b) The Oracle Net Manager opens a window. Click the “+” icon, right before the
word “Local,” then click the “+” icon, right before the word “Listeners” to expand
the nodes in the navigation tree until you see the listener, called “LISTENER.”
c) First select LISTENER in the left part of the window then, select Database
Services from the drop-down in the right part of the window.
d) Click the Add Database button
e) Enter or confirm the following values:
Global Database Name: ORCL_DUP
Oracle Home Directory: /u01/app/oracle/product/11.2.0/db_1
SID: ORCL_DUP
f) Click the Add Database button again.
g) Enter the following values:
Global Database Name: ORCL
Oracle Home Directory: /u01/app/oracle/acfsmounts/acfs_db1/
SID: ORCL
h) From the Oracle Net Manager menu bar, select File > Save Network
Configuration, then File > Exit.
cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_DUP)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = ORCL_DUP)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
####CHECK THE STATUS OF THE DATABASE IN THE LISTENER
[grid@OEL61B admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-APR-2013 01:28:26
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 20-APR-2013 00:28:57
Uptime 0 days 0 hr. 59 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/OEL61B/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL61B)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL_DUP" has 1 instance(s).
Instance "ORCL_DUP", status BLOCKED, has 1 handler(s) for this service... #THIS WILL BE BLOCKED INITIALLY
Service "rcat" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
Service "rcatXDB" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
The command completed successfully
###STOP AND START THE LISTENER
[grid@OEL61B admin]$ crsctl stop res ora.LISTENER.lsnr -f
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'oel61b'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'oel61b' succeeded
[grid@OEL61B admin]$ crsctl start res ora.LISTENER.lsnr
CRS-2672: Attempting to start 'ora.LISTENER.lsnr' on 'oel61b'
CRS-2676: Start of 'ora.LISTENER.lsnr' on 'oel61b' succeeded
###CHECK FOR THE LISTENER STATUS NOW
[grid@OEL61B admin]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-APR-2013 01:29:21
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 20-APR-2013 01:29:06
Uptime 0 days 0 hr. 0 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/OEL61B/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL61B)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL_DUP" has 1 instance(s).
Instance "ORCL_DUP", status UNKNOWN, has 1 handler(s) for this service... ##STATUS HAS BECOME UNKNOWN
The command completed successfully
###LETS START THE DUPLICATE COMMAND:
Lets do a Quick TNS PING:
[oracle@OEL61B dbs]$ tnsping ORCL
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 20-APR-2013 01:42:07
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
OK (0 msec)
[oracle@OEL61B dbs]$ tnsping ORCL_DUP
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL_DUP)))
OK (0 msec)
rman target sys/oracle@ORCL
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Apr 20 01:32:17 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1332308625)
RMAN> connect auxiliary sys/oracle@ORCL_DUP
connected to auxiliary database: ORCL_DUP (not mounted)
RMAN> run
{
set newname for datafile 1 to '+CLONE_DATA/ORCL_DUP/SYSTEM.dbf';
set newname for datafile 2 to '+CLONE_DATA/ORCL_DUP/SYSAUX.dbf';
set newname for datafile 3 to '+CLONE_DATA/ORCL_DUP/UNDOTBS1.dbf';
set newname for datafile 4 to '+CLONE_DATA/ORCL_DUP/USERS.dbf';
duplicate target database to 'ORCL_DUP' pfile='initORCL_DUP.ora';
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 20-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=69 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=132 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=192 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 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 176162816 bytes
Database Buffers 369098752 bytes
Redo Buffers 7847936 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+CLONE_DATA/orcl_dup/controlfile/current.256.813202701'', ''+CLONE_LOG/orcl_dup/controlfile/current.256.813202703'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ORCL_DUP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+CLONE_DATA/orcl_dup/controlfile/current.256.813202701'', ''+CLONE_LOG/orcl_dup/controlfile/current.256.813202703'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ORCL_DUP'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 176162816 bytes
Database Buffers 369098752 bytes
Redo Buffers 7847936 bytes
Starting restore at 20-APR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=69 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=131 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=192 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=6 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +BKP1/orcl/autobackup/2013_04_20/s_813200595.301.813200597
channel ORA_AUX_DISK_1: piece handle=+BKP1/orcl/autobackup/2013_04_20/s_813200595.301.813200597 tag=TAG20130420T010315
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+CLONE_DATA/orcl_dup/controlfile/current.256.813202701
output file name=+CLONE_LOG/orcl_dup/controlfile/current.256.813202703
Finished restore at 20-APR-13
database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set until scn 1973203;
set newname for datafile 1 to
"+CLONE_DATA/ORCL_DUP/SYSTEM.dbf";
set newname for datafile 2 to
"+CLONE_DATA/ORCL_DUP/SYSAUX.dbf";
set newname for datafile 3 to
"+CLONE_DATA/ORCL_DUP/UNDOTBS1.dbf";
set newname for datafile 4 to
"+CLONE_DATA/ORCL_DUP/USERS.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-APR-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy RECID=33 STAMP=813200593 file name=+BKP1/orcl/datafile_1.dbf
destination for restore of datafile 00001: +CLONE_DATA/ORCL_DUP/SYSTEM.dbf
channel ORA_AUX_DISK_2: restoring datafile 00002
input datafile copy RECID=32 STAMP=813200581 file name=+BKP1/orcl/datafile_2.dbf
destination for restore of datafile 00002: +CLONE_DATA/ORCL_DUP/SYSAUX.dbf
channel ORA_AUX_DISK_3: restoring datafile 00003
input datafile copy RECID=31 STAMP=813200462 file name=+BKP1/orcl/datafile_3.dbf
destination for restore of datafile 00003: +CLONE_DATA/ORCL_DUP/UNDOTBS1.dbf
channel ORA_AUX_DISK_4: restoring datafile 00004
input datafile copy RECID=30 STAMP=813200447 file name=+BKP1/orcl/datafile_4.dbf
destination for restore of datafile 00004: +CLONE_DATA/ORCL_DUP/USERS.dbf
channel ORA_AUX_DISK_4: copied datafile copy of datafile 00004
output file name=+CLONE_DATA/orcl_dup/users.dbf
channel ORA_AUX_DISK_3: copied datafile copy of datafile 00003
output file name=+CLONE_DATA/orcl_dup/undotbs1.dbf
channel ORA_AUX_DISK_2: copied datafile copy of datafile 00002
output file name=+CLONE_DATA/orcl_dup/sysaux.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001
output file name=+CLONE_DATA/orcl_dup/system.dbf
Finished restore at 20-APR-13
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=34 STAMP=813202916 file name=+CLONE_DATA/orcl_dup/system.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=35 STAMP=813202916 file name=+CLONE_DATA/orcl_dup/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=36 STAMP=813202916 file name=+CLONE_DATA/orcl_dup/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=37 STAMP=813202916 file name=+CLONE_DATA/orcl_dup/users.dbf
contents of Memory Script:
{
set until scn 1973203;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-APR-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file +BKP1/orcl/archivelog/2013_04_20/thread_1_seq_4.302.813200687
archived log file name=+BKP1/orcl/archivelog/2013_04_20/thread_1_seq_4.302.813200687 thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-APR-13
Oracle instance started
Total System Global Area 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 176162816 bytes
Database Buffers 369098752 bytes
Redo Buffers 7847936 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCL_DUP'' 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;
sql clone "create pfile= ''ORCL_DUP.ora'' from spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''ORCL_DUP'' 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 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 180357120 bytes
Database Buffers 364904448 bytes
Redo Buffers 7847936 bytes
sql statement: create pfile= ''ORCL_DUP.ora'' from spfile
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL_DUP" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+clone_log', '+clone_log' ) SIZE 50 M REUSE,
GROUP 2 ( '+clone_log', '+clone_log' ) SIZE 50 M REUSE,
GROUP 3 ( '+clone_log', '+clone_log' ) SIZE 50 M REUSE,
GROUP 4 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE,
GROUP 5 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE,
GROUP 6 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE,
GROUP 7 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE,
GROUP 8 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE
DATAFILE
'+CLONE_DATA/orcl_dup/system.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"+clone_data";
switch clone tempfile all;
catalog clone datafilecopy "+CLONE_DATA/orcl_dup/sysaux.dbf",
"+CLONE_DATA/orcl_dup/undotbs1.dbf",
"+CLONE_DATA/orcl_dup/users.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +clone_data in control file
cataloged datafile copy
datafile copy file name=+CLONE_DATA/orcl_dup/sysaux.dbf RECID=1 STAMP=813202941
cataloged datafile copy
datafile copy file name=+CLONE_DATA/orcl_dup/undotbs1.dbf RECID=2 STAMP=813202941
cataloged datafile copy
datafile copy file name=+CLONE_DATA/orcl_dup/users.dbf RECID=3 STAMP=813202941
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=813202941 file name=+CLONE_DATA/orcl_dup/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=813202941 file name=+CLONE_DATA/orcl_dup/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=813202941 file name=+CLONE_DATA/orcl_dup/users.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-APR-13
RMAN> exit
Recovery Manager complete.
#####IN CASE there is an entry ending with #Deprecate parameter in initORCL_DUP.ora remove it
######STARTUP THE DATABASE IN NOMOUNT STATE USING THE initORCL_DUP.ora
######CREATE THE SPFILE IN THE ASM
SQL> create spfile='+CLONE_DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL_DUP.ora';
File created.
@@@@@@FROM GRID , LOGIN TO ASM and retrieve the PATH of the SPFILE
@@@@@@ALSO CREATE THE ALIAS FOR EASE OF MANAGEMENT
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE APR 20 01:00:00 Y spfile.262.813203635
ASMCMD> cd +CLONE_DATA/ORCL_DUP/
ASMCMD> mkalias +CLONE_DATA/ORCL_DUP/PARAMETERFILE/spfile.262.813203635 spfileORCL_DUP.ora
######LOG BACK to ORACLE USER
SHUTDOWN THE INSTANCE.
cat > initORCL_DUP.ora
spfile='+CLONE_DATA/ORCL_DUP/spfileORCL_DUP.ora'
PRESS CTRL + D
######START THE DATABASE INSTANCE
[oracle@OEL61B dbs]$ sqlplus '/as sysdba'
SQL> startup;
ORACLE instance started.
Total System Global Area 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 180357120 bytes
Database Buffers 364904448 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------ ----------- ---------------------------------------------
spfile string +CLONE_DATA/orcl_dup/spfileorcl_dup.ora
####SET YOUR DATAFILE CREATION DEST
SQL> alter system set db_create_file_dest='+CLONE_DATA' SCOPE=BOTH;
System altered.
SQL> @/home/oracle/get_list.sql
DB FILE
+CLONE_DATA/orcl_dup/system.dbf
+CLONE_DATA/orcl_dup/sysaux.dbf
+CLONE_DATA/orcl_dup/undotbs1.dbf
+CLONE_DATA/orcl_dup/users.dbf
CONTROLFILE
+CLONE_DATA/orcl_dup/controlfile/current.256.813202701
+CLONE_LOG/orcl_dup/controlfile/current.256.813202703
LOGFILE
1 +CLONE_LOG/orcl_dup/onlinelog/group_1.257.813202941
2 +CLONE_LOG/orcl_dup/onlinelog/group_2.258.813202945
3 +CLONE_LOG/orcl_dup/onlinelog/group_3.259.813202949
4 +CLONE_LOG/orcl_dup/onlinelog/group_4.260.813202953
5 +CLONE_LOG/orcl_dup/onlinelog/group_5.261.813202963
6 +CLONE_LOG/orcl_dup/onlinelog/group_6.262.813202971
7 +CLONE_LOG/orcl_dup/onlinelog/group_7.263.813202979
8 +CLONE_LOG/orcl_dup/onlinelog/group_8.264.813202985
8 rows selected.
BDUMP ,UDUMP ,CDUMP ,ADUMP
background_dump_dest /u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/trace
user_dump_dest /u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/trace
core_dump_dest /u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/cdump
audit_file_dest /u01/app/oracle/admin/ORCL_DUP/adump
---END---
REF:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#i1008564
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm
######SET THE ENVIRONMENT
[oracle@OEL61B ~]$ . oraenv
ORACLE_SID = [ORCL] ? ORCL
cat > get_list.sql
SET LINESIZE 168
SET SERVEROUTPUT OFF
SET PAGES 500
COL MEMBER FOR A60
COL NAME FOR A60
COL FILE_NAME FOR A60
COL VALUE FOR A60
COL GROUP# for 99
SET HEADING OFF
PROMPT DB FILE
SELECT NAME FROM V$DATAFILE;
PROMPT CONTROLFILE
SELECT NAME FROM V$CONTROLFILE;
PROMPT LOGFILE
SELECT GROUP#,MEMBER FROM V$LOGFILE ORDER BY 1;
PROMPT BDUMP ,UDUMP ,CDUMP ,ADUMP
SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME IN ('background_dump_dest','user_dump_dest','core_dump_dest','audit_file_dest');
EXIT
PRESS CTRL+D
####FILE LAYOUT OF THE DATABASE ORCL
sqlplus -s '/as sysdba' @get_list.sql
DB FILE
+DATA/orcl/datafile/system.258.812679447
+DATA/orcl/datafile/sysaux.259.812679447
+DATA/orcl/datafile/undotbs1.270.812679447
+DATA/orcl/datafile/users.257.812679447
CONTROLFILE
+DATA/orcl/controlfile/current.260.804527889
LOGFILE
1 +FRA/orcl/onlinelog/group_1.272.812702221
1 +DATA/orcl/onlinelog/group_1.279.812702217
2 +DATA/orcl/onlinelog/group_2.278.812702225
2 +FRA/orcl/onlinelog/group_2.271.812702229
3 +DATA/orcl/onlinelog/group_3.277.812702233
3 +FRA/orcl/onlinelog/group_3.292.812702237
4 +FRA/orcl/onlinelog/group_4.286.812702249
4 +DATA/orcl/onlinelog/group_4.275.812702243
5 +FRA/orcl/onlinelog/group_5.285.812702269
5 +DATA/orcl/onlinelog/group_5.274.812702257
6 +FRA/orcl/onlinelog/group_6.284.812702287
6 +DATA/orcl/onlinelog/group_6.273.812702279
7 +DATA/orcl/onlinelog/group_7.280.812702295
7 +FRA/orcl/onlinelog/group_7.283.812702303
8 +FRA/orcl/onlinelog/group_8.281.812702321
8 +DATA/orcl/onlinelog/group_8.271.812702313
16 rows selected.
BDUMP ,UDUMP ,CDUMP ,ADUMP
background_dump_dest /u01/app/oracle/diag/rdbms/orcl/ORCL/trace
user_dump_dest /u01/app/oracle/diag/rdbms/orcl/ORCL/trace
core_dump_dest /u01/app/oracle/diag/rdbms/orcl/ORCL/cdump
audit_file_dest /u01/app/oracle/admin/ORCL/adump
####NOW Lets get our TARGET DATABASE ORCLDUP ENV READY
CREATE THE PFILE for the AUXILARY DATABASE (ORCL_DUP)
[oracle@OEL61B ~]$ sqlplus '/as sysdba'
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL_DUP.ora' from spfile;
File created.
SQL> exit
####ORIGINAL PARAMETERS:
[oracle@OEL61B dbs]$ cat initORCL_DUP.ora
ORCL.__db_cache_size=285212672
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=12582912
ORCL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=188743680
ORCL.__sga_target=557842432
ORCL.__shared_io_pool_size=0
ORCL.__shared_pool_size=243269632
ORCL.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/orcl/controlfile/current.260.804527889'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+FRA'
*.db_domain=''
*.db_name='ORCL'
*.db_recovery_file_dest='+BKP1'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.open_cursors=300
*.pga_aggregate_target=185597952
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names=''
*.sga_target=557842432
*.undo_tablespace='UNDOTBS1'
####MODIFIED PARAMETERS :
[oracle@OEL61B dbs]$ cat initORCL_DUP.ora
*.db_name='ORCL_DUP'
*.db_domain=''
*.db_recovery_file_dest='+CLONE_LOG'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL_DUPXDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names=''
*.pga_aggregate_target=185597952
*.sga_target=557842432
*.undo_tablespace='UNDOTBS1'
*.audit_file_dest='/u01/app/oracle/admin/ORCL_DUP/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files=('+CLONE_DATA','+CLONE_LOG')
*.db_block_size=8192
*.db_file_name_convert=('+DATA','+CLONE_DATA')
*.log_file_name_convert=('+DATA','+CLONE_LOG','+FRA','+CLONE_LOG')
###CREATE THE PASWORD FILE
pwd :
/u01/app/oracle/product/11.2.0/db_1/dbs
orapwd file=orapwORCL_DUP entries=5 force=y
Enter password for SYS: ******
##APPEND THE BELOW ENTRY IN /etc/oratab
ORCL_DUP:/u01/app/oracle/product/11.2.0/db_1:N
####CREATE DIRECTORIES
mkdir -p /u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/trace \
/u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/trace \
/u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/cdump\
/u01/app/oracle/admin/ORCL_DUP/adump \
####BACKUP UP our PRIMARY DATABASE - METHOD IMAGE COPY:
rman target / catalog rcowner/rcowner@rcat
RMAN> backup as copy database tag 'ORCL_DUP' format '+BKP1/%d/datafile_%f.dbf';
Starting backup at 20-APR-13
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=71 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=10 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=138 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.258.812679447
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.259.812679447
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.270.812679447
channel ORA_DISK_4: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.257.812679447
output file name=+BKP1/orcl/datafile_4.dbf tag=ORCL_DUP RECID=30 STAMP=813200447
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:55
output file name=+BKP1/orcl/datafile_3.dbf tag=ORCL_DUP RECID=31 STAMP=813200462
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:15
output file name=+BKP1/orcl/datafile_2.dbf tag=ORCL_DUP RECID=32 STAMP=813200581
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:03:05
output file name=+BKP1/orcl/datafile_1.dbf tag=ORCL_DUP RECID=33 STAMP=813200593
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:15
Finished backup at 20-APR-13
Starting Control File and SPFILE Autobackup at 20-APR-13
piece handle=+BKP1/orcl/autobackup/2013_04_20/s_813200595.301.813200597 comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-13
RMAN> sql 'alter system archive log current';
sql statement: alter system archive log current
RMAN>
####STARTUP THE INSTANCE IN NOMOUNT
[oracle@OEL61B dbs]$ sqlplus '/as sysdba'
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL_DUP.ora';
ORACLE instance started.
Total System Global Area 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 171968512 bytes
Database Buffers 373293056 bytes
Redo Buffers 7847936 bytes
####ADD THE BELOW TNS ENTRY to the tnsnames.ora file under the ORACLE USER
ORCL_DUP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_DUP)
)
)
###MODIFY THE LISTENER ENTRY TO THE BELOW under the GRID USER for STATIC REGISTRATION.
###LOGIN as GRID USER
. oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/grid
$ netmgr
b) The Oracle Net Manager opens a window. Click the “+” icon, right before the
word “Local,” then click the “+” icon, right before the word “Listeners” to expand
the nodes in the navigation tree until you see the listener, called “LISTENER.”
c) First select LISTENER in the left part of the window then, select Database
Services from the drop-down in the right part of the window.
d) Click the Add Database button
e) Enter or confirm the following values:
Global Database Name: ORCL_DUP
Oracle Home Directory: /u01/app/oracle/product/11.2.0/db_1
SID: ORCL_DUP
f) Click the Add Database button again.
g) Enter the following values:
Global Database Name: ORCL
Oracle Home Directory: /u01/app/oracle/acfsmounts/acfs_db1/
SID: ORCL
h) From the Oracle Net Manager menu bar, select File > Save Network
Configuration, then File > Exit.
cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_DUP)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = ORCL_DUP)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
####CHECK THE STATUS OF THE DATABASE IN THE LISTENER
[grid@OEL61B admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-APR-2013 01:28:26
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 20-APR-2013 00:28:57
Uptime 0 days 0 hr. 59 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/OEL61B/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL61B)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL_DUP" has 1 instance(s).
Instance "ORCL_DUP", status BLOCKED, has 1 handler(s) for this service... #THIS WILL BE BLOCKED INITIALLY
Service "rcat" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
Service "rcatXDB" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
The command completed successfully
###STOP AND START THE LISTENER
[grid@OEL61B admin]$ crsctl stop res ora.LISTENER.lsnr -f
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'oel61b'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'oel61b' succeeded
[grid@OEL61B admin]$ crsctl start res ora.LISTENER.lsnr
CRS-2672: Attempting to start 'ora.LISTENER.lsnr' on 'oel61b'
CRS-2676: Start of 'ora.LISTENER.lsnr' on 'oel61b' succeeded
###CHECK FOR THE LISTENER STATUS NOW
[grid@OEL61B admin]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-APR-2013 01:29:21
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 20-APR-2013 01:29:06
Uptime 0 days 0 hr. 0 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/OEL61B/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL61B)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL_DUP" has 1 instance(s).
Instance "ORCL_DUP", status UNKNOWN, has 1 handler(s) for this service... ##STATUS HAS BECOME UNKNOWN
The command completed successfully
###LETS START THE DUPLICATE COMMAND:
Lets do a Quick TNS PING:
[oracle@OEL61B dbs]$ tnsping ORCL
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 20-APR-2013 01:42:07
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
OK (0 msec)
[oracle@OEL61B dbs]$ tnsping ORCL_DUP
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL_DUP)))
OK (0 msec)
rman target sys/oracle@ORCL
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Apr 20 01:32:17 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1332308625)
RMAN> connect auxiliary sys/oracle@ORCL_DUP
connected to auxiliary database: ORCL_DUP (not mounted)
RMAN> run
{
set newname for datafile 1 to '+CLONE_DATA/ORCL_DUP/SYSTEM.dbf';
set newname for datafile 2 to '+CLONE_DATA/ORCL_DUP/SYSAUX.dbf';
set newname for datafile 3 to '+CLONE_DATA/ORCL_DUP/UNDOTBS1.dbf';
set newname for datafile 4 to '+CLONE_DATA/ORCL_DUP/USERS.dbf';
duplicate target database to 'ORCL_DUP' pfile='initORCL_DUP.ora';
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 20-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=69 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=132 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=192 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 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 176162816 bytes
Database Buffers 369098752 bytes
Redo Buffers 7847936 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+CLONE_DATA/orcl_dup/controlfile/current.256.813202701'', ''+CLONE_LOG/orcl_dup/controlfile/current.256.813202703'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ORCL_DUP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+CLONE_DATA/orcl_dup/controlfile/current.256.813202701'', ''+CLONE_LOG/orcl_dup/controlfile/current.256.813202703'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ORCL_DUP'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 176162816 bytes
Database Buffers 369098752 bytes
Redo Buffers 7847936 bytes
Starting restore at 20-APR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=69 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=131 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=192 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=6 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +BKP1/orcl/autobackup/2013_04_20/s_813200595.301.813200597
channel ORA_AUX_DISK_1: piece handle=+BKP1/orcl/autobackup/2013_04_20/s_813200595.301.813200597 tag=TAG20130420T010315
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+CLONE_DATA/orcl_dup/controlfile/current.256.813202701
output file name=+CLONE_LOG/orcl_dup/controlfile/current.256.813202703
Finished restore at 20-APR-13
database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set until scn 1973203;
set newname for datafile 1 to
"+CLONE_DATA/ORCL_DUP/SYSTEM.dbf";
set newname for datafile 2 to
"+CLONE_DATA/ORCL_DUP/SYSAUX.dbf";
set newname for datafile 3 to
"+CLONE_DATA/ORCL_DUP/UNDOTBS1.dbf";
set newname for datafile 4 to
"+CLONE_DATA/ORCL_DUP/USERS.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-APR-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy RECID=33 STAMP=813200593 file name=+BKP1/orcl/datafile_1.dbf
destination for restore of datafile 00001: +CLONE_DATA/ORCL_DUP/SYSTEM.dbf
channel ORA_AUX_DISK_2: restoring datafile 00002
input datafile copy RECID=32 STAMP=813200581 file name=+BKP1/orcl/datafile_2.dbf
destination for restore of datafile 00002: +CLONE_DATA/ORCL_DUP/SYSAUX.dbf
channel ORA_AUX_DISK_3: restoring datafile 00003
input datafile copy RECID=31 STAMP=813200462 file name=+BKP1/orcl/datafile_3.dbf
destination for restore of datafile 00003: +CLONE_DATA/ORCL_DUP/UNDOTBS1.dbf
channel ORA_AUX_DISK_4: restoring datafile 00004
input datafile copy RECID=30 STAMP=813200447 file name=+BKP1/orcl/datafile_4.dbf
destination for restore of datafile 00004: +CLONE_DATA/ORCL_DUP/USERS.dbf
channel ORA_AUX_DISK_4: copied datafile copy of datafile 00004
output file name=+CLONE_DATA/orcl_dup/users.dbf
channel ORA_AUX_DISK_3: copied datafile copy of datafile 00003
output file name=+CLONE_DATA/orcl_dup/undotbs1.dbf
channel ORA_AUX_DISK_2: copied datafile copy of datafile 00002
output file name=+CLONE_DATA/orcl_dup/sysaux.dbf
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001
output file name=+CLONE_DATA/orcl_dup/system.dbf
Finished restore at 20-APR-13
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=34 STAMP=813202916 file name=+CLONE_DATA/orcl_dup/system.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=35 STAMP=813202916 file name=+CLONE_DATA/orcl_dup/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=36 STAMP=813202916 file name=+CLONE_DATA/orcl_dup/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=37 STAMP=813202916 file name=+CLONE_DATA/orcl_dup/users.dbf
contents of Memory Script:
{
set until scn 1973203;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-APR-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file +BKP1/orcl/archivelog/2013_04_20/thread_1_seq_4.302.813200687
archived log file name=+BKP1/orcl/archivelog/2013_04_20/thread_1_seq_4.302.813200687 thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-APR-13
Oracle instance started
Total System Global Area 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 176162816 bytes
Database Buffers 369098752 bytes
Redo Buffers 7847936 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCL_DUP'' 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;
sql clone "create pfile= ''ORCL_DUP.ora'' from spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''ORCL_DUP'' 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 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 180357120 bytes
Database Buffers 364904448 bytes
Redo Buffers 7847936 bytes
sql statement: create pfile= ''ORCL_DUP.ora'' from spfile
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL_DUP" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+clone_log', '+clone_log' ) SIZE 50 M REUSE,
GROUP 2 ( '+clone_log', '+clone_log' ) SIZE 50 M REUSE,
GROUP 3 ( '+clone_log', '+clone_log' ) SIZE 50 M REUSE,
GROUP 4 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE,
GROUP 5 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE,
GROUP 6 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE,
GROUP 7 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE,
GROUP 8 ( '+clone_log', '+clone_log' ) SIZE 100 M REUSE
DATAFILE
'+CLONE_DATA/orcl_dup/system.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"+clone_data";
switch clone tempfile all;
catalog clone datafilecopy "+CLONE_DATA/orcl_dup/sysaux.dbf",
"+CLONE_DATA/orcl_dup/undotbs1.dbf",
"+CLONE_DATA/orcl_dup/users.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +clone_data in control file
cataloged datafile copy
datafile copy file name=+CLONE_DATA/orcl_dup/sysaux.dbf RECID=1 STAMP=813202941
cataloged datafile copy
datafile copy file name=+CLONE_DATA/orcl_dup/undotbs1.dbf RECID=2 STAMP=813202941
cataloged datafile copy
datafile copy file name=+CLONE_DATA/orcl_dup/users.dbf RECID=3 STAMP=813202941
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=813202941 file name=+CLONE_DATA/orcl_dup/sysaux.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=813202941 file name=+CLONE_DATA/orcl_dup/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=813202941 file name=+CLONE_DATA/orcl_dup/users.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-APR-13
RMAN> exit
Recovery Manager complete.
#####IN CASE there is an entry ending with #Deprecate parameter in initORCL_DUP.ora remove it
######STARTUP THE DATABASE IN NOMOUNT STATE USING THE initORCL_DUP.ora
######CREATE THE SPFILE IN THE ASM
SQL> create spfile='+CLONE_DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL_DUP.ora';
File created.
@@@@@@FROM GRID , LOGIN TO ASM and retrieve the PATH of the SPFILE
@@@@@@ALSO CREATE THE ALIAS FOR EASE OF MANAGEMENT
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE APR 20 01:00:00 Y spfile.262.813203635
ASMCMD> cd +CLONE_DATA/ORCL_DUP/
ASMCMD> mkalias +CLONE_DATA/ORCL_DUP/PARAMETERFILE/spfile.262.813203635 spfileORCL_DUP.ora
######LOG BACK to ORACLE USER
SHUTDOWN THE INSTANCE.
cat > initORCL_DUP.ora
spfile='+CLONE_DATA/ORCL_DUP/spfileORCL_DUP.ora'
PRESS CTRL + D
######START THE DATABASE INSTANCE
[oracle@OEL61B dbs]$ sqlplus '/as sysdba'
SQL> startup;
ORACLE instance started.
Total System Global Area 555339776 bytes
Fixed Size 2230272 bytes
Variable Size 180357120 bytes
Database Buffers 364904448 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------ ----------- ---------------------------------------------
spfile string +CLONE_DATA/orcl_dup/spfileorcl_dup.ora
####SET YOUR DATAFILE CREATION DEST
SQL> alter system set db_create_file_dest='+CLONE_DATA' SCOPE=BOTH;
System altered.
SQL> @/home/oracle/get_list.sql
DB FILE
+CLONE_DATA/orcl_dup/system.dbf
+CLONE_DATA/orcl_dup/sysaux.dbf
+CLONE_DATA/orcl_dup/undotbs1.dbf
+CLONE_DATA/orcl_dup/users.dbf
CONTROLFILE
+CLONE_DATA/orcl_dup/controlfile/current.256.813202701
+CLONE_LOG/orcl_dup/controlfile/current.256.813202703
LOGFILE
1 +CLONE_LOG/orcl_dup/onlinelog/group_1.257.813202941
2 +CLONE_LOG/orcl_dup/onlinelog/group_2.258.813202945
3 +CLONE_LOG/orcl_dup/onlinelog/group_3.259.813202949
4 +CLONE_LOG/orcl_dup/onlinelog/group_4.260.813202953
5 +CLONE_LOG/orcl_dup/onlinelog/group_5.261.813202963
6 +CLONE_LOG/orcl_dup/onlinelog/group_6.262.813202971
7 +CLONE_LOG/orcl_dup/onlinelog/group_7.263.813202979
8 +CLONE_LOG/orcl_dup/onlinelog/group_8.264.813202985
8 rows selected.
BDUMP ,UDUMP ,CDUMP ,ADUMP
background_dump_dest /u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/trace
user_dump_dest /u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/trace
core_dump_dest /u01/app/oracle/diag/rdbms/orcl_dup/ORCL_DUP/cdump
audit_file_dest /u01/app/oracle/admin/ORCL_DUP/adump
---END---
REF:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#i1008564
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm
0 comments:
Post a Comment