日志挖掘的流程
1 建立一目录（存储挖掘数据）
2 指定你要分析日志
3 开始挖
4 查询结果
5 结束挖掘
1、Specify a LogMiner dictionary.

Use the DBMS_LOGMNR_D.BUILD procedure or specify the dictionary when you start LogMiner (in Step 3), or both, depending on the type of dictionary you plan to use.

2、Specify a list of redo log files for analysis.

Use the DBMS_LOGMNR.ADD_LOGFILE procedure, or direct LogMiner to create a list of log files for analysis automatically when you start LogMiner (in Step 3).

3、Start LogMiner.

Use the DBMS_LOGMNR.START_LOGMNR procedure.

Request the redo data of interest.

4、Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view.)

5、End the LogMiner session.

Use the DBMS_LOGMNR.END_LOGMNR procedure.

oracle日志分析工具LogMiner使用

日志挖掘先决条件
1、归档模式
查询：archive log list;
开启：shutdown immediate;
startup mount;
alter database archive log;
alter database open;
2、开启日志补全
查询：select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
开启：ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

3、设置时间格式

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’;
设置时间可以方便查出什么时候发生的错误操作

日志挖掘
操作过程中千万千万不能退出会话，否则要重新4~6的步骤！！！
1、设置挖掘字典
show parameter utl;
alter system set utl_file_dir=’/tmp’ scope=spfile;
shutdown immediate;
startup；

2、生成挖掘字典文件
EXECUTE DBMS_LOGMNR_D.BUILD(‘dictionary.ora’, ‘/tmp’,DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

3、添加要分析的日志到挖掘字典文件里
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => ‘+DATA/orcl/onlinelog/group_3.263.822001563′,OPTIONS => DBMS_LOGMNR.NEW);

4、开始挖掘
EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME =>’/tmp/dictionary.ora’);

5、查询分析日志
SELECT OPERATION, SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = ‘OE’ AND SEG_NAME = ‘ORDERS’ AND
OPERATION = ‘DELETE’ AND USERNAME = ‘RON’;

SELECT SQL_REDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = ‘EMP’; –查找当时错误的操作，如果操作太多，带上时间

SELECT SQL_undo
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = ‘EMP’; –找出对应UNDO 还原数据

注意设置文本长度，避免部分数据太长无法导入
set pagesize 0
set linesize 200
spool /tmp/undo.data
SELECT SQL_undo
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = ‘EMP’;
spool off –重定向到文件中
vim /tmp/undo.data 去掉无用信息
@/tmp/undo.data

6、检查是否还原错误操作

7、转存挖掘结果，关闭工具
create table xxx as select * from v$logmnr_contents;

execute dbms_logmnr.end_logmnr();