[Logmnr]对重做日志进行数据挖掘

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、查询重做日志相关信息

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、添加需要进行解析的日志文件

SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/neal/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed

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

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

4、使用在线字典进行解析+只查询commit的数据

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);
PL/SQL procedure successfully completed

5、查询解析结果

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

SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed