記一次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
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部落格 ” ，連結：http://blog.itpub.net/26355921/viewspace-1248106/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle LogMinerOracle
- Oracle9i LogMiner實驗記錄Oracle
- [zt] Oracle LogMinerOracle
- Oracle logminer(轉)Oracle
- Oracle Logminer 說明Oracle
- Oracle logminer學習Oracle
- 【Oracle】Oracle logminer功能介紹Oracle
- ORACLE logminer 日誌挖掘Oracle
- （轉）Oracle Logminer 說明Oracle
- oracle之logminer的使用Oracle
- Oracle高可用之LogMinerOracle
- oracle 10g logminerOracle 10g
- 安裝oracle 的LogMinerOracle
- ORACLE的日誌挖掘 logminerOracle
- [zt] Oracle LogMiner 終結版Oracle
- LOGMINER的記憶體消耗記憶體
- 使用oracle的logminer同步資料Oracle
- Oracle 12c logminer測試Oracle
- oracle9i logMiner的使用Oracle
- Oracle 10g Logminer clob bugOracle 10g
- oracle 10g中logminer的使用Oracle 10g
- 記一次Oracle死鎖/阻塞排查Oracle
- Oracle閃回原理-Logminer解讀redo（r11筆記第17天）Oracle筆記
- 捎帶手，簡單看看Oracle的LogMinerOracle
- oracle 9i logminer日誌分析01Oracle
- Oracle 10g Logminer 研究及測試Oracle 10g
- oracle logminer分析線上 離線日誌方法Oracle
- oracle logminer恢復truncate table的資料Oracle
- Oracle 8i 新分析工具-LogMiner（轉）Oracle
- Oracle 9i LogMiner工具使用說明Oracle
- 記一次Oracle故障：磁碟空間滿Oracle
- 記一次 oracle expdp 匯出錯誤Oracle
- oracle 11g logminer 進行日誌挖掘Oracle
- 使用Oracle的logminer工具進行日誌挖掘Oracle
- 使用Oracle 10g的Logminer挖掘日誌Oracle 10g
- Oracle9i使用logminer恢復資料Oracle
- 理解和使用Oracle 8i分析工具－LogMiner(轉)Oracle
- 記一次慘敗的Oracle DBA面試經歷Oracle面試