Redo Log Generation and Size per Day

Redo Logs Generation count per Hour

SELECT TRUNC (first_time) as Date, inst_id, TO_CHAR (first_time, 'Dy') as Day,

COUNT (1) as Total,
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '00', 1, 0)) "H0",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '01', 1, 0)) "H1",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '02', 1, 0)) "H2",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '03', 1, 0)) "H3",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '04', 1, 0)) "H4",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '05', 1, 0)) "H5",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '06', 1, 0)) "H6",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '07', 1, 0)) "H7",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '08', 1, 0)) "H8",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '09', 1, 0)) "H9",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '10', 1, 0)) "H10",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '11', 1, 0)) "H11",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '12', 1, 0)) "H12",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '13', 1, 0)) "H13",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '14', 1, 0)) "H14",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '15', 1, 0)) "H15",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '16', 1, 0)) "H16",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '17', 1, 0)) "H17",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '18', 1, 0)) "H18",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '19', 1, 0)) "H19",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '20', 1, 0)) "H20",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '21', 1, 0)) "H21",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '22', 1, 0)) "H22",
SUM (DECODE (TO_CHAR (first_time, 'HH24'), '23', 1, 0)) "H23",
ROUND (COUNT (1) / 24, 2) as Avg
FROM gv$log_history
WHERE thread# = inst_id
AND first_time > sysdate -30
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')

ORDER BY 1,2;

select GROUP#, THREAD#, SEQUENCE#, BYTES/1024/1024 as SIZE_MB, BLOCKSIZE, MEMBERS, ARCHIVED, STATUS, FIRST_CHANGE#, FIRST_TIME, NEXT_CHANGE#, NEXT_TIME, CON_ID from v$log;

To check Archives Size Generation per Day

select
  to_char(first_time,'DD-MM-YYYY') as day,
  COUNT(*) as TOT,
  COUNT(*)*(SELECT distinct (TO_CHAR(bytes / 1024 / 1024 /1024)) FROM v$log) as SIZE_REQUIRED_GB
from v$log_history
group by to_char(first_time,'DD-MM-YYYY')
order by day;

To check Log switches time difference

select  b.recid,
        to_char(b.first_time, 'dd-mon-yy hh:mi:ss') as start_time,
        to_char(a.first_time, 'dd-mon-yy hh:mi:ss') as end_time,
        round(((a.first_time-b.first_time)*25)*60,2) as minutes,
    a.recid,
from    v$log_history a, v$log_history b
where   a.recid = b.recid + 1
order   by a.first_time asc;