Restore Database RAC from multiple Instances

This is restore & recovery of RAC database running on 3 nodes.

While restore & recover database we allocate channel to all Instances for Load balance.

Oracle Database version is 11.2.0.3.

Steps to perform restore & recovery.

Restore spfile
Restore controlfile
Bring all the instances in mount state (do not put cluster_database=false)
Allocate channel using multiple instances
Restore database
Recover database
Open the database in resetlogs in one node
Open the database on other nodes

---------RESTORE SPFILE 

TEST271 | DUMMYGG1 | /export/home/oracle/scripts/rman
> rman target / catalog rman/RMAN@catalogdb

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 3 12:06:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> set dbid=90275615

executing command: SET DBID
database name is "DUMMYGG" and DBID is 90275615

RMAN> list backup of spfile;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7639172 Incr 0  80.00K     DISK        00:00:01     02-JAN-13
        BP Key: 7639190   Status: AVAILABLE  Compressed: NO  Tag: TAG20130102T121926
        Piece Name: /ORAGG/backup/DUMMYGG15_12nudf6q_1_1
  SPFILE Included: Modification time: 02-JAN-13
  SPFILE db_unique_name: DUMMYGG

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7640203 Incr 0  80.00K     DISK        00:00:01     02-JAN-13
        BP Key: 7640224   Status: AVAILABLE  Compressed: NO  Tag: TAG20130102T124658
        Piece Name: /ORAGG/backup/DUMMYGG05_2gnudgqg_1_1
  SPFILE Included: Modification time: 02-JAN-13
  SPFILE db_unique_name: DUMMYGG

RMAN> STARTUP FORCE NOMOUNT;
  
RMAN> restore spfile from '/ORAGG/backup/DUMMYGG05_2gnudgqg_1_1';

Starting restore at 03-JAN-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /ORAGG/backup/DUMMYGG05_2gnudgqg_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 03-JAN-13

RMAN> exit

Now copy spfile to all nodes & rename according to it.


------Restore controlfile

> rman target / catalog rman/RMAN@catalogdb

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jan 3 12:14:22 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DUMMYGG (not mounted)
connected to recovery catalog database

RMAN> set dbid=90275615;

executing command: SET DBID
database name is "DUMMYGG" and DBID is 90275615

RMAN> restore controlfile;

Starting restore at 03-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 instance=DUMMYGG1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: copied control file copy
input file name=/u00/app/oracle/diag/control.ctl
output file name=+TESTGG/DUMMYGG/controlfile/current.958.803736875
Finished restore at 03-JAN-13

----------Mount all Instance.

Mount database on All nodes & make sure cluster_database parameter is set to TRUE

----------Restore database.

rman target / catalog rman/RMAN@catalogdb

run {
sql 'alter system set "_backup_disk_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_disk_bufsz"=1048576 scope=memory';
sql 'alter system set "_backup_file_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_file_bufsz"=1048576 scope=memory';
allocate channel c01 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG01_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c02 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG02_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c03 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG03_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c04 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG04_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c05 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG05_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c06 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG06_%U' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c07 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG07_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c08 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG08_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c09 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG09_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c10 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG10_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c11 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG11_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c12 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG12_%U' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c13 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG13_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c14 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG14_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c15 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG15_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c16 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG16_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c17 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG17_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
allocate channel c18 DEVICE TYPE DISK FORMAT '/ORAGG/backup/DUMMYGG18_%U' CONNECT 'sys/SYSTEM@DUMMYGG3';
restore database;
release channel c01;
release channel c02;
release channel c03;
release channel c04;
release channel c05;
release channel c06;
release channel c07;
release channel c08;
release channel c09;
release channel c10;
release channel c11;
release channel c12;
release channel c13;
release channel c14;
release channel c15;
release channel c16;
release channel c17;
release channel c18;
}

---------recover database.

RMAN> run {
sql 'alter system set "_backup_disk_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_disk_bufsz"=1048576 scope=memory';
sql 'alter system set "_backup_file_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_file_bufsz"=1048576 scope=memory';
allocate channel c01 DEVICE TYPE DISK FORMAT '/ORAGG/backup/' CONNECT 'sys/SYSTEM@DUMMYGG1';
allocate channel c07 DEVICE TYPE DISK FORMAT '/ORAGG/backup/' CONNECT 'sys/SYSTEM@DUMMYGG2';
allocate channel c13 DEVICE TYPE DISK FORMAT '/ORAGG/backup/' CONNECT 'sys/SYSTEM@DUMMYGG3';
set until time "to_date('2013-01-02:13:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
recover database;
release channel c01;
release channel c07;
release channel c13;
}

---------Open database in resetlogs on first instance & rest with normal.

TEST271 | DUMMYGG1 | /export/home/oracle/scripts/rman/log
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 3 12:46:30 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

12:47:01 @DUMMYGG1 SQL>alter database open resetlogs;

Database altered.

Elapsed: 00:00:14.39

TEST272 | DUMMYGG2 | /export/home/oracle
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 3 12:47:43 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

12:47:43 @DUMMYGG2 SQL>alter database open;

Database altered.

Elapsed: 00:00:03.92
12:47:50 @DUMMYGG2 SQL>

---------output of restore

sql statement: alter system set "_backup_disk_bufcnt"=64 scope=memory

sql statement: alter system set "_backup_disk_bufsz"=1048576 scope=memory

sql statement: alter system set "_backup_file_bufcnt"=64 scope=memory

sql statement: alter system set "_backup_file_bufsz"=1048576 scope=memory

allocated channel: c01
channel c01: SID=222 instance=DUMMYGG1 device type=DISK

allocated channel: c02
channel c02: SID=5 instance=DUMMYGG1 device type=DISK

allocated channel: c03
channel c03: SID=68 instance=DUMMYGG1 device type=DISK

allocated channel: c04
channel c04: SID=99 instance=DUMMYGG1 device type=DISK

allocated channel: c05
channel c05: SID=129 instance=DUMMYGG1 device type=DISK

allocated channel: c06
channel c06: SID=162 instance=DUMMYGG1 device type=DISK

allocated channel: c07
channel c07: SID=67 instance=DUMMYGG2 device type=DISK

allocated channel: c08
channel c08: SID=161 instance=DUMMYGG2 device type=DISK

allocated channel: c09
channel c09: SID=191 instance=DUMMYGG2 device type=DISK

allocated channel: c10
channel c10: SID=222 instance=DUMMYGG2 device type=DISK

allocated channel: c11
channel c11: SID=5 instance=DUMMYGG2 device type=DISK

allocated channel: c12
channel c12: SID=38 instance=DUMMYGG2 device type=DISK

allocated channel: c13
channel c13: SID=134 instance=DUMMYGG3 device type=DISK

allocated channel: c14
channel c14: SID=72 instance=DUMMYGG3 device type=DISK

allocated channel: c15
channel c15: SID=133 instance=DUMMYGG3 device type=DISK

allocated channel: c16
channel c16: SID=194 instance=DUMMYGG3 device type=DISK

allocated channel: c17
channel c17: SID=10 instance=DUMMYGG3 device type=DISK

allocated channel: c18
channel c18: SID=74 instance=DUMMYGG3 device type=DISK

Starting restore at 03-JAN-13

channel c01: starting datafile backup set restore
channel c01: specifying datafile(s) to restore from backup set
channel c01: restoring datafile 00003 to +TESTGG/DUMMYGG/datafile/undotbs1.258.792518685
channel c01: reading from backup piece /ORAGG/backup/DUMMYGG05_22nudgq6_1_1
channel c02: starting datafile backup set restore
channel c02: specifying datafile(s) to restore from backup set
channel c02: restoring datafile 00005 to +TESTGG/DUMMYGG/datafile/undotbs2.264.792518777
channel c02: reading from backup piece /ORAGG/backup/DUMMYGG06_23nudgq6_1_1
channel c03: starting datafile backup set restore
channel c03: specifying datafile(s) to restore from backup set
channel c03: restoring datafile 00006 to +TESTGG/DUMMYGG/datafile/undotbs3.265.792518777
channel c03: reading from backup piece /ORAGG/backup/DUMMYGG07_24nudgq6_1_1
channel c04: starting datafile backup set restore
channel c04: specifying datafile(s) to restore from backup set
channel c04: restoring datafile 00013 to +TESTGG/DUMMYGG/datafile/c2oindx_tbs.974.799865727
channel c04: reading from backup piece /ORAGG/backup/DUMMYGG08_25nudgq8_1_1
channel c05: starting datafile backup set restore
channel c05: specifying datafile(s) to restore from backup set
channel c05: restoring datafile 00014 to +TESTGG/DUMMYGG/datafile/c2odata_tbs.939.799865745
channel c05: reading from backup piece /ORAGG/backup/DUMMYGG09_26nudgqa_1_1
channel c06: starting datafile backup set restore
channel c06: specifying datafile(s) to restore from backup set
channel c06: restoring datafile 00009 to +TESTGG/DUMMYGG/datafile/idm_data.344.793627825
channel c06: reading from backup piece /ORAGG/backup/DUMMYGG10_27nudgqa_1_1
channel c07: starting datafile backup set restore
channel c07: specifying datafile(s) to restore from backup set
channel c07: restoring datafile 00015 to +TESTGG/DUMMYGG/datafile/c2odata_tbs_reorg1.948.799865763
channel c07: reading from backup piece /ORAGG/backup/DUMMYGG14_2bnudgqc_1_1
channel c08: starting datafile backup set restore
channel c08: specifying datafile(s) to restore from backup set
channel c08: restoring datafile 00016 to +TESTGG/DUMMYGG/datafile/mvlog_data.735.799865763
channel c08: reading from backup piece /ORAGG/backup/DUMMYGG15_2cnudgqc_1_1
channel c09: starting datafile backup set restore
channel c09: specifying datafile(s) to restore from backup set
channel c09: restoring datafile 00017 to +TESTGG/DUMMYGG/datafile/c2odata_tbs_reorg0.305.799865765
channel c09: reading from backup piece /ORAGG/backup/DUMMYGG16_2dnudgqe_1_1
channel c10: starting datafile backup set restore
channel c10: specifying datafile(s) to restore from backup set
channel c10: restoring datafile 00004 to +TESTGG/DUMMYGG/datafile/users.259.792518685
channel c10: reading from backup piece /ORAGG/backup/DUMMYGG18_2fnudgqg_1_1
channel c11: starting datafile backup set restore
channel c11: specifying datafile(s) to restore from backup set
channel c11: restoring datafile 00007 to +TESTGG/DUMMYGG/datafile/gg_data.312.793368947
channel c11: reading from backup piece /ORAGG/backup/DUMMYGG11_28nudgqb_1_1
channel c12: starting datafile backup set restore
channel c12: specifying datafile(s) to restore from backup set
channel c12: restoring datafile 00011 to +TESTGG/DUMMYGG/datafile/soa_data.328.795
channel c12: reading from backup piece /ORAGG/backup/DUMMYGG13_2anudgqc_1_1
channel c13: starting datafile backup set restore
channel c13: specifying datafile(s) to restore from backup set
channel c13: restoring datafile 00001 to +TESTGG/DUMMYGG/datafile/system.256.79251
channel c13: reading from backup piece /ORAGG/backup/DUMMYGG12_29nudgqb_1_1
channel c14: starting datafile backup set restore
channel c14: specifying datafile(s) to restore from backup set
channel c14: restoring datafile 00012 to +TESTGG/DUMMYGG/datafile/taps_data.617.79
channel c14: reading from backup piece /ORAGG/backup/DUMMYGG03_20nudgq5_1_1
channel c15: starting datafile backup set restore
channel c15: specifying datafile(s) to restore from backup set
channel c15: restoring datafile 00002 to +TESTGG/DUMMYGG/datafile/sysaux.257.79251
channel c15: reading from backup piece /ORAGG/backup/DUMMYGG04_21nudgq6_1_1
channel c16: starting datafile backup set restore
channel c16: specifying datafile(s) to restore from backup set
channel c16: restoring datafile 00008 to +TESTGG/DUMMYGG/datafile/identity_data.34
channel c16: reading from backup piece /ORAGG/backup/DUMMYGG01_1unudgq5_1_1
channel c17: starting datafile backup set restore
channel c17: specifying datafile(s) to restore from backup set
channel c17: restoring datafile 00010 to +TESTGG/DUMMYGG/datafile/taps_data.329.79
channel c17: reading from backup piece /ORAGG/backup/DUMMYGG02_1vnudgq5_1_1
channel c07: piece handle=/ORAGG/backup/DUMMYGG14_2bnudgqc_1_1 tag=TAG20130102T124658
channel c07: restored backup piece 1
channel c07: restore complete, elapsed time: 00:00:05
channel c08: piece handle=/ORAGG/backup/DUMMYGG15_2cnudgqc_1_1 tag=TAG20130102T124658
channel c08: restored backup piece 1
channel c08: restore complete, elapsed time: 00:00:05
channel c09: piece handle=/ORAGG/backup/DUMMYGG16_2dnudgqe_1_1 tag=TAG20130102T124658
channel c09: restored backup piece 1
channel c09: restore complete, elapsed time: 00:00:05
channel c10: piece handle=/ORAGG/backup/DUMMYGG18_2fnudgqg_1_1 tag=TAG20130102T124658
channel c10: restored backup piece 1
channel c10: restore complete, elapsed time: 00:00:17
channel c11: piece handle=/ORAGG/backup/DUMMYGG11_28nudgqb_1_1 tag=TAG20130102T124658
channel c11: restored backup piece 1
channel c11: restore complete, elapsed time: 00:00:17
channel c12: piece handle=/ORAGG/backup/DUMMYGG13_2anudgqc_1_1 tag=TAG20130102T124658
channel c12: restored backup piece 1
channel c12: restore complete, elapsed time: 00:00:17
channel c13: piece handle=/ORAGG/backup/DUMMYGG12_29nudgqb_1_1 tag=TAG20130102T124658
channel c13: restored backup piece 1
channel c13: restore complete, elapsed time: 00:00:37
channel c06: piece handle=/ORAGG/backup/DUMMYGG10_27nudgqa_1_1 tag=TAG20130102T124658
channel c06: restored backup piece 1
channel c06: restore complete, elapsed time: 00:00:57
channel c01: piece handle=/ORAGG/backup/DUMMYGG05_22nudgq6_1_1 tag=TAG20130102T124658
channel c01: restored backup piece 1
channel c01: restore complete, elapsed time: 00:01:58
channel c02: piece handle=/ORAGG/backup/DUMMYGG06_23nudgq6_1_1 tag=TAG20130102T124658
channel c02: restored backup piece 1
channel c02: restore complete, elapsed time: 00:02:48
channel c03: piece handle=/ORAGG/backup/DUMMYGG07_24nudgq6_1_1 tag=TAG20130102T124658
channel c03: restored backup piece 1
channel c03: restore complete, elapsed time: 00:02:48
channel c04: piece handle=/ORAGG/backup/DUMMYGG08_25nudgq8_1_1 tag=TAG20130102T124658
channel c04: restored backup piece 1
channel c04: restore complete, elapsed time: 00:02:48
channel c05: piece handle=/ORAGG/backup/DUMMYGG09_26nudgqa_1_1 tag=TAG20130102T124658
channel c05: restored backup piece 1
channel c05: restore complete, elapsed time: 00:02:48
channel c15: piece handle=/ORAGG/backup/DUMMYGG04_21nudgq6_1_1 tag=TAG20130102T124658
channel c15: restored backup piece 1
channel c15: restore complete, elapsed time: 00:04:37
channel c14: piece handle=/ORAGG/backup/DUMMYGG03_20nudgq5_1_1 tag=TAG20130102T124658
channel c14: restored backup piece 1
channel c14: restore complete, elapsed time: 00:05:07
channel c17: piece handle=/ORAGG/backup/DUMMYGG02_1vnudgq5_1_1 tag=TAG20130102T124658
channel c17: restored backup piece 1
channel c17: restore complete, elapsed time: 00:10:38
channel c16: piece handle=/ORAGG/backup/DUMMYGG01_1unudgq5_1_1 tag=TAG20130102T124658
channel c16: restored backup piece 1
channel c16: restore complete, elapsed time: 00:10:48
Finished restore at 03-JAN-13

released channel: c01

released channel: c02

released channel: c03

released channel: c04

released channel: c05

released channel: c06

released channel: c07

released channel: c08

released channel: c09

released channel: c10

released channel: c11

released channel: c12

released channel: c13

released channel: c14

released channel: c15

released channel: c16

released channel: c17

released channel: c18

RMAN> quit


Recovery Manager complete.

------output of recover database.

sql statement: alter system set "_backup_disk_bufcnt"=64 scope=memory

sql statement: alter system set "_backup_disk_bufsz"=1048576 scope=memory

sql statement: alter system set "_backup_file_bufcnt"=64 scope=memory

sql statement: alter system set "_backup_file_bufsz"=1048576 scope=memory

allocated channel: c01
channel c01: SID=5 instance=DUMMYGG1 device type=DISK

allocated channel: c07
channel c07: SID=67 instance=DUMMYGG2 device type=DISK

allocated channel: c13
channel c13: SID=133 instance=DUMMYGG3 device type=DISK

executing command: SET until clause

Starting recover at 03-JAN-13

starting media recovery

archived log for thread 1 with sequence 1697 is already on disk as file +TESTGG/DUMMYGG/archivelog/2013_01_02/thread_1_seq_1697.776.803667609
archived log for thread 2 with sequence 1140 is already on disk as file +TESTGG/DUMMYGG/archivelog/2013_01_02/thread_2_seq_1140.1090.803668029
archived log for thread 3 with sequence 1195 is already on disk as file +TESTGG/DUMMYGG/archivelog/2013_01_02/thread_3_seq_1195.485.803669415
archived log file name=+TESTGG/DUMMYGG/archivelog/2013_01_02/thread_1_seq_1697.776.803667609 thread=1 sequence=1697
archived log file name=+TESTGG/DUMMYGG/archivelog/2013_01_02/thread_2_seq_1140.1090.803668029 thread=2 sequence=1140
archived log file name=+TESTGG/DUMMYGG/archivelog/2013_01_02/thread_3_seq_1195.485.803669415 thread=3 sequence=1195
media recovery complete, elapsed time: 00:00:01
Finished recover at 03-JAN-13

released channel: c01

released channel: c07

released channel: c13

RMAN>