ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120523]关于11GR2无法通过logminer看到DML的问题.txt

[20120523]关于11GR2无法通过logminer看到DML的问题.txt

原创 Linux操作系统 作者:lfree 时间:2012-05-27 11:49:57 0 删除 编辑
[20120523]关于11GR2无法通过logminer看到DML的问题.txt


昨天工作需要,使用logminer挖掘一些dml信息,我发现漏掉一些信息,也就是一些dml语句无法抓取.
我在测试环境做了一些测试(我的测试环境11.2.0.1),btw,这个问题也存在10.2.0.4版本中.

1.测试脚本:
SQL> select * from v$version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> drop table t purge ;
SQL> create table t as select rownum id ,'test' name from dual connect by level<=3;
SQL> create unique index pk_t ON t (ID);
SQL> alter table t add constraint pk_t  primary key (id);

SQL> alter system archive log current  ;
System altered.

SQL> insert into t  values(4,'abcd');
1 row created.

SQL> commit ;

SQL> alter system archive log current  ;
System altered.

SQL> select rowid,ora_rowscn,t.* from t;
ROWID              ORA_ROWSCN         ID NAME
------------------ ---------- ---------- ----
AAAWvOAAEAAAAILAAA   11268540          1 test
AAAWvOAAEAAAAILAAB   11268540          2 test
AAAWvOAAEAAAAILAAC   11268540          3 test
AAAWvOAAEAAAAIPAAA   11268598          4 abcd

确定使用archive文件使用rman命令.

RMAN> list  archivelog from scn 11268598    ;

List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
505     1    505     A 2012-05-27 10:47:20
        Name: /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_05_27/o1_mf_1_505_7w35gdnx_.arc

2.使用logminer分析:
Begin
  SYS.DBMS_LOGMNR.ADD_LOGFILE( '/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_05_27/o1_mf_1_505_7w35gdnx_.arc', sys.dbms_logmnr.New);
end;

Begin
  SYS.DBMS_LOGMNR.START_LOGMNR ( ptions => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG+sys.dbms_logmnr.COMMITTED_DATA_ONLY);
end;

SELECT   SCN, TIMESTAMP, seg_owner, seg_name, row_id, session# session_num, username, sql_redo
    FROM v$logmnr_contents
   WHERE row_id = 'AAAWvOAAEAAAAIPAAA' OR (seg_owner = USER AND seg_name = 'T') OR SCN = 11268598
ORDER BY 1;

no rows selected

而执行如下:
$ strings /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_05_27/o1_mf_1_505_7w35gdnx_.arc
}|{z
{TEST
Thread 0001, Seq# 0000000505, SCN 0x000000abf1f1-0x000000abf201
HbE#v
SCOTT
hisdg
pts/2
oracle11g
6txd
7978
H_1O
abcd
--可以发现insert的字串abcd确实在redo中.不知道logminer为什么不能抽取DML语句.
--BTW,如果多插入几行,有时候能看到一些,但是不全.

3.修改隐含参数"_in_memory_undo".
SQL> alter system set "_in_memory_undo"=false;
System altered.

SQL> alter system archive log current  ;
System altered.

SQL> insert into t  values(5,'efgh');
1 row created.

SQL> commit ;
Commit complete.

SQL> alter system archive log current  ;
System altered.

SQL> select rowid,ora_rowscn,t.* from t;

ROWID              ORA_ROWSCN         ID NAME
------------------ ---------- ---------- ----
AAAWvOAAEAAAAILAAA   11268540          1 test
AAAWvOAAEAAAAILAAB   11268540          2 test
AAAWvOAAEAAAAILAAC   11268540          3 test
AAAWvOAAEAAAAIPAAA   11268970          4 abcd
AAAWvOAAEAAAAIPAAB   11268970          5 efgh


--重复logminer的分析:
RMAN> list  archivelog from scn 11268598    ;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name TEST
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
505     1    505     A 2012-05-27 10:47:20  Name: /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_05_27/o1_mf_1_505_7w35gdnx_.arc
506     1    506     A 2012-05-27 10:47:40  Name: /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_05_27/o1_mf_1_506_7w364r2p_.arc
507     1    507     A 2012-05-27 10:59:35  Name: /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_05_27/o1_mf_1_507_7w365h78_.arc

$ strings /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_05_27/o1_mf_1_507_7w365h78_.arc
}|{z
{TEST
Thread 0001, Seq# 0000000507, SCN 0x000000abf35c-0x000000abf36f
HbE#v
SCOTT0
hisdg
pts/2
oracle11g
8509
H_"O
'       IS
XQC=-
'       IS
'       IS
efgh
--可以发现insert的字串efgh确实在redo中.

SELECT   SCN, TIMESTAMP, seg_owner, seg_name, row_id, session# session_num, username, sql_redo
    FROM v$logmnr_contents
   WHERE row_id in ( 'AAAWvOAAEAAAAIPAAA','AAAWvOAAEAAAAIPAAB') OR (seg_owner = USER AND seg_name = 'T') OR SCN in ( 11268598,11268970)
ORDER BY 1;

SCN,TIMESTAMP,SEG_OWNER,SEG_NAME,ROW_ID,SESSION_NUM,USERNAME,SQL_REDO
------------------------------------------------------------------------------------------------------------------------
11268965,2012-5-27 10:59:46,SCOTT,T,AAAWvOAAEAAAAIPAAB,0,UNKNOWN,insert into "SCOTT"."T"("ID","NAME") values ('5','efgh');,
11268970,2012-5-27 10:59:57,,,AAAAAAAAAAAAAAAAAA,0,UNKNOWN,commit;,

--可以发现可以找到insert信息.


4.还原参数设置:
SQL> alter system set "_in_memory_undo"=true;
System altered.

SQL> alter system reset "_in_memory_undo" sid='*';
System altered.

5.很明显关闭IMU不是很好.

SELECT supplemental_log_data_all, supplemental_log_data_fk, supplemental_log_data_min, supplemental_log_data_pk,
       supplemental_log_data_ui
    FROM v$database;
SUP SUP SUPPLEME SUP SUP
--- --- -------- --- ---
NO  NO  NO       NO  NO

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

SELECT supplemental_log_data_all, supplemental_log_data_fk, supplemental_log_data_min, supplemental_log_data_pk,
       supplemental_log_data_ui
  3      FROM v$database;
SUP SUP SUPPLEME SUP SUP
--- --- -------- --- ---
NO  NO  IMPLICIT YES YES

SQL> alter system archive log current  ;
System altered.

SQL> insert into t  values(6,'ijkl');
1 row created.

SQL> commit;
Commit complete.

SQL> alter system archive log current  ;
System altered.

--再次使用logminer分析:
SELECT   SCN, TIMESTAMP, seg_owner, seg_name, row_id, session# session_num, username, sql_redo
    FROM v$logmnr_contents
   WHERE row_id in ( 'AAAWvOAAEAAAAIPAAA','AAAWvOAAEAAAAIPAAB') OR (seg_owner = USER AND seg_name = 'T') OR SCN in ( 11268598,11268970)
ORDER BY 1;

SCN,TIMESTAMP,SEG_OWNER,SEG_NAME,ROW_ID,SESSION_NUM,USERNAME,SQL_REDO
11270096,2012-5-27 11:33:40,SCOTT,T,AAAWvOAAEAAAAIOAAA,136,UNKNOWN,insert into "SCOTT"."T"("ID","NAME") values ('6','ijkl');,

SQL> select rowid,ora_rowscn,t.* from t;

ROWID              ORA_ROWSCN         ID NAME
------------------ ---------- ---------- ----
AAAWvOAAEAAAAILAAA   11268540          1 test
AAAWvOAAEAAAAILAAB   11268540          2 test
AAAWvOAAEAAAAILAAC   11268540          3 test
AAAWvOAAEAAAAIOAAA   11270099          6 ijkl
AAAWvOAAEAAAAIPAAA   11268970          4 abcd
AAAWvOAAEAAAAIPAAB   11268970          5 efgh
6 rows selected.

--但是奇怪的问题是SCN与select rowid,ora_rowscn,t.* from t;查询不一致?why?
SELECT   SCN, TIMESTAMP, seg_owner, seg_name, row_id, session# session_num, username, sql_redo
    FROM v$logmnr_contents
   WHERE scn between 11270090 and  11270100 
ORDER BY 1;

SCN,TIMESTAMP,SEG_OWNER,SEG_NAME,ROW_ID,SESSION_NUM,USERNAME,SQL_REDO
11270093,2012-5-27 11:33:31,,,AAAAAAAAAAAAAAAAAA,136,UNKNOWN,set transaction read write;,
11270093,2012-5-27 11:33:31,SYS,AUD$,AAAAAAAAAAAAAAAAAA,136,UNKNOWN,insert into "SYS"."AUD$"("SESSIONID","ENTRYID","STATEMENT","TIMESTAMP#","USERID","USERHOST","TERMINAL","ACTION#","RETURNCODE","OBJ$CREATOR","OBJ$NAME","AUTH$PRIVILEGES","AUTH$GRANTEE","NEW$OWNER","NEW$NAME","SES$ACTIONS","SES$TID","LOGOFF$LREAD","LOGOFF$PREAD","LOGOFF$LWRITE","LOGOFF$DEAD","LOGOFF$TIME","COMMENT$TEXT","CLIENTID","SPARE1","SPARE2","OBJ$LABEL","SES$LABEL","PRIV$USED","SESSIONCPU","NTIMESTAMP#","PROXY$SID","USER$GUID","INSTANCE#","PROCESS#","XID","AUDITID","SCN","DBID","SQLBIND","SQLTEXT","OBJ$EDITION") values ('666067','2','11',NULL,'SCOTT','hisdg','pts/2','49','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'oracle11g',NULL,NULL,NULL,'3',NULL,TO_TIMESTAMP('2012-05-27 03:33:31.252996'),NULL,NULL,'0','10332',NULL,NULL,NULL,'2071943378',EMPTY_CLOB(),EMPTY_CLOB(),NULL);,
11270093,2012-5-27 11:33:31,SYS,AUD$,AAAAF/AABAAAWc1AAU,136,UNKNOWN,update "SYS"."AUD$" set "SQLBIND" = NULL, "SQLTEXT" = NULL where "SESSIONID" = '666067' and "ENTRYID" = '2' and "STATEMENT" = '11' and "TIMESTAMP#" IS NULL and "USERID" = 'SCOTT' and "USERHOST" = 'hisdg' and "TERMINAL" = 'pts/2' and "ACTION#" = '49' and "RETURNCODE" = '0' and "OBJ$CREATOR" IS NULL and "OBJ$NAME" IS NULL and "AUTH$PRIVILEGES" IS NULL and "AUTH$GRANTEE" IS NULL and "NEW$OWNER" IS NULL and "NEW$NAME" IS NULL and "SES$ACTIONS" IS NULL and "SES$TID" IS NULL and "LOGOFF$LREAD" IS NULL and "LOGOFF$PREAD" IS NULL and "LOGOFF$LWRITE" IS NULL and "LOGOFF$DEAD" IS NULL and "LOGOFF$TIME" IS NULL and "COMMENT$TEXT" IS NULL and "CLIENTID" IS NULL and "SPARE1" = 'oracle11g' and "SPARE2" IS NULL and "OBJ$LABEL" IS NULL and "SES$LABEL" IS NULL and "PRIV$USED" = '3' and "SESSIONCPU" IS NULL and "NTIMESTAMP#" = TO_TIMESTAMP('2012-05-27 03:33:31.252996') and "PROXY$SID" IS NULL and "USER$GUID" IS NULL and "INSTANCE#" = '0' and "PROCESS#" = '10332' and "XID" IS NULL and "AUDITID" IS NULL and "SCN" IS NULL and "DBID" = '2071943378' and "OBJ$EDITION" IS NULL and ROWID = 'AAAAF/AABAAAWc1AAU';,
11270094,2012-5-27 11:33:31,,,AAAAAAAAAAAAAAAAAA,136,UNKNOWN,commit;,
11270096,2012-5-27 11:33:40,,,AAAWvOAAAAAAAAAAAA,136,UNKNOWN,set transaction read write;,
11270096,2012-5-27 11:33:40,SCOTT,T,AAAWvOAAEAAAAIOAAA,136,UNKNOWN,insert into "SCOTT"."T"("ID","NAME") values ('6','ijkl');,
11270099,2012-5-27 11:33:43,,,AAAAAAAAAAAAAAAAAA,136,UNKNOWN,commit;,
11270100,2012-5-27 11:33:45,,,AAABbKAAAAAAAAAAAA,196,SYS,set transaction read write;,
11270100,2012-5-27 11:33:45,SYS,SCHEDULER$_JOB,AAABbKAABAAAC2SAAG,196,SYS,update "SYS"."SCHEDULER$_JOB" set "JOB_STATUS" = '3', "LAST_START_DATE" = TO_TIMESTAMP_TZ('2012-05-27 11:33:45.038928'), "RUNNING_INSTANCE" = '1', "RUNNING_SLAVE" = '0' where "OBJ#" = '57372' and "JOB_STATUS" = '1' and "LAST_START_DATE" = TO_TIMESTAMP_TZ('2012-05-27 10:36:09.037681') and "RUNNING_INSTANCE" IS NULL and "RUNNING_SLAVE" IS NULL and ROWID = 'AAABbKAABAAAC2SAAG';,




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-731054/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2326
  • 访问量
    6056429