AI 助理
备案控制台
开发者社区 数据库 文章 正文

记一次Oracle logminer

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

本文主要描述一次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
目录
相关文章
嘟嘟嘟嘟嘟嘟
|
8月前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版操作报错之使用oracle-cdc的，遇到错误：ORA-01292: no log file has been specified for the current LogMiner session，该如何处理
在使用实时计算Flink版过程中，可能会遇到各种错误，了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题，查看Flink的日志是关键，它们通常会提供更详细的错误信息和堆栈跟踪，有助于定位问题。此外，Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
嘟嘟嘟嘟嘟嘟
331 0
技术小胖子
|
Oracle 关系型数据库 数据库
使用LogMiner分析oracle的redo日志和归档
技术小胖子
3277 0
技术小美
|
监控 Oracle 关系型数据库
总结：基于Oracle Logminer数据同步
技术小美
2681 0
余二五
|
SQL Oracle 关系型数据库
使用logminer分析oracle日志
余二五
1234 0
笱局长
|
SQL 监控 Oracle
Oracle Logminer 日志挖掘
生产环境中考虑到数据库的性能问题，很少会打开数据的审计功能，应用层也不会记录SQL的执行信息；但是生产上经常会遇到某张表的某几条被修改掉，但是应用又查不到是哪个接口修改的记录，这时候Logminer 就派上用场了。
笱局长
3420 0
andrew征程
|
SQL 监控 Oracle
使用Oracle logminer 找回delete的数据
Oracle免费工具logminer，找回delete数据。
andrew征程
3285 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包含了所有进行恢复操作所需要的信息。
小麦苗
1772 0
jeanron100
|
SQL Oracle 关系型数据库
Oracle闪回原理-Logminer解读redo（r11笔记第17天）
说到闪回日志，我们都知道闪回日志中记录的都是逆操作，那么就有两个问题需要解释了。 闪回日志和回滚段保存的数据有什么差别？ 如果做了truncate操作，闪回日志是怎么记录的，怎么能够通过闪回恢复数据。
jeanron100
1114 0
东方瀚海鲍
|
SQL 监控 Oracle
DBA_Oracle LogMiner分析重做和归档日志（案例）
2014-08-19 Created By BaoXinjian 一、摘要 Oracle LogMiner 是一个非常有用并且完全免费的分析工具，可轻松获得Oracle 重做日志文件（归档日志文件）中的具体内容，它是由一组PL/SQL包和一些动态视图组成。
东方瀚海鲍
1227 0
重庆八怪
|
SQL 监控 Oracle
Oracle LogMiner
Oracle LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 重作日志文件（归档日志文件）中的具体内容，特别是，该工具可以分析出所有对于数据库操作的DML（insert、update、delete等）语句，另外还可分析得到一些必要的回滚SQL语句。
重庆八怪
1034 0