王朝网络
分享
 
 
 

logmnr无法生成部分表的DML跟踪语句问题

王朝other·作者佚名  2008-06-12
宽屏版  字体: |||超大  

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的一致。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
>>返回首页<<
推荐阅读
 
 
频道精选
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
© 2005- 王朝网络 版权所有