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

今天实验内容是日志挖掘工具LOGMNR的使用，我的测试版本是10.2.0.1默认就自带了，无需另外安装。
如果未安装过提示无法使用这个dbms包，则可以用SYSDBA登陆，然后依次执行：
@$Oracle_HOME dbmsadmindbmslm.sql;
@$ORACLE_HOME dbmsadmindbmslmd.sql;
第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。
第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。--开始实验[oracle@ora10g ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 26 09:33:33 2014Copyright （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工具用到的相关视图:

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这个视图，里面存放里挖掘日志获得的内容，来看一下表结构:
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
--通过抓取关键字"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建表语句已经被记录到v$logmnr_contents表中了
--结束日志挖掘
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
一旦结束日志挖掘后，就无法再对v$logmgr_contents视图进行查询，会报错：
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
并且要重新指定挖掘的日志对象，否则依然报错：
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;
*
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用户给的默认表空间WEB空间比较小，只有10M，因此报空间不足了
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
由于之前创建t1表的操作会产生一定的redo,并且是一个DDL操作，会自动commit，当前日志已经切换到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;
既然当前日志是group 1的日志文件，而之前挖掘对象是group 2的日志文件，所以查询v$logmgr_contetns视图发现，第2条建表语句并没有被记录下来
--重新对group 1的redo01.log日志进行挖掘
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内容表
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;对redo01.log日志挖掘后，新的两条创建t2表的语句就被写入v$logmnr_contents中了，包括之前由于空间不足创建失败的那条语句，也做了记录，他们各自都有自己的scn号，不会互相冲突。注意，挖掘日志完毕以后，记得用exec dbms_logmnr.end_logmnr;关闭，否则会消耗数据库资源。总结：通过使用dbms_logmnr包，可以对在线日志进行挖掘，来获对数据库操作相关语句，对于审计也有一定的作用。高级用法还有很多，包括对归档日志的挖掘，对其他内容的挖掘等，这里只是做了一个简单的测试，觉得这个工具还是挺好用的。Oracle LOGMNR挖掘日志与DUMP日志对比 http://www.linuxidc.com/Linux/2013-04/83497.htmOracle Redo Log 及Logmnr配置使用 http://www.linuxidc.com/Linux/2012-12/76058.htmOracle Logmnr 日志挖掘一例 http://www.linuxidc.com/Linux/2012-06/61764.htmOracle利用Logmnr找回误删除数据 http://www.linuxidc.com/Linux/2010-12/30295.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址