LogMiner配置使用

Table of Contents

■启动/关闭supplemental log，不需重启数据库

否则不能捕获所有的DML/DDL/DCL等操作日志
select supplemental_log_data_min from v$database;
alter database add supplemental log data;
alter database drop supplemental log data;

■如没安装包dbms_logmnr/dbms_logmnr_d，则需安装【11g默认已安装】

@?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslmd.sql
select table_name from dict where table_name like '%LOGM%';

■配置LogMiner文件夹，需重启数据库

CREATE DIRECTORY utlfile AS '/home/oracle/logmnr';
alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;
show parameter utl_file_dir

■创建字典文件

dba用户登陆，执行：
EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logmnr');
select db_name,filename from v$logmnr_dictionary;

■加入需分析的日志文件

Oracle的LogMiner可以分析在线（online）和归档（offline）两种日志文件，
加入分析日志文件使用dbms_logmnr.add_logfile过程，第一个文件使用dbms_logmnr.NEW参数，后面文件使用dbms_logmnr.ADDFILE参数。

BEGIN
dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/hebeicnc/redo01a.log',options=>dbms_logmnr.NEW);
dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/hebeicnc/redo02a.log',options=>dbms_logmnr.ADDFILE);
END;
/

■使用LogMiner进行日志分析

Oracle的LogMiner分析时分为无限制条件和限制条件两种
execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora');
execute dbms_logmnr.start_logmnr(options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dictionary.ora',
starttime=>to_date('2019-07-09 10:25:00','YYYY-MM-DD HH24:MI:SS'),
endtime=>to_date('2019-07-09 10:45:59','YYYY-MM-DD HH24:MI:SS'));

select username,timestamp,sql_redo from v$logmnr_contents t where t.seg_name ='T_EOM_DISPATCH_TABLE';
select username,timestamp,sql_redo from v$logmnr_contents t where t.seg_name ='T_EOM_GENERAL_INFO';
select username,timestamp,sql_redo from v$logmnr_contents t where t.seg_name ='T_EOM_FEEDBACK_TABLE';

select username,timestamp,sql_redo from v$logmnr_contents t where lower(sql_redo) like '%delete from t_eom_dispatch_table%';
select username,timestamp,sql_redo from v$logmnr_contents t where lower(sql_redo) like '%delete from t_eom_general_info%';

根据时间、监听日志可以查到操作来源

■结束日志挖掘

exec dbms_logmnr.end_logmnr;

■■example
--
BEGIN
  sys.dbms_logmnr.add_logfile('/oradata01/jkdb/redo01.log', sys.dbms_logmnr.new);
END;
/
SELECT MIN(low_time), MAX(high_time), MIN(low_scn), MAX(next_scn) - 1 FROM v$logmnr_logs;

BEGIN
  sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);
END;
/

SELECT scn, TIMESTAMP, sql_redo, session# session_num, username, session_info
  FROM v$logmnr_contents
 WHERE sql_redo LIKE '%update%'
 ORDER BY 1;

BEGIN
  sys.dbms_logmnr.end_logmnr();
END;
/
--

SYS@jkdb1> select member from v$logfile;
--------------------------------------------------------------------------------
/oradata01/jkdb/redo01.log
/oradata01/jkdb/redo02.log
/oradata01/jkdb/jkdb_redo1_5.log
/oradata01/jkdb/jkdb_redo1_6.log
/oradata01/jkdb/redo03.log
/oradata01/jkdb/redo04.log
/oradata01/jkdb/jkdb_redo1_7.log
/oradata01/jkdb/jkdb_redo1_8.log

BEGIN
  sys.dbms_logmnr.remove_logfile('+DATA/prod/onlinelog/group_1.261.900756585');
END;
/
SELECT MIN(low_time), MAX(high_time), MIN(low_scn), MAX(next_scn) - 1 FROM v$logmnr_logs;

■sqlarea
SELECT first_load_time, sql_text
  FROM gv$sqlarea
 WHERE upper(sql_text) LIKE '%T_BPM_CRM_XML_TMP%'
   AND first_load_time > to_date('20170818184000', 'yyyy/mm/dd hh24:mi:ss')
   AND first_load_time < to_date('20170818185000', 'yyyy/mm/dd hh24:mi:ss')
 ORDER BY first_load_time;

■dba_audit_trail
SELECT obj_name, TIMESTAMP, action_name
  FROM dba_audit_trail
 WHERE obj_name = 'T_BPM_CRM_XML_TMP'
 ORDER BY TIMESTAMP;