首页 > 代码库 > 如何利用DBMS_LOGMNR包挖掘在线日志

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

2024-08-10 01:37:45   219人阅读

今天实验内容是日志挖掘工具LOGMNR的使用，我的测试版本是10.2.0.1默认就自带了，无需另外安装。
如果未安装过提示无法使用这个dbms包，则可以用SYSDBA登陆，然后依次执行：
@$ORACLE_HOME\rdbms\admin\dbmslm.sql;
@$ORACLE_HOME\rdbms\admin\dbmslmd.sql;
第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。 
第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

--开始实验

[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工具用到的相关视图:
 
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包，可以对在线日志进行挖掘，来获对数据库操作相关语句，对于审计也有一定的作用。高级用法还有很多，包括对归档日志的挖掘，对其他内容的挖掘等，这里只是做了一个简单的测试，觉得这个工具还是挺好用的。 

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


联系
我们