Optimal redo logfile size in Oracle

If you have a small redo log file then you will see frequent log switches, in case of large redo logfile you might be at risk of losing data during instance crash. The optimal redo logfile size should not have more than 5 switches per hour.

You can use below query to find the number of log switches per hour

col day format a15;
col hour format a4;
col total format 999;
select to_char(first_time,'yyyy-mm-dd') day, to_char(first_time,'hh24') hour, count(*) total
from v$log_history
group by to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
order by to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24') asc;

To resize the redo logfile size follow the steps in below link

http://www.oracleracexpert.com/2013/07/thread-1-cannot-allocate-new-log.html

In Oracle 10g, the Redo Logfile Size Advisor introduced and using this you can determine the optimal redo log size based upon FAST_START_MTTR_TARGET parameter. You must set a non-zero value to enable redo log file size advisor.

FAST_START_MTTR_TARGET – this parameter enables you to specific number of seconds the database takes to perform crash recovery. Based up on this value Oracle determines the checkpoint writes to meet the target.

If you DONT set FAST_START_MTTR_TARGET then OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY will not populated with recommend redo log file size.

SQL> show parameter FAST_START_MTTR_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0

SQL> select optimal_logfile_size from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------------------

User must set FAST_START_MTTR_TARGET to a non-zero value then OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY will be populated with recommend redo log file size.

Here I am setting FAST_START_MTTR_TARGET=60 (sec), you will see that OPTIMAL_LOGFILE_SIZE will be populated with recommended value.

SQL> alter system set FAST_START_MTTR_TARGET=60 scope=both;
System altered.

SQL> select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------
151

Now the checkpoints are driven by FAST_START_MTTR_TARGET parameter.

In some cases user will see many log switches during batch job window and there is no log switches out of batch job window. In this case you need to optimal value for redo log size and may need to set archive_lag_target to force redo log switches to increase the frequency during non-batch job window.

For more information on archive_lag_target refer below Oracle document.
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams009.htm

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com