查找OGG trail file中是否存在相關記錄的命令 · Oracle GoldenGate

查找OGG trail file中是否存在相關記錄的命令如下是查找一個insert into USERA.TABLE1是否存在于OGG源頭的trail file中，同時，本文也可以視為OGG 目的端rep進程報 SQL error 1403 mapping USERA.TABLE1 to USERA.TABLE1的問題診斷思路。如下診斷用到了ogg自帶的logdump工具拿USERA.TABLE1表來說，我們通過查詢 rep進程的丟棄文件(dsc文件，也就是discard文件，位于$OGG_HOME/dirrpt下)中該表的報錯信息，如下：確認有如下記錄（僅僅拿一個記錄為例）肯定是沒有insert到數據庫中的， USERA.TABLE1 表中id=53874605的記錄, 然后在源頭數據庫上查出ID,之所以查詢出ID是因為在logdump的輸出中，這個值比較直觀： ~~~ select ID from USERA.TABLE1 where id=53874605 ~~~ 獲得id的值： ~~~ WW151124160530101510740104947088A ~~~ 以此值到源頭trailfile 中進行搜索，注意如下的搜索方法： ~~~ oracle@host2:/gg/ggs$ logudump Oracle GoldenGate Log File Dump Utility for Oracle Version 11.2.1.0.27 19591627 20148126 Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. Logdump 644 >open /gg/ggs/dirdat/sd021739 ghdr on detail on detail data ggstoken on usertoken on ggstokens detail filter include FILENAME USERA.TABLE1 filter include STRING 'WW1511241605301015107401049470856A' filter match all show filter---->此處是命令的結尾,然后敲回車即可 Current LogTrail is /gg/ggs/dirdat/sd021739 Logdump 645 >Logdump 646 >Logdump 647 >Logdump 648 >Logdump 649 >Logdump 650 >Logdump 651 >Logdump 652 >Logdump 653 >Logdump 654 > Data filters are ENABLED Include Match ALL Filename-0 : USERA.TABLE1 String-0 : (34), CaseSensitive 4648 3135 3131 3234 3136 3035 3330 3130 3135 3130 | WW151124160530101510 3734 3031 3034 3934 3730 3835 3641 | 7401049470888A Exclude Match ANY Logdump 655 >n Scanned 10000 records, RBA 5742984, 2015/11/14 03:43:16.000.000 Scanned 20000 records, RBA 11498403, 2015/11/14 03:48:10.000.000 Scanned 30000 records, RBA 17287262, 2015/11/14 03:53:32.000.000 Scanned 40000 records, RBA 22759004, 2015/11/14 03:58:06.000.000 Scanned 50000 records, RBA 28498882, 2015/11/14 04:03:16.000.000 Scanned 60000 records, RBA 34247854, 2015/11/14 04:07:40.000.000 Scanned 70000 records, RBA 40072024, 2015/11/14 04:12:40.000.000 Scanned 80000 records, RBA 45960655, 2015/11/14 04:16:56.000.000 Filtering suppressed 87002 records Logdump 656 >nt LogTrail /gg/ggs/dirdat/sd021739 closed Current LogTrail is /gg/ggs/dirdat/sd021740 Logdump 657 >n Scanned 10000 records, RBA 5833212, 2015/11/14 04:24:45.000.000 Scanned 20000 records, RBA 11584249, 2015/11/14 04:29:13.000.000 Scanned 30000 records, RBA 17067992, 2015/11/14 04:32:52.000.000 Scanned 40000 records, RBA 22918136, 2015/11/14 04:36:57.000.000 Scanned 50000 records, RBA 28712547, 2015/11/14 04:41:22.000.000 Scanned 60000 records, RBA 34567058, 2015/11/14 04:46:24.000.000 Scanned 70000 records, RBA 40357769, 2015/11/14 04:51:08.000.000 Scanned 80000 records, RBA 46148680, 2015/11/14 04:55:49.000.000 Filtering suppressed 86515 records Logdump 658 >exit ~~~ 如上的結果顯示：USERA.TABLE1 中? id=53874605的記錄不在源頭的trail file中，也就是說：問題得到定性：ogg源頭的抽取進程漏抽數據。插曲：憑什么就定位到/gg/ggs/dirdat/sd021739這個源頭的trail file？要知道rep進程的丟棄文件中只會顯示目的端的trailfile號。熟悉ogg的人都知道，源頭trail file 號與目的端trail file號沒有任何的等價關系，也沒有加1 或者減1的關系。這里就需要去看源頭dp（datapump進程）的rpt文件，根據大體的時間，獲得如下信息： ~~~ 2015-11-14 03:09:57 INFO OGG-01026 Rolling over remote file /ogg/ggs/dirdat/pa059719. Switching to next trail file /gg/ggs/dirdat/sd021739 at 2015-11-14 03:37:59 due to EOF, with current RBA 49999824 Opened trail file /gg/ggs/dirdat/sd021739 at 2015-11-14 03:37:59 2015-11-14 03:56:52 INFO OGG-01026 Rolling over remote file /ogg/ggs/dirdat/pa059720. Switching to next trail file /gg/ggs/dirdat/sd021740 at 2015-11-14 04:20:08 due to EOF, with current RBA 49999149 Opened trail file /gg/ggs/dirdat/sd021740 at 2015-11-14 04:20:08 ~~~ 上面的/gg/ggs/dirdat/sd021739就是源頭的trail文件號。 ?