logminer系列文章二（logminer如何对DML进行分析）

转自 http://blog.itpub.net/26613085/viewspace-1064010/

上篇文章通过logminer查看oracle日志的操作，对数据库进行的操作如下

SQL> create table dcits_test(id number);

Table created.

SQL> insert into dcits_test values(1);

1 row created.

SQL> insert into dcits_test values(2);

1 row created.

SQL> insert into dcits_test values(3);

1 row created.

SQL> drop table dcits_test;

Table dropped.

但是通过logminer只能查看到create和drop等DDL操作语句，如果想看用户都做了那些DML操作该怎么办呢？
需要更改数据库（alter database add supplemental log data）

SQL> alter database add supplemental log data;

Database altered.

SQL> create table test(id number);

Table created.

SQL> insert into test values(1);

1 row created.

 

SQL> insert into test values(2);

1 row created.

SQL> update test set id=10000 where id=1;

1 row updated.

SQL> delete from test where id =2;

1 row deleted.

SQL> insert into test values(3);

1 row created.

 

 

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         11   52428800          1 NO  CURRENT                 569316 22-DEC-13
         2          1          9   52428800          1 YES INACTIVE                568691 22-DEC-13
         3          1         10   52428800          1 YES INACTIVE                569176 22-DEC-13

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/dcits/redo03.log           NO
         2         ONLINE  /u01/app/oracle/oradata/dcits/redo02.log           NO
         1         ONLINE  /u01/app/oracle/oradata/dcits/redo01.log           NO

 

SQL> begin
  2    sys.dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/dcits/redo01.log',
  3                                     Options=> dbms_logmnr.new);
  4  end;
  5  /

PL/SQL procedure successfully completed.
下面开始分析日志的方法可以不用生成字典文件，前提是要分析的日志都是当前库生成的

SQL> begin
  2   sys.dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sql_redo from V$LOGMNR_CONTENTS where upper(sql_redo) like '%TEST%';

SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","S
PARE5","SPARE6") values ('51405','51405','0','TEST','1',NULL,'2',TO_DATE('23-DEC-13', 'DD-MON-RR'),TO_DATE('23-DEC-13', 'DD-MON-RR'),TO_DATE('23-DEC-13', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1',NU
LL,NULL,NULL,NULL);

create table test(id number);
insert into "SYS"."TEST"("ID") values ('1');
insert into "SYS"."TEST"("ID") values ('2');
update "SYS"."TEST" set "ID" = '10000' where "ID" = '1' and ROWID = 'AAAMjNAABAAAOkCAAA';
delete from "SYS"."TEST" where "ID" = '2' and ROWID = 'AAAMjNAABAAAOkCAAB';
insert into "SYS"."TEST"("ID") values ('3');

7 rows selected.

 

SQL> begin
  2    sys.dbms_logmnr.end_logmnr;
  3  end;
  4  /

PL/SQL procedure successfully completed.

也可以使用数据字典来做分析

SQL> begin
  2  sys.dbms_logmnr_d.build(dictionary_filename =>'logminerdcit.ora',
  3                          dictionary_location =>'/u01/logminer_dir/',
  4                                       options=>dbms_logmnr_d.store_in_flat_file);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> begin
  2    sys.dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/dcits/redo01.log',
  3                                     Options=> dbms_logmnr.new);
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> begin
  2    sys.dbms_logmnr.start_logmnr(DictFileName =>'/u01/logminer_dir/logminerdcit.ora');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sql_redo from V$LOGMNR_CONTENTS where upper(sql_redo) like '%TEST%';

SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","S
PARE5","SPARE6") values ('51405','51405','0','TEST','1',NULL,'2',TO_DATE('23-DEC-13', 'DD-MON-RR'),TO_DATE('23-DEC-13', 'DD-MON-RR'),TO_DATE('23-DEC-13', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1',NU
LL,NULL,NULL,NULL);

create table test(id number);
insert into "SYS"."TEST"("ID") values ('1');
insert into "SYS"."TEST"("ID") values ('2');
update "SYS"."TEST" set "ID" = '10000' where "ID" = '1' and ROWID = 'AAAMjNAABAAAOkCAAA';
delete from "SYS"."TEST" where "ID" = '2' and ROWID = 'AAAMjNAABAAAOkCAAB';
insert into "SYS"."TEST"("ID") values ('3');

7 rows selected.

SQL> begin
  2    sys.dbms_logmnr.end_logmnr;
  3  end;
  4  /

PL/SQL procedure successfully completed.