Oracle10G LOGMNR捕獲不到記錄

Oracle10G LOGMNR捕獲不到記錄（zt）很早以前就碰到這個問題，一直以為是由於沒有設定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:ORACLEORADATAYTK102REDO03.LOG
2 E:ORACLEORADATAYTK102REDO02.LOG
1 E:ORACLEORADATAYTK102REDO01.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:ORACLEORADATAYTK102REDO03.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;
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;
SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;
SQL> DROP TABLE T PURGE;
SQL> CREATE TABLE T (ID NUMBER);
SQL> INSERT INTO T VALUES (1);
SQL> COMMIT;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('E:ORACLEORADATAYTK102REDO01.LOG', SYS.DBMS_LOGMNR.NEW)

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。

上一篇文章說明了LOGMNR找不到資料的現象和對應的解決方法，但是並沒有說明導致問題產生的原因。

而Oracle在METALINK的另一篇文章中對這個問題進行簡單的說明：Bug No. 3877515。

根據這篇文章的描述，在沒有設定SUPPLEMENTAL LOG DATA時，10g使用記憶體UNDO技術IMU（IN-MEMORY UNDO），而這種技術是LOGMNR所不支援的。因此通過LOGMNR分析10g的記錄是得不到結果的。而9i則沒有這種情況。

如果確實如METALINK所說，Oracle使用的是IN MEMORY UNDO技術，那麼重啟資料庫後第一次操作肯定是要被記錄到日誌中的：

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP
--- ---
YES YES

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

資料庫已更改。

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUP SUP
--- ---
NO NO

SQL> ALTER SYSTEM SWITCH LOGFILE;

系統已更改。

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

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 248 INACTIVE
2 249 ACTIVE
3 250 CURRENT

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

GROUP# MEMBER
---------- --------------------------------------------------
3 E:ORACLEORADATAYTK102REDO03.LOG
2 E:ORACLEORADATAYTK102REDO02.LOG
1 E:ORACLEORADATAYTK102REDO01.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:ORACLEORADATAYTK102REDO03.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);

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 過程已成功完成。

改變SUPPLEMENTAL LOG DA他的狀態，回到預設情況下，這時通過LOGMNR沒有獲取到DML操作，下面重啟資料庫：

SQL> CONN / AS SYSDBA已連線到空閒例程。
SQL> SHUTDOWN IMMEDIATE資料庫已經關閉。已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> STARTUP
ORACLE 例程已經啟動。

Total System Global Area 603979776 bytes
Fixed Size 1249332 bytes
Variable Size 226496460 bytes
Database Buffers 369098752 bytes
Redo Buffers 7135232 bytes資料庫裝載完畢。資料庫已經開啟。

下面重新執行上面的指令碼，檢查是否可以在LOGMNR中獲取DML：

SQL> CONN YANGTK/YANGTK@YTK102已連線。
SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 251 CURRENT
2 249 INACTIVE
3 250 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:ORACLEORADATAYTK102REDO01.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);

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 過程已成功完成。

仍然無法獲取DML，看來這種IN-MEMORY UNDO和資料庫重啟沒有關係。那麼建立一個從未建立過的表，插入一條新的記錄，是否能捕獲到呢：

SQL> CREATE TABLE T_NO_EXISTS_BEFORE (ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> INSERT INTO T_NO_EXISTS_BEFORE VALUES (281082347, 'NAME NEVER BEEN INSERTED');

已建立 1 行。

SQL> COMMIT;

提交完成。

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

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 251 ACTIVE
2 252 CURRENT
3 250 INACTIVE

SQL> ALTER SYSTEM SWITCH LOGFILE;

系統已更改。

SQL> EXEC SYS.DBMS_LOGMNR.ADD_LOGFILE('E:ORACLEORADATAYTK102REDO02.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_NO_EXISTS_BEFORE';

SQL_REDO
---------------------------------------------------------------------
CREATE TABLE T_NO_EXISTS_BEFORE (ID NUMBER, NAME VARCHAR2(30));

SQL> EXEC SYS.DBMS_LOGMNR.END_LOGMNR

PL/SQL 過程已成功完成。

同樣無法捕獲，看來IMU和名稱上表示出來的含義還是有一定差距的。不過值得欣慰的是，Oracle11g中，即使不設定SUPPLEMENTAL LOG DATA，也可以通過LOGMNR獲取DML，Oracle的LOGMNR預設狀態又恢復到了9I的情況：

SQL> CONN YANGTK/yangtk@TEST11G已連線。
SQL> SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1279 CURRENT
2 1277 INACTIVE
3 1278 INACTIVE

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

GROUP# MEMBER
---------- --------------------------------------------------
1 /data/oracle/oradata/test11g/redo01.log
2 /data/oracle/oradata/test11g/redo02.log
3 /data/oracle/oradata/test11g/redo03.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('/data/oracle/oradata/test11g/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 過程已成功完成。

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Producti
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
NO NO

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24558279/viewspace-746152/，如需轉載，請註明出處，否則將追究法律責任。