Tuning Recovery - Expert Oracle RAC Performance Diagnostics and Tuning - page 321

Tuning Instance Recovery
One of the primary reasons for implementing a RAC solution is continued availability. When one server or instance
fails, there are other servers and instances in the configuration that will support and continue to provide data
availability to the application and users. This means that when one instance or server fails, one of the surviving
instances should be able to read the changes made by the failed instance and perform any data recovery. This is
the reason why it's a requirement that redo log files are located on shared storage and accessible by all instances in
the cluster.
When a server or instance fails, users connected to the instance can failover or get reconnected transparently
to one of the other instances depending on the type of connection implementation. As discussed in the previous
section, instance recovery is performed in two phases: cache recovery followed by transaction recovery. Until instance
recovery is completed, data changes made on instance 1 are not available or written to the datafiles.
What is being recovered? Data blocks changed by committed transactions (and hence written to redo log files)
but not yet written to datafiles will need to be written to datafiles. This would mean the size and content of the redo
log files would have an impact on the instance recovery. The first step would be to tune the redo log activity, what size
of redo and how frequently the redo log files should switch, and how to size the redo logs.
Like most performance optimization situations, there is a positive and negative side to optimizing the redo log
activity. Keeping the redo log files small and switching the redo log files more frequently (frequent checkpoint) would
help reduce the recovery time; however, the I/O activity on the database files would increase.
Depending on the number of datafiles in a database, a checkpoint can be a highly resource intensive operation
because all datafile headers are frozen during the checkpoint. The idea is to balance or have a performance trade-off
when it comes to tuning frequency of log switches. Ideally, it would be a good practice to have about four to five redo
log switches per hour. Using the query following, the hourly redo log switches could be determined for a week:
Script: MVRACPDnTap_redologswitches.sql
select
substr(to_char(first_time,'MM/DD-Day'),1,9) day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23",