DanBrother的部落格

1.  將第一個需要分析的Redo Log Files (Also apply to archive log files) 加入 (注意 .new)

exec dbms_logmnr.add_logfile(logfilename=>'G:\oracle\oradata\MYDB\ARCHIVE\MYDB0001S00118.ARC',options=>dbms_logmnr.new);

2. 如需要額外分析其他logfile,可以執行下列類似語法 (注意 .addfile) :

exec dbms_logmnr.add_logfile(logfilename=>'G:\oracle\oradata\MYDB\ARCHIVE\MYDB0001S00119.ARC',options=>dbms_logmnr.addfile);
　

SQL>desc v$logmnr_logs;
Name Null? Type
------------------------------  ----------------------------
LOG_ID                                     NUMBER
FILENAME                               VARCHAR2(512)
LOW_TIME                              DATE
HIGH_TIME                             DATE
DB_ID                                      NUMBER
DB_NAME                              VARCHAR2(8)
RESET_SCN                           NUMBER
RESET_SCN_TIME                DATE
COMPATIBLE                       VARCHAR2(17)     -- 11g and up
THREAD_ID                           NUMBER
THREAD_SQN                       NUMBER
LOW_SCN                              NUMBER
NEXT_SCN                             NUMBER
DICTIONARY_BEGIN          VARCHAR2(3)   -- 9i and up
DICTIONARY_END              VARCHAR2(3)    -- 9i and up
TYPE                                      VARCHAR2(7)      -- 10g and up 
BLOCKSIZE                           NUMBER  -- 10g and up
FILESIZE                                NUMBER  -- 10g and up
INFO                                       VARCHAR2(32)
STATUS                                  NUMBER
　

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select log_id,low_scn,low_time,next_scn,high_time
from v$logmnr_logs;

LOG_ID    LOW_SCN LOW_TIME                 NEXT_SCN         HIGH_TIME
---------- ---------------   ----------------------------   ----------------   ----------------------------
118         259550761   2010-11-05 11:14:52     259554500     2010-11-16 10:55:56
119         259554500   2010-11-16 10:55:56     259574502     2010-11-16 11:09:33


3.  建立字典檔(Dictionary File)  ( * 9i and up 可省略此步驟,直接用Dictionary Online Catalog)
set linesize 120
sho parameter utl_file_dir

NAME            TYPE         VALUE
--------------   --------------   ---------------
utl_file_dir   string             H:\LogMiner

exec dbms_logmnr_d.build('l_dictionary.ora','H:\LogMiner');

PL/SQL 程序順利完成


4. 開始分析Redo Log Files

exec dbms_logmnr.start_logmnr(startTime => to_date('2010-12-16 10:00:00','yyyy-mm-dd hh24:mi:ss'), -
endTime => to_date('2010-12-16 11:00:00','yyyy-mm-dd hh24:mi:ss'), -
DictFileName => 'H:\LogMiner\l_dictionary.ora');



exec dbms_logmnr.start_logmnr(startTime => to_date('2010-12-16 10:00:00','yyyy-mm-dd hh24:mi:ss'), -
                                               endTime => to_date('2010-12-16 11:00:00','yyyy-mm-dd hh24:mi:ss'), -
                                               Options => 'dbms_logmr.dict_from_online_catalog');

col username format a20
col sql_redo format a30
col operation format a30
select timestamp,username,session#,sql_redo,operation
from v$logmnr_contents
where username = 'SCOTT';

4. 結束Redo Log File 分析
exec dbms_logmnr.end_logmnr;