使用LogMiner工具可以轻松获得Oracle 重作日志文件及归档日志文件中的具体内容,特别是,该工具可以分析出所有对于数据库操作的DML语句,另外还可分析得到一些必要的回滚SQL语句。该工具特别适用于调试、审计或者回退某个特定的事务。
其主要用途有:
1. 跟踪数据库的变化:可以离线跟踪数据库的变化,而不会影响在线系统的性能;
2. 回退数据库的变化:回退特定的变化数据至某个scn时间点;
3. 优化和扩容计划:可通过分析日志文件中的数据以分析数据增长模式。
一, 首先,建立一个字典文件(dictionary file)。
Oracle 的日志文件中,对于表等用户对象( Object),并不是保存名字,而是保存一个ID 号。建立字典文件的目的就是使logminer在分析时可以将Object ID翻译成我们所熟悉的对象名。
建立字典文件之前,先要确保数据库的初始化参数 UTL_FILE_DIR 已经正确地设置。在sqlplus 下键入 show parameters utl_file_dir, 可以看到该参数的当前设置。如果没有值,必须修改数据库的initsid.ora文件,将utl_file_dir 指向一个你想用来保存字典文件的路径。本例中,笔者设置 UTL_FILE_DIR=/u11/logs。
创建字典文件的语句如下例:
begin sys.dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora', dictionary_location =>'/u11/logs');
end;
其中,dictionary.ora是笔者给字典文件起的文件名(可任意)。
整个创建过程,可能需要十几分钟到一个小时,视该数据库的object 个数以及繁忙程度而定。完成后,会在/u11/logs目录下看到一个名为dictionary.ora的文件。
二, 选取要分析的文件
日志文件和归档日志文件的数量是非常多的。以笔者公司的数据库而言,每半小时要产生一个50M的日志文件,数据量非常之大。因此事实上不可能把所有的日志文件都分析一遍(你要做也行,不过要保证有足够的空间和时间,并且不怕影响数据库性能),通常选取你感兴趣的时间段内的日志进行分析。
选取日志文件的操作如下例:
begin sys.dbms_logmnr.add_logfile (logfilename =>'/02/admin/mydb/redo01.log', options=>sys.dbms_logmnr.NEW);
end;
一次只能选取一个文件。若要增加文件,使用下例:
begin sys.dbms_logmnr.add_logfile (logfilename =>'/02/admin/mydb/redo02.log', options=>sys.dbms_logmnr.ADDFILE);
end;
若想去掉一个已经选取或增加的文件,使用REMOVEFILE:
begin sys.dbms_logmnr.add_logfile (logfilename =>'/02/admin/mydb/redo02.log', options=>sys.dbms_logmnr.REMOVEFILE);
end;
如此反复操作,可以把所有要分析的文件都选取进去。
三, 进行分析
选取好所有需要分析的文件后,执行下面的命令,开始分析:
begin sys.dbms_logmnr.start_logmnr (dictfilename =>'dictionary.ora');
end;
注意,这里的dictionary.ora就是前面创建的字典文件名。
分析过程根据所选取文件的数据量,可能需要几个小时。有时候,DBA可能并不需要这些日志文件中所有的数据,那么能否只分析部分数据呢?Oracle 容许你只分析指定时间段或者指定SCN段的数据,语法示例如下:
begin sys.dbms_logmnr.start_logmnr (dictfilename =>'dictionary.ora',starttime =>to_date('01-Aug-2001 08:30:00', 'DD-MON-YYYY HH:MI:SS'),endtime => to_date('01-Aug-2001 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
end;
或者,
begin sys.dbms_logmnr.start_logmnr (dictfilename =>'dictionary.ora',startscn =>100,endscn =>500);
end;
分析结束后,所分析到的数据可以从一个名为 V$LOGMNR_CONTENTS的视图中查询到。我们就可以应用这个视图中的内容来达成目的。
1、LogMiner可以帮你确定在某段时间所发的各种DML,DDL操作的具体时间和SCN号,它所依据的是归档日志文件及联机日志文件。
2、它只能在Oracle8i及以后的版本中使用,不过它可以分析Oracle8的日志。
3、Oracle8i只能用于分析DML操作,到Oracle9i则可以分析DDL操作了。
4、LogMiner不支持索引组织表、Long、LOB及集合类型。
5、MTS的环境也不能使用LogMiner.
【以下为配置好logminer后的一个找回数据的例子】
【场景】数据被修改了,过了一阵子发现改错了,要改回来,咋整?
【处理】找到那个时候的归档日志,通过日志挖掘恢复被修改的数据。
1、根据用户反馈的时间,找出所在时间点的归档日志
select thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$log_history order by first_time asc
2、拷贝归档日志到分析日志的数据库上,ASM方式可通过asmcmd的cp方式拷贝到文件系统再操作。所有操作均在分析数据库上运行
SQL> alter system set utl_file_dir='/oracle/logminer' scope=spfile sid='*';SQL> shutdown immediate;
SQL> startup
SQL> BEGIN
dbms_logmnr_d.build(
dictionary_filename=>'logminer_dict.dat',
dictionary_location=>'/oracle/logminer'
);
END;
/
SQL> shutdown immediate;
SQL> startup mount;
SQL> BEGIN
dbms_logmnr.add_logfile(
options=>dbms_logmnr.new,
logfilename=>'/oracle/8586.log'
);
END;
/
SQL> BEGIN
dbms_logmnr.add_logfile(
options => dbms_logmnr.addfile,
logfilename => '/oracle/8586.log'
);
END;
/
3、查看要挖掘的归档日志的时间范围
SQL> select filename,log_id,low_scn,to_char(low_time,'yyyy-mm-dd hh24:mi:ss'),next_scn,to_char(high_time,'yyyy-mm-dd hh24:mi:ss') from v$logmnr_logs;FILENAME LOG_ID LOW_SCN LOW_TIME NEXT_SCN HIGH_TIME
-------------------- ---------- ---------- ------------------- ---------- -------------------
/oracle/8585.log 8585 500368502 2015-09-01 18:31:22 500647663 2015-09-01 19:38:41
/oracle/8586.log 8586 500647663 2015-09-01 19:38:41 500905317 2015-09-01 21:00:26
4、开始日志挖掘
BEGIN
dbms_logmnr.start_logmnr(
dictfilename=>'/oracle/logminer/logminer_dict.dat',
starttime=>to_date('20150901 19:00:00','yyyymmdd hh24:mi:ss'),
endtime=>to_date('20150901 19:30:00','yyyymmdd hh24:mi:ss')
);
END;
/
当挖掘的库没有数据字典时,只会挖掘出对象号对应的SQL,需要注销dictfilename参数,否则无法进行。可考虑将system表空间备份恢复到挖掘库后在进行,以便于阅读。
BEGIN
dbms_logmnr.start_logmnr(
dictfilename=>'/oracle/logminer/logminer_dict.dat',
starttime=>to_date('20150901 19:00:00','yyyymmdd hh24:mi:ss'),
endtime=>to_date('20150901 19:30:00','yyyymmdd hh24:mi:ss')
);
END;
/
5、根据日志挖掘的内容筛选所需要的数据
select sample_time,session_id,session_serial#,operation,sql_redo,sql_undo,machine,program,module from v$logmnr_contents;