Redolog File Advisor

When we set "fast_start_mttr_target" parameter MTTR Advisor  and Redolog file size advisor is also enabled on database 

Use following Query to check size of the Redolog group & location on database
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group# 
ORDER BY a.GROUP# ASC;

GROUP# THREAD#  SEQUENCE# ARC STATUS       REDOLOG_FILE_NAME                    SIZE_MB
---------- ---------- ---------- --- ---------------- ---------------------------------
  1     1       29 NO  INACTIVE       +FRA/P1TNCD/ONLINELOG/group_1.257.903695363    50
  1     1       29 NO  INACTIVE       +SDXH/P1TNCD/ONLINELOG/group_1.262.903695363  50
  2     1       30 NO  CURRENT       +FRA/P1TNCD/ONLINELOG/group_2.258.903695363    50
  2     1       30 NO  CURRENT       +SDXH/P1TNCD/ONLINELOG/group_2.263.903695363  50
  3     2       23 NO  INACTIVE       +SDXH/P1TNCD/ONLINELOG/group_3.269.903695731  50
  3     2       23 NO  INACTIVE       +FRA/P1TNCD/ONLINELOG/group_3.259.903695731    50
  4     2       24 NO  CURRENT       +SDXH/P1TNCD/ONLINELOG/group_4.270.903695731  50
  4     2       24 NO  CURRENT       +FRA/P1TNCD/ONLINELOG/group_4.260.903695731    50

when you look at column "v$instance_recovery.OPTIMAL_LOGFILE_SIZE" this gives optimal size of the redolog on current workload on database. 

desc v$instance_recovery;
SQL> select  OPTIMAL_LOGFILE_SIZE,  TARGET_MTTR from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE TARGET_MTTR
-------------------- -----------
236       13

For current database it is suggested that optimal size of the redolog's is 236 MB.
current size of the redolog on database is 50 MB drop and recreate to 236 MB, which is recommended by redolog size advisory


####

select
   Start_Date,
   Start_Time,
   Num_Logs,
   Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes,
   Vdb.NAME AS Dbname
FROM
   (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time,
   COUNT(Vlh.Thread#) Num_Logs
FROM
   V$log_History Vlh
GROUP BY
   To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
   To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
   V$log Vl,
   V$database Vdb
WHERE
   Vl.Group# = 1
ORDER BY
   Log_Hist.Start_Date,
   Log_Hist.Start_Time;

START_DATE START   NUM_LOGS MBYTES DBNAME

---------- ----- ---------- ---------- ---------

2016-02-13    10:00   8               400 P1TNCD

2016-02-13    11:00   1                 50 P1TNCD

2016-02-13    14:00   1                 50 P1TNCD

2016-02-13    20:00   2               100 P1TNCD

2016-02-13    23:00   1                 50 P1TNCD

2016-02-14    00:00   2               100 P1TNCD

2016-02-14    13:00   5               250 P1TNCD

2016-02-14    14:00   1                 50 P1TNCD

2016-02-14    17:00   2               100 P1TNCD

2016-02-14    20:00   1                 50 P1TNCD

2016-02-14    21:00   1                 50 P1TNCD