oralce數據庫日志查看方法探討
2024-07-21 02:33:59
供稿：網友
研究了好幾天，終于有了一個比較完整查看Oracle的數據日志方法，oracle沒有想sqlserver那樣有sqlE工具來輔助查詢。只能利用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:/oracle/admin/ora/log');
BEGIN dbms_logmnr_d.build('oradict.ora','c:/oracle/admin/ora/log'); 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:/oracle/ora81/rdbms/admin/dbmslmd.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:/oracle/admin/ora/log');
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);
