[Logmnr]對歸檔日誌進行資料探勘

梓沐發表於2016-02-15
1、建立演示資料
  1. SQL> update emp set sal=sal+100 where deptno=10;
    SQL> update emp set sal=sal+200 where deptno=20;
    SQL> update emp set sal=sal+300 where deptno=30;
    SQL> update emp set sal=sal+400 where deptno=40;
    SQL> update emp set sal=sal-400 where deptno=10;
2、手動切換歸檔日誌
  1. SQL> alter system switch logfile;
  2. System altered
3、查詢歸檔日誌相關資訊
  1. SQL> select name,sequence# from v$archived_log;
    NAME                                                SEQUENCE#
    --------------------------------------------------- ----------
    /u01/oracle/archive/1_34_895068568.dbf              34
    /u01/oracle/archive/1_35_895068568.dbf              35
    /u01/oracle/archive/1_36_895068568.dbf              36
    3 rows selected
4、新增需要進行解析的日誌檔案
  1. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_34_895068568.dbf',dbms_logmnr.new);
  2. PL/SQL procedure successfully completed

  4. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_35_895068568.dbf',dbms_logmnr.addfile);
  5. PL/SQL procedure successfully completed

  7. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_36_895068568.dbf',dbms_logmnr.addfile);
  8. PL/SQL procedure successfully completed
5、使用線上字典進行解析+只查詢commit的資料+不顯示rowid
  1. SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only+dbms_logmnr.no_rowid_in_stmt);
  2. PL/SQL procedure successfully completed
6、查詢解析結果
  1. SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='EMP';
    SQL_REDO                                                      SQL_UNDO
    ------------------------------------------------------------------------------------------------------------------------
    update "SCOTT"."EMP" set "SAL" = '2472' where "SAL" = '2462' update "SCOTT"."EMP" set "SAL" = '2462' where "SAL" = '2472'
    update "SCOTT"."EMP" set "SAL" = '5022' where "SAL" = '5012' update "SCOTT"."EMP" set "SAL" = '5012' where "SAL" = '5022'
    update "SCOTT"."EMP" set "SAL" = '1322' where "SAL" = '1312' update "SCOTT"."EMP" set "SAL" = '1312' where "SAL" = '1322'
    update "SCOTT"."EMP" set "SAL" = '2572' where "SAL" = '2472' update "SCOTT"."EMP" set "SAL" = '2472' where "SAL" = '2572'
    update "SCOTT"."EMP" set "SAL" = '5122' where "SAL" = '5022' update "SCOTT"."EMP" set "SAL" = '5022' where "SAL" = '5122'
    update "SCOTT"."EMP" set "SAL" = '1422' where "SAL" = '1322' update "SCOTT"."EMP" set "SAL" = '1322' where "SAL" = '1422'
    update "SCOTT"."EMP" set "SAL" = '1020' where "SAL" = '820'  update "SCOTT"."EMP" set "SAL" = '820' where "SAL" = '1020'
    update "SCOTT"."EMP" set "SAL" = '3195' where "SAL" = '2995' update "SCOTT"."EMP" set "SAL" = '2995' where "SAL" = '3195'
    update "SCOTT"."EMP" set "SAL" = '3220' where "SAL" = '3020' update "SCOTT"."EMP" set "SAL" = '3020' where "SAL" = '3220'
    update "SCOTT"."EMP" set "SAL" = '1320' where "SAL" = '1120' update "SCOTT"."EMP" set "SAL" = '1120' where "SAL" = '1320'
    update "SCOTT"."EMP" set "SAL" = '3220' where "SAL" = '3020' update "SCOTT"."EMP" set "SAL" = '3020' where "SAL" = '3220'
    update "SCOTT"."EMP" set "SAL" = '1930' where "SAL" = '1630' update "SCOTT"."EMP" set "SAL" = '1630' where "SAL" = '1930'
    update "SCOTT"."EMP" set "SAL" = '1580' where "SAL" = '1280' update "SCOTT"."EMP" set "SAL" = '1280' where "SAL" = '1580'
    update "SCOTT"."EMP" set "SAL" = '1580' where "SAL" = '1280' update "SCOTT"."EMP" set "SAL" = '1280' where "SAL" = '1580'
    update "SCOTT"."EMP" set "SAL" = '3180' where "SAL" = '2880' update "SCOTT"."EMP" set "SAL" = '2880' where "SAL" = '3180'
    update "SCOTT"."EMP" set "SAL" = '1830' where "SAL" = '1530' update "SCOTT"."EMP" set "SAL" = '1530' where "SAL" = '1830'
    update "SCOTT"."EMP" set "SAL" = '1280' where "SAL" = '980'  update "SCOTT"."EMP" set "SAL" = '980' where "SAL" = '1280'
    update "SCOTT"."EMP" set "SAL" = '2172' where "SAL" = '2572' update "SCOTT"."EMP" set "SAL" = '2572' where "SAL" = '2172'
    update "SCOTT"."EMP" set "SAL" = '4722' where "SAL" = '5122' update "SCOTT"."EMP" set "SAL" = '5122' where "SAL" = '4722'
    update "SCOTT"."EMP" set "SAL" = '1022' where "SAL" = '1422' update "SCOTT"."EMP" set "SAL" = '1422' where "SAL" = '1022'
    20 rows selected
7、結束整個Logminer
  1. SQL> exec dbms_logmnr.end_logmnr();
  2. PL/SQL procedure successfully completed
其中第5步也可以使用utl_file_dir（不建議,該引數只是為了相容9i），具體步驟如下：
①修改utl_file_dir引數，並重啟資料庫
  1. SQL> alter system set utl_file_dir='/home/oracle/' scope=spfile;
  2. System altered.

  4. SQL> shutdown immediate;

  6. SQL> startup
  7. ORACLE instance started.
②使用dbms_logmnr_d.build建立OS上的字典檔案
  1. SQL> exec dbms_logmnr_d.build('logmnr_dict.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file);
  2. PL/SQL procedure successfully completed.
③新增歸檔日誌檔案
  1. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_32_895068568.dbf',dbms_logmnr.new);
  2. PL/SQL procedure successfully completed.

  4. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_33_895068568.dbf',dbms_logmnr.addfile);
  5. PL/SQL procedure successfully completed.

  7. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_34_895068568.dbf',dbms_logmnr.addfile);
  8. PL/SQL procedure successfully completed.
④啟用Logmnr分析
  1. SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr_dict.ora');
  2. PL/SQL procedure successfully completed.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29812844/viewspace-1988827/，如需轉載，請註明出處，否則將追究法律責任。

相關文章