Oracle 日志挖掘logminer

用途

• 对oracle在线redo，归档日志进行分析。

目的

• 修正误操作
• 审计

dbms_logmnr

• 可以基于日志文件分析（一个或者多个）
• 可以基于时间段分析
• 可以基于SCN分析

实例

往t表插入数字1至9

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as test@local

SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         3         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO03.LOG                                                NO
         2         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO02.LOG                                                NO
         1         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO01.LOG                                                NO

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
         1          1         10   52428800        512          1 NO       INACTIVE               1089257 2018/12/17       1097614 2018/12/17
         2          1         11   52428800        512          1 NO       CURRENT                1097614 2018/12/17  281474976710 
         3          1          9   52428800        512          1 NO       INACTIVE               1058362 2018/12/2 1      1089257 2018/12/17

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1098917

SQL> insert into t select rownum from dual connect by rownum<10;
9 rows inserted

SQL> commit;
Commit complete

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1098933

SQL> exec dbms_logmnr.add_logfile('E:\ORACLE\ORADATA\ORCL\REDO02.LOG',dbms_logmnr.new);
PL/SQL procedure successfully completed

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog,StartScn=>1098917,EndScn=>1098933);
PL/SQL procedure successfully completed

SQL> select operation,sql_redo,sql_undo from v$logmnr_contents;
OPERATION                        SQL_REDO                                                                         SQL_UNDO
-------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
START                            set transaction read write;                                                      
INSERT                           insert into "TEST"."T"("NO") values ('1');                                       delete from "TEST"."T" where "NO" = '1' and ROWID = 'AAAR7VAAFAAAACGAAA';
INSERT                           insert into "TEST"."T"("NO") values ('2');                                       delete from "TEST"."T" where "NO" = '2' and ROWID = 'AAAR7VAAFAAAACGAAB';
INSERT                           insert into "TEST"."T"("NO") values ('3');                                       delete from "TEST"."T" where "NO" = '3' and ROWID = 'AAAR7VAAFAAAACGAAC';
INSERT                           insert into "TEST"."T"("NO") values ('4');                                       delete from "TEST"."T" where "NO" = '4' and ROWID = 'AAAR7VAAFAAAACGAAD';
INSERT                           insert into "TEST"."T"("NO") values ('5');                                       delete from "TEST"."T" where "NO" = '5' and ROWID = 'AAAR7VAAFAAAACGAAE';
INSERT                           insert into "TEST"."T"("NO") values ('6');                                       delete from "TEST"."T" where "NO" = '6' and ROWID = 'AAAR7VAAFAAAACGAAF';
INSERT                           insert into "TEST"."T"("NO") values ('7');                                       delete from "TEST"."T" where "NO" = '7' and ROWID = 'AAAR7VAAFAAAACGAAG';
INSERT                           insert into "TEST"."T"("NO") values ('8');                                       delete from "TEST"."T" where "NO" = '8' and ROWID = 'AAAR7VAAFAAAACGAAH';
INSERT                           insert into "TEST"."T"("NO") values ('9');                                       delete from "TEST"."T" where "NO" = '9' and ROWID = 'AAAR7VAAFAAAACGAAI';
COMMIT                           commit;                                                                          
11 rows selected

实验中遇到的错误

• 启用补充日志
  不启用不影响整个过程，但最终查询时部分操作（像插入，删除，会报"Unsupported"错误）分析不出来
  启用补充日志命令：alter database add supplemental log data;
  删除补充日志命令：alter database drop supplemental log data;