使用歸檔日誌分析解決歸檔日誌迅速增長問題(logmnr)

在資料庫執行維護中，常常遇到日誌迅猛增長的問題，迅猛增長的日誌，將會把日誌目錄撐爆，導致資料庫日誌無法切換，最終導致資料庫停止執行和響應。這個問題的大部分問題是 BAD SQL造成的。我們可以透過歸檔日誌的分析，迅速定位bad sql，提交給開發者進行改進。

使用DBMS_LOGMNR包進行日誌分析。

LogMiner包含兩個PL/SQL包和幾個檢視：

1、dbms_logmnr_d包，這個包只包括一個用於提取資料字典資訊的過程，即dbms_logmnr_d.build()過程。

2、dbms_logmnr包，它有三個過程：

add_logfile(name varchar2, options number) - 用來新增/刪除用於分析的日誌檔案；

start_logmnr(start_scn number, end_scn number, start_time number,end_time number, dictfilename varchar2, options number) - 用來開啟日誌分析，同時確定分析的時間/SCN視窗以及確認是否使用提取出來的資料字典資訊。

end_logmnr() - 用來終止分析會話，它將回收LogMiner所佔用的記憶體。

與LogMiner相關的資料字典。

1、v$logmnr(dictionary，LogMiner可能使用的資料字典資訊，因logmnr可以有多個字典檔案，該檢視用於顯示這方面資訊。

2、v$logmnr_parameters，當前LogMiner所設定的引數資訊。

3、v$logmnr_logs，當前用於分析的日誌列表。

4、v$logmnr_contents，日誌分析結果。

[@more@]

日誌分析的步驟：

一。產生字典檔案（非必須）

如果想要使用字典檔案，資料庫至少應該出於MOUNT狀態。然後執行dbms_logmnr_d.build過程將資料字典資訊提取到一個外部檔案中。下面是具體分析步驟：

1、確認設定了初始化引數：UTL_FILE_DIR，並確認Oracle對改目錄擁有讀寫許可權，然後啟動例項。示例中UTL_FILE_DIR引數如下：

SQL> show parameter utl

NAME TYPE VALUE

------------------------ ----------- ------------------------------

utl_file_dir string /data6/cyx/logmnr

這個目錄主要用於存放dbms_logmnr_d.build過程所產生的字典資訊檔案，如果不用這個，則可以不設，也就跳過下面一步。

2、生成字典資訊檔案：

exec dbms_logmnr_d.build(dictionary_filename =>'dic.ora',dictionary_location => '/rman/log/');

其中dictionary_location指的是字典資訊檔案的存放位置,它必須完全匹配UTL_FILE_DIR的值

二。新增需要分析的日誌檔案

exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93220_643131044.dbf',options=>dbms_logmnr.new);

exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93221_643131044.dbf',options=>dbms_logmnr.addfile);

這裡的options選項有三個引數可以用：

NEW - 表示建立一個新的日誌檔案列表

ADDFILE - 表示向這個列表中新增日誌檔案，如下面的例子

REMOVEFILE - 和addfile相反。

三。進行日誌分析

當你新增了需要分析的日誌檔案後，我們就可以讓LogMiner開始分析了：

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/data6/cyx/logmnr/dic.ora');

PL/SQL procedure successfully completed.

如果你沒有使用字典資訊檔案（此時我們只需要啟動例項就可以了），那麼就不需要跟dictfilename引數：

SQL> exec dbms_logmnr.start_logmnr();

PL/SQL procedure successfully completed.

四。查詢日誌分析結果，幾個檢視中v$logmnr_contents（記錄日誌分析結果）是最重要的。

可以透過下面的語句查詢，頻繁執行的SQL.

Select substr(sql_redo,1,100),count(1) from v$logmnr_contents group by substr(sql_redo,1,100) having count(1)>100 order by 2;

查詢出BAD SQL ，可以透過ALL_SOURCE檢視查詢出包含這些語句的procedure和function，定位錯誤源，提供給開發者。

一次日誌分析的例子（不使用字典檔案）：

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93222_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93223_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93224_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93225_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93226_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93227_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93228_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93229_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.start_logmnr;

SQL>Select substr(sql_redo,1,100),count(1) from v$logmnr_contents group by substr(sql_redo,1,100) having count(1)>100 order by 2;

update "UNKNOWN"."OBJ# 116803" set "COL 30" = HEXTORAW('3336') where "COL 30" IS

NULL and ROWID = 'A

SUBSTR(SQL_REDO,1,100)

--------------------------------------------------------------------------------

COUNT(1)

----------

19754

20567

update "UNKNOWN"."OBJ# 116803" set "COL 29" = HEXTORAW('414141') where "COL 29"

IS NULL and ROWID =

355475

SQL> select object_name,object_type,owner from all_objects where object_id=116803;

OBJECT_NAME OBJECT_TYPE

------------------------------ -------------------

OWNER

------------------------------

DM_USER_CREDIT_TEMP TABLE

CREDIT

問題出在credit.dm_user_credit_temp 表，透過查詢all_source可以查詢出使用了這個表的PROCEDURE和function.

SQL>exec end_logmnr() ；