---与LogMiner相关的数据字典
v$loglist 它用于显示历史日志文件的一些信息
v$logmnr_dictionary 因logmnr可以有多个字典文件，该视图用于显示这方面信息。
v$logmnr_parameters 它用于显示logmnr的参数
v$logmnr_logs 它用于显示用于分析的日志列表信息。
1.设置logminer使用目录
alter system set utl_file_dir='/data/logmnr' scope=spfile;
shutdown immediate
startup
2.生成logminer package
conn /as sysdba
@?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslmd.sql
3.生成logminer dicttionary file
execute dbms_logmnr_d.build('logminer.ora','/data/logmnr');
4.将要分析的日志加入要分析的log list 中
execute dbms_logmnr.add_logfile ('/data/logmnr/REDO03.LOG',dbms_logmnr.new);
execute dbms_logmnr.add_logfile ('/data/logmnr/REDO02.LOG',dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile ('/data/logmnr/REDO01.LOG',dbms_logmnr.addfile);
如果需要从分析列表里去掉一个文件用
execute dbms_logmnr.remove_logfile('/u02/oradata/hkeudm/redo06.log');
5. 查询分析的日志文件包含的scn范围和日期范围。
set lines 220
column low_scn format 9999999999
column next_scn format 9999999999
column filename format a50
select log_id,low_time,high_time,low_scn,next_scn,FILENAME from v$logmnr_logs;
6. 执行分析:
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
execute dbms_logmnr.start_logmnr (dictfilename=> 'F:\logminer\logminer.ora', startscn=> 541945,endscn=>555792,
starttime => to_date('2009-02-25 10:00:02','yyyy-mm-dd hh24:mi:ss'), endtime => to_date('2009-02-27 10:40:02','yyyy
-mm-dd hh24:mi:ss') );
7045928
7047343
7049185
281474976710655
7.查看redo sql
select operation,sql_redo,sql_undo from v$logmnr_contents where seg_name is not null and rownum<10;
Select SCN,timestamp, session# session_num,sql_redo From V$LOGMNR_CONTENTS where username='HKEU_DATA' -- Order by 1
8 end
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.
reference:
http://www.oracleblog.org/working-case/dba-always-bad-luck-with-careless-customer/