[zt] Oracle LogMiner

Oracle LogMiner 是Oracle公司從產品8i以後提供的一個實際非常有用的分析工具，使用該工具可以輕鬆獲得Oracle 重作日誌檔案（歸檔日誌檔案）中的具體內容，特別是，該工具可以分析出所有對於資料庫操作的DML（insert、update、delete等）語句，另外還可分析得到一些必要的回滾SQL語句。該工具特別適用於除錯、審計或者回退某個特定的事務。（實際上dump日誌檔案出來後也可以檢視到的，不過工程量何其大也！但是也是這個思路了。利用日誌檔案本身的資料結構查詢一定時間的歷史操作）

　　LogMiner分析工具實際上是由一組PL/SQL包和一些動態檢視組成，它作為Oracle資料庫的一部分來發布，是提供的一個完全免費的工具。但該工具和其他Oracle內建工具相比使用起來顯得有些複雜，主要原因是該工具沒有提供任何的圖形使用者介面（GUI）。本文將詳細介紹如何安裝以及使用該工具。必須呼叫相應的包和過程來完成。

　　一、LogMiner的用途

　　日誌檔案中存放著所有進行資料庫恢復的資料，記錄了針對資料庫結構的每一個變化，也就是對資料庫操作的所有DML語句。

　　在Oracle 8i之前，Oracle沒有提供任何協助資料庫管理員來讀取和解釋重作日誌檔案內容的工具。系統出現問題，對於一個普通的資料管理員來講，唯一可以作的工作就是將所有的log檔案打包，然後發給Oracle公司的技術支援，然後靜靜地等待Oracle 公司技術支援給我們最後的答案。然而從8i以後，Oracle提供了這樣一個強有力的工具-LogMiner。

　　LogMiner 工具即可以用來分析線上，也可以用來分析離線日誌檔案，即可以分析本身自己資料庫的重作日誌檔案，也可以用來分析其他資料庫的重作日誌檔案。

　　總的說來，LogMiner工具的主要用途有：

　　1． 跟蹤資料庫的變化：可以離線的跟蹤資料庫的變化，而不會影響線上系統的效能。

　　2． 回退資料庫的變化：回退特定的變化資料，減少point-in-time recovery的執行。（10g中的回閃值得研究！同時基於表空間的tsintr？也可以實現。）

　　3． 最佳化和擴容計劃：可透過分析日誌檔案中的資料以分析資料增長模式。

　　二、安裝LogMiner

　　要安裝LogMiner工具，必須首先要執行下面這樣兩個指令碼，

　　l $ORACLE_HOME/rdbms/admin/dbmslm.sql

　　2 $ORACLE_HOME/rdbms/admin/dbmslmd.sql.

　　這兩個指令碼必須均以SYS使用者身份執行。其中第一個指令碼用來建立DBMS_LOGMNR包，該包用來分析日誌檔案。第二個指令碼用來建立DBMS_LOGMNR_D包，該包用來建立資料字典檔案。

　　三、使用LogMiner工具

　　下面將詳細介紹如何使用LogMiner工具。

　　1、建立資料字典檔案（data-dictionary）就是存放解析出來的結果放到哪裡？

　　前面已經談到，LogMiner工具實際上是由兩個新的PL/SQL內建包（(DBMS_LOGMNR 和 DBMS_ LOGMNR_D）和四個V$動態效能檢視（檢視是在利用過程DBMS_LOGMNR.START_LOGMNR啟動LogMiner時建立）組成。在使用LogMiner工具分析redo log檔案之前，可以使用DBMS_LOGMNR_D 包將資料字典匯出為一個文字檔案。該字典檔案是可選的，但是如果沒有它，LogMiner解釋出來的語句中關於資料字典中的部分（如表名、列名等）和數值都將是16進位制的形式（預設的），我們是無法直接理解的。例如，下面的sql語句：

　　INSERT INTO dm_dj_swry (rydm, rymc) VALUES (00005, '張三'); 直接dump log就是這個結果。可以值得檢視一下包的生成程式碼，也許對dump會有幫助。

oracle好象一個開原始碼軟體哦：）

　　LogMiner解釋出來的結果將是下面這個樣子，

　　insert into Object#308(col#1, col#2) values (hextoraw('c30rte567e436'), hextoraw('4a6f686e20446f65'));

　　建立資料字典的目的就是讓LogMiner引用涉及到內部資料字典中的部分時為他們實際的名字，而不是系統內部的16進位制。資料字典檔案是一個文字檔案，使用包DBMS_LOGMNR_D來建立。如果我們要分析的資料庫中的表有變化，影響到庫的資料字典也發生變化，這時就需要重新建立該字典檔案。另外一種情況是在分析另外一個資料庫檔案的重作日誌時，也必須要重新生成一遍被分析資料庫的資料字典檔案。（這一點挺重要的哦！）

.建立DBMS_LOGMNR包
SQL> @d:/oracle/ora92/rdbms/admin/dbmslms.sql
SQL>@d:/oracle/ora92/rdbms/admin/dbmslm.sql
SQL>@d:/oracle/ora92/rdbms/admin/dbmslmd.sql
SQL>@d:/oracle/ora92/rdbms/admin/prvtlm.plb

　　在init.ora初始化引數檔案中，指定資料字典檔案的位置，也就是新增一個引數UTL_FILE_DIR，該引數值為伺服器中放置資料字典檔案的目錄。如：

alter database add supplemental log data; 和

execute DBMS_LOGMNR_D.BUILD(options=>DBMS_LOGMNR_D.store_in_redo_logs這兩個過程是趕什麼？

　　UTL_FILE_DIR = (e:Oraclelogs)

　　重新啟動資料庫，使新加的引數生效，然後建立資料字典檔案：

　　SQL> CONNECT SYS

　　SQL> execute DBMS_LOGMNR_D.BUILD('shwdict.ora','E:oracle');

PL/SQL 過程已成功完成。

必須要見好目錄，否則回提示（偶記得有這個目錄的呀，浪費半天時間來檢查，鬱悶！）

SQL> execute DBMS_LOGMNR_D.BUILD('shwdict.ora','E:oracle');
BEGIN DBMS_LOGMNR_D.BUILD('shwdict.ora','E:oracle'); END;

*
第 1 行出現錯誤:
ORA-01336: 無法開啟指定的字典檔案
ORA-29283: 檔案操作無效
ORA-06512: 在 "SYS.UTL_FILE", line 475
ORA-29283: 檔案操作無效
ORA-06512: 在 "SYS.DBMS_LOGMNR_INTERNAL", line 3474
ORA-06512: 在 "SYS.DBMS_LOGMNR_INTERNAL", line 3552
ORA-06512: 在 "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: 在 line 1

　　2、建立要分析的日誌檔案列表

　　Oracle的重作日誌分為兩種，線上（online）和離線（offline）歸檔日誌檔案，下面就分別來討論這兩種不同日誌檔案的列表建立。

　　（1）分析線上重作日誌檔案

　　A. 建立列表

　　SQL> EXECUTE dbms_logmnr.add_logfile('D:oracleproduct10.2.0oradatatestred
03.log',dbms_logmnr.new);

PL/SQL 過程已成功完成。

　　B. 新增其他日誌檔案到列表

　　　SQL> EXECUTE dbms_logmnr.add_logfile('D:oracleproduct10.2.0oradatatestred
03.log',Options=>dbms_logmnr.addfile);

（2）分析離線日誌檔案

　　A.建立列表

　　SQL> EXECUTE dbms_logmnr.add_logfile(

　　LogFileName=>' E:OracleoradatasxfarchiveARCARC09108.001',

　　Options=>dbms_logmnr.new);

　　B.新增另外的日誌檔案到列表

　　SQL> EXECUTE dbms_logmnr.add_logfile(

　　LogFileName=>' E:OracleoradatasxfarchiveARCARC09109.001',

　　Options=>dbms_logmnr.addfile);關於這個日誌檔案列表中需要分析日誌檔案的個數完全由你自己決定，但這裡建議最好是每次只新增一個需要分析的日誌檔案，在對該檔案分析完畢後，再新增另外的檔案。

　　和新增日誌分析列表相對應，使用過程 'dbms_logmnr.removefile' 也可以從列表中移去一個日誌檔案。下面的例子移去上面新增的日誌檔案e:Oracleoradatasxfredo02.log。

　　SQL> EXECUTE dbms_logmnr.add_logfile(

　　LogFileName=>' e:Oracleoradatasxfredo02.log',

　　Options=>dbms_logmnr. REMOVEFILE);

　　建立了要分析的日誌檔案列表，下面就可以對其進行分析了。

　　3、使用LogMiner進行日誌分析

　　（1）無限制條件

　　SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName => 'e:oracleshwdict.ora');

PL/SQL 過程已成功完成。

　　（2）有限制條件

　　透過對過程DBMS_ LOGMNR.START_LOGMNR中幾個不同引數的設定（引數含義見表1），可以縮小要分析日誌檔案的範圍。透過設定起始時間和終止時間引數我們可以限制只分析某一時間範圍的日誌。如下面的例子，我們僅僅分析2001年9月18日的日誌，：

　　SQL> EXECUTE dbms_logmnr.start_logmnr(

　　DictFileName => ' e:oraclelogs v816dict.ora ',

　　StartTime => to_date('2001-9-18 00:00:00','YYYY-MM-DD HH24:MI:SS')

　　EndTime => to_date('2001-9-18 23:59:59','YYYY-MM-DD HH24:MI:SS '));

　　也可以透過設定起始SCN和截至SCN來限制要分析日誌的範圍：

　　SQL> EXECUTE dbms_logmnr.start_logmnr(

　　DictFileName => ' e:oraclelogs v816dict.ora ',

　　StartScn => 20,

　　EndScn => 50);

　　表1 DBMS_LOGMNR.START__LOGMNR過程引數含義

　　4、觀察分析結果（v$logmnr_contents）

　　到現在為止，我們已經分析得到了重作日誌檔案中的內容。動態效能檢視v$logmnr_contents包含LogMiner分析得到的所有的資訊。

　spool e:test.sql;　

SELECT sql_redo FROM v$logmnr_contents;

　　如果我們僅僅想知道某個使用者對於某張表的操作，可以透過下面的SQL查詢得到，該查詢可以得到使用者DB_ZGXT對錶SB_DJJL所作的一切工作。

　　SQL> SELECT sql_redo FROM v$logmnr_contents WHERE username='DB_ZGXT' AND tablename='SB_DJJL';(查詢限制條件！)

　　需要強調一點的是，檢視v$logmnr_contents中的分析結果僅在我們執行過程'dbms_logmrn.start_logmnr'這個會話的生命期中存在。這是因為所有的LogMiner儲存都在PGA記憶體中，所有其他的程式是看不到它的，同時隨著程式的結束，分析結果也隨之消失。（在高峰期做這個操作的話肯定會給效能帶來影響了！）

　　最後，使用過程DBMS_LOGMNR.END_LOGMNR終止日誌分析事務，此時PGA記憶體區域被清除，分析結果也隨之不再存在。（應該記住這步操作！）

　　四、其他注意事項

　　我們可以利用LogMiner日誌分析工具來分析其他資料庫例項產生的重作日誌檔案，而不僅僅用來分析本身安裝LogMiner的資料庫例項的redo logs檔案。使用LogMiner分析其他資料庫例項時，有幾點需要注意：

　　1. LogMiner必須使用被分析資料庫例項產生的字典檔案（本身就是利用它來解析它嘛！），而不是安裝LogMiner的資料庫產生的字典檔案，另外必須保證安裝LogMiner資料庫的字符集和被分析資料庫的字符集相同。

　　2. 被分析資料庫平臺必須和當前LogMiner所在資料庫平臺一樣，也就是說如果我們要分析的檔案是由執行在UNIX平臺上的Oracle 8i產生的，那麼也必須在一個執行在UNIX平臺上的Oracle例項上執行LogMiner，而不能在其他如Microsoft NT上執行LogMiner。當然兩者的硬體條件不一定要求完全一樣。

　　3. LogMiner日誌分析工具僅能夠分析Oracle 8以後的產品，對於8以前的產品，該工具也無能為力。

　　五、結語

　　LogMiner對於資料庫管理員（DBA）來講是個功能非常強大的工具，也是在日常工作中經常要用到的一個工具，藉助於該工具，可以得到大量的關於資料庫活動的資訊。其中一個最重要的用途就是不用全部恢復資料庫就可以恢復資料庫的某個變化。另外，該工具還可用來監視或者審計使用者的活動，如你可以利用LogMiner工具察看誰曾經修改了那些資料以及這些資料在修改前的狀態。我們也可以藉助於該工具分析任何Oracle 8及其以後版本產生的重作日誌檔案。另外該工具還有一個非常重要的特點就是可以分析其他資料庫的日誌檔案。總之，該工具對於資料庫管理員來講，是一個非常有效的工具，深刻理解及熟練掌握該工具，對於每一個資料庫管理員的實際工作是非常有幫助的。

logminer 如何分析其他機器的archive log？謝謝

執行過程中報錯

ORA-01295 DB_ID mismatch between dictionary string and logfiles
Cause: The dictionary file is produced by a database that is different from that produced the log files.
Action: Specify a compatible dictionary file.

如何解決這個問題，現在生產庫沒有設定utl_file這個引數，現在還不能重啟資料庫，是否有其他的辦法？謝謝

一、確定獲取資料字典的redo log files

SQL> alter database add supplemental log data;

資料庫已更改。

SQL> execute DBMS_LOGMNR_D.BUILD(options=>DBMS_LOGMNR_D.store_in_redo_logs);

PL/SQL 過程已成功完成。

Depending on the size of the dictionary, it may be contained in multiple redo log files. If the relevant redo log files have been archived, you can find out which redo log files contain the start and end of an extracted dictionary. To do so, query the V$ARCHIVED_LOG view, as follows:

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

二、新增要分析的redo log 檔案
Specify the list of the redo log files of interest. Add the redo log files that contain the start and end of the dictionary and the redo log file that you want to analyze. You can add the redo log files in any order.SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
       LOGFILENAME => '/usr/oracle/data/db1arch_1_210_482701534.dbf', -
           PTIONS => DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
       LOGFILENAME => '/usr/oracle/data/db1arch_1_208_482701534.dbf');
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
       LOGFILENAME => '/usr/oracle/data/db1arch_1_207_482701534.dbf');
這裡需要注意的是把包含資料字典所有的redo log files都add 進去，不然會報資料字典不完整的錯。三、查詢miner的log filesIn the output, LogMiner flags a missing redo log file. LogMiner lets you proceed with mining, provided that you do not specify an option that requires the missing redo log file for proper functioning.SQL> SELECT FILENAME AS name, LOW_TIME, HIGH_TIME FROM V$LOGMNR_LOGS;
 NAME                                  LOW_TIME              HIGH_TIME
-------------------------------------   --------------------  --------------------
/usr/data/db1arch_1_207_482701534.dbf   10-jan-2003 12:01:34  10-jan-2003 13:32:46

/usr/data/db1arch_1_208_482701534.dbf   10-jan-2003 13:32:46  10-jan-2003 15:57:03

Missing logfile(s) for thread number 1, 10-jan-2003 15:57:03  10-jan-2003 15:59:53
sequence number(s) 209 to 209

/usr/data/db1arch_1_210_482701534.dbf   10-jan-2003 15:59:53  10-jan-2003 16:07:41

四、Start LogMinerStart LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY and PRINT_PRETTY_SQL options.SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
       PTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
                  DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                  DBMS_LOGMNR.PRINT_PRETTY_SQL);五、 Query the V$LOGMNR_CONTENTS viewSELECT username AS USR,       session#,       serial#,       (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,       SQL_REDO,       SQL_UNDO  FROM V$LOGMNR_CONTENTS WHERE username IN ('SYS');