logmnr無法生成部分表的DML跟蹤語句問題

2024-07-21 02:43:34

字體：大中小

來源：轉載

供稿：網友

logmnr無法生成部分表的DML跟蹤語句的解決方法：

新建映射數據字典信息文件：

SQL> execute dbms_logmnr_d.build('shwdict.ora','d:Oracleoradatarmanutl');

PL/SQL PRocedure successfully completed

SQL> insert into test values (1,'sdf

2 ');

SQL> exec dbms_logmnr.add_logfile('d:oracleoradatarmanarchive1_332.DBF',dbms_logmnr.addfile);

PL/SQL procedure successfully completed

SQL> select * from v$log_history;

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- ----------- ------------

334 593625290 1 334 6624931 2006-6-20 1 6625329

335 593625882 1 335 6625329 2006-6-20 1 6626813

336 593626064 1 336 6626813 2006-6-20 1 6627301

SQL> exec dbms_logmnr.start_logmnr(dictfilename => 'd:oracleoradatarmanutlshwdict.ora',startScn => '6624931',endScn => '6627301');

PL/SQL procedure successfully completed

SQL> SELECT OperaTION, SQL_REDO, SQL_UNDO

SQL> FROM V$LOGMNR_CONTENTS

SQL> WHERE SEG_OWNER = 'SILENCE' and OPERATION='INSERT';

OPERATION SQL_REDO SQL_UNDO

-------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------

INSERT insert into "SILENCE"."TEST"("ID","TEXT") values ('1','sdf delete from "SILENCE"."TEST" where "ID" = '1' and "TEXT" = 'sdf

'); ' and ROWID = 'AAAHdAAABAAAMdqAAC';

新建表AA 并對AA 進行添加刪除修改等DML語句操作:

SQL> create table aa as select * from dba_objects;

Table created

SQL> alter table aa add constraint pk_object_id primary key (object_id);

Table altered

SQL> delete from aa where rownum<=1000;

1000 rows deleted

SQL> commit;

Commit complete

SQL> update aa set owner='silence' where object_id=22727;

1 row updated

SQL> commit;

Commit complete

SQL> insert into aa (object_id) values (1101010101);

1 row inserted

SQL> commit;

Commit complete

對test 、test_1 進行添加刪除修改等DML語句操作

SQL> DESC test_1

Name Type Nullable Default Comments

---- ------------- -------- ------- --------

ID NUMBER Y

TEXT VARCHAR2(100) Y

SQL> desc test

Name Type Nullable Default Comments

---- ------------- -------- ------- --------

ID NUMBER Y

TEXT VARCHAR2(100) Y

SQL> insert into test_1 values (1,'dsf');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into test values (1,'dsf');

1 row inserted

SQL> commit;

SQL> update test set id=2 where text='test';

1 row updated

SQL> delete from test_1 where id=1;

1 row deleted

SQL> commit;

SQL> exec dbms_logmnr.add_logfile('d:oracleoradatarmanarchive1_333.DBF',dbms_logmnr.addfile);

PL/SQL procedure successfully completed

SQL> select * from v$log_history;

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

---------- ---------- ---------- ---------- ------------- ----------- ------------

334 593625290 1 334 6624931 2006-6-20 1 6625329

335 593625882 1 335 6625329 2006-6-20 1 6626813

336 593626064 1 336 6626813 2006-6-20 1 6627301

337 593626171 1 337 6627301 2006-6-20 1 6627621

338 593627862 1 338 6627621 2006-6-20 1 6631912

SQL> exec dbms_logmnr.start_logmnr(dictfilename => 'd:oracleoradatarmanutlshwdict.ora',startScn => '6579577',endScn => '6624357');

PL/SQL procedure successfully completed

SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO

SQL> FROM V$LOGMNR_CONTENTS

SQL> WHERE SEG_OWNER = 'SILENCE' and OPERATION='INSERT';

OPERATION SQL_REDO SQL_UNDO

-------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------

DDL create table aa as select * from dba_objects

;

DDL CREATE UNIQUE INDEX "SILENCE"."PK_OBJECT_ID" on "SILENCE"."AA"("OBJECT_ID") NOPA

DDL alter table aa add constraint pk_object_id primary key (object_id)

;

INSERT insert into "SILENCE"."TEST_1"("ID","TEXT") values ('1','dsf'); delete from "SILENCE"."TEST_1" where "ID" = '1' and "TEXT" = 'dsf' and ROWID = '

INSERT insert into "SILENCE"."TEST"("ID","TEXT") values ('1','dsf'); delete from "SILENCE"."TEST" where "ID" = '1' and "TEXT" = 'dsf' and ROWID = 'AA

UPDATE update "SILENCE"."TEST" set "ID" = '2' where "ID" = '1' and ROWID = 'AAAHdAAABAA update "SILENCE"."TEST" set "ID" = '1' where "ID" = '2' and ROWID = 'AAAHdAAABAA

DDL create table test_2 as select * from test_1

;

DDL drop table test_2

;

DELETE delete from "SILENCE"."TEST_1" where "ID" = '1' and "TEXT" = 'dsf' and ROWID = ' insert into "SILENCE"."TEST_1"("ID","TEXT") values ('1','dsf');

重新生成數據字典映射文件:

SQL> execute dbms_logmnr_d.build('shwdict.ora','d:oracleoradatarmanutl');

PL/SQL procedure successfully completed

SQL> update aa set owner='silence' where OBJECT_ID=13314;

1 row updated

SQL> commit;

SQL> delete from aa where OBJECT_ID=13314;

1 row deleted

SQL> commit;

Commit complete

SQL> alter system checkpoint;

System altered

SQL> alter system switch logfile;

System altered

SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO

SQL> FROM V$LOGMNR_CONTENTS

SQL> WHERE SEG_OWNER = 'SILENCE' ;

OPERATION SQL_REDO SQL_UNDO

-------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------

DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'DEFROLE$' insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'PROFILE$' insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'PROFNAME$' insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'DEPENDENCY insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'access$' a insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'AUD$' and insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'LINK$' and insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'TRUSTED_LI insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

INTERNAL

UPDATE update "SILENCE"."AA" set "OWNER" = 'silence' where "OWNER" = 'SYS' and ROWID = update "SILENCE"."AA" set "OWNER" = 'SYS' where "OWNER" = 'silence' and ROWID =

DELETE delete from "SILENCE"."AA" where "OWNER" = 'silence' and "OBJECT_NAME" = '/6a00f insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D

INTERNAL

由于aa 表是在映射數據字典信息文件生成后新建的。

因此，在生成跟蹤視圖(v$logmnr_content) 只有aa 表的DDL語句，卻沒有任何關于aa表的dml語句信息。

因此在做logmnr 做本地或遠程數據庫跟蹤前最好重新生成數據字典映射文件。

備注：

配置LOGMNR:

execute dbms_logmnr_d.build('shwdict.ora','c:oracle');

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

指定utl_file_dir 參數：

我們要在操作logminer的數據庫服務器上配置該參數。以讓logminer能找到我們上一步產生的數據字典文件。

utl_file_dir = 'c:oracle'

添加要分析的日志：