LogMiner配置使用

■启动/关闭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;

发表评论

电子邮件地址不会被公开。 必填项已用*标注