Oracle logminer 异机日志挖掘分析

1、把字典放到在线日志文件

SQL> EXECUTE DBMS_LOGMNR_D.BUILD( -OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);> 

PL/SQL procedure successfully completed.

--执行命令后产生的日志
Mon May 21 16:45:39 2018
......
Mon May 21 16:46:12 2018
Mon May 21 16:46:12 2018
Logminer Bld: Done

--查看字典所在归档文件
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
NAME
--------------------------------------------------------------------------------
+DG_ARCH/xxdb/xxdb1_25077_90789xxxx.arc

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
NAME
--------------------------------------------------------------------------------
+DG_ARCH/xxdb/xxdb1_25078_90789xxxx.arc

SQL> 

--日志文件大小
-rw-r-----  1 oracle  asmadmin  24M May 21 16:50 xxdb1_25077_90789xxxx.dbf
-rw-r-----  1 oracle  asmadmin 1.6M May 21 16:51 xxdb1_25078_90789xxxx.dbf

--copy日志文件到本地磁盘
rman  copy 归档
copy archivelog '+DG_ARCH/xxdb/xxdb1_25077_90789xxxx.arc' to '/tmp/xxdb1_25077_90789xxxx.dbf';
copy archivelog '+DG_ARCH/xxdb/xxdb1_25078_90789xxxx.arc' to '/tmp/xxdb1_25078_90789xxxx.dbf';

2、添加归档文件，并进行日志分析

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/optt/xxdb/arch/xxdb1_25077_90789xxxx.dbf',OPTIONS => DBMS_LOGMNR.NEW)
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/optt/xxdb/arch/xxdb1_25078_90789xxxx.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE)
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/optt/xxdb/arch/1_24777_90789xxxx.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/optt/xxdb/arch/1_24778_90789xxxx.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/optt/xxdb/arch/1_24779_90789xxxx.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/optt/xxdb/arch/2_24457_90789xxxx.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/optt/xxdb/arch/2_24458_90789xxxx.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);

3、使用在线日志数据字典启动logminer进行分析

execute dbms_logmnr.start_logmnr(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS, StartTime =>to_date('2018-05-07 09:40:00','YYYY-MM-DD HH24:MI:SS'), EndTime =>to_date('2018-05-07 10:00:00','YYYY-MM-DD HH24:MI:SS '));

--注意需要加参数DBMS_LOGMNR.DICT_FROM_REDO_LOGS

4、创建一张表来存储分析结果

create table t3 as select * from v$logmnr_contents;

5、过滤分析结果

select * from sys.t3 where sql_redo like '%APP_ONDEMAND_ORDER%' AND sql_redo like '%11180507094646032798%'; 

--下面是记录信息，可以定位到操作人详细登录信息及操作语句