RMAN ACTIVE DUPLICATION ASM TO ASM
AIM: RMAN Cloning using ACTIVE duplicate command from BR8PROD to BR8DEV without downtime.
0. Overview
1. Environment
2. Create Pfile and Edit (RAC1)
3. Copy password file/initBR8DEV.ora to auxiliary side
4. Create required directories (On the auxiliary host)
5. Add oratab entry (On the auxiliary host)
6. Add both TNS Entries on both sides (***Mandatory)
7. Static Registration on LISTENER (On the auxiliary host)
8. Start the auxilary instance (On the auxiliary host)
9. TNS Verification
10. RMAN Connectvity Verification (On the auxiliary host)
11. Run RMAN Duplicate (On the auxiliary host)
12. Verify
13. Create the spfile in ASM disk
It is a new feature in Oracle 11g where cloning is done from one database to another database without any outage or downtime of the source (Primary) database. Earlier we use to clone the database using cold back or rman backup. But RMAN Active duplication feature allows a database to be duplicated directly from its live source database instead of using its backup. RMAN directly reads the data from the database using source (Primary) database CONTROLFILE.
Source database:
Database name: BR8PROD Archivelog Mode: ON RAC: No (Oracle Restart) Version: 11.2.0.4 Hostname: rac1.rajasekhar.com Filesystem: ASM Diskgroup: +DATA1,FRA GI_HOME: /u01/app/11.2.0/grid ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1 SQL> select name, open_mode, dbid, created from v$database; NAME OPEN_MODE DBID CREATED --------- -------------------- ---------- -------------------- BR8PROD READ WRITE 4192214970 23-NOV-2016 01:43:22 SQL> select instance_name,host_name from v$instance; INSTANCE_NAME HOST_NAME ---------------- ------------------------------------ BR8PROD rac1.rajasekhar.com SQL> select name from v$datafile union select name from v$controlfile union select member from v$logfile union select name from v$tempfile / 2 3 4 5 6 7 8 NAME -------------------------------------------------- +DATA1/br8prod/controlfile/current.260.928633403 +DATA1/br8prod/datafile/example.265.928633413 +DATA1/br8prod/datafile/sysaux.257.928633345 +DATA1/br8prod/datafile/system.256.928633345 +DATA1/br8prod/datafile/undotbs1.258.928633345 +DATA1/br8prod/datafile/users.259.928633345 +DATA1/br8prod/onlinelog/group_1.261.928633405 +DATA1/br8prod/onlinelog/group_2.262.928633405 +DATA1/br8prod/onlinelog/group_3.263.928633407 +DATA1/br8prod/tempfile/temp.264.928633411 10 rows selected. SQL> select * from global_name; GLOBAL_NAME -------------------------------- BR8PROD.RAJASEKHAR.COM SQL>
Target database (On the auxiliary host):
Database name: BR8DEV Archivelog Mode: ON RAC: No (Oracle Restart) Version: 11.2.0.4 Hostname: rac2.rajasekhar.com Filesystem: ASM Diskgroup: +DATA,ARCH GI_HOME: /u01/app/11.2.0/grid ORACLE_HOME: /u01/app/oracle/product/11.2.0.4/db_1
2. Create Pfile and Edit (RAC1)
SQL> show parameter pfile NAME TYPE VALUE --------- ----------- ------------------------------ spfile string +DATA1/br8prod/spfilebr8prod.ora SQL> create pfile='/home/oracle/initBR8PROD.ora' from spfile; File created. SQL> [oracle@rac1 ~]$ cat /home/oracle/initBR8PROD.ora BR8PROD.__db_cache_size=486539264 BR8PROD.__java_pool_size=16777216 BR8PROD.__large_pool_size=33554432 BR8PROD.__pga_aggregate_target=520093696 BR8PROD.__sga_target=754974720 BR8PROD.__shared_io_pool_size=0 BR8PROD.__shared_pool_size=201326592 BR8PROD.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/BR8PROD/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='+DATA1/br8prod/controlfile/current.260.928633403' *.db_block_size=8192 *.db_create_file_dest='+DATA1' *.db_domain='RAJASEKHAR.COM' *.db_name='BR8PROD' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=BR8PRODXDB)' *.log_archive_dest_1='LOCATION=+FRA' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=1262485504 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' [oracle@rac1 ~]$ Edited Parameter File for Auxiliary Database: cat initBR8DEV.ora BR8DEV.__db_cache_size=486539264 BR8DEV.__java_pool_size=16777216 BR8DEV.__large_pool_size=33554432 BR8DEV.__pga_aggregate_target=520093696 BR8DEV.__sga_target=754974720 BR8DEV.__shared_io_pool_size=0 BR8DEV.__shared_pool_size=201326592 BR8DEV.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/BR8DEV/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='+DATA','+ARCH' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='RAJASEKHAR.COM' *.db_name='BR8DEV' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=BR8DEVXDB)' *.log_archive_dest_1='LOCATION=+ARCH' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=1262485504 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' *.db_file_name_convert='+DATA1','+DATA' *.log_file_name_convert='+DATA1','+DATA'
3. Copy password file/initBR8DEV.ora to auxiliary side
[oracle@rac1 ~]$ scp initBR8DEV.ora oracle@rac2:/u01/app/oracle/product/11.2.0.4/db_1/dbs initBR8DEV.ora 100% 789 0.8KB/s 00:00 [oracle@rac1 ~]$ [oracle@rac1 dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@rac1 dbs]$ scp orapwBR8PROD oracle@rac2:/u01/app/oracle/product/11.2.0.4/db_1/dbs orapwBR8PROD 100% 1536 1.5KB/s 00:00 [oracle@rac1 dbs]$ [oracle@rac2 dbs]$ mv orapwBR8PROD orapwBR8DEV [oracle@rac2 dbs]$ ls -ltr orapwBR8DEV -rw-r----- 1 oracle oinstall 1536 Nov 23 13:56 orapwBR8DEV [oracle@rac2 dbs]$
4. Create required directories (On the auxiliary host)
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/BR8DEV/adump [oracle@rac2 ~]$ mkdir -p /u01/app/oracle [oracle@rac2 ~]$
5. Add oratab entry (On the auxiliary host)
[oracle@rac2 ~]$ cat /etc/oratab | grep -i BR8DEV
BR8DEV:/u01/app/oracle/product/11.2.0.4/db_1:N
[oracle@rac2 ~]$
6. Add both TNS Entries on both sides (***Mandatory)
RAC1:
BR8DEV = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DEV.rajasekhar.com) ) ) BR8PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM) ) )
RAC2:
BR8DEV = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DEV.rajasekhar.com) ) ) BR8PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM) ) )
7. Static Registration on LISTENER (On the auxiliary host)
On RAC2 – Add below entry to listener.ora on auxiliary side
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = BR8DEV)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
(GLOBAL_DBNAME = BR8DEV.RAJASEKHAR.COM)
)
)
Before
[oracle@rac2 ~]$ 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. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) ) ) ADR_BASE_LISTENER = /u01/app/oracle ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent [oracle@rac2 ~]$ [oracle@rac2 ~]$ ps -ef | grep tns root 9 2 0 13:33 ? 00:00:00 [netns] oracle 3428 1 0 13:36 ? 00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit oracle 4197 3369 0 14:35 pts/1 00:00:00 grep tns [oracle@rac2 ~]$ [oracle@rac2 ~]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:35:09 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 23-NOV-2016 13:36:25 Uptime 0 days 0 hr. 58 min. 44 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/oracle/diag/tnslsnr/rac2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1522))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rac2 ~]$
After
[oracle@rac2 ~]$ 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. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) ) ) ADR_BASE_LISTENER = /u01/app/oracle ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = BR8DEV) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1) (GLOBAL_DBNAME = BR8DEV.RAJASEKHAR.COM) ) ) [oracle@rac2 ~]$ [oracle@rac2 ~]$ lsnrctl reload LISTENER LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:35:33 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) The command completed successfully [oracle@rac2 ~]$ [oracle@rac2 admin]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:51:35 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 23-NOV-2016 13:36:25 Uptime 0 days 1 hr. 15 min. 10 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/oracle/diag/tnslsnr/rac2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1522))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "BR8DEV.RAJASEKHAR.COM" has 1 instance(s). Instance "BR8DEV", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@rac2 admin]$
8. Start the auxiliary instance (On the auxiliary host)
[oracle@rac2 ~]$ . oraenv BR8DEV [oracle@rac2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:44:39 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/home/oracle/initBR8DEV.ora'; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes SQL>
From RAC1:
[oracle@rac1 ~]$ tnsping BR8PROD TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 20:30:39 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM))) OK (0 msec) [oracle@rac1 ~]$ [oracle@rac1 ~]$ tnsping BR8DEV TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 20:30:49 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DEV.rajasekhar.com))) OK (0 msec) [oracle@rac1 ~]$
From RAC2:
[oracle@rac2 ~]$ tnsping BR8DEV TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:58:46 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DEV.rajasekhar.com))) OK (0 msec) [oracle@rac2 ~]$ [oracle@rac2 ~]$ tnsping BR8PROD TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:58:53 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM))) OK (0 msec) [oracle@rac2 ~]$ [oracle@rac2 ~]$ sqlplus sys/sys@BR8DEV as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:59:22 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@rac2 ~]$ sqlplus sys/sys@BR8PROD as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:59:34 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@rac2 ~]$
10. RMAN Connectivity Verification (On the auxiliary host)
restore_connectivity.sh ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1 ORACLE_SID=BR8DEV BACKUP_LOG_PATH=/home/oracle export ORACLE_BASE export ORACLE_HOME export ORACLE_SID export BACKUP_LOG_PATH LOG_FILE=${BACKUP_LOG_PATH}/restore_connectivity.log $ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF connect target sys/sys@BR8PROD connect auxiliary sys/sys@BR8DEV run{ allocate channel t1 type disk; allocate auxiliary channel a1 device type disk; release channel t1; } exit; EOF Please click here to download script <----- [oracle@rac2 ~]$ chmod 775 restore_connectivity.sh [oracle@rac2 ~]$ ./restore_connectivity.sh RMAN> RMAN> RMAN> 2> 3> 4> 5> RMAN> [oracle@rac2 ~]$
Output
[oracle@rac2 ~]$ cat restore_connectivity.log Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 23 15:09:25 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> connected to target database: BR8PROD (DBID=4192214970) RMAN> connected to auxiliary database: BR8DEV (not mounted) RMAN> 2> 3> 4> 5> using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=25 device type=DISK allocated channel: a1 channel a1: SID=23 device type=DISK released channel: t1 released channel: a1 RMAN> Recovery Manager complete. [oracle@rac2 ~]$
11. Run RMAN Duplicate (On the auxiliary host)
[oracle@rac2 ~]$ cat restore.sh ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1 ORACLE_SID=BR8DEV BACKUP_LOG_PATH=/home/oracle export ORACLE_BASE export ORACLE_HOME export ORACLE_SID export BACKUP_LOG_PATH LOG_FILE=${BACKUP_LOG_PATH}/restore_db.log $ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF connect target sys/sys@BR8PROD connect auxiliary sys/sys@BR8DEV run{ allocate channel t1 type disk; allocate auxiliary channel a1 device type disk; duplicate target database to BR8DEV from active database nofilenamecheck; release channel t1; } exit; EOF [oracle@rac2 ~]$ Please click here to download the script <---- [oracle@rac2 ~]$ chmod 775 restore.sh [oracle@rac2 ~]$ [oracle@rac2 ~]$ nohup ./restore.sh & [1] 5483 [oracle@rac2 ~]$ nohup: appending output to `nohup.out' [oracle@rac2 ~]$ jobs -l [1]+ 5483 Running nohup ./restore.sh & [oracle@rac2 ~]$ tail -f restore_db.log <--- you can monitor progress
output
[oracle@rac2 ~]$ cat restore_db.log Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 23 16:09:06 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> connected to target database: BR8PROD (DBID=4192214970) RMAN> connected to auxiliary database: BR8DEV (not mounted) RMAN> 2> 3> 4> 5> 6> using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=50 device type=DISK allocated channel: a1 channel a1: SID=24 device type=DISK Starting Duplicate Db at 23-NOV-16 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 1269366784 bytes Fixed Size 2252864 bytes Variable Size 822087616 bytes Database Buffers 436207616 bytes Redo Buffers 8818688 bytes allocated channel: a1 channel a1: SID=23 device type=DISK contents of Memory Script: { sql clone "alter system set control_files = ''+DATA/br8dev/controlfile/current.260.928685355'', ''+ARCH/br8dev/controlfile/current.262.928685355'' comment= ''Set by RMAN'' scope=spfile"; sql clone "alter system set db_name = ''BR8PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''BR8DEV'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount backup as copy current controlfile auxiliary format '+DATA/br8dev/controlfile/current.261.928685355'; restore clone controlfile to '+ARCH/br8dev/controlfile/current.263.928685355' from '+DATA/br8dev/controlfile/current.261.928685355'; sql clone "alter system set control_files = ''+DATA/br8dev/controlfile/current.261.928685355'', ''+ARCH/br8dev/controlfile/current.263.928685355'' 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 = ''+DATA/br8dev/controlfile/current.260.928685355'', ''+ARCH/br8dev/controlfile/current.262.928685355'' comment= ''Set by RMAN'' scope=spfile sql statement: alter system set db_name = ''BR8PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''BR8DEV'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 822087616 bytes Database Buffers 436207616 bytes Redo Buffers 8818688 bytes allocated channel: a1 channel a1: SID=23 device type=DISK Starting backup at 23-NOV-16 channel t1: starting datafile copy copying current control file output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BR8PROD.f tag=TAG20161123T160920 RECID=2 STAMP=928685361 channel t1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 23-NOV-16 Starting restore at 23-NOV-16 channel a1: copied control file copy Finished restore at 23-NOV-16 sql statement: alter system set control_files = ''+DATA/br8dev/controlfile/current.261.928685355'', ''+ARCH/br8dev/controlfile/current.263.928685355'' comment= ''Set by RMAN'' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 822087616 bytes Database Buffers 436207616 bytes Redo Buffers 8818688 bytes allocated channel: a1 channel a1: SID=23 device type=DISK 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 newname for datafile 1 to "+data"; set newname for datafile 2 to "+data"; set newname for datafile 3 to "+data"; set newname for datafile 4 to "+data"; set newname for datafile 5 to "+data"; backup as copy reuse datafile 1 auxiliary format "+data" datafile 2 auxiliary format "+data" datafile 3 auxiliary format "+data" datafile 4 auxiliary format "+data" datafile 5 auxiliary format "+data" ; 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 Starting backup at 23-NOV-16 channel t1: starting datafile copy input datafile file number=00001 name=+DATA1/br8prod/datafile/system.256.928633345 output file name=+DATA/br8dev/datafile/system.262.928685383 tag=TAG20161123T160941 channel t1: datafile copy complete, elapsed time: 00:00:46 channel t1: starting datafile copy input datafile file number=00002 name=+DATA1/br8prod/datafile/sysaux.257.928633345 output file name=+DATA/br8dev/datafile/sysaux.263.928685427 tag=TAG20161123T160941 channel t1: datafile copy complete, elapsed time: 00:00:35 channel t1: starting datafile copy input datafile file number=00005 name=+DATA1/br8prod/datafile/example.265.928633413 output file name=+DATA/br8dev/datafile/example.264.928685463 tag=TAG20161123T160941 channel t1: datafile copy complete, elapsed time: 00:00:25 channel t1: starting datafile copy input datafile file number=00003 name=+DATA1/br8prod/datafile/undotbs1.258.928633345 output file name=+DATA/br8dev/datafile/undotbs1.265.928685487 tag=TAG20161123T160941 channel t1: datafile copy complete, elapsed time: 00:00:07 channel t1: starting datafile copy input datafile file number=00004 name=+DATA1/br8prod/datafile/users.259.928633345 output file name=+DATA/br8dev/datafile/users.266.928685495 tag=TAG20161123T160941 channel t1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 23-NOV-16 sql statement: alter system archive log current contents of Memory Script: { backup as copy reuse archivelog like "+FRA/br8prod/archivelog/2016_11_23/thread_1_seq_5.260.928685495" auxiliary format "+ARCH" ; catalog clone start with "+ARCH"; switch clone datafile all; } executing Memory Script Starting backup at 23-NOV-16 channel t1: starting archived log copy input archived log thread=1 sequence=5 RECID=3 STAMP=928685496 output file name=+ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497 RECID=0 STAMP=0 channel t1: archived log copy complete, elapsed time: 00:00:03 Finished backup at 23-NOV-16 searching for all files that match the pattern +ARCH List of Files Unknown to the Database ===================================== File Name: +arch/BR8DEV/ARCHIVELOG/2016_11_23/thread_1_seq_5.264.928685497 File Name: +arch/BR8DEV/CONTROLFILE/Current.256.928682009 File Name: +arch/BR8DEV/CONTROLFILE/Current.257.928682009 File Name: +arch/BR8DEV/CONTROLFILE/Current.258.928683095 File Name: +arch/BR8DEV/CONTROLFILE/Current.259.928683095 File Name: +arch/BR8DEV/CONTROLFILE/Current.260.928684163 File Name: +arch/BR8DEV/CONTROLFILE/Current.261.928684163 File Name: +arch/BR8DEV/CONTROLFILE/Current.262.928685355 cataloging files... cataloging done List of Cataloged Files ======================= File Name: +arch/BR8DEV/ARCHIVELOG/2016_11_23/thread_1_seq_5.264.928685497 List of Files Which Where Not Cataloged ======================================= File Name: +arch/BR8DEV/CONTROLFILE/Current.256.928682009 RMAN-07517: Reason: The file header is corrupted File Name: +arch/BR8DEV/CONTROLFILE/Current.257.928682009 RMAN-07517: Reason: The file header is corrupted File Name: +arch/BR8DEV/CONTROLFILE/Current.258.928683095 RMAN-07517: Reason: The file header is corrupted File Name: +arch/BR8DEV/CONTROLFILE/Current.259.928683095 RMAN-07517: Reason: The file header is corrupted File Name: +arch/BR8DEV/CONTROLFILE/Current.260.928684163 RMAN-07517: Reason: The file header is corrupted File Name: +arch/BR8DEV/CONTROLFILE/Current.261.928684163 RMAN-07517: Reason: The file header is corrupted File Name: +arch/BR8DEV/CONTROLFILE/Current.262.928685355 RMAN-07517: Reason: The file header is corrupted datafile 1 switched to datafile copy input datafile copy RECID=2 STAMP=928685500 file name=+DATA/br8dev/datafile/system.262.928685383 datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=928685500 file name=+DATA/br8dev/datafile/sysaux.263.928685427 datafile 3 switched to datafile copy input datafile copy RECID=4 STAMP=928685500 file name=+DATA/br8dev/datafile/undotbs1.265.928685487 datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=928685500 file name=+DATA/br8dev/datafile/users.266.928685495 datafile 5 switched to datafile copy input datafile copy RECID=6 STAMP=928685500 file name=+DATA/br8dev/datafile/example.264.928685463 contents of Memory Script: { set until scn 996825; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 23-NOV-16 starting media recovery archived log for thread 1 with sequence 5 is already on disk as file +ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497 archived log file name=+ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497 thread=1 sequence=5 media recovery complete, elapsed time: 00:00:00 Finished recover at 23-NOV-16 Oracle instance started Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 822087616 bytes Database Buffers 436207616 bytes Redo Buffers 8818688 bytes contents of Memory Script: { sql clone "alter system set db_name = ''BR8DEV'' 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 = ''BR8DEV'' 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 1269366784 bytes Fixed Size 2252864 bytes Variable Size 822087616 bytes Database Buffers 436207616 bytes Redo Buffers 8818688 bytes allocated channel: a1 channel a1: SID=23 device type=DISK sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BR8DEV" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+data' ) SIZE 50 M REUSE, GROUP 2 ( '+data' ) SIZE 50 M REUSE, GROUP 3 ( '+data' ) SIZE 50 M REUSE DATAFILE '+DATA/br8dev/datafile/system.262.928685383' CHARACTER SET WE8MSWIN1252 contents of Memory Script: { set newname for tempfile 1 to "+data"; switch clone tempfile all; catalog clone datafilecopy "+DATA/br8dev/datafile/sysaux.263.928685427", "+DATA/br8dev/datafile/undotbs1.265.928685487", "+DATA/br8dev/datafile/users.266.928685495", "+DATA/br8dev/datafile/example.264.928685463"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to +data in control file cataloged datafile copy datafile copy file name=+DATA/br8dev/datafile/sysaux.263.928685427 RECID=1 STAMP=928685514 cataloged datafile copy datafile copy file name=+DATA/br8dev/datafile/undotbs1.265.928685487 RECID=2 STAMP=928685514 cataloged datafile copy datafile copy file name=+DATA/br8dev/datafile/users.266.928685495 RECID=3 STAMP=928685514 cataloged datafile copy datafile copy file name=+DATA/br8dev/datafile/example.264.928685463 RECID=4 STAMP=928685514 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=928685514 file name=+DATA/br8dev/datafile/sysaux.263.928685427 datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=928685514 file name=+DATA/br8dev/datafile/undotbs1.265.928685487 datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=928685514 file name=+DATA/br8dev/datafile/users.266.928685495 datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=928685514 file name=+DATA/br8dev/datafile/example.264.928685463 contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 23-NOV-16 released channel: t1 released channel: a1 RMAN> Recovery Manager complete. [oracle@rac2 ~]$
SQL> select name, open_mode, dbid, created from v$database; NAME OPEN_MODE DBID CREATED --------- -------------------- ---------- -------------------- BR8DEV READ WRITE 3533049546 23-NOV-2016 16:11:54 SQL> select name from v$datafile union select name from v$controlfile union select member from v$logfile union select name from v$tempfile / 2 3 4 5 6 7 8 NAME -------------------------------------------------------------------------------- +ARCH/br8dev/controlfile/current.263.928685355 +DATA/br8dev/controlfile/current.261.928685355 +DATA/br8dev/datafile/example.264.928685463 +DATA/br8dev/datafile/sysaux.263.928685427 +DATA/br8dev/datafile/system.262.928685383 +DATA/br8dev/datafile/undotbs1.265.928685487 +DATA/br8dev/datafile/users.266.928685495 +DATA/br8dev/onlinelog/group_1.267.928685515 +DATA/br8dev/onlinelog/group_2.268.928685515 +DATA/br8dev/onlinelog/group_3.269.928685517 +DATA/br8dev/tempfile/temp.270.928685523 11 rows selected. SQL>
13. Create the spfile in ASM disk.
SQL> show parameter pfile NAME TYPE VALUE -------- ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 .4/db_1/dbs/spfileBR8DEV.ora SQL> [oracle@rac2 ~]$ ls -ltr /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileBR8DEV.ora ls: /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileBR8DEV.ora: No such file or directory [oracle@rac2 ~]$ [oracle@rac2 ~]$ . oraenv ORACLE_SID = [+ASM] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac2 ~]$ asmcmd ASMCMD> cd DATA/BR8DEV ASMCMD> ls -l Type Redund Striped Time Sys Name Y CONTROLFILE/ Y DATAFILE/ Y ONLINELOG/ Y TEMPFILE/ ASMCMD> mkdir PARAMETERFILE ASMCMD> cd PARAMETERFILE ASMCMD> pwd +DATA/BR8DEV/PARAMETERFILE ASMCMD> SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup nomount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes SQL> SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SQL> SQL> create spfile='+DATA/BR8DEV/PARAMETERFILE/spfileBR8DEV.ora' from PFILE; File created. SQL> ASMCMD> pwd +DATA/BR8DEV/PARAMETERFILE ASMCMD> ls -l Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE NOV 23 23:00:00 Y spfile.271.928710147 N spfileBR8DEV.ora => +DATA/BR8DEV/PARAMETERFILE/spfile.271.928710147 ASMCMD> [oracle@rac2 dbs]$ cp initBR8DEV.ora initBR8DEV.ora.bkp modify initBR8DEV.ora with spfile location [oracle@rac2 dbs]$ cat initBR8DEV.ora SPFILE='+DATA/BR8DEV/PARAMETERFILE/spfileBR8DEV.ora' [oracle@rac2 dbs]$ SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> SQL> startup; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes ORA-00205: error in identifying control file, check alert log for more info SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/br8dev/parameterfile/spf ilebr8dev.ora SQL> SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string +DATA, +ARCH control_management_pack_access string DIAGNOSTIC+TUNING SQL> SQL> ALTER SYSTEM SET CONTROL_FILES='+ARCH/br8dev/controlfile/current.263.928685355','+DATA/br8dev/controlfile/current.261.928685355' scope=spfile; System altered. SQL> SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes Database mounted. Database opened. SQL> SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- BR8DEV READ WRITE SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala