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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28194062/viewspace-1700310/