分析Oracle資料庫日誌檔案(1) [轉]

guyuexue發表於2007-11-22
【簡 介】
  作為Oracle DBA,我們有時候需要追蹤資料誤刪除或使用者的惡意操作情況,此時我們不僅需要查出執行這些操作的資料庫賬號,還需要知道操作是由哪臺客戶端(IP地址等)發出的。針對這些問題,一個最有效實用而又低成本的方法就是分析Oracle資料庫的日誌檔案。本文將就Oracle日誌分析技術做深入探討。


一、如何分析即LogMiner解釋
從目前來看,分析Oracle日誌的唯一方法就是使用Oracle公司提供的LogMiner來進行, Oracle資料庫的所有更改都記錄在日誌中,但是原始的日誌資訊我們根本無法看懂,而LogMiner就是讓我們看懂日誌資訊的工具。從這一點上看,它和tkprof差不多,一個是用來分析日誌資訊,一個則是格式化跟蹤檔案。透過對日誌的分析我們可以實現下面的目的:
1、查明資料庫的邏輯更改;
2、偵察並更正使用者的誤操作;
3、執行事後審計;
4、執行變化分析。
不僅如此,日誌中記錄的資訊還包括:資料庫的更改歷史、更改型別(INSERT、UPDATE、DELETE、DDL等)、更改對應的SCN號、以及執行這些操作的使用者資訊等,LogMiner在分析日誌時,將重構等價的SQL語句和UNDO語句(分別記錄在V$LOGMNR_CONTENTS檢視的SQL_REDO和SQL_UNDO中)。這裡需要注意的是等價語句,而並非原始SQL語句,例如:我們最初執行的是“delete a where c1 <>'cyx';”,而LogMiner重構的是等價的6條DELETE語句。所以我們應該意識到V$LOGMNR_CONTENTS檢視中顯示的並非是原版的現實,從資料庫角度來講這是很容易理解的,它記錄的是元操作,因為同樣是“delete a where c1 <>'cyx';”語句,在不同的環境中,實際刪除的記錄數可能各不相同,因此記錄這樣的語句實際上並沒有什麼實際意義,LogMiner重構的是在實際情況下轉化成元操作的多個單條語句。
另外由於Oracle重做日誌中記錄的並非原始的物件(如表以及其中的列)名稱,而只是它們在Oracle資料庫中的內部編號(對於表來說是它們在資料庫中的物件ID,而對於表中的列來說,對應的則是該列在表中的排列序號:COL 1, COL 2 等),因此為了使LogMiner重構出的SQL語句易於識別,我們需要將這些編號轉化成相應的名稱,這就需要用到資料字典(也就說LogMiner本身是可以不用資料字典的,詳見下面的分析過程),LogMiner利用DBMS_LOGMNR_D.BUILD()過程來提取資料字典資訊。
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,日誌分析結果。
二、Oracle9i LogMiner的增強:
1、支援更多資料/儲存型別:連結/遷移行、CLUSTER表操作、DIRECT PATH插入以及DDL操作。在V$LOGMNR_CONTENTS的SQL_REDO中可以看到DDL操作的原句(CREATE USER除外,其中的密碼將以加密的形式出現,而不是原始密碼)。如果TX_AUDITING初始化引數設為TRUE,則所有操作的資料庫賬號將被記錄。
2、提取和使用資料字典的選項:現在資料字典不僅可以提取到一個外部檔案中,還可以直接提取到重做日誌流中,它在日誌流中提供了操作當時的資料字典快照,這樣就可以實現離線分析。
3、允許對DML操作按事務進行分組:可以在START_LOGMNR()中設定COMMITTED_DATA_ONLY選項,實現對DML操作的分組,這樣將按SCN的順序返回已經提交的事務。
4、支援SCHEMA的變化:在資料庫開啟的狀態下,如果使用了LogMiner的DDL_DICT_TRACKING選項,Oracle9i的LogMiner將自動對比最初的日誌流和當前系統的資料字典,並返回正確的DDL語句,並且會自動偵察並標記當前資料字典和最初日誌流之間的差別,這樣即使最初日誌流中所涉及的表已經被更改或者根本已經不存在,LogMiner同樣會返回正確的DDL語句。
5、在日誌中記錄更多列資訊的能力:例如對於UPDATE操作不僅會記錄被更新行的情況,還可以捕捉更多前影資訊。
6、支援基於數值的查詢:Oracle9i LogMiner在支援原有基於後設資料(操作、物件等)查詢的基礎上,開始支援基於實際涉及到的資料的查詢。例如涉及一個工資表,現在我們可以很容易地查出員工工資由1000變成2000的原始更新語句,而在之前我們只能選出所有的更新語句。
三、Oracle8i/9i的日誌分析過程
LogMiner只要在例項起來的情況下都可以執行,LogMiner使用一個字典檔案來實現Oracle內部物件名稱的轉換,如果沒有這個字典檔案,則直接顯示內部物件編號,例如我們執行下面的語句:
delete from "C"."A" where "C1" = ‘gototop’ and ROWID = 'AAABg1AAFAAABQaAAH';
如果沒有字典檔案,LogMiner分析出來的結果將是:
delete from "UNKNOWN"."OBJ# 6197" where "COL 1" = HEXTORAW('d6a7d4ae') and ROWID
= 'AAABg1AAFAAABQaAAH';

如果想要使用字典檔案,資料庫至少應該出於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 => '/data6/cyx/logmnr');

其中dictionary_location指的是字典資訊檔案的存放位置,它必須完全匹配UTL_FILE_DIR的值,例如:假設UTL_FILE_DIR=/data6/cyx/logmnr/,則上面這條語句會出錯,只因為UTL_FILE_DIR後面多了一個“/”,而在很多其它地方對這一“/”是不敏感的。
dictionary_filename指的是放於字典資訊檔案的名字,可以任意取。當然我們也可以不明確寫出這兩個選項,即寫成:
exec dbms_logmnr_d.build('dic.ora','/data6/cyx/logmnr');

如果你第一步的引數沒有設,而直接開始這一步,Oracle會報下面的錯誤:
ERROR at line 1:
ORA-01308: initialization parameter utl_file_dir is not set
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 923
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 1938
ORA-06512: at line 1

需要注意的是,在oracle817 for Windows版中會出現以下錯誤:
14:26:05 SQL> execute dbms_logmnr_d.build('oradict.ora','c:oracleadminoralog');
BEGIN dbms_logmnr_d.build('oradict.ora','c:oracleadminoralog'); END;
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
ORA-06512: at line 1

解決辦法:
編輯"$ORACLE_HOME/rdbms/admindbmslmd.sql"檔案,把其中的
TYPE col_desc_array IS VARRAY(513) OF col_description;
改成:
TYPE col_desc_array IS VARRAY(700) OF col_description;

儲存檔案,然後執行一遍這個指令碼:
15:09:06 SQL> @c:oracleora81rdbmsadmindbmslmd.sql
Package created.
Package body created.
No errors.
Grant succeeded.

然後重新編譯DBMS_LOGMNR_D包:
15:09:51 SQL> alter package DBMS_LOGMNR_D compile body;
Package body altered.
之後重新執行dbms_logmnr_d.build即可:
15:10:06 SQL> execute dbms_logmnr_d.build('oradict.ora','c:oracleadminoralog');
PL/SQL procedure successfully completed.

3、新增需要分析的日誌檔案
SQL>exec dbms_logmnr.add_logfile( logfilename=>'
/data6/cyx/rac1arch/arch_1_197.arc', options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.

這裡的options選項有三個引數可以用:
NEW - 表示建立一個新的日誌檔案列表
ADDFILE - 表示向這個列表中新增日誌檔案,如下面的例子
REMOVEFILE - 和addfile相反。
SQL> exec dbms_logmnr.add_logfile( logfilename=>'
/data6/cyx/rac1arch/arch_2_86.arc', options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.

4、當你新增了需要分析的日誌檔案後,我們就可以讓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.

當然dbms_logmnr.start_logmnr()過程還有其它幾個用於定義分析日誌時間/SCN視窗的引數,它們分別是:
STARTSCN / ENDSCN - 定義分析的起始/結束SCN號,
STARTTIME / ENDTIME - 定義分析的起始/結束時間。
例如下面的過程將只分析從 '2003-09-21 09:39:00'到'2003-09-21 09:45:00'這段時間的日誌:
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/data6/cyx/logmnr/dic.ora' , -
starttime => '2003-09-21 09:39:00',endtime => '2003-09-21 09:45:00');
PL/SQL procedure successfully completed.

上面過程第一行結尾的“-”表示轉行,如果你在同一行,則不需要。我們可以看到有效日誌的時間戳:
SQL> select distinct timestamp from v$logmnr_contents;
TIMESTAMP
-------------------
2003-09-21 09:40:02
2003-09-21 09:42:39

這裡需要注意的是,因為我之前已經設定NLS_DATE_FORMAT環境變數,所以上面的日期可以直接按這個格式寫就行了,如果你沒有設,則需要使用to_date函式來轉換一下。
SQL> !env grep NLS
NLS_LANG=american_america.zhs16cgb231280
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
ORA_NLS33=/oracle/oracle9/app/oracle/product/9.2.0/ocommon/nls/admin/data
使用to_date的格式如下:
exec dbms_logmnr.start_logmnr(dictfilename=>'/data6/cyx/logmnr/dic.ora',-
starttime => to_date('2003-09-21 09:39:00','YYYY-MM-DD HH24:MI:SS'),-
endtime => to_date('2003-09-21 09:45:00','YYYY-MM-DD HH24:MI:SS'));

STARTSCN 和ENDSCN引數使用方法類似。
5、好了,在上面的過程執行結束之後,我們就可以透過訪問與LogMiner相關的幾個檢視來提取我們需要的資訊了。其中在v$logmnr_logs中可以看到我們當前分析的日誌列表,如果資料庫有兩個例項(即OPS/RAC),在v$logmnr_logs中會有兩個不同的THREAD_ID。
而真正的分析結果是放在v$logmnr_contents中,這裡面有很多資訊,我們可以根據需要追蹤我們感興趣的資訊。後面我將單獨列出來講常見的追蹤情形。
6、全部結束之後,我們可以執行dbms_logmnr.end_logmnr過程退出LogMiner分析過程,你也可以直接退出SQL*PLUS,它會自動終止。
[@more@]

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/503782/viewspace-983265/,如需轉載,請註明出處,否則將追究法律責任。

相關文章