Here We are performing the restore and recovery on ASM storage .
Source Database : PROD
Number of Nodes : 2
Version : 11.2.0.4
Storage : ASM
Diskgroups : BACKUP, PROD_DATA
Target Database : PROD
Number of Nodes : 2
Version : 11.2.0.4
Stroage : ASM
Diskgroups : TEST_DATA
Gather Source Database Information :-
srvctl status database -d PROD
Instance PROD1 is running on node st1aparac11
Instance PROD2 is running on node st1aparac12
srvctl config database -d PROD
Database unique name: PROD
Database name: PROD
Oracle home: /u01/app/oracle/product/11.2.0.4/DB
Oracle user: oracle
Spfile: +PROD_DATA/PROD/spfilePROD.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: PROD
Database instances: PROD1,PROD2
Disk Groups: PROD_DATA,BACKUP
Mount point paths:
Services:
Type: RAC
Database is administrator managed
set pages 10000
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile;
--------------------------------------------------------------------------------
+BACKUP/PROD/controlfile/current.268.910890941
+BACKUP/PROD/onlinelog/group_1.269.910890943
+BACKUP/PROD/onlinelog/group_2.270.910890947
+BACKUP/PROD/onlinelog/group_3.271.910891021
+BACKUP/PROD/onlinelog/group_4.272.910891025
+PROD_DATA/PROD/controlfile/current.260.910890941
+PROD_DATA/PROD/datafile/flux_coxhs_data_idx.273.910894103
+PROD_DATA/PROD/datafile/sb_data.268.910894051
+PROD_DATA/PROD/datafile/st1vpptsb_data.271.910894091
+PROD_DATA/PROD/datafile/st1vpptsb_idx.272.910894097
+PROD_DATA/PROD/datafile/st1vpsb_data.269.910894067
+PROD_DATA/PROD/datafile/st1vpsb_idx.270.910894081
+PROD_DATA/PROD/datafile/sysaux.257.910890873
+PROD_DATA/PROD/datafile/system.256.910890873
+PROD_DATA/PROD/datafile/undotbs1.258.910890873
+PROD_DATA/PROD/datafile/undotbs2.264.910890969
+PROD_DATA/PROD/datafile/users.259.910890873
+PROD_DATA/PROD/onlinelog/group_1.261.910890945
+PROD_DATA/PROD/onlinelog/group_2.262.910890947
+PROD_DATA/PROD/onlinelog/group_3.265.910891023
+PROD_DATA/PROD/onlinelog/group_4.266.910891025
+PROD_DATA/PROD/tempfile/temp.263.910890951
22 rows selected.
Step 1 :- I am using recent RMAN L0/FULL backup and Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.
run
{
allocate channel c01 device type disk;
allocate channel c02 device type disk;
allocate channel c03 device type disk;
allocate channel c04 device type disk;
allocate channel c05 device type disk;
BACKUP FORMAT '/backup/PROD/dbf_%U' DATABASE TAG Full_bk;
BACKUP CURRENT ControlFile FORMAT '/backup/PROD/ctl_%U';
sql 'alter system archive log current';
BACKUP FORMAT '/backup/PROD/arch_%U' ARCHIVELOG ALL TAG Archivelog_bk DELETE INPUT;
RESTORE DATABASE VALIDATE;
CROSSCHECK BACKUP OF DATABASE;
REPORT OBSOLETE;
release channel c01;
release channel c02;
release channel c03;
release channel c04;
release channel c05;
}
RMAN> list backup of spfile;
-- Note down output of backup piece its required to restore on target from this piece
Output:-
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
207 Full 80.00K DISK 00:00:02 10-OCT-2016 15:47:11
BP Key: 207 Status: AVAILABLE Compressed: NO Tag: FULL_BK
Piece Name: /backup/PROD/dbf_6jri15ft_1_1
SPFILE Included: Modification time: 21-SEP-2016 18:02:24
SPFILE db_unique_name: PROD
RMAN> list backup of controlfile;
-- Note down output of backup peice its required to restore on target from this piece
Output:-
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
206 Full 17.95M DISK 00:00:28 10-OCT-2016 15:47:04
BP Key: 206 Status: AVAILABLE Compressed: NO Tag: FULL_BK
Piece Name: /backup/PROD/dbf_6iri15es_1_1
Control File Included: Ckp SCN: 35721110 Ckp time: 10-OCT-2016 15:46:39
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
212 Full 17.95M DISK 00:00:02 10-OCT-2016 15:47:48
BP Key: 212 Status: AVAILABLE Compressed: NO Tag: TAG20161010T154746
Piece Name: /backup/PROD/ctl_6kri15h2_1_1
Control File Included: Ckp SCN: 35721170 Ckp time: 10-OCT-2016 15:47:46
Step 2 :- Copy the backup and required files to Target server
i) Copy the RMAN backup files to Target server.
ii) copy the password file to target server
iii) Create the file system directories on target server if required
The RMAN backup files are coped to TARGET server as below.
mkdir -p /tempnfs/OSS_INSTALL/BACKUP/ST_PROD
chmod 777 /tempnfs/OSS_INSTALL/BACKUP/ST_PROD
Also copy the password file from target server to auxiliary database server.
scp $ORACLE_HOME/dbs/orapwPROD1 snanabala@test.corp.com:/tempnfs/OSS_INSTALL/BACKUP/password
-- target once moved
cp /tempnfs/OSS_INSTALL/BACKUP/orapwPROD1 $ORACLE_HOME/dbs
mkdir -p /u00/app/oracle/admin/PROD/adump ==> create adump directory since its not on target server )
=====================> ON TARGET SERVER <============================
Step 3:- start DB with Nomount with Temp init file
-- start database nomount with temp init.ora with sufficient parameters
startup nomount pfile='$ORACLE_HOME/dbs/inittemp.ora'
Output:-
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 683672456 bytes
Database Buffers 373293056 bytes
Redo Buffers 9711616 bytes
Contents of inittemp.ora
SQL> ! cat $ORACLE_HOME/dbs/inittemp.ora
db_name='PROD'
memory_target=1G
processes = 150
audit_file_dest='/u00/app/oracle/admin/PROD/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
diagnostic_dest='/u00/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
Step 4 :- Restore the SPFILE
rman target /
restore spfile from '/BACKUP/ST_PROD/dbf_6jri15ft_1_1';
Output:-
RMAN> restore spfile from '/BACKUP/ST_PROD/dbf_6jri15ft_1_1';
Starting restore at 10-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=241 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /BACKUP/ST_PROD/dbf_6jri15ft_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 10-OCT-16
Note :- make sure backup pieces having all read write execute preivliges chmod 777 on all backup files other wise it will fail sometime.
corp.com | PROD | /u00/app/oracle/product/11.2.0.4/db/dbs
> ls -ltrh
total 20K
-rw-r--r-- 1 oracle dba 2.8K May 15 2009 init.ora
-rw-r--r-- 1 oracle dba 470 Oct 10 15:28 inittemp.ora
-rwxr-xr-x 1 oracle dba 1.5K Oct 10 16:25 orapwPROD1*
-rw-rw---- 1 oracle dba 1.6K Oct 10 16:26 hc_PROD1.dat
-rw-r----- 1 oracle dba 4.5K Oct 10 16:27 spfilePROD1.ora
Step 5:- Create pfile from RESTORED SPFILE and Modify
Create pfile from above restored spfile and modify the parameters according to auxiliary database server. Copy the updated parameter file under $ORACLE_HOME/dbs location.
Here is the modified pfile in auxiliary database under $ORACLE_HOME/dbs
cd $ORACLE_HOME/dbs
SQL> create pfile from spfile='/u00/app/oracle/product/11.2.0.4/db/dbs/spfilePROD1.ora';
File created.
SQL> shutdown immediate
remove any old references file systems below are the change in my initPROD1.ora
#*.cluster_database=TRUE ====> Turn off oracle RAC clustering by commenting
*.remote_listener='test_scan.cox.com:1521' ===> make sure you update right scan name on new cluster name
*.audit_file_dest='/u00/app/oracle/admin/TEST /adump'
#*.control_files='/u02/oradata/PROD/====> comment out this control_files
*.db_create_file_dest='+TEST_DATA' Source Database : PROD
Number of Nodes : 2
Version : 11.2.0.4
Storage : ASM
Diskgroups : BACKUP, PROD_DATA
Target Database : PROD
Number of Nodes : 2
Version : 11.2.0.4
Stroage : ASM
Diskgroups : TEST_DATA
Gather Source Database Information :-
srvctl status database -d PROD
Instance PROD1 is running on node st1aparac11
Instance PROD2 is running on node st1aparac12
srvctl config database -d PROD
Database unique name: PROD
Database name: PROD
Oracle home: /u01/app/oracle/product/11.2.0.4/DB
Oracle user: oracle
Spfile: +PROD_DATA/PROD/spfilePROD.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: PROD
Database instances: PROD1,PROD2
Disk Groups: PROD_DATA,BACKUP
Mount point paths:
Services:
Type: RAC
Database is administrator managed
set pages 10000
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile;
--------------------------------------------------------------------------------
+BACKUP/PROD/controlfile/current.268.910890941
+BACKUP/PROD/onlinelog/group_1.269.910890943
+BACKUP/PROD/onlinelog/group_2.270.910890947
+BACKUP/PROD/onlinelog/group_3.271.910891021
+BACKUP/PROD/onlinelog/group_4.272.910891025
+PROD_DATA/PROD/controlfile/current.260.910890941
+PROD_DATA/PROD/datafile/flux_coxhs_data_idx.273.910894103
+PROD_DATA/PROD/datafile/sb_data.268.910894051
+PROD_DATA/PROD/datafile/st1vpptsb_data.271.910894091
+PROD_DATA/PROD/datafile/st1vpptsb_idx.272.910894097
+PROD_DATA/PROD/datafile/st1vpsb_data.269.910894067
+PROD_DATA/PROD/datafile/st1vpsb_idx.270.910894081
+PROD_DATA/PROD/datafile/sysaux.257.910890873
+PROD_DATA/PROD/datafile/system.256.910890873
+PROD_DATA/PROD/datafile/undotbs1.258.910890873
+PROD_DATA/PROD/datafile/undotbs2.264.910890969
+PROD_DATA/PROD/datafile/users.259.910890873
+PROD_DATA/PROD/onlinelog/group_1.261.910890945
+PROD_DATA/PROD/onlinelog/group_2.262.910890947
+PROD_DATA/PROD/onlinelog/group_3.265.910891023
+PROD_DATA/PROD/onlinelog/group_4.266.910891025
+PROD_DATA/PROD/tempfile/temp.263.910890951
22 rows selected.
Step 1 :- I am using recent RMAN L0/FULL backup and Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.
run
{
allocate channel c01 device type disk;
allocate channel c02 device type disk;
allocate channel c03 device type disk;
allocate channel c04 device type disk;
allocate channel c05 device type disk;
BACKUP FORMAT '/backup/PROD/dbf_%U' DATABASE TAG Full_bk;
BACKUP CURRENT ControlFile FORMAT '/backup/PROD/ctl_%U';
sql 'alter system archive log current';
BACKUP FORMAT '/backup/PROD/arch_%U' ARCHIVELOG ALL TAG Archivelog_bk DELETE INPUT;
RESTORE DATABASE VALIDATE;
CROSSCHECK BACKUP OF DATABASE;
REPORT OBSOLETE;
release channel c01;
release channel c02;
release channel c03;
release channel c04;
release channel c05;
}
RMAN> list backup of spfile;
-- Note down output of backup piece its required to restore on target from this piece
Output:-
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
207 Full 80.00K DISK 00:00:02 10-OCT-2016 15:47:11
BP Key: 207 Status: AVAILABLE Compressed: NO Tag: FULL_BK
Piece Name: /backup/PROD/dbf_6jri15ft_1_1
SPFILE Included: Modification time: 21-SEP-2016 18:02:24
SPFILE db_unique_name: PROD
RMAN> list backup of controlfile;
-- Note down output of backup peice its required to restore on target from this piece
Output:-
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
206 Full 17.95M DISK 00:00:28 10-OCT-2016 15:47:04
BP Key: 206 Status: AVAILABLE Compressed: NO Tag: FULL_BK
Piece Name: /backup/PROD/dbf_6iri15es_1_1
Control File Included: Ckp SCN: 35721110 Ckp time: 10-OCT-2016 15:46:39
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
212 Full 17.95M DISK 00:00:02 10-OCT-2016 15:47:48
BP Key: 212 Status: AVAILABLE Compressed: NO Tag: TAG20161010T154746
Piece Name: /backup/PROD/ctl_6kri15h2_1_1
Control File Included: Ckp SCN: 35721170 Ckp time: 10-OCT-2016 15:47:46
Step 2 :- Copy the backup and required files to Target server
i) Copy the RMAN backup files to Target server.
ii) copy the password file to target server
iii) Create the file system directories on target server if required
The RMAN backup files are coped to TARGET server as below.
mkdir -p /tempnfs/OSS_INSTALL/BACKUP/ST_PROD
chmod 777 /tempnfs/OSS_INSTALL/BACKUP/ST_PROD
Also copy the password file from target server to auxiliary database server.
scp $ORACLE_HOME/dbs/orapwPROD1 snanabala@test.corp.com:/tempnfs/OSS_INSTALL/BACKUP/password
-- target once moved
cp /tempnfs/OSS_INSTALL/BACKUP/orapwPROD1 $ORACLE_HOME/dbs
mkdir -p /u00/app/oracle/admin/PROD/adump ==> create adump directory since its not on target server )
=====================> ON TARGET SERVER <============================
Step 3:- start DB with Nomount with Temp init file
-- start database nomount with temp init.ora with sufficient parameters
startup nomount pfile='$ORACLE_HOME/dbs/inittemp.ora'
Output:-
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 683672456 bytes
Database Buffers 373293056 bytes
Redo Buffers 9711616 bytes
Contents of inittemp.ora
SQL> ! cat $ORACLE_HOME/dbs/inittemp.ora
db_name='PROD'
memory_target=1G
processes = 150
audit_file_dest='/u00/app/oracle/admin/PROD/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
diagnostic_dest='/u00/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
Step 4 :- Restore the SPFILE
rman target /
restore spfile from '/BACKUP/ST_PROD/dbf_6jri15ft_1_1';
Output:-
RMAN> restore spfile from '/BACKUP/ST_PROD/dbf_6jri15ft_1_1';
Starting restore at 10-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=241 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /BACKUP/ST_PROD/dbf_6jri15ft_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 10-OCT-16
Note :- make sure backup pieces having all read write execute preivliges chmod 777 on all backup files other wise it will fail sometime.
corp.com | PROD | /u00/app/oracle/product/11.2.0.4/db/dbs
> ls -ltrh
total 20K
-rw-r--r-- 1 oracle dba 2.8K May 15 2009 init.ora
-rw-r--r-- 1 oracle dba 470 Oct 10 15:28 inittemp.ora
-rwxr-xr-x 1 oracle dba 1.5K Oct 10 16:25 orapwPROD1*
-rw-rw---- 1 oracle dba 1.6K Oct 10 16:26 hc_PROD1.dat
-rw-r----- 1 oracle dba 4.5K Oct 10 16:27 spfilePROD1.ora
Step 5:- Create pfile from RESTORED SPFILE and Modify
Create pfile from above restored spfile and modify the parameters according to auxiliary database server. Copy the updated parameter file under $ORACLE_HOME/dbs location.
Here is the modified pfile in auxiliary database under $ORACLE_HOME/dbs
cd $ORACLE_HOME/dbs
SQL> create pfile from spfile='/u00/app/oracle/product/11.2.0.4/db/dbs/spfilePROD1.ora';
File created.
SQL> shutdown immediate
remove any old references file systems below are the change in my initPROD1.ora
#*.cluster_database=TRUE ====> Turn off oracle RAC clustering by commenting
*.remote_listener='test_scan.cox.com:1521' ===> make sure you update right scan name on new cluster name
*.audit_file_dest='/u00/app/oracle/admin/TEST /adump'
#*.control_files='/u02/oradata/PROD/
log_archive_dest_1='location=+TEST_DATA'
*.DB_CREATE_ONLINE_LOG_DEST_1='+TEST_DATA'
db_file_name_convert=='/u02/oradata/PROD/datafile/','+SAMP_DATA','/u02/oradata/PROD/datafile/','+SAMP_DATA','/u02/oradata/','+SAMP_DATA' ===> make sure you edit this parameter
log_file_name_convert='/u02/oradata/PROD/onlinelog/','+SAMP_DATA'
-- start with above initPROD1.ora database
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initPROD1.ora'
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 721424360 bytes
Database Buffers 520093696 bytes
Redo Buffers 8892416 bytes
Step 6:- Restore the Control file
RMAN> RESTORE CONTROLFILE FROM '/BACKUP/ST_PROD/ctl_6kri15h2_1_1';
Starting restore at 10-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+TEST_DATA/PROD/controlfile/current.276.924885615
output file name=+TEST_DATA/PROD/controlfile/current.270.924885615
Finished restore at 10-OCT-16
RMAN> exit
Step 7:- Update the correct control file name in pfile.
Here is the updated pfile info with control file information from above step under $ORACLE_HOME/dbs
grep -i control initPROD.ora
*.control_files='+TEST_DATA/PROD/controlfile/current.276.924885615','+TEST_DATA/PROD/controlfile/current.270.924885615'
shutdown immediate
startup mount pfile='$ORACLE_HOME/dbs/initPROD1.ora'
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 721424360 bytes
Database Buffers 520093696 bytes
Redo Buffers 8892416 bytes
Database mounted.
Step 8:- catalog the database backups
rman target /
catalog start with '/BACKUP/ST_PROD/';
OUTPUT:-
RMAN> catalog start with '/BACKUP/ST_PROD/';
Starting implicit crosscheck backup at 10-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=230 device type=DISK
Crosschecked 42 objects
Finished implicit crosscheck backup at 10-OCT-16
Starting implicit crosscheck copy at 10-OCT-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 10-OCT-16
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /BACKUP/ST_PROD/
List of Files Unknown to the Database
=====================================
File Name: /BACKUP/ST_PROD/arch_6lri15hh_1_1
File Name: /BACKUP/ST_PROD/arch_6mri15hh_1_1
File Name: /BACKUP/ST_PROD/arch_6nri15hh_1_1
File Name: /BACKUP/ST_PROD/arch_6ori15hh_1_1
File Name: /BACKUP/ST_PROD/ctl_6kri15h2_1_1
File Name: /BACKUP/ST_PROD/dbf_6eri15er_1_1
File Name: /BACKUP/ST_PROD/dbf_6fri15er_1_1
File Name: /BACKUP/ST_PROD/dbf_6gri15er_1_1
File Name: /BACKUP/ST_PROD/dbf_6hri15er_1_1
File Name: /BACKUP/ST_PROD/dbf_6iri15es_1_1
File Name: /BACKUP/ST_PROD/dbf_6jri15ft_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /BACKUP/ST_PROD/arch_6lri15hh_1_1
File Name: /BACKUP/ST_PROD/arch_6mri15hh_1_1
File Name: /BACKUP/ST_PROD/arch_6nri15hh_1_1
File Name: /BACKUP/ST_PROD/arch_6ori15hh_1_1
File Name: /BACKUP/ST_PROD/ctl_6kri15h2_1_1
File Name: /BACKUP/ST_PROD/dbf_6eri15er_1_1
File Name: /BACKUP/ST_PROD/dbf_6fri15er_1_1
File Name: /BACKUP/ST_PROD/dbf_6gri15er_1_1
File Name: /BACKUP/ST_PROD/dbf_6hri15er_1_1
File Name: /BACKUP/ST_PROD/dbf_6iri15es_1_1
File Name: /BACKUP/ST_PROD/dbf_6jri15ft_1_1
Step 9:- Start Restore Database & Recovery
run {
allocate channel c01 device type disk;
allocate channel c02 device type disk;
allocate channel c03 device type disk;
allocate channel c04 device type disk;
allocate channel c05 device type disk;
set newname for database to '+TEST_DATA';
restore database;
switch datafile all;
RECOVER DATABASE;
release channel c01;
release channel c02;
release channel c03;
release channel c04;
release channel c05;
}
OUTPUT:-
RMAN> run {
allocate channel c01 device type disk;
2> 3> allocate channel c02 device type disk;
4> allocate channel c03 device type disk;
5> allocate channel c04 device type disk;
6> allocate channel c05 device type disk;
7> set newname for database to '+TEST_DATA';
8> restore database;
9> switch datafile all;
10> RECOVER DATABASE;
11> release channel c01;
12> release channel c02;
13> release channel c03;
14> release channel c04;
15> release channel c05;
16> }
released channel: ORA_DISK_1
allocated channel: c01
channel c01: SID=230 device type=DISK
allocated channel: c02
channel c02: SID=2205 device type=DISK
allocated channel: c03
channel c03: SID=306 device type=DISK
allocated channel: c04
channel c04: SID=382 device type=DISK
allocated channel: c05
channel c05: SID=458 device type=DISK
executing command: SET NEWNAME
Starting restore at 10-OCT-16
channel c01: starting datafile backup set restore
channel c01: specifying datafile(s) to restore from backup set
channel c01: restoring datafile 00003 to +TEST_DATA
channel c01: restoring datafile 00007 to +TEST_DATA
channel c01: restoring datafile 00008 to +TEST_DATA
channel c01: reading from backup piece /BACKUP/ST_PROD/dbf_6eri15er_1_1
channel c02: starting datafile backup set restore
channel c02: specifying datafile(s) to restore from backup set
channel c02: restoring datafile 00001 to +TEST_DATA
channel c02: restoring datafile 00011 to +TEST_DATA
channel c02: reading from backup piece /BACKUP/ST_PROD/dbf_6fri15er_1_1
channel c03: starting datafile backup set restore
channel c03: specifying datafile(s) to restore from backup set
channel c03: restoring datafile 00004 to +TEST_DATA
channel c03: restoring datafile 00006 to +TEST_DATA
channel c03: restoring datafile 00009 to +TEST_DATA
channel c03: reading from backup piece /BACKUP/ST_PROD/dbf_6hri15er_1_1
channel c04: starting datafile backup set restore
channel c04: specifying datafile(s) to restore from backup set
channel c04: restoring datafile 00002 to +TEST_DATA
channel c04: restoring datafile 00005 to +TEST_DATA
channel c04: restoring datafile 00010 to +TEST_DATA
channel c04: reading from backup piece /BACKUP/ST_PROD/dbf_6gri15er_1_1
channel c01: piece handle=/BACKUP/ST_PROD/dbf_6eri15er_1_1 tag=FULL_BK
channel c01: restored backup piece 1
channel c01: restore complete, elapsed time: 00:00:38
channel c03: piece handle=/BACKUP/ST_PROD/dbf_6hri15er_1_1 tag=FULL_BK
channel c03: restored backup piece 1
channel c03: restore complete, elapsed time: 00:00:37
channel c02: piece handle=/BACKUP/ST_PROD/dbf_6fri15er_1_1 tag=FULL_BK
channel c02: restored backup piece 1
channel c02: restore complete, elapsed time: 00:00:47
channel c04: piece handle=/BACKUP/ST_PROD/dbf_6gri15er_1_1 tag=FULL_BK
channel c04: restored backup piece 1
channel c04: restore complete, elapsed time: 00:00:46
Finished restore at 10-OCT-16
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/system.267.924886105
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/sysaux.262.924886109
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/undotbs1.278.924886105
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/users.263.924886107
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/undotbs2.298.924886109
datafile 6 switched to datafile copy
input datafile copy RECID=17 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/sb_data.274.924886107
datafile 7 switched to datafile copy
input datafile copy RECID=18 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/st1vpsb_data.277.924886105
datafile 8 switched to datafile copy
input datafile copy RECID=19 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/st1vpsb_idx.258.924886105
datafile 9 switched to datafile copy
input datafile copy RECID=20 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/st1vpptsb_data.264.924886107
datafile 10 switched to datafile copy
input datafile copy RECID=21 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/st1vpptsb_idx.256.924886109
datafile 11 switched to datafile copy
input datafile copy RECID=22 STAMP=924886152 file name=+TEST_DATA/PROD/datafile/flux_coxhs_data_idx.292.924886105
Starting recover at 10-OCT-16
starting media recovery
channel c01: starting archived log restore to default destination
channel c01: restoring archived log
archived log thread=1 sequence=423
channel c01: reading from backup piece /BACKUP/ST_PROD/arch_6lri15hh_1_1
channel c02: starting archived log restore to default destination
channel c02: restoring archived log
archived log thread=2 sequence=232
channel c02: reading from backup piece /BACKUP/ST_PROD/arch_6mri15hh_1_1
channel c03: starting archived log restore to default destination
channel c03: restoring archived log
archived log thread=2 sequence=233
channel c03: reading from backup piece /BACKUP/ST_PROD/arch_6nri15hh_1_1
channel c04: starting archived log restore to default destination
channel c04: restoring archived log
archived log thread=1 sequence=424
channel c04: reading from backup piece /BACKUP/ST_PROD/arch_6ori15hh_1_1
channel c01: piece handle=/BACKUP/ST_PROD/arch_6lri15hh_1_1 tag=ARCHIVELOG_BK
channel c01: restored backup piece 1
channel c01: restore complete, elapsed time: 00:00:00
archived log file name=+TEST_DATA/PROD/archivelog/2016_10_10/thread_1_seq_423.261.924886157 thread=1 sequence=423
channel c03: piece handle=/BACKUP/ST_PROD/arch_6nri15hh_1_1 tag=ARCHIVELOG_BK
channel c03: restored backup piece 1
channel c03: restore complete, elapsed time: 00:00:00
channel c02: piece handle=/BACKUP/ST_PROD/arch_6mri15hh_1_1 tag=ARCHIVELOG_BK
channel c02: restored backup piece 1
channel c02: restore complete, elapsed time: 00:00:01
archived log file name=+TEST_DATA/PROD/archivelog/2016_10_10/thread_2_seq_232.260.924886157 thread=2 sequence=232
archived log file name=+TEST_DATA/PROD/archivelog/2016_10_10/thread_2_seq_233.259.924886157 thread=2 sequence=233
channel c04: piece handle=/BACKUP/ST_PROD/arch_6ori15hh_1_1 tag=ARCHIVELOG_BK
channel c04: restored backup piece 1
channel c04: restore complete, elapsed time: 00:00:02
archived log file name=+TEST_DATA/PROD/archivelog/2016_10_10/thread_1_seq_424.266.924886157 thread=1 sequence=424
unable to find archived log
archived log thread=1 sequence=425
released channel: c01
released channel: c02
released channel: c03
released channel: c04
released channel: c05
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/10/2016 16:49:18
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 425 and starting SCN of 35721193
Applied the archive log files till sequence# 424
Step 10:- Rename online redo logs
SQL> select member from gv$logfile;
MEMBER
--------------------------------------------------------------------------------
+BACKUP/PROD/onlinelog/group_2.270.910890947
+PROD_DATA/PROD/onlinelog/group_2.262.910890947
+BACKUP/PROD/onlinelog/group_1.269.910890943
+PROD_DATA/PROD/onlinelog/group_1.261.910890945
+BACKUP/PROD/onlinelog/group_3.271.910891021
+PROD_DATA/PROD/onlinelog/group_3.265.910891023
+BACKUP/PROD/onlinelog/group_4.272.910891025
+PROD_DATA/PROD/onlinelog/group_4.266.910891025
8 rows selected.
SQL> select group#,thread#,status from gv$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 2 INACTIVE
4 2 CURRENT
-- Rename Old 1,2,3 redo log files
cat rename_old_redologs.sql
set time on echo on timing on
spoo rename_old_redologs.log
alter database rename file '+BACKUP/PROD/onlinelog/group_2.270.910890947' to '+TEST_DATA/PROD/onlinelog/group_2.270.910890947';
alter database rename file '+PROD_DATA/PROD/onlinelog/group_2.262.910890947' to '+TEST_DATA/PROD/onlinelog/group_2.262.910890947';
alter database rename file '+BACKUP/PROD/onlinelog/group_1.269.910890943' to '+TEST_DATA/PROD/onlinelog/group_1.269.910890943';
alter database rename file '+PROD_DATA/PROD/onlinelog/group_1.261.910890945' to '+TEST_DATA/PROD/onlinelog/group_1.261.910890945';
alter database rename file '+BACKUP/PROD/onlinelog/group_3.271.910891021' to '+TEST_DATA/PROD/onlinelog/group_3.271.910891021';
alter database rename file '+PROD_DATA/PROD/onlinelog/group_3.265.910891023' to '+TEST_DATA/PROD/onlinelog/group_3.265.910891023';
alter database rename file '+BACKUP/PROD/onlinelog/group_4.272.910891025' to '+TEST_DATA/PROD/onlinelog/group_4.272.910891025';
alter database rename file '+PROD_DATA/PROD/onlinelog/group_4.266.910891025' to '+TEST_DATA/PROD/onlinelog/group_4.266.910891025';
spool off
Execute the rename_old_redologs.sql in database
sqlplus / as sysdba
@rename_old_redologs.sql
output:-
SQL> @rename_old_redologs.sql
16:57:06 SQL> spoo rename_old_redologs.log
16:57:06 SQL> alter database rename file '+BACKUP/PROD/onlinelog/group_2.270.910890947' to '+TEST_DATA/PROD/onlinelog/group_2.270.910890947';
Database altered.
Elapsed: 00:00:00.12
16:57:06 SQL> alter database rename file '+PROD_DATA/PROD/onlinelog/group_2.262.910890947' to '+TEST_DATA/PROD/onlinelog/group_2.262.910890947';
Database altered.
Elapsed: 00:00:00.01
16:57:06 SQL> alter database rename file '+BACKUP/PROD/onlinelog/group_1.269.910890943' to '+TEST_DATA/PROD/onlinelog/group_1.269.910890943';
Database altered.
Elapsed: 00:00:00.01
16:57:06 SQL> alter database rename file '+PROD_DATA/PROD/onlinelog/group_1.261.910890945' to '+TEST_DATA/PROD/onlinelog/group_1.261.910890945';
Database altered.
Elapsed: 00:00:00.01
16:57:06 SQL> alter database rename file '+BACKUP/PROD/onlinelog/group_3.271.910891021' to '+TEST_DATA/PROD/onlinelog/group_3.271.910891021';
Database altered.
Elapsed: 00:00:00.04
16:57:06 SQL> alter database rename file '+PROD_DATA/PROD/onlinelog/group_3.265.910891023' to '+TEST_DATA/PROD/onlinelog/group_3.265.910891023';
Database altered.
Elapsed: 00:00:00.01
16:57:06 SQL> alter database rename file '+BACKUP/PROD/onlinelog/group_4.272.910891025' to '+TEST_DATA/PROD/onlinelog/group_4.272.910891025';
Database altered.
Elapsed: 00:00:00.01
16:57:06 SQL> alter database rename file '+PROD_DATA/PROD/onlinelog/group_4.266.910891025' to '+TEST_DATA/PROD/onlinelog/group_4.266.910891025';
Database altered.
Elapsed: 00:00:00.01
16:57:06 SQL> spool off
SQL> select member from gv$logfile;
MEMBER
--------------------------------------------------------------------------------
+TEST_DATA/PROD/onlinelog/group_2.270.910890947
+TEST_DATA/PROD/onlinelog/group_2.262.910890947
+TEST_DATA/PROD/onlinelog/group_1.269.910890943
+TEST_DATA/PROD/onlinelog/group_1.261.910890945
+TEST_DATA/PROD/onlinelog/group_3.271.910891021
+TEST_DATA/PROD/onlinelog/group_3.265.910891023
+TEST_DATA/PROD/onlinelog/group_4.272.910891025
+TEST_DATA/PROD/onlinelog/group_4.266.910891025
8 rows selected.
Elapsed: 00:00:00.01
SQL> select group#,thread#,status from gv$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 2 INACTIVE
4 2 CURRENT
Step 11:- Open the database with reset logs
SQL> alter database open resetlogs;
Database altered.
Step 12:- Add temp file if it is not automatically added
-- Check if Temp is there or not
select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+TEST_DATA/PROD/tempfile/temp.258.924620691
-- No need to create the Temp file in this case.
Step 13:- Rename the spfile which is restored by step 4
-- check is there any spfile under $ORACLE_HOME/dbs then rename if exists
cd $ORACLE_HOME/dbs
mv spfilePROD1.ora spfilePROD1.ora.bak
Step 14:- Modify the init parameter file and place under all nodes under cluster
uncomment the few parameters in initPROD1.ora
CLUSTER_DATABASE=TRUE
Now copy the init & password to rest of the nodes in cluster :-
cd $ORACLE_HOME/dbs
scp initPROD1.ora oracle@node2:/u00/app/oracle/product/11.2.0.4/db/dbs/initPROD2.ora
scp orapwPROD1 oracle@node2:/u00/app/oracle/product/11.2.0.4/db/dbs/orapwPROD2
Step 15 :- Add instance intries to /etc/oratab for rest of the nodes
on Node 1:-
grep -i PROD /etc/oratab
PROD1:/u00/app/oracle/product/11.2.0.4/db:N
on Node 2:-
grep -i PROD /etc/oratab
PROD2:/u00/app/oracle/product/11.2.0.4/db:N
Step 16 :- Add required directories on rest of the nodes
on Node 2:-
mkdir -p /u00/app/oracle/admin/PROD/adump
Step 17:- Add database & Instances to OCR
-- To verify is there any databases exists
srvctl config database
Add Database name to OCR
srvctl add database -d PROD -o /u00/app/oracle/product/11.2.0.4/db
Add Instance PROD1
srvctl add instance -d PROD -i PROD1 -n node1
Add Instance PROD2
srvctl add instance -d PROD -i PROD2 -n node2
> srvctl status database -d PROD
Instance PROD1 is not running on node node1
Instance PROD2 is not running on node node2
Check Configuration of Database
> srvctl config database -d PROD
Database unique name: PROD
Database name:
Oracle home: /u00/app/oracle/product/11.2.0.4/db
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: PROD
Database instances: PROD1,PROD2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed
start database with srvctl ( we placed init files under db_homes on node1 & node 2 )
> srvctl start database -d PROD
node1 | PROD1 | /u00/app/oracle/product/11.2.0.4/db/dbs
> srvctl status database -d PROD
Instance PROD1 is running on node node1
Instance PROD2 is running on node node2
Step 18:- Create the spfile in ASM disk
We could do this step at the beginning too. However, i do this step at the end for my convenience.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL>
SQL> create spfile='+TEST_DATA/PROD/PARAMETERFILE/spfilePROD.ora' from PFILE;
File created.
Stop database with SRVCTL
srvctl stop database -d PROD
Updated the parameter file to point to SPFILE in ASM disk :-
Before update make sure take backup in case if required later
On 1st node:-
cp initPROD1.ora initPROD1.ora.bak
Update with below information
cat initPROD1.ora
SPFILE='+TEST_DATA/PROD/PARAMETERFILE/spfilePROD.ora'
On 2nd node:-
cp initPROD2.ora initPROD2.ora.bak
cat initPROD2.ora
SPFILE='+TEST_DATA/PROD/PARAMETERFILE/spfilePROD.ora'
On 2nd node
cat initPROD2.ora
SPFILE='+TEST_DATA/PROD/PARAMETERFILE/spfilePROD.ora'
Update spfile location in OCR
srvctl modify database -d PROD -p '+TEST_DATA/PROD/PARAMETERFILE/spfilePROD.ora'
> srvctl config database -d PROD
Database unique name: PROD
Database name:
Oracle home: /u00/app/oracle/product/11.2.0.4/db
Oracle user: oracle
Spfile: +TEST_DATA/PROD/PARAMETERFILE/spfilePROD.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: PROD
Database instances: PROD1,PROD2
Disk Groups: TEST_DATA
Mount point paths:
Services:
Type: RAC
Database is administrator managed
Start Cluster database
srvctl start database -d PROD
> srvctl status database -d PROD
Instance PROD1 is running on node node1
Instance PROD2 is running on node node2
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +TEST_DATA/PROD/parameterfile/spfilePROD.ora
Step 19:- Verify all the files and make sure all files are moved to ASM disk.
Also watch out the alert log for a while. Create the listener and make sure able to connect outside the database server.
col HOST_NAME for a30
select instance_name,host_name from gv$instance;
INSTANCE_NAME HOST_NAME
---------------- ------------------------------
PROD1 node1.corp.cox.com
PROD2 node2.corp.cox.com
select banner from gv$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
10 rows selected.
set pages 10000
select name from gv$datafile
union
select name from gv$controlfile
union
select member from gv$logfile
union
select name from gv$tempfile;
NAME
--------------------------------------------------------------------------------
+TEST_DATA/PROD/controlfile/current.270.924885615
+TEST_DATA/PROD/controlfile/current.276.924885615
+TEST_DATA/PROD/datafile/flux_coxhs_data_idx.292.924886105
+TEST_DATA/PROD/datafile/sb_data.274.924886107
+TEST_DATA/PROD/datafile/st1vpptsb_data.264.924886107
+TEST_DATA/PROD/datafile/st1vpptsb_idx.256.924886109
+TEST_DATA/PROD/datafile/st1vpsb_data.277.924886105
+TEST_DATA/PROD/datafile/st1vpsb_idx.258.924886105
+TEST_DATA/PROD/datafile/sysaux.262.924886109
+TEST_DATA/PROD/datafile/system.267.924886105
+TEST_DATA/PROD/datafile/undotbs1.278.924886105
+TEST_DATA/PROD/datafile/undotbs2.298.924886109
+TEST_DATA/PROD/datafile/users.263.924886107
+TEST_DATA/PROD/onlinelog/group_1.294.924886725
+TEST_DATA/PROD/onlinelog/group_1.295.924886725
+TEST_DATA/PROD/onlinelog/group_2.257.924886725
+TEST_DATA/PROD/onlinelog/group_2.265.924886725
+TEST_DATA/PROD/onlinelog/group_3.293.924886725
+TEST_DATA/PROD/onlinelog/group_3.297.924886725
+TEST_DATA/PROD/onlinelog/group_4.268.924886727
+TEST_DATA/PROD/onlinelog/group_4.296.924886727
+TEST_DATA/PROD/tempfile/temp.269.924886729
22 rows selected.
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +TEST_DATA/PROD/parameterfile/spfilePROD.ora
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
switch few logs and verify is there any errors on alert.log make sure there are no errors.
That concludes our restoration of RAC database using RMAN On ASM
No comments:
Post a Comment