深入了解數據庫之LogMiner

LogMiner 是Oracle公司從產品8i以后提供的一個實際非常有用的分析工具，使用該工具可以輕松獲得Oracle 重做日志文件（歸檔日志文件）中的具體內容，LogMiner分析工具實際上是由一組PL/SQL包和一些動態視圖組成，可以通過它分析在線日志和歸檔日志獲取數據庫過往詳細、具體的操作，非常有用。

為什么會用到LogMiner?

主要出于以下幾個緣由:

1. 當數據庫發生了誤操作，需要不完全恢復，為確認誤操作準確的時間點或SCN號，此時需用到LogMiner。
2. 傳統恢復一個上TB或是恢復一個幾百GB表空間中的一個小表，標準的操作是把整個表空間恢復到之前的狀態，然后再應用歸檔日志，加上搭建恢復環境的時間，整個時間會很長。通過LogMiner可以換一種恢復思維，能通過它解析到這個表上所有的DML操作，可以做反向修復。合理的使用會大幅降低此情況恢復的復雜度和時間。
3. 可以把LogMiner當成最強大的數據庫審計工具。
4. 在過去的某個時間數據庫很忙或是產生了大量的歸檔日志，通過LogMiner可以知道是哪些操作，哪個表占比較大，主要矛盾在哪。
</ol>




幾個概念

源庫：生成歸檔日志和在線日志的庫

目標庫：執行LogMiner進行日志分析的庫

字典：英文名為CATALOG,用于把日志中的內部信息翻譯成實際的表名、列名等有價值信息

源庫與目標庫可以是同一個，也可以是不同的。如果不同，要求目標庫數據庫版本高于或等于源庫；字符集要相同;操作系統、硬件平臺要相同。

源庫需做的調整




1. 源庫需運行在歸檔模式下
2. 源庫需啟用追加日志
</ol>







ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

查看一下結果：
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEMENTAL_LOG_DATA_MIN
YES</pre>客觀的說追加日志不是必需，如果不啟用追加日志SESSION_INFO等很多有用的信息解析后都沒有，顯示會為“UNKNOWN”。




 LogMiner字典模式 





使用在線字典 
</ol>




這種方式是Oracle推薦的，適用于在源庫做LogMiner，也是最易用的一種方式。 

舉例: 




EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
 



 2.  把字典放到在線日志文件 

適用于源庫與目標庫不同這樣的方式 

舉例： 




EXECUTE DBMS_LOGMNR_D.BUILD( -
OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

看字典存放到了哪個歸檔日志：
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';</pre> 



 3.  把字典生成OS上的一個文件 

這樣的方式是要是為了兼容9i及之前的版本，實際使用中這樣的方式需要在源庫設定UTL_FILE_DIR參數，不方便。 




EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -
'/oracle/database/', -
DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
Oracle已不推薦這樣方式。




以一個例子說明一下LogMiner流程 

1.從磁帶庫恢復出所需的日志 




 run
{
 ALLOCATE channel t1 type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=linuxbak,NSR_CLIENT=jfdb1,NSR_DATA_VOLUME_POOL=jfdb)';
 set archivelog destination to '/jfdb_arch/yang';
 restore archivelog from logseq 247761 until logseq 247763 thread 1;
 release channel t1;
}
2.使用在線字典解析日志







SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/zhjf02_dlbk/yang/zhjf2_2_202749_570073317.arc',OPTIONS => DBMS_LOGMNR.NEW)
PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/zhjf02_dlbk/yang/zhjf2_2_202750_570073317.arc',OPTIONS => DBMS_LOGMNR.ADDFILE)
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
PL/SQL procedure successfully completed.</pre>3. 查詢解析結果







SQL> create table qiuyb.logcontents_2_202749 nologging tablespace TBSI_RAT_32M  as select * from V$LOGMNR_CONTENTS ;
Table created.
需要說明的是V$LOGMNR_CONTENTS是會話期存在的，其它會話是查不到結果的。因此本例子把結果存放在了一個實體表中。另外，每次查詢V$LOGMNR_CONTENTS這個視圖都會實際觸發一次日志解析，也就是說這個視圖的內容不是START_LOGMNR生成的，而是每次查生成的，生成一個實體表也可以大幅降低系統開銷。




4.結束LogMiner解析 




SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
PL/SQL procedure successfully completed.
整個LogMiner會話結束。




一些有用的選項 

1. COMMITTED_DATA_ONLY 

顧名思義就是只顯示已經提交了的，那些正在進行中的及Oracle內部操作都忽略掉了，5的舉例里使用的就是這個選項。 

2.PRINT_PRETTY_SQL 

SQL_REDO和SQL_UNDO中SQL以易讀的方式顯示，舉例如下： 




SQL>  EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY+DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.

SQL> SELECT USERNAME, SEG_OWNER, SEG_NAME, SQL_REDO, SQL_UNDO
  2    FROM v$LOGMNR_CONTENTS;
  3   WHERE SEG_OWNER NOT IN ('SYS', 'SYSTEM')
  4     AND SEG_NAME = 'T2'
  5  /
USERNAME  SEG_OWNER   SEG_NAME   SQL_REDO                      SQL_UNDO

QIUYB     QIUYB       T2         insert into "QIUYB"."T2"      delete from "QIUYB"."T2"
                                 values                        where
                                    "ID" = 105,                   "ID" = 105 and
                                    "NAME" = 'test 105',          "NAME" = 'test 105' and
                                    "FLAG" = 5;                   "FLAG" = 5 and
                                                                  ROWID = 'AAAUcRAAFAAABj1AAA';
SQL></pre> 



4.DDL_DICT_TRACKING 

適用于在線日志存放LogMiner字典的情況，當表發生了添加字段等情況，字典自動更新。 

舉例： 




EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
DBMS_LOGMNR.DDL_DICT_TRACKING + DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
4. NO_SQL_DELIMITER

去掉SQL_REDO及SQL_UNDO中SQL語句最后的分號，以CURSOR方式循環執行解析出的SQL會很方便和快捷。

舉例：







EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
DBMS_LOGMNR.NO_SQL_DELIMITER + DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
5. NO_ROWID_IN_STMT

在SQL_REDO和SQL_UNDO列語句中去掉ROWID。舉例： 






EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
DBMS_LOGMNR.NO_ROWID_IN_STMT + DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
 



適用于在源庫以外的其它庫重新執行解析出的SQL,因為相應的ROWID在目標庫是不存在的。 

有用視圖 





V$LOGMNR_LOGS 添加的需解析的日志列表 

V$LOGMNR_CONTENTS 解析結果 
</ol>




本文作者楊寶秋，Oracle ACE，十多年的TB級數據庫設計、建設、管理、運行維護、調優經驗，也做了7年的Hp和IBM Rs6000的系統管理員，而且是獲得了BCFP認證的SAN管理員，現為中國聯通黑龍江分公司數據庫主管。 

原文鏈接：http://www.qiuyb.com/archives/171  （責編/劉亞瓊） 
</div>