[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
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';,
时间： 2024-09-29 19:38:03