备案控制台
探索云世界
开发者社区 数据库 文章 正文

记一次Oracle logminer

2017-12-08 1141
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

本文主要描述一次Oracle logminer的实际使用的记录。

欢迎转载，请注明作者、出处。
作者：张正
blog：http://space.itpub.net/26355921 
QQ：176036317
如有疑问，欢迎联系。

此方法不用修改utl_file_dir参数，因此数据库不需要重启就能使用logminer，但是加入需要挖掘的日志后，只在session有效。

1.查找远端与目标端 不一致记录：

select * from ucr_trade_01.tl_b_deliver_cardcharge where order_no='100601101741312220959350332030' and PARTITION_ID=31; 

select PARTITION_ID,BSS_TRADE_NO,CARD_PROVINCE_CODE,CARD_CITY_CODE,CARD_NETTYPE_CODE,BSS_TRADE_CREATE_TIME,BSS_TRADE_COMPLETE_TIME,CONTRAST_DATE from ucr_trade_01.tl_b_deliver_cardcharge where order_no='100601101741312220959350332030' and PARTITION_ID=31;

souce:

PARTITION_ID BSS_TRADE_NO                   CARD_P CARD_C CARD_NETTYPE_CODE    BSS_TRADE_CREATE_TI BSS_TRADE_COMPLETE_ CONTRAST_DATE

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

          31 98131222095935330059           098    980    ALL                  2013-12-22 10:00:04                     20131222

 

target:

PARTITION_ID BSS_TRADE_NO                   CAR CAR CARD_NETTY BSS_TRADE_CREATE_TI BSS_TRADE_COMPLETE_ CONTRAST

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

          31 98131222095935330059                              2013-12-22 10:00:04                     20131222

发现记录的值不一致。

时间为：

2013-12-22 10:00:04

-------------查找之后时间两天的归档

2.查找相应时间的归档日志：

select name,dest_id,thread#,sequence#,FIRST_TIME,NEXT_TIME,COMPLETION_TIME from v$archived_log

where FIRST_TIME('2013-12-22>

and COMPLETION_TIME>to_date('2013-12-22 10:00:04','YYYY-MM-DD HH24:MI:SS');

select name,dest_id,thread#,sequence#,FIRST_TIME,NEXT_TIME,COMPLETION_TIME from v$archived_log

where FIRST_TIME>=to_date('2013-12-22 10:00:04','YYYY-MM-DD HH24:MI:SS')

and COMPLETION_TIME<=to_date('2013-12-24 00:00:00') order by thread#,sequence#;

source：

NAME                                                  DEST_ID  THREAD#  SEQUENCE# FIRST_TIME          NEXT_TIME           COMPLETION_TIME

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

/upay04/arch/2_97374_746980697.dbf                          1        2      97374 2013-12-22 09:36:31 2013-12-22 10:07:21 2013-12-22 10:07:32

/upay04/arch/1_92621_746980697.dbf                          1        1      92621 2013-12-22 09:42:18 2013-12-22 10:22:30 2013-12-22 10:22:40

target:

没有开归档日志

3.添加归档日志(source)：

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92621_746980697.dbf',options=>dbms_logmnr.new);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97374_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92622_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92623_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92624_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92625_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92626_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92627_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92628_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92629_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92630_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92631_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92632_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92633_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92634_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92635_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92636_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92637_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92638_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92639_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92640_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92641_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92642_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92643_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92644_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92645_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92646_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92647_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92648_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92649_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92650_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92651_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92652_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92653_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92654_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92655_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92656_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92657_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92658_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92659_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92660_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92661_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92662_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92663_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92664_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92665_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92666_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92667_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92668_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92669_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92670_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92671_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92672_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92673_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92674_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92675_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92676_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92677_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/1_92678_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97375_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97376_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97377_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97378_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97379_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97380_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97381_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97382_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97383_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97384_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97385_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97386_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97387_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97388_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97389_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97390_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97391_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97392_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97393_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97394_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97395_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97396_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97397_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97398_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97399_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97400_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97401_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97402_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97403_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97404_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97405_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97406_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97407_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97408_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97409_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97410_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97411_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97412_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97413_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97414_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97415_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97416_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97417_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97418_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97419_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97420_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97421_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97422_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97423_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97424_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97425_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97426_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97427_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97428_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97429_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97430_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97431_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97432_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97433_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97434_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97435_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97436_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97437_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97438_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97439_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97440_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97441_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97442_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97443_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97444_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97445_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97446_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97447_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97448_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97449_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97450_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97451_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97452_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97453_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97454_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97455_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97456_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97457_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97458_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97459_746980697.dbf',options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(logfilename=>'/upay04/arch/2_97460_746980697.dbf',options=>dbms_logmnr.addfile);

4.启动logminer：

execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

5.创建表，获取logminer的信息：

set numw 20 

create table logmnr_tmp tablespace sysaux as

select * from v$logmnr_contents where seg_owner='UCR_TRADE_01'

and seg_name='TL_B_DELIVER_CARDCHARGE'

and OPERATION = 'UPDATE';

或者：

create table logmnr_tmp tablespace sysaux as

select * from v$logmnr_contents where seg_owner='UCR_TRADE_01'

and seg_name='TL_B_DELIVER_CARDCHARGE'

and OPERATION = 'UPDATE'

and SYS.DBMS_LOGMNR.MINE_VALUE(REDO_VALUE,'用户.表名.列名a')=x;

表示：日志挖掘 只 选取 该表被修改后，记录中，列a的值为x的记录。

如果

SYS.DBMS_LOGMNR.MINE_VALUE(REDO_VALUE,'用户.表名.列名a')=x;

则表示：日志挖掘 选取 该表被修改前，记录中，列a的值为x的记录。

一般该列可使用主键，或者被修改的列。

eg:

and dbms_logmnr.MINE_VALUE(REDO_VALUE,'UCR_TRADE_01.TL_B_DELIVER_CARDCHARGE.DELIVER_LOGID')=1312227748549931

and dbms_logmnr.MINE_VALUE(REDO_VALUE,'UCR_TRADE_01.TL_B_DELIVER_CARDCHARGE.PARTITION_ID')=31

示例：

SQL> SELECT SQL_REDO FROM V$LOGMNR_CONTENTS

  2  WHERE SEG_OWNER = USER

  3  AND TABLE_NAME = 'T'

  4  AND OPERATION = 'UPDATE'

  5  AND SYS.DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'YANGTK.T.FLAG') = 0;

SQL_REDO

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

update "YANGTK"."T" set "FLAG" = '0' where "FLAG" = '1' and ROWID = 'AAAN4SAAGAAABhvAB/';

update "YANGTK"."T" set "FLAG" = '0' where "FLAG" = '1' and ROWID = 'AAAN4SAAGAAABmaAB3';

update "YANGTK"."T" set "FLAG" = '0' where "FLAG" = '1' and ROWID = 'AAAN4SAAIAAABUtAAH';

update "YANGTK"."T" set "FLAG" = '0' where "FLAG" = '1' and ROWID = 'AAAN4SAAIAAABViAAA';

update "YANGTK"."T" set "FLAG" = '0' where "FLAG" = '1' and ROWID = 'AAAN4SAAGAAABjAABA';

创建过程需要花很大时间，

可通过tail -f alert*.log查看目前logminer到哪：

LOGMINER: Begin mining logfile for session -2147482622 thread 1 sequence 92621, /upay04/arch/1_92621_746980697.dbf

Wed Dec 25 15:40:43 EAT 2013

LOGMINER: Begin mining logfile for session -2147482622 thread 2 sequence 97374, /upay04/arch/2_97374_746980697.dbf

Wed Dec 25 15:40:43 EAT 2013

LOGMINER: Begin mining logfile for session -2147482622 thread 1 sequence 92621, /upay04/arch/1_92621_746980697.dbf

Wed Dec 25 15:40:43 EAT 2013

LOGMINER: Begin mining logfile for session -2147482622 thread 2 sequence 97374, /upay04/arch/2_97374_746980697.dbf

6.查询logmnr_tmp表

本文转自ITPUB博客84223932的博客，原文链接：记一次Oracle logminer，如需转载请自行联系原博主。

文章标签：
日志服务
关系型数据库
监控
Oracle
关键词：
Oracle数据库一键上云Logminer
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
李博 bluemind
目录
相关文章
技术小胖子
|
Oracle 关系型数据库 数据库
使用LogMiner分析oracle的redo日志和归档
技术小胖子
3038 0
技术小美
|
监控 Oracle 关系型数据库
总结：基于Oracle Logminer数据同步
技术小美
2558 0
余二五
|
SQL Oracle 关系型数据库
使用logminer分析oracle日志
余二五
1177 0
笱局长
|
SQL 监控 Oracle
Oracle Logminer 日志挖掘
生产环境中考虑到数据库的性能问题，很少会打开数据的审计功能，应用层也不会记录SQL的执行信息；但是生产上经常会遇到某张表的某几条被修改掉，但是应用又查不到是哪个接口修改的记录，这时候Logminer 就派上用场了。
笱局长
3337 0
andrew征程
|
SQL 监控 Oracle
使用Oracle logminer 找回delete的数据
Oracle免费工具logminer，找回delete数据。
andrew征程
3210 0
小麦苗
|
SQL 监控 Oracle
Oracle日志挖掘之LogMiner
Oracle日志挖掘之LogMiner 官方文档地址：http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019 众所周知，所有对用户数据和数据字典的改变都记录在Oracle的Redo Log中，因此，Redo Log包含了所有进行恢复操作所需要的信息。
小麦苗
1626 0
jeanron100
|
SQL Oracle 关系型数据库
Oracle闪回原理-Logminer解读redo（r11笔记第17天）
说到闪回日志，我们都知道闪回日志中记录的都是逆操作，那么就有两个问题需要解释了。 闪回日志和回滚段保存的数据有什么差别？ 如果做了truncate操作，闪回日志是怎么记录的，怎么能够通过闪回恢复数据。
jeanron100
1066 0
重庆八怪
|
SQL 监控 Oracle
Oracle LogMiner
Oracle LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 重作日志文件（归档日志文件）中的具体内容，特别是，该工具可以分析出所有对于数据库操作的DML（insert、update、delete等）语句，另外还可分析得到一些必要的回滚SQL语句。
重庆八怪
992 0
jimbuster
|
SQL 监控 Oracle
理解和使用Oracle分析工具LogMiner
用LogMiner             理解和使用Oracle   8i分析工具LogMiner                 Oracle   LogMiner   是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle   重作日志文件（归档日志文件）中的具体内容，特别是，该工具可以分析出所有对于数据库操作的DML（insert、update、delete等）语句，另外还可分析得到一些必要的回滚SQL语句。
jimbuster
1457 0
SarPro
|
13天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
SarPro
52 7

热门文章

最新文章

  • 1
    Seata常见问题之oracle 数据库 报 just support mysql如何解决
  • 2
    【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
  • 3
    【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
  • 4
    【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
  • 5
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
  • 6
    Oracle数据库基本概念理解（3）
  • 7
    oracle基本笔记整理及案例分析1
  • 8
    oracle 访问控制
  • 9
    Oracle数据区：数据王国的黄金地段
  • 10
    Oracle数据库基本概念理解（1）
  • 1
    Oracle数据守卫（DG）：数据的“守护者”与“时光机”
    9
  • 2
    Oracle 12c的内存列存储：数据的“闪电侠”
    13
  • 3
    Oracle 12c的多重索引：数据的“多维导航仪”
    8
  • 4
    Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
    13
  • 5
    Oracle 12c多租户架构：数据管理的“摩天大楼”
    12
  • 6
    Oracle数据泵：数据迁移的魔法棒
    5
  • 7
    Oracle中的触发器与序列：自增列的魔法组合
    9
  • 8
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
    27
  • 9
    Oracle的PL/SQL循环语句：数据的“旋转木马”与“无限之旅”
    10
  • 10
    Oracle的PL/SQL条件控制：数据的“红绿灯”与“分岔路”
    11

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 推荐镜像

    更多
  • oracle
  • mysql
  • arch4edu
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）