Oracle logminer 常用命令

1、11g安装logminer

@?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslmd.sql
alter database add supplemental log data; 
select SUPPLEMENTAL_LOG_DATA_MIN from v$database; 
alter system set utl_file_dir='/oracle/oradata/oradb11/LOGMNR' scope=spfile;

2、生成数据字典

execute dbms_logmnr_d.build(dictionary_filename =>'dictionary.ora',dictionary_location =>'/oracle/oradata/oradb11/LOGMNR');
execute dbms_logmnr_d.build ('logminer_dict.ora'，'/u01/app/oracle/arch'，dbms_logmnr_d.store_in_flat_file);

3、添加要解析的归档

---添加第一个归档
exec dbms_logmnr.add_logfile(logfilename=>'/archive/erp/1_1100_946834694.dbf',options=>dbms_logmnr.new);
--添加其他归档
exec dbms_logmnr.add_logfile(logfilename=>'/archive/erp/1_1101_946834694.dbf',options=>dbms_logmnr.addfile);

4、日志解析

--使用源数据库的数据字典（online catalog） 
execute dbms_logmnr.start_logmnr (options=>dbms_logmnr.dict_from_online_catalog);

----摘取LogMiner数据字典到操作系统文件 
execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/oradata/oradb11/LOGMNR/dictionary.ora');
---按时间段来分析
execute dbms_logmnr.start_logmnr(startTime => to_date('2017-03-06 11:00:18','yyyy-mm-dd hh24:mi:ss'),
                                 endTime => to_date('2017-03-06 11:02:06','yyyy-mm-dd hh24:mi:ss'),
                                 DictFileName => '/oracle/oradata/oradb11/LOGMNR/dictionary.ora');
----将数据存储到临时表
Create table tab_logminer_20180805 tablespace TBS_CRM as select * from V$LOGMNR_CONTENTS;
--释放内存
execute dbms_logmnr.end_logmnr;

5、查询脚本 

select SCN,
       to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') date_time,
       OPERATION,
       SQL_REDO,
       SQL_UNDO
from   sys.tab_logminer_20180814
where  SEG_OWNER = upper('Hicano_POS')
and    ROLLBACK = 0
and    TIMESTAMP < to_date('2018-08-14 13:00:00', 'yyyy-mm-dd hh24:mi:ss');