ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验-Oracle10G LOGMNR捕获不到记录

oracle实验-Oracle10G LOGMNR捕获不到记录

原创 Linux操作系统 作者:279531451 时间:2009-09-10 23:20:19 0 删除 编辑
Oracle10G LOGMNR捕获不到记录(zt)2008-06-24 12:24很早以前就碰到这个问题,一直以为是由于没有设置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 DATA的状态,回到默认情况下,这时通过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/7551038/viewspace-614392/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-01

  • 博文量
    100
  • 访问量
    153843