oracle LogMiner配置使用

標籤：style   blog   color   io   使用   ar   檔案   資料   sp   

一、安裝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-14SQL> 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>回到原sessionSQL> 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-14SQL> 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;

 

oracle LogMiner配置使用