作為Oracle DBA，我們有時候須要追蹤資料誤刪除或使用者的惡意操作情況，此時我們不僅須要查出履行這些操作的資料庫賬號，還須要知道操作是由哪臺客戶端（IP地址等）發出的。針對這些問題，一個最有效實用而又低成本的辦法就是分析Oracle資料庫的日誌檔案。本文將就Oracle日誌分析技能做深入探討。[@more@]

一、如何分析即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:oracleora81
dbmsadmindbmslmd.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

這裡須要注意的是，因為我之前已經設定NL2005-1-31_FORMAT環境變數，所以上面的日期可以直接按這個格式寫就行了，如果你沒有設，則須要應用to_date函式來轉換一下。

SQL> !env|grep NLS
NLS_LANG=american_america.zhs16cgb231280
NL2005-1-31_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，它會自動終止。

四、如何利用LogMiner分析Oracle8的日誌檔案

雖然說LogMiner是Oracle8i才推出來，但我們同樣可以用它來分析Oracle8的日誌檔案，只不過稍微麻煩了一點，並且有必定的限制，下面是具體做法：

我們首先複製Oracle8i的$ORACLE_HOME/rdbms/admin/dbmslmd.sql指令碼到Oracle8資料庫所在主機的同樣目錄；這個指令碼用於創造dbms_logmnr_d包（注意，Oracle9i中還將創造dbms_logmnr包），如果是8.1.5指令碼名字為dbmslogmnrd.sql。然後在Oracle8的資料庫上執行這個指令碼，之後應用dbms_logmnr_d.build過程創造字典資訊檔案。現在我們就可以把Oracle8的歸檔日誌連同這個字典資訊檔案複製到Oracle8i資料庫所在的主機上，之後在Oracle8i資料庫中從上面分析過程的第三步開始分析Oracle8的日誌，不過

dbms_logmnr.start_logmnr()中應用的是Oracle8的字典資訊檔案。

依照我前面所說的那樣，如果不是字典檔案，我們則可以直接將Oracle8的歸檔日誌複製到Oracle8i資料庫所在主機，然後對它進行分析。

其實這裡涉及到了一個跨平臺應用LogMiner的問題，筆者做過試驗，也可以在Oracle9i中來分析Oracle8i的日誌。但這些都是有所限制的，首要表現在：

1、LogMiner所應用的字典檔案必需和所分析的日誌檔案是同一個資料庫所產生的，並且該資料庫的字符集應和履行LogMiner資料庫的相同。這很好理解，如果不是同一個資料庫所產生就不存在對應關係了。

2、生成日誌的資料庫硬體平臺和履行LogMiner資料庫的硬體平臺請求一致，操作體系版本可以不一致。筆者做試驗時（如果讀者有興致可以到我網站上下載試驗全過程，因為太長就不放在這裡了），所用的兩個資料庫操作體系都是Tru64 UNIX，但一個是 V5.1A，另一個則是V4.0F。如果操作體系不一致則會湧現下面的差錯：

ORA-01284: file /data6/cyx/logmnr/arch_1_163570.arc cannot be opened
ORA-00308: cannot open archived log /data6/cyx/logmnr/arch_1_163570.arc
ORA-27048: skgfifi: file header information is invalid
ORA-06512: at SYS.DBMS_LOGMNR, line 63
ORA-06512: at line 1

五、分析v$logmnr_contents

前面我們已經知道了LogMiner的分析成果是放在v$logmnr_contents中，這裡面有很多資訊，我們可以依據須要追蹤我們感興致的資訊。那麼我們通常感興致的有哪些呢？

1、追蹤資料庫結構變化情況，即DDL操作，如前所述，這個只有Oracle9i才支援：

SQL> select timestamp,sql_redo from v$logmnr_contents2 
where upper(sql_redo) like %CREATE%;
TIMESTAMP
-------------------
SQL_REDO
-------------------------
2003-09-21 10:01:55
create table t (c1 number);

2、追蹤使用者誤操作或惡意操作：

例如我們現實中有這樣需求，有一次我們發明一位員工透過程式修改了業務資料庫資訊，把部分電話的收費型別改成免費了，現在就請求我們從資料庫中查出到底是誰幹的這件事？怎麼查？LogMiner供給了我們分析日誌檔案的手段，其中v$logmnr_contents的SESSION_INFO列包括了下面的資訊：

login_username=NEW_97 
client_info= OS_username=oracle8 Machine_name=phoenix1
 OS_terminal=ttyp3 OS_process_id=8004 OS_program name=sqlplus@phoenix1
 (TNS V1-V3)

雖然其中資訊已經很多了，但在我們的業務資料庫中，程式是透過相同的login_username登入資料庫的，這樣單從上面的資訊是很難斷定的。

不過我們注意到，因為公司利用伺服器不是每個人都有許可權在上面寫程式的，一般惡意程式都是直接透過他自己的PC連到資料庫的，這就須要一個精確的定位。IP追蹤是我們首先想到的，並且也滿足我們的實際請求，因為公司內部IP地址分配是統一管理的，能追蹤到IP地址我們就可以準肯定位了。但從面的SESSION_INFO中我們並不能直接看到IP，不過我們還是有方法的，因為這個SESSION_INFO裡面的內容其實是日誌從V$SESSION檢視裡提取的，我們可以在生產資料庫中創造一個追蹤客戶端IP地址的觸發器：

create or replace trigger on_logon_trigger
after logon on database
begin
  dbms_application_info.set_client_info(sys_context(userenv, ip_address));
end;
/

現在，我們就可以在V$SESSION檢視的CLIENT_INFO列中看到新登入的客戶端IP地址了。那麼上面的提出的問題就可以迎刃而解了。假如被更新的表名為HMLX，我們就可以透過下面的SQL來找到所需資訊：

SQL > select session_info ,sql_redo from v$logmnr_contents 
2 where upper(operation) = UPDATE  and upper(sql_redo) like %HMLX%
3 /
SESSION_INFO
-----------------------------------------
SQL_REDO
-----------------------------------------
login_username=C client_info=10.16.98.26 OS_username=sz-xjs-chengyx Machine_name
=GDTELSZ-XJS-CHENGYX
update C.HMLX set NAME = free where NAME = ncn.cn and ROWID = AAABhTAA
FAAABRaAAE;

好了，到此為止，這篇文章就要收場了，如果讀者朋友還有什麼疑問，可以登入我的個人網站（）來獲得最新訊息，也可以透過MSN（gototop_ncn@hotmail.com）直接和我聯絡。

六、參考資料：

1、Technical White Paper Oracle9i LogMiner

2、Metalink文件：How to Setup LogMiner（文件ID：111886.1）

深入分析Oracle日誌檔案

相關文章