Oracle Logminer 数据挖掘

cdchenli

　　Logminer是oracle从8i开始提供的用于分析重做日志信息的工具，它包括DBMS_LOGMNR和DBMS_LOGMNR_D两个package，后边的D是字典的意思。它既能分析redo log file，也能分析归档后的archive log file。在分析日志的过程中需要使用数据字典，一般先生成数据字典文件后使用，10g以后的版本还可以使用在线数据字典。

　　Logminer可以分析其它数据库的重做日志文件，但是必须使用重做日志所在数据库的数据字典，否则会出现无法识别的乱码。另外被分析数据库的操作系统平台最好和当前Logminer所在数据库的运行平台一样，且block>　　LogMiner用于分析重做日志和归档日志所记载的事务操作。
　　一、确定数据库的逻辑损坏时间。假定某个用户执行drop table误删除了重要表sales，通过LogMiner可以准确定位该误操作的执行时间和SCN值，然后通过基于时间恢复或者基于SCN恢复可以完全恢复该表数据。
　　二、确定事务级要执行的精细逻辑恢复操作。假定某些用户在某表上执行了一系列DML操作并提交了事务，并且其中某个用户的DML操作存在错误。通过LogMiner可以取得任何用户的DML操作及相应的UNDO操作，通过执行UNDO操作可以取消用户的错误操作。
　　三、执行后续审计。通过LogMiner可以跟踪Oracle数据库的所有DML、DDL和DCL操作，从而取得执行这些操作的时间顺序、执行这些操作的用户等信息。
　　1、LogMiner基本对象
　　源数据库（source database）：该数据库是指包含了要分析重做日志和归档日志的产品数据库。
　　分析数据库（mining database）：该数据库是指执行LogMiner操作所要使用的数据库。
　　LogMiner字典：LogMiner字典用于将内部对象ID号和数据类型转换为对象名和外部数据格式。使用LogMiner分析重做日志和归档日志时，应该生成LogMiner字典，否则将无法读懂分析结果。
　　2、LogMiner配置要求
　　（1）源数据库和分析数据库(源数据库和分析数据库可以是同一个数据库）
　　源数据库和分析数据库必须运行在相同硬件平台上；
　　分析数据库可以是独立数据库或源数据库；
　　分析数据库的版本不能低于源数据库的版本；
　　分析数据库与源数据库必须具有相同的字符集。
　　（2）LogMiner字典：LogMiner字典必须在源数据库中生成。
　　（3）重做日志文件
　　当分析多个重做日志和归档日志时，它们必须是同一个源数据库的重做日志和归档日志；
　　当分析多个重做日志和归档日志时，它们必须具有相同的resetlogs  scn；
　　当分析的重做日志和归档日志必须在Oracle8.0版本以上。
　　3、补充日志（suppplemental logging）
　　重做日志用于实现例程恢复和介质恢复，这些操作所需要的数据被自动记录在重做日志中。但是，重做应用可能还需要记载其他列信息到重做日志中，记录其他列的日志过程被称为补充日志。
　　默认情况下，Oracle数据库没有提供任何补充日志，从而导致默认情况下LogMiner无法支持以下特征：
　　索引簇、链行和迁移行；
　　直接路径插入；
　　摘取LogMiner字典到重做日志；
　　跟踪DDL；
　　生成键列的SQL_REDO和SQL_UNDO信息；
　　LONG和LOB数据类型。
　　因此，为了充分利用LogMiner提供的特征，必须激活补充日志。在数据库级激活补充日志的示例如下：
　　SQL> conn /as sysdba
　　已连接。

　　SQL>>　　数据库已更改。
　　注意：这激活不用重启数据库，数据库联机即可。
　　LOGMNR 练习
　　1)DML 操作分析
　　06:02:11 SQL> conn /as sysdba
　　Connected.
　　06:02:17 SQL> show parameter utl
　　NAME                                 TYPE                   VALUE
　　------------------------------------ ---------------------- ------------------------------
　　create_stored_outlines               string
　　utl_file_dir                         string
　　06:02:19 SQL> conn scott/tiger
　　Connected.
　　06:04:04 SQL> select * from dept1;
　　DEPTNO DNAME                        LOC
　　---------- ---------------------------- --------------------------
　　50 Account                      Beijing
　　60 Market                       ShangHai
　　70 Study                        BeiJing
　　80 Technolygy                   BeiJing
　　10 ACCOUNTING                   NEW YORK
　　20 RESEARCH                     DALLAS
　　30 SALES                        CHICAGO
　　40 OPERATIONS                   BOSTON
　　90 Market                       BeiJing
　　80 Sales                        BeiJing
　　80 Sales                        ￡?￡?￡?￡?￡?￡?
　　11 rows selected.
　　06:04:18 SQL> delete from dept1 where deptno=80;
　　3 rows deleted.
　　06:04:35 SQL> select * from dept1;
　　DEPTNO DNAME                        LOC
　　---------- ---------------------------- --------------------------
　　50 Account                      Beijing
　　60 Market                       ShangHai
　　70 Study                        BeiJing
　　10 ACCOUNTING                   NEW YORK
　　20 RESEARCH                     DALLAS
　　30 SALES                        CHICAGO
　　40 OPERATIONS                   BOSTON
　　90 Market                       BeiJing
　　8 rows selected.
　　06:04:42 SQL> commit;
　　Commit complete.
　　06:04:55 SQL> select * from dept1;
　　DEPTNO DNAME                        LOC
　　---------- ---------------------------- --------------------------
　　50 Account                      Beijing
　　60 Market                       ShangHai
　　70 Study                        BeiJing
　　10 ACCOUNTING                   NEW YORK
　　20 RESEARCH                     DALLAS
　　30 SALES                        CHICAGO
　　40 OPERATIONS                   BOSTON
　　90 Market                       BeiJing
　　8 rows selected.
　　06:04:58 SQL> delete from dept1 where deptno=90;
　　1 row deleted.
　　06:05:12 SQL> commit;
　　Commit complete.

　　06:05:19 SQL>>　　alter system switch logfile
　　*
　　ERROR at line 1:
　　ORA-01031: insufficient privileges
　　06:05:40 SQL> conn /as sysdba
　　Connected.

　　06:05:46 SQL>>
　　System>　　06:05:52 SQL> select * from v$log;
　　GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS                           FIRST_CHANGE# FIRST_TIME
　　---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
　　1          1         14   52428800          2 YES    ACTIVE                                 1748577 02-APR-11
　　2          1         15   52428800          2 NO     CURRENT                                1756744 07-APR-11
　　3          1         10   52428800          2 YES    INACTIVE                               1663012 31-MAR-11
　　4          1         11   52428800          2 YES    INACTIVE                               1673418 01-APR-11
　　5          1         12   52428800          2 YES    INACTIVE                               1705162 02-APR-11
　　6          1         13   52428800          2 YES    INACTIVE                               1727817 02-APR-11
　　6 rows selected.
　　06:06:25 SQL> select name,sequence# from v$archived_log;
　　NAME                                                SEQUENCE#
　　-------------------------------------------------- ----------
　　/disk1/arch/43_1_741450701.log                             43
　　/disk1/arch/44_1_741450701.log                             44
　　/disk1/arch/45_1_741450701.log                             45
　　/disk1/arch/46_1_741450701.log                             46
　　/disk1/arch/47_1_741450701.log                             47
　　/disk1/arch/48_1_741450701.log                             48
　　/disk1/arch/49_1_741450701.log                             49
　　/disk1/arch/50_1_741450701.log                             50
　　/disk1/arch/47_1_741450701.log                             47
　　/disk1/arch/48_1_741450701.log                             48
　　/disk1/arch/46_1_741450701.log                             46
　　/disk1/arch/49_1_741450701.log                             49
　　/disk1/arch/50_1_741450701.log                             50
　　/disk1/arch/51_1_741450701.log                             51
　　/disk1/arch/1_1_746079267.log                               1
　　/disk1/arch/2_1_746079267.log                               2
　　/disk1/arch/3_1_746079267.log                               3
　　NAME                                                SEQUENCE#
　　-------------------------------------------------- ----------
　　/disk1/arch/4_1_746079267.log                               4
　　/disk1/arch/5_1_746079267.log                               5
　　/disk1/arch/6_1_746079267.log                               6
　　/disk1/arch/7_1_746079267.log                               7
　　/disk1/arch/8_1_746079267.log                               8
　　/disk1/arch/9_1_746079267.log                               9
　　/disk1/arch/10_1_746079267.log                             10
　　/disk1/arch/11_1_746079267.log                             11
　　/disk1/arch/12_1_746079267.log                             12
　　/disk1/arch/13_1_746079267.log                             13
　　/disk1/arch/14_1_746079267.log                             14
　　28 rows selected.
　　06:07:01 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk1/arch/14_1_746079267.log',options=>dbms_logmnr.new);
　　PL/SQL procedure successfully completed.
　　06:08:19 SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
　　PL/SQL procedure successfully completed.
　　06:09:08 SQL> col username for a8
　　06:09:26 SQL> col sql_redo for a50

　　06:09:36 SQL>>
　　Session>　　06:10:03 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='SCOTT.DEPT1';
　　no rows selected
　　06:14:38 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='DEPT1';
　　USERNAME        SCN TIMESTAMP    SQL_REDO
　　-------- ---------- ------------ --------------------------------------------------
　　1756706 07-APR-11    delete from "SCOTT"."DEPT1" where "DEPTNO" = '80'
　　and "DNAME" = 'Technolygy' and "LOC" = 'BeiJing' a
　　nd ROWID = 'AAANaPAAEAAAAAcAAD';
　　1756707 07-APR-11    delete from "SCOTT"."DEPT1" where "DEPTNO" = '80'
　　and "DNAME" = 'Sales' and "LOC" = 'BeiJing' and RO
　　WID = 'AAANaPAAEAAAAAgAAC';
　　1756707 07-APR-11    delete from "SCOTT"."DEPT1" where "DEPTNO" = '80'
　　and "DNAME" = 'Sales' and "LOC" = '￡?￡?￡?￡?￡?￡?' a
　　nd ROWID = 'AAANaPAAEAAAAAgAAD';
　　1756728 07-APR-11    delete from "SCOTT"."DEPT1" where "DEPTNO" = '90'
　　and "DNAME" = 'Market' and "LOC" = 'BeiJing' and R
　　OWID = 'AAANaPAAEAAAAAgAAA';
　　06:15:09 SQL>
　　结束日志分析
　　09:18:14 SQL>  execute dbms_logmnr.end_logmnr;
　　PL/SQL procedure successfully completed.
　　2）DDL 操作分析
　　06:20:46 SQL> conn  scott/tiger
　　Connected.
　　06:20:54 SQL> select * from tab;
　　TNAME                                                        TABTYPE         CLUSTERID
　　------------------------------------------------------------ -------------- ----------
　　EMP                                                          TABLE
　　DEPT                                                         TABLE
　　BONUS                                                        TABLE
　　SALGRADE                                                     TABLE
　　QUEST_SL_TEMP_EXPLAIN1                                       TABLE
　　EMP1                                                         TABLE
　　ERRLOG                                                       TABLE
　　PART_SALES                                                   TABLE
　　T01                                                          TABLE
　　DEPT1                                                        TABLE
　　TT01                                                         TABLE
　　USERLOG                                                      TABLE
　　ERRORS                                                       TABLE
　　EMP_HIS                                                      TABLE
　　14 rows selected.
　　06:21:02 SQL> drop table emp_his purge;
　　Table dropped.
　　06:21:21 SQL> drop table tt01 purge;
　　Table dropped.
　　06:21:32 SQL> drop table t01 purge;
　　Table dropped.
　　06:21:38 SQL> select * from tab;
　　TNAME                                                        TABTYPE         CLUSTERID
　　------------------------------------------------------------ -------------- ----------
　　EMP                                                          TABLE
　　DEPT                                                         TABLE
　　BONUS                                                        TABLE
　　SALGRADE                                                     TABLE
　　QUEST_SL_TEMP_EXPLAIN1                                       TABLE
　　EMP1                                                         TABLE
　　ERRLOG                                                       TABLE
　　PART_SALES                                                   TABLE
　　DEPT1                                                        TABLE
　　USERLOG                                                      TABLE
　　ERRORS                                                       TABLE
　　11 rows selected.
　　06:22:17 SQL> conn /as sysdba
　　Connected.

　　06:22:21 SQL>>
　　System>　　06:22:23 SQL> show parameter utl
　　NAME                                 TYPE                   VALUE
　　------------------------------------ ---------------------- ------------------------------
　　create_stored_outlines               string
　　utl_file_dir                         string

　　06:22:33 SQL>>
　　System>　　shutdown immediate
　　Database closed.
　　Database dismounted.
　　ORACLE instance shut down.
　　06:23:59 SQL> startup
　　ORACLE instance started.
　　Total System Global Area  524288000 bytes

　　Fixed>
　　Variable>　　Database Buffers          331350016 bytes
　　Redo Buffers                2973696 bytes
　　Database mounted.
　　Database opened.
　　06:24:24 SQL> show parameter utl
　　NAME                                 TYPE                   VALUE
　　------------------------------------ ---------------------- ------------------------------
　　create_stored_outlines               string
　　utl_file_dir                         string                 /home/oracle/logmnr
　　06:26:28 SQL> execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
　　PL/SQL procedure successfully completed.
　　06:27:46 SQL> select name,sequence# from v$archived_log;
　　NAME                                                SEQUENCE#
　　-------------------------------------------------- ----------
　　/disk1/arch/44_1_741450701.log                             44
　　/disk1/arch/45_1_741450701.log                             45
　　/disk1/arch/46_1_741450701.log                             46
　　/disk1/arch/47_1_741450701.log                             47
　　/disk1/arch/48_1_741450701.log                             48
　　/disk1/arch/49_1_741450701.log                             49
　　/disk1/arch/50_1_741450701.log                             50
　　/disk1/arch/47_1_741450701.log                             47
　　/disk1/arch/48_1_741450701.log                             48
　　/disk1/arch/46_1_741450701.log                             46
　　/disk1/arch/49_1_741450701.log                             49
　　/disk1/arch/50_1_741450701.log                             50
　　/disk1/arch/51_1_741450701.log                             51
　　/disk1/arch/1_1_746079267.log                               1
　　/disk1/arch/2_1_746079267.log                               2
　　/disk1/arch/3_1_746079267.log                               3
　　/disk1/arch/4_1_746079267.log                               4
　　NAME                                                SEQUENCE#
　　-------------------------------------------------- ----------
　　/disk1/arch/5_1_746079267.log                               5
　　/disk1/arch/6_1_746079267.log                               6
　　/disk1/arch/7_1_746079267.log                               7
　　/disk1/arch/8_1_746079267.log                               8
　　/disk1/arch/9_1_746079267.log                               9
　　/disk1/arch/10_1_746079267.log                             10
　　/disk1/arch/11_1_746079267.log                             11
　　/disk1/arch/12_1_746079267.log                             12
　　/disk1/arch/13_1_746079267.log                             13
　　/disk1/arch/14_1_746079267.log                             14
　　/disk1/arch/15_1_746079267.log                             15
　　28 rows selected.
　　06:28:09 SQL>execute dbms_logmnr.add_logfile(logfilename=>'/disk1/arch/15_1_746079267.log',options=>dbms_logmnr.new);
　　PL/SQL procedure successfully completed.
　　06:28:58 SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
　　PL/SQL procedure successfully completed.
　　06:30:58 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents
　　06:31:24   2   where lower(sql_redo) like '%table%';
　　USERNAME        SCN TIMESTAMP    SQL_REDO
　　-------- ---------- ------------ --------------------------------------------------
　　1757390 07-APR-11    drop table emp_his purge;
　　1757409 07-APR-11    drop table tt01 purge;
　　1757425 07-APR-11    drop table t01 purge;
　　06:31:45 SQL>
　　结束日志分析
　　SQL> execute dbms_logmnr.end_logmnr;
　　PL/SQL procedure successfully completed.
　　oracle视频教程请关注:http://down.51cto.com/4202939/up