Redo Logfile Size Advisor
For checking the optimal logfile size you need to set the FAST_START_MTTR_TARGET parameter.If its zero then optimal_logfile_size not returned any row.
SQL> select optimal_logfile_size from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------
1 row selected.
FAST_START_MTTR_TARGET is used to control the duration of startup after instance crash(like shutdown abort);
It’s help to take number of seconds database take to up.
SQL> Show parameter fast_Start_mttr_target
NAME TYPE VALUE
------------------------------------ ----------- --------
fast_start_mttr_target integer 0
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
--------------------
49