|
本文主要描述一次Oracle logminer的实际使用的记录。 此方法不用修改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<to_date('2013-12-22 10:00:04','YYYY-MM-DD HH24:MI:SS') 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表 (责任编辑：IT) |