如何利用DBMS_LOGMNR包挖掘在線日志

今天實(shí)驗(yàn)內(nèi)容是日志挖掘工具LOGMNR的使用，我的測(cè)試版本是10.2.0.1默認(rèn)就自帶了，無需另外安裝。如果未安裝過提示無法使用這個(gè)dbms包，則可以用SYSDBA登陸，然后依次執(zhí)行：@$ORACLE_HOME\rdbms\admin\dbmslm.sql;@$ORACLE_HOME\rdbms\admin\dbmslmd.sql;第一

今天實(shí)驗(yàn)內(nèi)容是日志挖掘工具LOGMNR的使用，我的測(cè)試版本是10.2.0.1默認(rèn)就自帶了，無需另外安裝。 如果未安裝過提示無法使用這個(gè)dbms包，則可以用SYSDBA登陸，然后依次執(zhí)行： @$ORACLE_HOME\rdbms\admin\dbmslm.sql; @$ORACLE_HOME\rdbms\admin\dbmslmd.sql; 第一個(gè)腳本用來創(chuàng)建DBMS_LOGMNR包，該包用來分析日志文件。 第二個(gè)腳本用來創(chuàng)建DBMS_LOGMNR_D包，該包用來創(chuàng)建數(shù)據(jù)字典文件。
--開始實(shí)驗(yàn)
[oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 26 09:33:33 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> set line 130 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- 1 1 8 52428800 1 YES INACTIVE 1402743 2014-12-24 2 1 9 52428800 1 NO CURRENT 1402823 2014-12-24 3 1 7 52428800 1 YES INACTIVE 1401824 2014-12-24
SQL> col member for a45 SQL> select group#,member from v$logfile;
GROUP# MEMBER ---------- --------------------------------------------- 3 /u01/app/oracle/oradata/ora10g/redo03.log 2 /u01/app/oracle/oradata/ora10g/redo02.log 1 /u01/app/oracle/oradata/ora10g/redo01.log
--啟用日志挖掘
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ora10g/redo02.log');
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
來看一下LOGMNR工具用到的相關(guān)視圖: SQL> set pages 100 SQL> col comments for a40 SQL> select * from dict t where t.table_name like '%LOGMNR%';
TABLE_NAME COMMENTS ------------------------------ ---------------------------------------- DBA_LOGMNR_LOG DBA_LOGMNR_SESSION DBA_LOGMNR_PURGED_LOG V$LOGMNR_CONTENTS Synonym for V_$LOGMNR_CONTENTS V$LOGMNR_PARAMETERS Synonym for V_$LOGMNR_PARAMETERS V$LOGMNR_DICTIONARY Synonym for V_$LOGMNR_DICTIONARY V$LOGMNR_LOGS Synonym for V_$LOGMNR_LOGS V$LOGMNR_STATS Synonym for V_$LOGMNR_STATS V$LOGMNR_DICTIONARY_LOAD Synonym for V_$LOGMNR_DICTIONARY_LOAD GV$LOGMNR_CONTENTS Synonym for GV_$LOGMNR_CONTENTS GV$LOGMNR_PARAMETERS Synonym for GV_$LOGMNR_PARAMETERS GV$LOGMNR_DICTIONARY Synonym for GV_$LOGMNR_DICTIONARY GV$LOGMNR_LOGS Synonym for GV_$LOGMNR_LOGS V$LOGMNR_LOGFILE Synonym for V_$LOGMNR_LOGFILE V$LOGMNR_PROCESS Synonym for V_$LOGMNR_PROCESS V$LOGMNR_LATCH Synonym for V_$LOGMNR_LATCH V$LOGMNR_TRANSACTION Synonym for V_$LOGMNR_TRANSACTION V$LOGMNR_REGION Synonym for V_$LOGMNR_REGION V$LOGMNR_CALLBACK Synonym for V_$LOGMNR_CALLBACK V$LOGMNR_SESSION Synonym for V_$LOGMNR_SESSION GV$LOGMNR_LOGFILE Synonym for GV_$LOGMNR_LOGFILE GV$LOGMNR_PROCESS Synonym for GV_$LOGMNR_PROCESS GV$LOGMNR_LATCH Synonym for GV_$LOGMNR_LATCH GV$LOGMNR_TRANSACTION Synonym for GV_$LOGMNR_TRANSACTION GV$LOGMNR_REGION Synonym for GV_$LOGMNR_REGION GV$LOGMNR_CALLBACK Synonym for GV_$LOGMNR_CALLBACK GV$LOGMNR_SESSION Synonym for GV_$LOGMNR_SESSION GV$LOGMNR_STATS Synonym for GV_$LOGMNR_STATS GV$LOGMNR_DICTIONARY_LOAD Synonym for GV_$LOGMNR_DICTIONARY_LOAD
29 rows selected.
這里主要用到的是v$logmnr_contents這個(gè)視圖，里面存放里挖掘日志獲得的內(nèi)容，來看一下表結(jié)構(gòu):
SQL> desc v$logmnr_contents; Name Null? Type ----------------------------------------------------------------------- -------- ------------------------------------------------ SCN NUMBER CSCN NUMBER TIMESTAMP DATE COMMIT_TIMESTAMP DATE THREAD# NUMBER LOG_ID NUMBER XIDUSN NUMBER XIDSLT NUMBER XIDSQN NUMBER PXIDUSN NUMBER PXIDSLT NUMBER PXIDSQN NUMBER RBASQN NUMBER RBABLK NUMBER RBABYTE NUMBER UBAFIL NUMBER UBABLK NUMBER UBAREC NUMBER UBASQN NUMBER ABS_FILE# NUMBER REL_FILE# NUMBER DATA_BLK# NUMBER DATA_OBJ# NUMBER DATA_OBJD# NUMBER SEG_OWNER VARCHAR2(32) SEG_NAME VARCHAR2(256) TABLE_NAME VARCHAR2(32) SEG_TYPE NUMBER SEG_TYPE_NAME VARCHAR2(32) TABLE_SPACE VARCHAR2(32) ROW_ID VARCHAR2(18) SESSION# NUMBER SERIAL# NUMBER USERNAME VARCHAR2(30) SESSION_INFO VARCHAR2(4000) TX_NAME VARCHAR2(256) ROLLBACK NUMBER OPERATION VARCHAR2(32) OPERATION_CODE NUMBER SQL_REDO VARCHAR2(4000) SQL_UNDO VARCHAR2(4000) RS_ID VARCHAR2(32) SEQUENCE# NUMBER SSN NUMBER CSF NUMBER INFO VARCHAR2(32) STATUS NUMBER REDO_VALUE NUMBER UNDO_VALUE NUMBER SQL_COLUMN_TYPE VARCHAR2(30) SQL_COLUMN_NAME VARCHAR2(30) REDO_LENGTH NUMBER REDO_OFFSET NUMBER UNDO_LENGTH NUMBER UNDO_OFFSET NUMBER DATA_OBJV# NUMBER SAFE_RESUME_SCN NUMBER XID RAW(8) PXID RAW(8) AUDIT_SESSIONID NUMBER
--通過抓取關(guān)鍵字"create"，來獲得剛才的建表語句
SQL> select scn,timestamp,log_id,seg_owner,seg_type,table_space,data_blk#,data_obj#,data_objd#,session#,serial#,username,session_info,sql_redo,sql_undo 2 from v$logmnr_contents t 3 where t.sql_redo like 'create%';

SCN TIMESTAMP LOG_ID SEG_OWNER SEG_TYPE TABLE_SPACE DATA_BLK# ---------- ---------- ---------- -------------------------------- ---------- -------------------------------- ---------- DATA_OBJ# DATA_OBJD# SESSION# SERIAL# USERNAME ---------- ---------- ---------- ---------- ------------------------------ SESSION_INFO ---------------------------------------------------------------------------------------------------------------------------------- SQL_REDO ---------------------------------------------------------------------------------------------------------------------------------- SQL_UNDO ---------------------------------------------------------------------------------------------------------------------------------- 1404138 2014-12-26 9 ZLM 2 0 53863 0 0 0
create table zlm.t1 as select * from dba_objects;
可以看到，剛才的DDL建表語句已經(jīng)被記錄到v$logmnr_contents表中了
--結(jié)束日志挖掘
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
一旦結(jié)束日志挖掘后，就無法再對(duì)v$logmgr_contents視圖進(jìn)行查詢，會(huì)報(bào)錯(cuò)：
SQL> select scn,timestamp,log_id,seg_owner,seg_type,table_space,data_blk#,data_obj#,data_objd#,session#,serial#,username,session_info,sql_redo,sql_undo 2 from v$logmnr_contents t 3 where t.sql_redo like 'create%'; from v$logmnr_contents t * ERROR at line 2: ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents
并且要重新指定挖掘的日志對(duì)象，否則依然報(bào)錯(cuò)：
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); BEGIN dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); END;
【本文來自鴻網(wǎng)互聯(lián) (http://www.68idc.cn)】 * ERROR at line 1: ORA-01292: no log file has been specified for the current LogMiner session ORA-06512: at "SYS.DBMS_LOGMNR", line 58 ORA-06512: at line 1
SQL> create table zlm.t2 as select * from zlm.t1; create table zlm.t2 as select * from zlm.t1 * ERROR at line 1: ORA-01652: unable to extend temp segment by 128 in tablespace WEB
由于之前zlm用戶給的默認(rèn)表空間WEB空間比較小，只有10M，因此報(bào)空間不足了
SQL> select d.file#,d.ts#,d.bytes/1024/1024 as "Size(M)",t.name from v$datafile d,v$tablespace t where d.ts#=t.ts#;
FILE# TS# Size(M) NAME ---------- ---------- ---------- --------------------------------------------- 1 0 560 SYSTEM 2 1 165 UNDOTBS1 3 2 270 SYSAUX 4 4 40 USERS 5 6 100 EXAMPLE 6 7 50 ZLM 7 18 20 INDX2 8 19 10 WEB
8 rows selected.
--修改zlm缺省表空間為zlm
SQL> alter user zlm default tablespace zlm;
User altered.
SQL> create table zlm.t2 as select * from zlm.t1;
Table created.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- 1 1 11 52428800 1 NO CURRENT 1405472 2014-12-26 2 1 9 52428800 1 YES INACTIVE 1402823 2014-12-24 3 1 10 52428800 1 YES INACTIVE 1405357 2014-12-26
由于之前創(chuàng)建t1表的操作會(huì)產(chǎn)生一定的redo,并且是一個(gè)DDL操作，會(huì)自動(dòng)commit，當(dāng)前日志已經(jīng)切換到group 1了 SQL> select scn,timestamp,log_id,seg_owner,seg_type,table_space,data_blk#,data_obj#,data_objd#,session#,serial#,username,session_info,sql_redo,sql_undo 2 from v$logmnr_contents t 3 where t.sql_redo like 'create%';
SCN TIMESTAMP LOG_ID SEG_OWNER SEG_TYPE TABLE_SPACE DATA_BLK# ---------- ---------- ---------- -------------------------------- ---------- -------------------------------- ---------- DATA_OBJ# DATA_OBJD# SESSION# SERIAL# USERNAME ---------- ---------- ---------- ---------- ------------------------------ SESSION_INFO ---------------------------------------------------------------------------------------------------------------------------------- SQL_REDO ---------------------------------------------------------------------------------------------------------------------------------- SQL_UNDO ---------------------------------------------------------------------------------------------------------------------------------- 1404138 2014-12-26 9 ZLM 2 0 53863 0 0 0
create table zlm.t1 as select * from dba_objects;
既然當(dāng)前日志是group 1的日志文件，而之前挖掘?qū)ο笫莋roup 2的日志文件，所以查詢v$logmgr_contetns視圖發(fā)現(xiàn)，第2條建表語句并沒有被記錄下來
--重新對(duì)group 1的redo01.log日志進(jìn)行挖掘
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ora10g/redo01.log');
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
--查看LOGMNR內(nèi)容表
SQL> select scn,timestamp,log_id,seg_owner,seg_type,table_space,data_blk#,data_obj#,data_objd#,session#,serial#,username,session_info,sql_redo,sql_undo 2 from v$logmnr_contents t 3 where t.sql_redo like 'create%';
SCN TIMESTAMP LOG_ID SEG_OWNER SEG_TYPE TABLE_SPACE DATA_BLK# ---------- ---------- ---------- -------------------------------- ---------- -------------------------------- ---------- DATA_OBJ# DATA_OBJD# SESSION# SERIAL# USERNAME ---------- ---------- ---------- ---------- ------------------------------ SESSION_INFO ---------------------------------------------------------------------------------------------------------------------------------- SQL_REDO ---------------------------------------------------------------------------------------------------------------------------------- SQL_UNDO ---------------------------------------------------------------------------------------------------------------------------------- 1404138 2014-12-26 9 ZLM 2 0 53863 0 0 0
create table zlm.t1 as select * from dba_objects;

1406186 2014-12-26 11 ZLM 2 0 53864 0 0 0
create table zlm.t2 as select * from zlm.t1;

1406401 2014-12-26 11 ZLM 2 0 53865 0 0 0
create table zlm.t2 as select * from zlm.t1;
對(duì)redo01.log日志挖掘后，新的兩條創(chuàng)建t2表的語句就被寫入v$logmnr_contents中了，包括之前由于空間不足創(chuàng)建失敗的那條語句，也做了記錄，他們各自都有自己的scn號(hào)，不會(huì)互相沖突。注意，挖掘日志完畢以后，記得用exec dbms_logmnr.end_logmnr;關(guān)閉，否則會(huì)消耗數(shù)據(jù)庫資源。
總結(jié)：
通過使用dbms_logmnr包，可以對(duì)在線日志進(jìn)行挖掘，來獲對(duì)數(shù)據(jù)庫操作相關(guān)語句，對(duì)于審計(jì)也有一定的作用。高級(jí)用法還有很多，包括對(duì)歸檔日志的挖掘，對(duì)其他內(nèi)容的挖掘等，這里只是做了一個(gè)簡單的測(cè)試，覺得這個(gè)工具還是挺好用的。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com