oracle 挖掘日志,oracle 日志挖掘 归档日志

--创建日志挖掘使用的表空间

/*By default, all LogMiner tables are created to use the SYSAUX tablespace. However, it may be desirable to have LogMiner tables use an alternate tablespace.

Use this procedure to move LogMiner tables to an alternate tablespace */

create tablespace  logmnrts datafile '+DATA/prod1/datafile/logmnrts.dbf' size 500M;

execute dbms_logmnr_d.set_tablespace('logmnrts');

--创建日志字典，日志字典是指调用dbms_logmnr_d.build存储过程将logminer字典提取止源库的重做日志里，

begin

dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);

end;

/

--得到Logminer字典在哪些日志中，oracle提供了dictionary_begin和dictionary_end字段用来表示字典的起始和结尾。

select sequence#,name,dictionary_begin,dictionary_end

from v$archived_log

where  dictionary_begin='YES' or dictionary_end='YES';

select * from v$log;

select * from v$archived_log

--注册重做日志(如果采用日志字典作为Logminer字典，那么包含日志字典的日志必须注册)

--手动注册包含日志字典的日志

begin

dbms_logmnr.add_logfile

(

logfilename => '+FRA/prod1/archivelog/2015_06_15/thread_1_seq_82.279.882455199',

options=>dbms_logmnr.new

);

end;

--手动注册需要挖掘的重做日志

begin

dbms_logmnr.add_logfile

(

logfilename => '+FRA/prod1/archivelog/2015_06_15/thread_1_seq_85.278.882457457',

options=>dbms_logmnr.addfile

);

end;

select filename,dictionary_begin,dictionary_end from v$logmnr_logs;

--启动挖掘会话

begin

dbms_logmnr.start_logmnr

(

starttime=>to_date('2015-6-15 14:55:00','yyyy-mm-dd hh24:mi:ss'),

endtime=>to_date('2015-6-15 15:05:25','yyyy-mm-dd hh24:mi:ss'),

options=>

dbms_logmnr.dict_from_redo_logs+

dbms_logmnr.print_pretty_sql

);

end;

select * from v$logmnr_contents

where seg_owner='SCOTT' and

SEG_NAME='ZWJ';

create table zz tablespace users

as select * from v$logmnr_contents

--结束挖掘，释放系统资源

begin

dbms_logmnr.end_logmnr;

end;

挖掘数据：

1、常规挖掘：

select sql_redo,sql_undo from zz

where seg_owner='SCOTT' and

SEG_NAME='ZWJ';

插入操作：

sql_redo:

"insert into "SCOTT"."ZWJ"

values

"EMPNO" = 7369,

"ENAME" = 'SMITH',

"JOB" = 'CLERK',

"MGR" = 7902,

"HIREDATE" = '1980-12-17 12:00:00',

"SAL" = 800,

"COMM" IS NULL,

"DEPTNO" = 20;"

sql_undo:

"delete from "SCOTT"."ZWJ"

where

"EMPNO" = 7369 and

"ENAME" = 'SMITH' and

"JOB" = 'CLERK' and

"MGR" = 7902 and

"HIREDATE" = '1980-12-17 12:00:00' and

"SAL" = 800 and

"COMM" IS NULL and

"DEPTNO" = 20 and

ROWID = 'AAAUGdAAEAAAAITAAO';"

更新操作：

"update "SCOTT"."ZWJ"

set

"SAL" = 2940

where

"SAL" = 2450 and

ROWID = 'AAAUGdAAEAAAAITAAU';"

"update "SCOTT"."ZWJ"

set

"SAL" = 2450

where

"SAL" = 2940 and

ROWID = 'AAAUGdAAEAAAAITAAU';"

2、使用column_present函数和redo_value字段挖掘感兴趣的字段的变更

select sql_redo,sql_undo from v$logmnr_contents

where seg_owner='SCOTT' and

seg_name='ZWJ' and

dbms_logmnr.column_present

(

redo_value,'SCOTT.ZWJ.SAL'

)=1;

3、利用mine_value函数和redo_value,undo_value字段挖掘薪水幅度高于10%的变更

select sql_redo,sql_undo from v$logmnr_contents

where seg_owner='SCOTT' and

seg_name='ZWJ' and

dbms_logmnr.column_present

(

redo_value,'SCOTT.ZWJ.SAL'

)=1 and

dbms_logmnr.mine_value(redo_value,'SCOTT.ZWJ.SAL') /dbms_logmnr.mine_value(undo_value,'SCOTT.ZWJ.SAL')>1.1;

4、查询列表中的session_info,timestamp之类的字段使数据挖掘具有时候审计的味道。

select sql_redo,sql_undo，

session_info,

to_char(timestamp,'yyyy-mm-dd hh24:mi:ss')

from v$logmnr_contents

where seg_owner='SCOTT' and

seg_name='ZWJ' and

dbms_logmnr.column_present

(

redo_value,'SCOTT.ZWJ.SAL'

)=1 and

dbms_logmnr.mine_value(redo_value,'SCOTT.ZWJ.SAL') /dbms_logmnr.mine_value(undo_value,'SCOTT.ZWJ.SAL')>1.1;

select text from dba_source

where

owner='SYS' and

name='DBMS_LOGMNR' and

type='PACKAGE'

start with

text like 'COMMITTED_DATA_ONLY%CONSTANT%'

connect by

level <11 and

prior (line+1) = (line) and

prior (owner) =(owner) and

prior (name) = (name) and

prior (type) = (type)

order by line;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28194062/viewspace-1700310/，如需转载，请注明出处，否则将追究法律责任。