[Logmnr]對重做日誌進行資料探勘

梓沐發表於2016-02-15
1、建立演示資料
  1. SQL> conn scott/tiger@neal_192.168.8.205;
    Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
    Connected as scott@neal_192.168.8.205

    SQL> create table t(id number);
    Table created

    SQL> insert into t values(1);
    1 row inserted

    SQL> insert into t values(2);
    1 row inserted

    SQL> commit;
    Commit complete

    SQL> update emp set sal=sal+1 where deptno=10;
    3 rows updated

    SQL> commit;
    Commit complete
2、查詢重做日誌相關資訊
  1. SQL> conn sys/oracle@neal_192.168.8.205 as sysdba
    Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
    Connected as sys@neAL_192.168.8.205 AS SYSDBA

    SQL> select group#,sequence#,archived,status,first_change# from v$log;
        GROUP#  SEQUENCE# ARCHIVED STATUS           FIRST_CHANGE#
    ---------- ---------- -------- ---------------- -------------
             1         34 YES      INACTIVE               1368330
             2         35 NO       CURRENT                1368340
             3         33 YES      INACTIVE               1368318

    SQL> select member from v$logfile;
    MEMBER
    --------------------------------------------------------------------------------
    /u01/oracle/oradata/neal/redo03.log
    /u01/oracle/oradata/neal/redo02.log
    /u01/oracle/oradata/neal/redo01.log
3、新增需要進行解析的日誌檔案
  1. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/neal/redo01.log',dbms_logmnr.new);
  2. PL/SQL procedure successfully completed

  4. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/neal/redo02.log',dbms_logmnr.addfile);
  5. PL/SQL procedure successfully completed

  7. SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/neal/redo03.log',dbms_logmnr.addfile);
  8. PL/SQL procedure successfully completed
4、使用線上字典進行解析+只查詢commit的資料
  1. SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);
  2. PL/SQL procedure successfully completed
5、查詢解析結果
  1. SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='T' and operation='INSERT';
    SQL_REDO                                    SQL_UNDO
    ------------------------------------------- ------------------------------------------------------------
    insert into "SCOTT"."T"("ID") values ('1'); delete from "SCOTT"."T" where "ID" = '1' and ROWID = 'AAAVeY
    insert into "SCOTT"."T"("ID") values ('2'); delete from "SCOTT"."T" where "ID" = '2' and ROWID = 'AAAVeY


    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'
6、結束整個Logminer
  1. SQL> exec dbms_logmnr.end_logmnr();
  2. PL/SQL procedure successfully completed


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

相關文章