Find the log switches with following query:
SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",<br>COUNT (1) "Total",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23"<br>FROM gv$log_history<br>GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')<br>ORDER BY 1;
Calculate Fast Recovery Area size needs by monitoring the Archive logs production. Use the following query to see a per hour Archive data production:
SELECT TO_CHAR(FIRST_TIME,'MM-DD') DAY,<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',MB,0))) AS "00",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',MB,0))) AS "01",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',MB,0))) AS "02",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',MB,0))) AS "03",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',MB,0))) AS "04",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',MB,0))) AS "05",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',MB,0))) AS "06",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',MB,0))) AS "07",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',MB,0))) AS "08",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',MB,0))) AS "09",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',MB,0))) AS "10",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',MB,0))) AS "11",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',MB,0))) AS "12",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',MB,0))) AS "13",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',MB,0))) AS "14",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',MB,0))) AS "15",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',MB,0))) AS "16",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',MB,0))) AS "17",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',MB,0))) AS "18",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',MB,0))) AS "19",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',MB,0))) AS "20",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',MB,0))) AS "21",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',MB,0))) AS "22",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',MB,0))) AS "23",<br>ROUND(SUM(MB)) as "Tot"<br>FROM (SELECT DISTINCT SEQUENCE#, (BLOCKS*BLOCK_SIZE)/1048576 mb, FIRST_TIME FROM V$ARCHIVED_LOG)<br>GROUP BY TO_CHAR(FIRST_TIME,'MM-DD')<br>ORDER BY 1;
Same information can be extracted by:
select SUM (BLOCKS*BLOCK_SIZE)/1048576 mb, trunc(FIRST_TIME,'HH24') FROM V$ARCHIVED_LOG<br>group by trunc(FIRST_TIME,'HH24')<br>order by 2