[terry筆記]ArchiveLog歸檔日志激增解決思路

浸心閣 2015-07-06

展開全文

歸檔日志激增的危害是巨大的,，最嚴重的結果就是數(shù)據(jù)庫無法正常工作,，導致整個系統(tǒng)無法正常工作，其次就算數(shù)據(jù)庫可以正常工作,，但激增的歸檔會對磁盤產(chǎn)生大量消耗，導致性能下降,。
歸檔日志激增一般是因為異常的dml導致,，異常的dml使數(shù)據(jù)被頻繁的增刪改，以至redo日志頻繁切換,，再至歸檔日志激增,，最終磁盤爆滿，數(shù)據(jù)庫無法正常工作,。

1.首先查詢每天的redo日志切換頻率,，定位異常的時間點，并且還可以粗濾計算出日志量（就算無法解決程序問題,，也可以暫時擴大磁盤容量,，先讓數(shù)據(jù)庫正常歸檔工作，甚至切換到無歸檔模式）,。

select to_char(first_time,'mmdd hh24'),count(*) from v$log_history group by to_char(first_time,'mmdd hh24') order by 1;

2.查詢產(chǎn)生日志最多的會話,，基本可以確定是什么方向的應用

col program for a30
col machine for a30
select a.sid,a.logon_time,a.username,a.program,a.machine,a.status,round(b.value/1024/1024) mb from v$session a,(select * from (select * from v$sesstat where statistic# = (select statistic# from v$statname where name='redo size') order by value desc) where rownum<=100) b where a.sid=b.sid;

3.截取歸檔日志激增時候的AWR報告,，在sql統(tǒng)計中仔細排查dml類型sql，并查詢嫌疑的sql的執(zhí)行計劃,、統(tǒng)計信息等（@?/rdbms/admin/awrsqrpi）,，還可以找出嫌疑sql所對應的表信息是否有異常。還可以對應之前正常時候其sql的統(tǒng)計信息進行對比,。

4.查看產(chǎn)生大量歸檔日志的會話所打開的游標（所執(zhí)行的sql）,，這樣更加確定了異常sql和異常會話的關系。

select sql_id,sql_text from v$open_cursor where sid=xxx;

5.查看異常表的信息：

select avg_row_len from dba_tables where table_name=upper('table_name'); /*表的長度（字節(jié)）*/
select index_name from dba_indexes where table_name=upper('table_name'); /*其擁有的索引,，索引越多,，歸檔越多*/

6.挖掘歸檔內(nèi)容：

exec dbms_logmnr.ADD_LOGFILE('/datavg/archive/1_11111.dbf');
exec DBMS_LOGMNR.START_LOGMNR;
select substr(sql_redo,1,30),count(*) from v$logmnr_contents group by substr(sql_redo,1,30) ;