logminer

1.安装必要的package
以sys的身份运行下面两个sql
$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/dbmslmd.sql

2.数据字典
为logminer指定数据字典
使用存储过程dbms_logmnr.build或者在当你启动logminer的时候进行指定。只取决于你使用logminer字典的形式
使用字典三种方式
 一 online catalog
 execute dbms_logmnr.start_logmnr(-option=>dbms_logmnr.dict_from_online_catalog);
 二 到处数据字典信息到redologfile中
 execute dbms_logmnr_d.build(-option=>dbms_logmnr_d.store_in_redo_logs);
 查看哪些redolog file包含的有数据字典信息
 SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
三 将数据字典信息到处到第三方文件中
由于到处文件需要写到操作系统文件中，所以需要先设置一个参数 设定导出的位置信息
utl_file_dir=''
然后
EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dict20090830.dat',dictionary_location => '/home/u01');

ERROR at line 1:
ORA-01336: specified dictionary file cannot be opened
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3474
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3552
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1
google一把发现有的说不再支持外部字典表信息  但是文档上时这么介绍的 继续google------------------------

另外10g中默认不对dml在redo中进行记录，如果要记录需要执行下面语句：
SQL> alter database add supplemental log data(primary key,unique index)columns;

Database altered.

SQL> alter database force logging;

Database altered.



测试：
SQL>  SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          7 ACTIVE
         2          8 CURRENT
         3          6 INACTIVE


SQL> create table test(num number(2));

Table created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> insert into test values(3);

1 row created.

SQL> commit;

Commit complete.

select group#,member from v$logfile;

    GROUP# MEMBER
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 /home/u02/oradata/billy/redo01.log
         2 /home/u02/oradata/billy/redo02.log
         3 /home/u02/oradata/billy/redo03.log

SQL>  EXEC DBMS_LOGMNR.ADD_LOGFILE('/home/u02/oradata/billy/redo02.log', DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>  EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

 SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER ='DGYY' AND TABLE_NAME='TEST';

SQL_REDO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create table test(num number(2));
insert into "DGYY"."TEST"("NUM") values ('1');
insert into "DGYY"."TEST"("NUM") values ('2');
insert into "DGYY"."TEST"("NUM") values ('3');

4 rows selected.

按照时间来取
SQL>  EXECUTE DBMS_LOGMNR.START_LOGMNR( -
>    STARTTIME => '2009-08-30 22:00:00', -
>    ENDTIME => '2009-08-30 23:12:59', -
>    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
>    DBMS_LOGMNR.CONTINUOUS_MINE);

PL/SQL procedure successfully completed.


SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.