oracle LogMiner配置使用

一、安装LogMiner
1、@D:\app\product\11.1.0\db_1\RDBMS\ADMIN\dbmslm.sql

2、@D:\app\product\11.1.0\db_1\RDBMS\ADMIN\dbmslmd.sql

 

二、配置LogMiner文件夹

CREATE DIRECTORY utlfile AS 'D:\app\oradata\practice\LOGMNR';
alter system set utl_file_dir='D:\app\oradata\practice\LOGMNR' scope=spfile;

 

三、重启数据库

四、创建字典文件

 需要以DBA用户登录，创建到上面配置好的LogMiner文件夹中。

EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'D:\app\oradata\practice\LOGMNR');

五、加入需分析的日志文件

Oracle的LogMiner可以分析在线（online）和归档（offline）两种日志文件，
加入分析日志文件使用dbms_logmnr.add_logfile过程，第一个文件使用dbms_logmnr.NEW参数，后面文件使用dbms_logmnr.ADDFILE参数。

BEGIN
dbms_logmnr.add_logfile(logfilename=>'D:\app\oradata\orcl\REDO03.LOG',options=>dbms_logmnr.NEW);
dbms_logmnr.add_logfile(logfilename=>'D:\app\oradata\orcl\REDO02.LOG',options=>dbms_logmnr.ADDFILE);
dbms_logmnr.add_logfile(logfilename=>'D:\app\oradata\orcl\REDO01.LOG',options=>dbms_logmnr.ADDFILE);
END;

六、使用LogMiner进行日志分析

Oracle的LogMiner分析时分为无限制条件和限制条件两种，无限制条件中分析所有加入到分析列表日志文件，限制条件根据限制条件分析指定范围日志文件。

EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'D:\app\oradata\practice\LOGMNR\dictionary.ora');

OR

execute dbms_logmnr.start_logmnr(options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);

补充日志：
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> select supplemental_log_data_min from v$database;

七、示例：

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                   FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------
         1          1        169   52428800          1 NO        INACTIVE                                       7189694 11-OCT-14
         2          1        170   52428800          1 NO        INACTIVE                                       7200026 11-OCT-14
         3          1        171   52428800          1 NO        CURRENT                                        7209631 11-OCT-14

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                   FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------
         1          1        172   52428800          1 NO        CURRENT                                        7212426 11-OCT-14
         2          1        170   52428800          1 NO        INACTIVE                                       7200026 11-OCT-14
         3          1        171   52428800          1 NO        ACTIVE                                         7209631 11-OCT-14

另外一个session 
sqlplus scott/tiger
         
SQL> delete from emp2 where deptno =20;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into emp2 select * from emp t where t.deptno !=10;

11 rows created.

SQL> commit;

Commit complete.

SQL>

回到原session

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                   FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------
         1          1        172   52428800          1 NO        ACTIVE                                         7212426 11-OCT-14
         2          1        173   52428800          1 NO        CURRENT                                        7212451 11-OCT-14
         3          1        171   52428800          1 NO        ACTIVE                                         7209631 11-OCT-14

SQL> begin
  2  dbms_logmnr.add_logfile(logfilename=>'D:\app\oradata\orcl\REDO01.LOG',options=>dbms_logmnr.NEW);
  3  end;
  4  /

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.

SQL> select sql_redo from v$logmnr_contents t where t.seg_name ='EMP2';

SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
delete from "SCOTT"."EMP2" where "EMPNO" = '7369' and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR" = '7902' and "HIREDATE" = TO_DATE('17-DEC-80', 'DD-MON-RR') and "SAL" = '800'
and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAARiDAAEAAABgNAAC';
......

----

启动supplemental log：
       SQL>alter database add supplemental log data;
 
关闭supplemental log：
       SQL>alter database drop supplemental log data;
 
查看 supplemental log：
       SQL>select supplemental_log_data_min from v$database;