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>

Exadata Backup on ZFS.

Below is Test of Exadata x2 half rack Database backup on ZFS.


Size : 2 TB
Used Size : 1.6 TB
Time Taken: 1hr 34 Mins.
Storage: 4 share of 300TB ZFS.
Channels Allocated: 5 channels per node per share. Totally 20 channels across 4 nodes and 4 shares.

#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
export ORACLE_SID=DUMMYDB1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/u01/app/oracle/product/11.2.0.3/db_1/network/admin
export ALTER SESSION NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI'
export LOGFILE=/home/oracle/DUMMYDB_rman_zfs_level0_ver3.log

#setup oracle environment based on SID
ORAENV_ASK=NO
. oraenv

rman target / catalog rman/xxxx@catalogdb <> ${LOGFILE}
run {
sql 'alter system set "_backup_disk_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_disk_bufsz"=1048576 scope=memory';
allocate channel c01 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest301_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c02 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest302_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c03 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest303_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c04 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest304_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c05 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest305_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c06 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest306_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c07 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest307_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c08 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest308_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c09 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest309_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c10 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest310_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c11 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest311_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c12 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest312_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c13 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest313_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c14 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest314_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c15 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest315_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c16 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest316_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c17 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest317_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c18 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest318_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c19 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest319_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c20 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest320_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
sql "alter system archive log current";
backup incremental level 0 database plus archivelog;
sql "alter database backup controlfile to trace";
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;
release channel c19;
release channel c20;
}
exit
EOF

References:

http://www.oracle.com/technetwork/database/features/availability/maa-wp-dbm-zfs-backup-1593252.pdf

http://www.oracle.com/technetwork/database/features/availability/maa-tech-wp-sundbm-backup-11202-183503.pdf

GoldenGate performance Tuning - 1

Problem : Replicat was doing Full table scan on Updating 804000 rows.

Source. = 11.2.0.3 /TARGG

Below is update statement which was issued to update 804000 rows.
This completed in 15 seconds on SOURCE side DB.

sql_id :ghsutzgq0m8j9 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID ghsutzgq0m8j9
--------------------
UPDATE dummy11.OS_HISTORYSTEP_BLD SET ENTRY_ID=5555, STEP_ID=100000,
ACTION_ID=20000, OWNER='JIGNESHKANKRECHA',
START_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
FINISH_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
DUE_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000 PM','fmMMfm/fmDDfm/YYYY
fmHH12fm:MI:SS.FF AM'), STATUS='QC Validation failed', CALLER='KALIYA'
where ID  BETWEEN 1220000 AND 5999999

Plan hash value: 3578452229

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |   376 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |    47 |  4277 |   376   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------

------------------------------------------------------------------
Target. = 10.2.0.4 / SOUGG

Below statment Replicat executed ,
Because i had BATCHSQL replicat made BATCH of few rows and send for update.
that is the reason we see COUNT STOPKEY in execution plan.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID b4t7hv6p53165
--------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM = 1

Plan hash value: 185372276

------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |                    |       |       |   304 (100)|          |
|   1 |  UPDATE             | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   COUNT STOPKEY     |                    |       |       |            |          |
|   3 |    TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |     1 |    91 |   304   (1)| 00:00:04 |
------------------------------------------------------------------------------------------

--Excerpt from Report file.

BATCHSQL BATCHESPERQUEUE 100, BATCHTRANSOPS 10000, OPSPERBATCH 10000, OPSPERQUEUE 100000

MAP resolved (entry dummy11.OS_HISTORYSTEP_BLD):
  MAP "dummy11"."OS_HISTORYSTEP_BLD", TARGET dummy11.OS_HISTORYSTEP_BLD, KEYCOLS(ID);

2012-12-12 12:00:30  WARNING OGG-00869  No unique key is defined for table 'OS_HISTORYSTEP_BLD'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Using following columns in default map by name:
  ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, FINISH_DATE,
  DUE_DATE, STATUS, CALLER
Using the following key columns for target table dummy11.OS_HISTORYSTEP_BLD: ID.

--------some statistic of replicat. 
2012-12-12 12:18:22  INFO    OGG-01408  Restoring current schema for DDL operation to [GGADMIN].
              104578 records processed as of 2012-12-12 12:21:05 (rate 84,delta 236)
              457309 records processed as of 2012-12-12 13:35:07 (rate 80,delta 79)
              913119 records processed as of 2012-12-12 13:39:47 (rate 153,delta 1628)
              943118 records processed as of 2012-12-12 13:41:25 (rate 155,delta 305)
              953118 records processed as of 2012-12-12 13:42:43 (rate 155,delta 127)
              963118 records processed as of 2012-12-12 13:44:25 (rate 154,delta 98)
              973118 records processed as of 2012-12-12 13:46:29 (rate 153,delta 80)
              983118 records processed as of 2012-12-12 13:48:57 (rate 151,delta 67)
              993118 records processed as of 2012-12-12 13:51:47 (rate 148,delta 58)
             1003118 records processed as of 2012-12-12 13:54:59 (rate 146,delta 51)
             1013118 records processed as of 2012-12-12 13:58:35 (rate 143,delta 46)

SOLUTION:

1) kill session of replicat which was running update statement ,,This will abend replicat.
2) Add Index on ID column which was in KEYCOLS Of MAP statement.
3) start replicat .

And we are on Road again....

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:40

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:42

GGSCI (catlmsxt205) 6> INFO ALL
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:33:06

GGSCI (catlmsxt205) 7> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      00:00:03

Execution plan of Update after Index.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b4t7hv6p53165, child number 0
-------------------------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM =
1

Plan hash value: 2252287618

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |     5 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|*  2 |   COUNT STOPKEY    |                    |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| OS_HIST_BLDIDX     |     1 |    91 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)
   3 - access("ID"=TO_NUMBER(:B0))

GoldenGate High availability using Clusterware

This post is to setup High Availability for Goldengate.

Clusterware will manage Goldengate resources to start/stop/relocate of Goldengate Processes.

For 10g & 11gR1 Clusterware.

--Add VIP for Goldengate application

--As Oracle - Create resource profile.  Use IP address which was pre-allocated 1.2.2 section. 
/u00/app/oracle/product/10.2.0/CRS/bin/crs_profile -create ggatevip -t application -a /u00/app/oracle/product/10.2.0/CRS/bin/usrvip -p balanced -h dummyxt200,dummyxt205 -o oi=eth0,ov=10.10.10.10,on=255.255.255.0

--Next, Register the VIP as oracle:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_register ggatevip

--Because the assignment of an IP address is done by the root user, you have to set the ownership of the VIP to the root user. 

--Connect as root and execute:
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm ggatevip -o root

--As root, allow oracle to run the script to start the VIP.
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm ggatevip -u user:oracle:r-x

--Then, as oracle, start the VIP:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_start ggatevip

--To validate whether the VIP is running and on which node it is running, execute:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_stat ggatevip -t

Try to Ping Goldengate VIP. 
ping -c4 [Goldengate VIP]

Copy & Test GoldenGate action script

Place Goldengate Action script Which is in Addendum of This Post to $CRS_HOME/crs/public on each Node.  

$CRS_HOME/crs/public/ggaction.scr
/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr

Give full permission on it.
chmod 777 ggaction.scr

Once copied please test it using below argument. 

/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr  [start|stop|check]
/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr stop

Add Goldengate application resource to cluster

--As Oracle create profile for Goldengate Application 
/u00/app/oracle/product/10.2.0/CRS/bin/crs_profile -create goldengate_app -t application -r ggatevip -a /u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr -o ci=10

--As oracle
/u00/app/oracle/product/10.2.0/CRS/bin/crs_register goldengate_app

--As root
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm goldengate_app -o root

--As root
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm goldengate_app -u user:oracle:r-x

--As oracle
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_start goldengate_app

--As oracle.
/u00/app/oracle/product/10.2.0/CRS/bin/crs_stat goldengate_app -t

Name           Type           Target    State     Host
------------------------------------------------------------
ggatevip       application    ONLINE    ONLINE    dummyxt200
goldengate_app application    ONLINE    ONLINE    dummyxt200

Manage Application.

--To Relocate Goldengate on different Node. 
dummyxt200@:/u00/app/oracle/product/10.2.0/CRS/crs/public :CRS $crs_relocate -f goldengate_app
Attempting to stop `goldengate_app` on member `dummyxt200`
Stop of `goldengate_app` on member `dummyxt200` succeeded.
Attempting to stop `ggatevip` on member `dummyxt200`
Stop of `ggatevip` on member `dummyxt200` succeeded.
Attempting to start `ggatevip` on member `dummyxt208`
Start of `ggatevip` on member `dummyxt208` succeeded.
Attempting to start `goldengate_app` on member `dummyxt208`
Start of `goldengate_app` on member `dummyxt208` succeeded.

--Test relocation of resource on All Node in cluster. 

crs_relocate -f goldengate_app -n 

--Confirm Goldengate process has been started on relocated Node.

--Issue below command on Node where Goldengate application resource is relocated. 
> ps -ef | grep mgr
root     23942     1  0 15:41 ?        00:00:00 ./mgr PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/mgr.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
> ps -ef | grep extract
root     23958 23942  2 15:41 ?        00:00:01 /ORAGG/product/11.2.1/gghome10/extract PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/identde.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/IDENTDE.rpt PROCESSID IDENTDE USESUBDIRS
root     23959 23942  0 15:41 ?        00:00:00 /ORAGG/product/11.2.1/gghome10/extract PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/identdp.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/IDENTDP.rpt PROCESSID IDENTDP USESUBDIRS

--To stop Goldengate using clusterware. 
> crs_stop goldengate_app
Attempting to stop `goldengate_app` on member `dummyxt208`
Stop of `goldengate_app` on member `dummyxt208` succeeded.
dummyxt208 | CRS | /ORAGG/product/11.2.1/gghome10
> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ggatevip       application    ONLINE    ONLINE    dummyxt208
goldengate_app application    OFFLINE   OFFLINE

--To start Goldengate using clusterware. 
dummyxt200@:/ORAGG/product/11.2.1/gghome10 :CRS $crs_start goldengate_app
Attempting to start `ggatevip` on member `dummyxt200`
Start of `ggatevip` on member `dummyxt200` succeeded.
Attempting to start `goldengate_app` on member `dummyxt200`
Start of `goldengate_app` on member `dummyxt200` succeeded.

For 11gR2 Clusterware

--Add VIP Goldengate Application

. oraenv
ORACLE_SID = [TARGG1] ? GRID
The Oracle base for ORACLE_HOME=/u00/app/11.2.0/GRID is /u00/app/oracle

--As root 
sudo /u00/app/11.2.0/GRID/bin/appvipcfg create -network=1 -ip=10.10.10.10 -vipname=mvggatevip -user=root

-----------------Output---------------
Production Copyright 2007, 2008, Oracle.All rights reserved
2012-11-04 10:24:30: Creating Resource Type
2012-11-04 10:24:30: Executing cmd: /u00/app/11.2.0/GRID/bin/crsctl add type app.appvip.type -basetype cluster_resource -file /u00/app/11.2.0/GRID/crs/template/appvip.type
2012-11-04 10:24:30: Create the Resource
2012-11-04 10:24:30: Executing cmd: /u00/app/11.2.0/GRID/bin/crsctl add resource mvggatevip -type app.appvip.type -attr USR_ORA_VIP=10.10.10.10,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x'

dummyxt271 | GRID | /export/home/oracle
--As root,  Allow the Oracle Grid infrastructure software owner (e.g. oracle) to run the script to start the VIP.
sudo /u00/app/11.2.0/GRID/bin/crsctl setperm resource mvggatevip -u user:oracle:r-x

--As oracle, start the VIP:
/u00/app/11.2.0/GRID/bin/crsctl start resource mvggatevip
CRS-2672: Attempting to start 'mvggatevip' on 'dummyxt274'
CRS-2676: Start of 'mvggatevip' on 'dummyxt274' succeeded


--To validate whether the VIP is running and on which node it is running, execute:
/u00/app/11.2.0/GRID/bin/crsctl status resource mvggatevip
NAME=mvggatevip
TYPE=app.appvip.type
TARGET=ONLINE
STATE=ONLINE on dummyxt274

--At this point you can also connect to another server in the subnet and ping the VIP's IP address. You should get a reply from this IP address.

ping -c4 mvggatevip

Copy & Test Goldengate Action script.

--Place Goldengate Action script from Addendum of this Post to $GRID_HOME/crs/public  on each Node.  
/u00/app/11.2.0/GRID/crs/public/ggaction.scr [start|stop|check]
/u00/app/11.2.0/GRID/crs/public/ggaction.scr start

Add goldengate application resource.

--As Oracle
/u00/app/11.2.0/GRID/bin/crsctl add resource ggateapp -type cluster_resource -attr  "ACTION_SCRIPT=/u00/app/11.2.0/GRID/crs/public/ggaction.scr, CHECK_INTERVAL=30, START_DEPENDENCIES='hard(mvggatevip) pullup(mvggatevip)', STOP_DEPENDENCIES='hard(mvggatevip)'"

--As Oracle 
dummyxt271 | GRID | /u00/app/11.2.0/GRID/crs/public
crsctl start resource ggateapp
CRS-2672: Attempting to start 'ggateapp' on 'dummyxt274'
CRS-2676: Start of 'ggateapp' on 'dummyxt274' succeeded

--As Oracle 
crsctl status resource ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on dummyxt274

--confirm of Goldengate has started on perticular Node. 
dummyxt274 | ORA102 | /export/home/oracle
ps -ef | grep mgr
oracle   12582     1  0 10:46 ?        00:00:00 ./mgr PARAMFILE /ORAGG/product/11.2.1/gghome11/dirprm/mgr.prm REPORTFILE /ORAGG/product/11.2.1/gghome11/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

--Relocate Goldengate Application. 
crsctl relocate resource ggateapp -f
CRS-2673: Attempting to stop 'ggateapp' on 'dummyxt274'
CRS-2677: Stop of 'ggateapp' on 'dummyxt274' succeeded
CRS-2673: Attempting to stop 'mvggatevip' on 'dummyxt274'
CRS-2677: Stop of 'mvggatevip' on 'dummyxt274' succeeded
CRS-2672: Attempting to start 'mvggatevip' on 'dummyxt271'
CRS-2676: Start of 'mvggatevip' on 'dummyxt271' succeeded
CRS-2672: Attempting to start 'ggateapp' on 'dummyxt271'
CRS-2676: Start of 'ggateapp' on 'dummyxt271' succeeded

--Test Relocate on each node. 
crsctl relocate resource ggateapp -n Node_name -f

Goldengate Action script.

#!/bin/sh
#############################################################################
#@(#) Clusterware script to manage Golden Gate v1.0
# Script to Manage Golden Gate from Clusterware
# change required Environment. 
#############################################################################
GGS_HOME=/ORAGG/product/11.2.1/gghome11
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${GGS_HOME}
ORACLE_HOME=/u00/app/oracle/product/11.2.0/DB
export GGS_HOME LD_LIBRARY_PATH ORACLE_HOME
# Function runCmd to run the Golden Gate Script Execution
runCmd()
{
ggsciCmd=$1
result=`${GGS_HOME}/ggsci << EOF
${ggsciCmd}
exit
EOF`
}
# Function CheckMgr to check the Golden Gate Manager process
checkMgr()
{
if ( [ -f "${GGS_HOME}/dirpcs/MGR.pcm" ] )
then
pid=`cut -f8 "${GGS_HOME}/dirpcs/MGR.pcm"`
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |cut -d " " -f2` ]
then
exit 0
else
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |cut -d " " -f1` ]
then
exit 0
else
exit 1
fi
fi
else
exit 1
fi
}
# Main Code to get the input and run it
case $1 in
'start') runCmd 'start manager'
runCmd 'start er *'
sleep 5
checkMgr
;;
'stop') runCmd 'stop er *'
runCmd 'stop er *!'
runCmd 'stop manager!'
exit 0
;;
'check') checkMgr
;;
'clean') runCmd 'stop er *'
runCmd 'stop er *!'
runCmd 'kill er *'
runCmd 'stop manager!'
exit 0
;;
'abort') runCmd 'stop er *!'
runCmd 'kill er *'
runCmd 'stop manager!'
exit 0
;;
esac
# End of Script
#############################################################################

Reference: Oracle GoldenGate Best Practices: Oracle GoldenGate high availability using Oracle Clusterware [ID 1313703.1]

Exadata Upgrade / Upgrade GI to 11.2.0.3

Few days back we did upgrade from 11.2.0.2 to 11.2.0.3 on Exadata x2 half rack.

This was direct upgrade from 11.2.0.2 to 11.2.0.3 with Bundle Patch 7  /  patch 13992240

Some High level steps. 

• Oracle Grid Infrastructure 11.2.0.3 Installation (Dont run rootupgrade.sh at the end)
• Install BP7 on New Grid home.
• Run rootupgrade.sh (Outage Time)
• 11.2.0.3 database software Installation. 
• Install BP7 on New Oracle home
• Upgrade database to 11.2.0.3 (Outage Time)

Attached is Detail Upgrade Document on Google docs.

Upgrade Exadata from 11.2.0.2 to 11.2.0.3

Let me know if you any difficulties viewing above Document on jonyjt@gmail.com

OGG-01496 Failed to open target trail file

My Datapump was abending after start, because It was looking for MISSING remote trailfile at perticular RBA , that trailfile was deleted.

If you want to reposition extract please visit. Re-position Extract at particular RBA

here is from source side.

GGSCI (RACD1.localdomain.com) 6> INFO RMTTRAIL *

       Extract Trail: ./dirdat/DE
             Extract: RACDE
               Seqno: 1
                 RBA: 1049
           File Size: 10M

       Extract Trail: ./dirdat/DP
             Extract: RACDP
               Seqno: 0
                 RBA: 4499
           File Size: 10M

some output from ggserr.log

2012-10-17 19:47:58  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT RACDP starting.
2012-10-17 19:47:58  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP starting.
2012-10-17 19:47:59  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP started.
2012-10-17 19:48:04  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, racdp.prm:  Socket buffer size set to 27985 (flush size 27985).
--This is the error....
2012-10-17 19:48:04  ERROR   OGG-01496  Oracle GoldenGate Capture for Oracle, racdp.prm:  Failed to open target trail file ./dirdat/DP000000, at RBA 4499.
2012-10-17 19:48:04  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, racdp.prm:  PROCESS ABENDING.

I had trailfile missing on Target side, which DATAPUMP was looking for.

TEST:/u00/app/gghome/dirdat :TEST$ll
total 12
-rw-rw-rw-  1 oracle oinstall 1167 Aug  2 09:49 XP000000
drwxr-xr-x 14 oracle oinstall 4096 Oct 17 16:10 ..
drwxrwxr-x  2 oracle oinstall 4096 Oct 17 16:52 .

So i did this below on Source side and start datapump successfully.

GGSCI (RACD1.localdomain.com) 11> ALTER EXTRACT RACDP ETROLLOVER

2012-10-17 19:54:30  INFO    OGG-01520  Rollover performed.  For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file;  it will not happen automatically.
EXTRACT altered.


GGSCI (RACD1.localdomain.com) 12> INFO RMTTRAIL *

       Extract Trail: ./dirdat/DE
             Extract: RACDE
               Seqno: 1
                 RBA: 1049
           File Size: 10M

       Extract Trail: ./dirdat/DP
             Extract: RACDP
               Seqno: 1
                 RBA: 0
           File Size: 10M



GGSCI (RACD1.localdomain.com) 13> START RACDP

Sending START request to MANAGER ...
EXTRACT RACDP starting


GGSCI (RACD1.localdomain.com) 14> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     RACDE       00:00:00      00:00:01
EXTRACT     RUNNING     RACDP       00:00:00      00:00:02


2012-10-17 19:55:20  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT RACDP starting.
2012-10-17 19:55:20  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP starting.
2012-10-17 19:55:31  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP started.
2012-10-17 19:55:39  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, racdp.prm:  Socket buffer size set to 27985 (flush size 27985).
2012-10-17 19:55:40  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, racdp.prm:  No recovery is required for target file ./dirdat/DP000001, at RBA 0 (file not opened).
2012-10-17 19:55:40  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, racdp.prm:  Output file ./dirdat/DP is using format RELEASE 10.4/11.1.

Change Data guard Protection Mode

There are 3 ways of shipping redo data to a physical standby:

LGWR SYNC = Max Availability & Max Protection
LGWR ASYNC = Max Performance
ARCH


Maximum Protection = This protection level uses a synchronous replication process to ensure that no data loss will occur if the primary database fails. It also enforces rules that prevent multiple failure events from causing data loss. This protection level will never allow a primary database to acknowledge commit success for an unprotected transaction.To provide this level of protection, the redo data that is needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before Oracle can acknowledge commit success to the application. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to the standby redo log of at least one standby database.


Maximum Availability = This protection level uses a synchronous replication process that provides zero data loss protection without compromising the availability of the primary database. Like Maximum Protection, commit success is not acknowledged to the application until the redo that is needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one standby database. Unlike Maximum Protection, however, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. The primary database will stall for a maximum of net_timout seconds (user configurable) before proceeding, in order to maintain availability of the primary database. Data Guard automatically resynchronizes primary and standby databases when the connection is restored. Data loss is possible if a second failure occurs before the resynchronization process is complete.

Maximum performance = This protection mode (the default) is an asynchronous replication process that provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by acknowledging commit success as soon as the redo data that is needed to recover that transaction is written to the local online redo log without waiting for confirmation by the standby that the data is protected. The redo data stream of the primary database is transmitted to the standby database directly from the Oracle in-memory log buffer as quickly as it is generated. .

The default is Maximum Performance.

SYNC | ASYNC = Specifies whether the redo data is to be received at the destination before the transaction is committed. ASYNC is default.

AFFIRM | NOAFFIRM = Control whether the redo destination acknowledges received redo data. NOAFFIRM is the default for ASYNC if not specified. AFFIRM is the default for SYNC for if not specified.

NET_TIMEOUT = Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN = Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 seconds.

Use LGWR SYNC if you use either Maximum Protection OR Maximum Availability.
so here LGWR is directly connected with RFS,henc there will be no LNS process.

On the primary database, query the RECOVERY_MODE column in the V$ARCHIVE_DEST_STATUS view, which displays the standby database's operation as MANAGED_RECOVERY for Redo Apply and MANAGED REAL TIME APPLY for real-time apply.


By default standby is running in MAXIMUM PERORMANCE Mode,

SQL> alter system set log_archive_dest_2='SERVICE=STDBY LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

============Change to MAXIMUM AVAILABILITY========================

STDBY-> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

System altered.

--On Primary Change Log shipping attributes. 

PROD-> alter system set log_archive_dest_2='SERVICE=STDBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;

System altered.

--On Primary Change Mode.
PROD-> alter database set standby database to maximize availability;

Database altered.

---Start Recovery Process on STANDBY. 
STDBY-> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

STDBY-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

--In Primary database Alertlog.

ALTER SYSTEM SET log_archive_dest_2='SERVICE=STDBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH;
Wed Aug 15 06:35:30 2012
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected to archive thread 1 sequence 19
LGWR: Standby redo logfile selected for thread 1 sequence 19 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 19 (LGWR switch)
  Current log# 1 seq# 19 mem# 0: /u01/test/redo/redo01.log
Wed Aug 15 06:35:30 2012
Archived Log entry 24 added for thread 1 sequence 18 ID 0xbacde77 dest 1:
Wed Aug 15 06:35:48 2012
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected to archive thread 1 sequence 20
LGWR: Standby redo logfile selected for thread 1 sequence 20 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 20 (LGWR switch)
  Current log# 2 seq# 20 mem# 0: /u01/test/redo/redo02.log
Wed Aug 15 06:35:49 2012
Archived Log entry 26 added for thread 1 sequence 19 ID 0xbacde77 dest 1:
Wed Aug 15 06:36:29 2012
alter database set standby database to maximize availability
Completed: alter database set standby database to maximize availability

========================Change it to MAXIMUM PROTECTION ==============================

STDBY->select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Elapsed: 00:00:00.05

PROD-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

----STOP RECOVERY PROCESS ON STANDBY. 
STDBY->ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Elapsed: 00:00:00.22

--On Primary Change Mode.
PROD-> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.

PROD-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION   MAXIMUM PROTECTION

---Start Recovery Process on STANDBY.  
STDBY->ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Elapsed: 00:00:06.07
STDBY->select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION   MAXIMUM PROTECTION

Elapsed: 00:00:00.00

--In Standby database Alertlog.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Oct 01 13:29:59 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u00/app/oracle/diag/rdbms/stdby/STDBY/trace/STDBY_mrp0_3240.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1054907
Errors in file /u00/app/oracle/diag/rdbms/stdby/STDBY/trace/STDBY_mrp0_3240.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (STDBY)
Waiting for MRP0 pid 3240 to terminate
Managed Standby Recovery Canceled (STDBY)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Oct 01 13:34:39 2012
Changing standby controlfile to MAXIMUM PROTECTION mode
Mon Oct 01 13:34:39 2012
Archived Log entry 23 added for thread 1 sequence 30 ID 0xbacde77 dest 1:
RFS[1]: Selected log 4 for thread 1 sequence 31 dbid 195900002 branch 784012804
Mon Oct 01 13:34:59 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (STDBY)
Mon Oct 01 13:34:59 2012
MRP0 started with pid=28, OS id=3256
MRP0: Background Managed Standby Recovery process started (STDBY)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/stdby/archive/1_30_784012804.dbf
Media Recovery Waiting for thread 1 sequence 31 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 31 Reading mem 0
  Mem# 0: /u01/stdby/redoredo04.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Mon Oct 01 13:45:08 2012
Standby controlfile consistent with primary
RFS[1]: Selected log 5 for thread 1 sequence 32 dbid 195900002 branch 784012804
Mon Oct 01 13:45:10 2012
Archived Log entry 24 added for thread 1 sequence 31 ID 0xbacde77 dest 1:
Mon Oct 01 13:45:11 2012
Media Recovery Waiting for thread 1 sequence 32 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 32 Reading mem 0
  Mem# 0: /u01/stdby/redoredo05.log
  

===================================================================================================================