| 導購 | 订阅 | 在线投稿
分享
 
 
 

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

來源:互聯網  2008-06-12 07:23:55  評論

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'

添加要分析的日志:

在配置完以上兩個步驟後,我們就可以進行日志分析了。

對于第一個需要分析的日志我們可以進行以下命令:

execute dbms_logmnr.add_logfile('C:oracleora92rdbmsARC00126.001',dbms_logmnr.new);

如果不是第一個日志文件,

我們可以:execute dbms_logmnr.add_logfile('C:oracleora92rdbmsARC00130.001',dbms_logmnr.addfile);

設置時間格式:

必須設置如下時間格式,否則,在進行日志分析的過程中,

將報錯誤信息:

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'

分析日志: 我們可以發出以下命令,來進行日志分析了:

dbms_logmnr.start_logmnr(

dictfilename => 'C:oracleshwdict.ora', startscn=>xxxxxx,endscn=>xxxxx,

starttime => to_date('20030501 12:15:00','yyyymmdd hh24:mi:ss'),

endtime => to_date('20030501 15:40:30','yyyymmdd hh24:mi:ss'));

注意:大家必須知道dictfilename數據字典文件;scn的大小可以通過v$log_history來查詢,

endscn則最好比查詢得到的最後一個scn小一個數字。

查詢結果: 讀者可以查詢v$logmnr_contents來獲得日志分析結果。

注意事項: 進行logminer的db的OS平台、DB的字符集必須跟原來db的一致。

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' 添加要分析的日志: 在配置完以上兩個步驟後,我們就可以進行日志分析了。 對于第一個需要分析的日志我們可以進行以下命令: execute dbms_logmnr.add_logfile('C:oracleora92rdbmsARC00126.001',dbms_logmnr.new); 如果不是第一個日志文件, 我們可以:execute dbms_logmnr.add_logfile('C:oracleora92rdbmsARC00130.001',dbms_logmnr.addfile); 設置時間格式: 必須設置如下時間格式,否則,在進行日志分析的過程中, 將報錯誤信息: alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss' 分析日志: 我們可以發出以下命令,來進行日志分析了: dbms_logmnr.start_logmnr( dictfilename => 'C:oracleshwdict.ora', startscn=>xxxxxx,endscn=>xxxxx, starttime => to_date('20030501 12:15:00','yyyymmdd hh24:mi:ss'), endtime => to_date('20030501 15:40:30','yyyymmdd hh24:mi:ss')); 注意:大家必須知道dictfilename數據字典文件;scn的大小可以通過v$log_history來查詢, endscn則最好比查詢得到的最後一個scn小一個數字。 查詢結果: 讀者可以查詢v$logmnr_contents來獲得日志分析結果。 注意事項: 進行logminer的db的OS平台、DB的字符集必須跟原來db的一致。
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有