Oracle工具：logminer的简单使用

我的环境：

[root@localhost ~]# uname -a

Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

sys@ORCL> select * from v$version where rownum=1;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

10g其实不需要生成dictionary文件了。只要直接把日志文件加载，然后分析即可。

1 产生数据库操作

hr@ORCL> drop table t purge; 
 
Table dropped. 
 
hr@ORCL> create table logmnr_test (id number,name varchar2(20)); 
 
Table created. 
 
hr@ORCL> insert into logmnr_test values(1,'think'); 
 
1 row created. 
 
hr@ORCL> insert into logmnr_test values(2,'water'); 
 
1 row created. 
 
hr@ORCL> commit;                     
 
Commit complete. 
 
hr@ORCL> select sequence#,status from v$log; 
 
SEQUENCE# STATUS 
---------- ----------------  
        14 CURRENT 
        13 INACTIVE 
        12 INACTIVE 
hr@ORCL> update logmnr_test set name='think_pad' where id=2; 
 
1 row updated. 
 
hr@ORCL> commit; 
 
Commit complete. 
 
hr@ORCL> alter system switch logfile; 
 
System altered. 
 
hr@ORCL> select sequence#,name from v$archived_log; 
 
SEQUENCE# 
----------  
NAME 
----------------------------------------------------------------------------------------------------  
............................. 
        14 
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc 
2 为分析指定日志文件

sys@ORCL> select db_name,thread_sqn,filename 
  2         from v$logmnr_logs; 
 
no rows selected 
 
sys@ORCL> exec DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc',dbms_logmnr.NEW); 
 
PL/SQL procedure successfully completed. 
 
若想接着分析更多的日志，把dbms_logmnr.NEW改成dbms_logmnr.addfile即可。 
 
sys@ORCL> select db_name,thread_sqn,filename from v$logmnr_logs; 
 
DB_NAME  THREAD_SQN 
-------- ----------  
FILENAME 
----------------------------------------------------------------------------------------------------  
ORCL             14 
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc
3 启动logminer

sys@ORCL> exec DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); 
 
PL/SQL procedure successfully completed. 
 
若是大数据量的分析，可以指定SCN或者时间的范围。