这里日志挖掘只写出步骤(源在哪里忘记了).其他的就不多写了:

一环境

RHEL5U4 + oracle database 10.2.0.4 single

sid : testdb1

log_archive_dest_1 : /oracle/arch

二步骤

1.创建logmnr表空间

SQL> create tablespace logmnr datafile '/oracle/oradata/testdb1/logmnr01.dbf' size 512M autoextend on maxsize 32000M extent management local segment space management auto;

Tablespace created

2.创建数据字典
SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dict20100510.txt',dictionary_location => '/home/oracle/script');

PL/SQL procedure successfully completed

3.设置表空间

SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnr');

PL/SQL procedure successfully completed

4.添加日志文件 (注意不要一次加太多,除非你机器够强)

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/oracle/arch/1_3281_703872266.dbf',Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/oracle/arch/1_3282_703872266.dbf',Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed

5.分析,添加时间段
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/script/dict20100510.txt',starttime=>to_date('2010-05-10 09:00:00','YYYY-MM-DD HH24:MI:SS'),endtime=>to_date('2010-05-10 10:30:00','YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed

6.使用在线字典

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

PL/SQL procedure successfully completed

7.将查出结果存储到表

create table logmnr_test_drug tablespace logmnr as select * from v$logmnr_contents where SEG_NAME in ('ENT_PROD_CATEGORY','ENT_PHYSIC_NAME_LIST','ENT_PRODUCT') and seg_owner='TEST_DRUG';

8. 结束日志分析,释放pga

SQL> exec dbms_logmnr.end_logmnr;

附:logmnr_logs各列解释

http://www.oracle86.com/ora_doc/server.111/b28320/dynviews_2037.htm#REFRN30135

火大,破空间日志编辑模式一点都不人性,调个字体大小什么的还要到其他页面去

ITPub博客

10g日志挖掘logmnr

最新文章