归档日志异常增长的问题排查

1.归档日志产生的数量

1.1.按小时统计当天归档日志文件产生数量

SELECT TO_CHAR (FIRST_TIME, 'yyyymmddhh24'), COUNT (*)
    FROM sys.v_$archived_log t
   WHERE t.FIRST_TIME > TRUNC (SYSDATE)
   AND T.DEST_ID=1
GROUP BY TO_CHAR (FIRST_TIME, 'yyyymmddhh24')
ORDER BY TO_CHAR (FIRST_TIME, 'yyyymmddhh24');

1.2.按天统计产生的归档日志文件数量

SELECT TO_CHAR (COMPLETION_TIME, 'yyyymmdd'), COUNT (*)
    FROM v$archived_log t
   WHERE COMPLETION_TIME > SYSDATE - 5
   AND DEST_ID=1
GROUP BY TO_CHAR (COMPLETION_TIME, 'yyyymmdd')
ORDER BY TO_CHAR (COMPLETION_TIME, 'yyyymmdd');

1.3.按天和小时统计归档日志文件数量

SELECT    TO_CHAR(FIRST_TIME,'YYYY-MM-DD') "DAY",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999') "00",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999') "01",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999') "02",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999') "03",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999') "04",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999') "05",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999') "06",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999') "07",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999') "08",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999') "09",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999') "10",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999') "11",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999') "12",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999') "13",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999') "14",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999') "15",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999') "16",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999') "17",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999') "18",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999') "19",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999') "20",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999') "21",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999') "22",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999') "23",
                COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD') 
ORDER BY 1 DESC;

2.归档日志产生的大小

2.1.统计每小时归档日志产生的大小

SELECT   TRUNC (COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,
           ROUND (SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024, 0) SIZE_IN_MB
    FROM   V$ARCHIVED_LOG
    WHERE  DEST_ID=1
GROUP BY   TRUNC (COMPLETION_TIME, 'HH')
ORDER BY   1;

2.2.统计每天归档日志产生的大小

SELECT   TRUNC (COMPLETION_TIME) ARCHIVED_DATE,
           ROUND (SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024, 0) SIZE_IN_MB
    FROM   V$ARCHIVED_LOG
    WHERE DEST_ID=1
GROUP BY   TRUNC (COMPLETION_TIME)
ORDER BY   1;