oracle 归档日志突增排查
归档日志突增排查
排查思路，根据归档日志生成数量判断从哪天开始出现的增高，并结合AWR报告中的Segments by DB Blocks Changes（数据库快更改情况）进行判断哪个对象造成。
1.归档日志数量生成查询
-- 按天统计
select to_char(COMPLETION_TIME, 'yyyymmdd'), count(*)
from v$archived_log t
where COMPLETION_TIME > sysdate - 7
group by to_char(COMPLETION_TIME, 'yyyymmdd')
order by to_char(COMPLETION_TIME, 'yyyymmdd');
TO_CHAR( COUNT(*)
-------- ----------
20240730 18
20240731 132
20240801 116
20240802 132
20240803 144
20240804 88
20240805 82
20240806 128
20240807 56
--由上看出为7月30日后归档剧增，可根据天和小时具体排查哪个时间段开始增加。
-- 按天和小时统计
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"
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD')
ORDER BY 1 DESC;
--输出结果
DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
2024-08-07 3 3 3 3 2 3 3 2 3 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2024-08-06 3 2 3 2 3 3 2 3 2 3 3 2 3 4 3 2 3 3 2 3 2 3 3 2
2024-08-05 4 2 3 3 3 3 3 3 3 3 2 3 3 2 2 0 1 3 3 2 3 2 3 3
2024-08-04 3 3 3 3 3 3 3 3 3 2 3 3 3 4 3 3 3 3 3 3 3 3 3 3
2024-08-03 3 3 3 3 3 3 3 3 3 3 3 3 3 4 2 3 3 3 3 3 3 3 3 3
2024-08-02 3 3 2 3 2 3 3 2 3 2 3 3 2 4 3 2 3 2 3 3 3 3 3 3
2024-08-01 3 3 2 3 3 2 3 2 3 2 3 2 3 4 3 2 3 2 0 0 2 3 3 2
2024-07-31 3 3 2 3 3 2 3 2 3 2 3 2 3 6 2 3 2 3 3 3 2 3 3 2
2024-07-30 1 1 0 1 0 0 1 0 1 0 1 0 1 3 0 1 0 1 0 1 2 3 3 2
2024-07-29 1 0 0 1 0 1 0 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0
2024-07-28 1 0 1 0 1 0 0 1 0 1 0 1 0 2 0 1 0 1 0 0 1 1 0 1
2024-07-27 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 0 1 0 1 0
由上看出为30日20点前后归档日志增加，排查30日前后的AWR报告，进行排查数据库快更改情况，比对具体对象。
--生成awr报告
@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/awrrpt.sql
