Redo logs

Redo logs are critical for database and instance recovery. Proper redo log configuration is also critical for performance. Some recommendations for redo logs configuration include:

Redo file size

Your choice of redo file size depends on your trade-off between performance and availability, specifically the time needed to recover the Oracle instance in the event of a failure. For performance, some installations opt to create large redo logs to reduce the frequency of log switches. However, doing so means that there are potentially more transactions in the redo logs that have to be replayed during recovery. The general rule for sizing redo log files is to look at the time it takes to switch log files. By issuing the following query you can see how often the redo log files are changing. As a general rule the logs should not be switching more that once every twenty to thirty minutes:

   select * from v$loghist
   order by first_time desc
   

The following is an example of the output:

   THREAD#   SEQUENCE#   FIRST_CHANGE#              FIRST_TIME   SWITCH_CHANGE#
         1          97         7132082  10/20/2011 11:47:53 PM          7155874
         1          96         7086715  10/20/2011 11:32:04 PM          7132082
         1          95         7043684  10/20/2011 11:15:07 PM          7086715
         1          94         6998984  10/20/2011 11:00:57 PM          7043684
         1          93         6950799  10/20/2011 10:48:03 PM          6998984
   

In the example above, the logs switched every fifteen minutes.