Oracle10G LOGMNR捕獲不到記錄（一）

很早以前就碰到這個問題，一直以為是由於沒有設定FORCE_LOGGING的問題，今天才發現不是這個問題。

 

 

問題起源是在10g的版本上使用LOGMNR找不到剛剛執行的DML操作：

SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1        245 INACTIVE
         2        246 INACTIVE
         3        247 CURRENT

SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE;

    GROUP# MEMBER
---------- --------------------------------------------------
         3 E:\ORACLE\ORADATA\YTK102\REDO03.LOG
         2 E:\ORACLE\ORADATA\YTK102\REDO02.LOG
         1 E:\ORACLE\ORADATA\YTK102\REDO01.LOG

SQL> DROP TABLE T PURGE;

表已刪除。

SQL> CREATE TABLE T (ID NUMBER);

表已建立。

SQL> INSERT INTO T VALUES (1);

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> ALTER SYSTEM SWITCH LOGFILE;

系統已更改。

SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('E:\ORACLE\ORADATA\YTK102\REDO03.LOG', SYS.DBMS_LOGMNR.NEW)

PL/SQL 過程已成功完成。

SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 過程已成功完成。

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = USER AND TABLE_NAME = 'T';

SQL_REDO
------------------------------------------------------------------
CREATE TABLE T (ID NUMBER);

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 過程已成功完成。

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

本來一直是認為是沒有設定FORCE_LOGGING，導致部分記錄沒有在REDO檔案中被記錄，結果查詢V$DATABASE確發現當前正是FORCE LOGGING狀態：

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR
---
YES

同樣的問題從沒有在9i上發生過，說明應該是Oracle10g的某些改變導致了問題的產生。

查詢了metalink，Oracle在文件Doc ID:  Note:291574.1中對這個問題進行了詳細說明，如果希望LOGMNR可以得到記錄，應該設定SUPPLEMENTAL LOG DATA PRIMARY KEY和UNIQUE INDEX，這樣Oracle才能確保LOGMNR可以獲取SQL語句：

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP
--- ---
NO  NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

資料庫已更改。

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP
--- ---
YES YES

SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1        248 CURRENT
         2        246 INACTIVE
         3        247 INACTIVE

SQL> DROP TABLE T PURGE;

表已刪除。

SQL> CREATE TABLE T (ID NUMBER);

表已建立。

SQL> INSERT INTO T VALUES (1);

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> ALTER SYSTEM SWITCH LOGFILE;

系統已更改。

SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('E:\ORACLE\ORADATA\YTK102\REDO01.LOG', SYS.DBMS_LOGMNR.NEW)

PL/SQL 過程已成功完成。

SQL> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 過程已成功完成。

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = USER AND TABLE_NAME = 'T';

SQL_REDO
----------------------------------------------------------------------------------
DROP TABLE T PURGE;
CREATE TABLE T (ID NUMBER);
insert into "YANGTK"."T"("ID") values ('1');

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 過程已成功完成。

可以看到，在10g中預設情況下LOGMNR已經不是一個可靠的資料獲取的方式，希望透過這種方式獲取丟失資料，則需要提前設定SUPPLEMENTAL LOG DATA。