10g日志挖掘logmnr

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

一 环境

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博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-662518/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11780477/viewspace-662518/