GoldenGate "Error mapping from table.a to table.a"錯誤測試
測試資料：
源端：
D:\>db2 connect to topicis
資料庫連線資訊
資料庫伺服器 = DB2/NT 9.7.1
SQL 授權標識 = LIUXIAOH...
本地資料庫別名 = TOPICIS
D:\>db2 select * from topicis.aa
ID NAME
----------- ----------
1 a
2 b
3 c
4 d
5 e
5 條記錄已選擇。
目標端：
[topicis@localhost ~]$ db2 connect to topicis
Database Connection Information
Database server = DB2/LINUXX8664 9.7.3
SQL authorization ID = TOPICIS
Local database alias = TOPICIS
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
4 d
5 e
5 record(s) selected.
此時同步正常：
D:\>db2 delete from topicis.aa where id=5
DB20000I SQL 命令成功完成。
D:\>db2 select * from topicis.aa
ID NAME
----------- ----------
1 a
2 b
3 c
4 d
4 條記錄已選擇。
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
4 d
4 record(s) selected.
下面對目標端的資料手動update：
[topicis@localhost ~]$ db2 "update topicis.aa set id=5,name='test' where
id=4"
DB20000I The SQL command completed successfully.
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
5 test
4 record(s) selected.
這樣，目標端沒有了id=4這條資料。對源端id=4的資料進行update，看是否會報錯：
D:\>db2 update topicis.aa set name='xxx' where id=4
DB20000I SQL 命令成功完成。
D:\>db2 select * from topicis.aa
ID NAME
----------- ----------
1 a
2 b
3 c
4 xxx
4 條記錄已選擇。
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
5 test
4 xxx
5 record(s) selected.
發現目標端replicat程式沒有abend，而是多了一條id=4的資料！
原因是replicat程式設定了引數HANDLECOLLISIONS.
下面把HANDLECOLLISIONS引數註釋掉，註釋掉後再進行測試：
[topicis@localhost ~]$ db2 "delete from topicis.aa where id=4"
DB20000I The SQL command completed successfully.
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
5 test
4 record(s) selected.
這裡又把目標端id=4的行刪除，源端修改id=4的行：
D:\>db2 update topicis.aa set name='yyy' where id=4
DB20000I SQL 命令成功完成。
這樣，目標端replicat程式複製時，找不到id=4的行，程式會abend：
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
5 test
4 record(s) selected.
發現目標端不會再像之前那樣多一條id=4資料。
檢視程式狀態：
GGSCI (localhost.localdomain) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED RTOP1 00:00:04 00:00:10
可以看到rtop1程式abended！
檢視rtop1的report資訊，其中有下面的錯誤：
2013-01-30 09:22:52 ERROR OGG-01296 Error mapping from TOPICIS.AA to TO
PICIS.AA.
2013-01-30 09:22:52 ERROR OGG-01668 PROCESS ABENDING.
此時，啟動replicat程式，程式不能啟動：
GGSCI (localhost.localdomain) 10> start rtop1
Sending START request to MANAGER ...
REPLICAT RTOP1 starting
GGSCI (localhost.localdomain) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED RTOP1 00:18:52 00:00:01
檢視ggserr.log檔案，和report的報錯是一樣的：
2013-01-30 09:41:40 WARNING OGG-01154 Oracle GoldenGate Delivery for DB2, rtop1.prm: SQL error 100 mapping TOPICIS.AA to TOPICIS.AA [SQL error 100 (0x64)][IBM][CLI Driver][DB2/LINUXX8664] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000.
2013-01-30 09:41:40 WARNING OGG-01003 Oracle GoldenGate Delivery for DB2, rtop1.prm: Repositioning to rba 1355 in seqno 1.
2013-01-30 09:41:40 ERROR OGG-01296 Oracle GoldenGate Delivery for DB2, rtop1.prm: Error mapping from TOPICIS.AA to TOPICIS.AA.
2013-01-30 09:41:40 ERROR OGG-01668 Oracle GoldenGate Delivery for DB2, rtop1.prm: PROCESS ABENDING.
此時，再將HANDLECOLLISIONS引數新增上去，看問題會不會解除
GGSCI (localhost.localdomain) 4> start rtop1
Sending START request to MANAGER ...
REPLICAT RTOP1 starting
GGSCI (localhost.localdomain) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RTOP1 00:00:00 00:00:01
replicat程式啟動了，看一下資料：
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
5 test
4 yyy
5 record(s) selected.
目標端多了id=4資料！
下面logdump檢視一下rba 1355 in seqno 1處的內容：
Logdump 10 >ghdr on
Logdump 11 >detail on
Logdump 12 >open ./dirdat/tt000001
Current LogTrail is /opt/ggs/dirdat/tt000001
Logdump 13 >pos 1355
Reading forward from RBA 1355
Logdump 14 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 52 (x0034) IO Time : 2013/01/30 09:22:48.580.927
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 74613007
Continued : N (x00) RecCount : 1 (x01)
2013/01/30 09:22:48.580.927 GGSPKUpdate Len 52 RBA 1355
Name: TOPICIS.AA
After Image: Partition 4 G s
0019 0000 000a 0000 0000 0000 0000 0004 0001 0007 | ....................
0000 0003 7878 7800 0000 0a00 0000 0000 0000 0000 | ....xxx.............
0400 0100 0700 0000 0379 7979 | .........yyy
Before Image Len 27 (x0000001b)
KeyLen 25 (x00000019)
KeyCol 0 (x0000), Len 10 (x000a)
KeyCol 1 (x0001), Len 7 (x0007)
After Image Len 25 (x00000019)
Column 0 (x0000), Len 10 (x000a)
Column 1 (x0001), Len 7 (x0007)
從logdump也可以看出來，rba 1355 in seqno 1處是在對topicis.aa表進行update操作。
下面測試，通過從logdump檢視錯誤的位置，跳過錯誤從下一處開始，而不使用HANDLECOLLISIONS引數。
首先註釋掉HANDLECOLLISIONS引數：
GGSCI (localhost.localdomain) 4> edit params rtop1
--HANDLECOLLISIONS
兩端資料如下：
D:\>db2 select * from topicis.aa
ID NAME
----------- ----------
1 a
2 b
3 c
4 yyy
6 f
7 g
6 條記錄已選擇。
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
5 test
4 yyy
6 f
7 g
7 record(s) selected.
目標端刪除id>4的資料：
[topicis@localhost ~]$ db2 "delete from topicis.aa where id>4"
DB20000I The SQL command completed successfully.
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
4 yyy
4 record(s) selected.
啟動replicat程式。源端刪除id=6和id=7兩條資料時會發生錯誤，從而導致replicat程式abend：
GGSCI (localhost.localdomain) 6> start rtop1
Sending START request to MANAGER ...
REPLICAT RTOP1 starting
GGSCI (localhost.localdomain) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RTOP1 00:00:00 00:00:01
D:\>db2 delete from topicis.aa where id=6 or id=7
DB20000I SQL 命令成功完成。
D:\>db2 select * from topicis.aa
ID NAME
----------- ----------
1 a
2 b
3 c
4 yyy
4 條記錄已選擇。
檢視replicat程式狀態：
GGSCI (localhost.localdomain) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED RTOP1 00:00:00 00:00:44
檢視report資訊：
GGSCI (localhost.localdomain) 9> view report rtop1
2013-01-30 10:24:17 WARNING OGG-01004 Aborted grouped transaction on 'TOPICIS.AA', Database error 100 ([SQL err
or 100 (0x64)][IBM][CLI Driver][DB2/LINUXX8664] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the re
sult of a query is an empty table. SQLSTATE=02000
).
2013-01-30 10:24:17 WARNING OGG-01003 Repositioning to rba 1711 in seqno 1.
2013-01-30 10:24:17 WARNING OGG-01154 SQL error 100 mapping TOPICIS.AA to TOPICIS.AA [SQL error 100 (0x64)][IBM
][CLI Driver][DB2/LINUXX8664] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is
an empty table. SQLSTATE=02000.
2013-01-30 10:24:17 WARNING OGG-01003 Repositioning to rba 1711 in seqno 1.
Source Context :
SourceModule : [er.errors]
SourceID : [/scratch/aime1/adestore/views/aime1_adc4150256/oggcore/OpenSys/src/app/er/errors.cpp
]
SourceFunction : [take_rep_err_action]
SourceLine : [623]
ThreadBacktrace : [8] elements
: [/opt/ggs/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x2b2213a46bc6]]
: [/opt/ggs/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, .
..)+0x366) [0x2b2213a408b6]]
: [/opt/ggs/libgglog.so(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, ggs::gglib::ggap
p::CQualDBObjName const&, ggs::gglib::ggapp::CQualDBObjName const&, CMessageFactory::
MessageDisposition)+0x68) [0x2b2213a23378]]
: [/opt/ggs/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_
rec_hdr*, char*, file_def*, bool)+0x1f40) [0x5227a0]]
: [/opt/ggs/replicat(process_extract_loop()+0x4eca) [0x54552a]]
: [/opt/ggs/replicat(main+0xbc4) [0x560484]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x31b9e1d994]]
: [/opt/ggs/replicat(__gxx_personality_v0+0x2a2) [0x48e9ca]]
2013-01-30 10:24:17 ERROR OGG-01296 Error mapping from TOPICIS.AA to TOPICIS.AA.
2013-01-30 10:24:17 ERROR OGG-01668 PROCESS ABENDING.
可以看出，在rba 1711 in seqno 1處遇到了錯誤。
下面先在源端執行一些操作，由於源端的extract程式都正常，這些操作會被投遞到目標端trail檔案中，只是replicat程式不能進行復制。跳過錯誤點，看一下後面的內容會不會被複制。
D:\>db2 insert into topicis.aa values(10,'keep')
DB20000I SQL 命令成功完成。
D:\>db2 insert into topicis.aa values(20,'trying')
DB20000I SQL 命令成功完成。
D:\>db2 select * from topicis.aa
ID NAME
----------- ----------
1 a
2 b
3 c
4 yyy
10 keep
20 trying
6 條記錄已選擇。
在錯誤發生後，源端又插入了兩條資料。
用logdump檢視：
Logdump 37 >ghdr on
Logdump 38 >detail on
Logdump 39 >open ./dirdat/tt000001
Current LogTrail is /opt/ggs/dirdat/tt000001
Logdump 40 >pos 1711
Reading forward from RBA 1711
Logdump 41 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 23 (x0017) IO Time : 2013/01/30 10:24:11.773.278
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 74653147
Continued : N (x00) RecCount : 1 (x01)
2013/01/30 10:24:11.773.278 Delete Len 23 RBA 1711
Name: TOPICIS.AA
Before Image: Partition 4 G b
0000 000a 0000 0000 0000 0000 0006 0001 0005 0000 | ....................
0001 66 | ..f
Column 0 (x0000), Len 10 (x000a)
Column 1 (x0001), Len 5 (x0005)
可以看到該處是執行了delete操作。
由“TransInd : . (x00)”可以看出，這是一個事務的第一條語句（一條刪除語句刪除了兩行，這是第一行）。
Logdump 42 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 23 (x0017) IO Time : 2013/01/30 10:24:11.773.278
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 74653210
Continued : N (x00) RecCount : 1 (x01)
2013/01/30 10:24:11.773.278 Delete Len 23 RBA 1820
Name: TOPICIS.AA
Before Image: Partition 4 e
0000 000a 0000 0000 0000 0000 0007 0001 0005 0000 | ....................
0001 67 | ..g
Column 0 (x0000), Len 10 (x000a)
Column 1 (x0001), Len 5 (x0005)
由“TransInd : . (x02)”可以看出，RBA 1820處，該事務執行完畢。
Logdump 43 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 26 (x001a) IO Time : 2013/01/30 10:31:29.772.907
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 74852021
Continued : N (x00) RecCount : 1 (x01)
2013/01/30 10:31:29.772.907 Insert Len 26 RBA 1904
Name: TOPICIS.AA
After Image: Partition 4 G s
0000 000a 0000 0000 0000 0000 000a 0001 0008 0000 | ....................
0004 6b65 6570 | ..keep
Column 0 (x0000), Len 10 (x000a)
Column 1 (x0001), Len 8 (x0008)
這是一個新事務，也就是replicat程式abend後執行的插入操作。
有時候一個事務很大，用pos rba找到事務的起始位置後不可能一步一步看到事務的結尾，可以使用下面的命令直接檢視該事務的結尾：
Logdump 51 >SFET
End of Transaction found at RBA 1820
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 23 (x0017) IO Time : 2013/01/30 10:24:11.773.278
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 74653210
Continued : N (x00) RecCount : 1 (x01)
2013/01/30 10:24:11.773.278 Delete Len 23 RBA 1820
Name: TOPICIS.AA
Before Image: Partition 4 e
0000 000a 0000 0000 0000 0000 0007 0001 0005 0000 | ....................
0001 67 | ..g
Column 0 (x0000), Len 10 (x000a)
Column 1 (x0001), Len 5 (x0005)
下面使replicat程式從RBA 1904處開始執行：
GGSCI (localhost.localdomain) 2> alter rtop1,extseqno 1,extrba 1904
REPLICAT altered.
GGSCI (localhost.localdomain) 3> start rtop1
Sending START request to MANAGER ...
REPLICAT RTOP1 starting
GGSCI (localhost.localdomain) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RTOP1 00:00:00 00:00:02
檢視目標端topicis.aa表，看後面的事務是否執行成功：
[topicis@localhost ~]$ db2 "select * from topicis.aa"
ID NAME
----------- ----------
1 a
2 b
3 c
10 keep
4 yyy
20 trying
6 record(s) selected.
可以看到錯誤發生後插入的兩條資料被複制過來了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25744374/viewspace-753896/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- GoldenGate SQL error 01296 mapping 錯誤解決GoSQLErrorAPP
- 使用goldengate error log檢視錯誤資訊GoError
- Goldengate複製程式錯誤Fatal error executing DDLGoError
- Python 之 錯誤，除錯和測試Python除錯
- MySQL error 錯 誤 碼MySqlError
- PostgreSQL error 錯誤碼SQLError
- goldengate的sqlexec測試GoSQL
- GoldenGate 常見錯誤分析（二）Go
- 測試庫發生ora-12528錯誤及相應的該錯誤測試記錄
- Error Curves——錯誤曲線Error
- Python學習之錯誤除錯和測試Python除錯
- An error from listenerError
- GoldenGate同步DML功能測試Go
- docker image rm發生錯誤 Error response from daemon: conflict: unable to remove repository referenceDockerErrorREM
- 15-錯誤處理（Error）Error
- Js錯誤Error物件詳解JSError物件
- Datastore error in 'dirbdb'錯誤解決ASTError
- 記錄一次常見的錯誤:java.sql.SQLException: Got error 28 from storage engineJavaSQLExceptionGoError
- Oracle goldengate 11g 錯誤彙總OracleGo
- oracle 測試ORA-19815錯誤Oracle
- hibernate錯誤No CurrentSessionContext configured! Junit測試報錯SessionContext
- HTTP 錯誤 500.19- Internal Server Error 錯誤解決方法HTTPServerError
- PHP curl error 60 錯誤解決PHPError
- MySQL ERROR 1698 (28000) 錯誤MySqlError
- HTTP 錯誤 500.19 - Internal Server ErrorHTTPServerError
- Shrink 不支援IOT MAPPING TABLES的測試（轉）APP
- Angular 錯誤訊息：ERROR Error NullInjectorError No provider for XXAngularErrorNullIDE
- GoldenGate的安裝、配置與測試Go
- yml 配置檔案提示錯誤mapping values are not allowed in this contextAPPContext
- PHP檔案上傳錯誤程式碼，狀態測試，除錯PHP除錯
- DB2 DDL操作引起的GoldenGate錯誤DB2Go
- android 單元測試出現錯誤及改正Android
- 解決docker啟動映象報錯：docker: Error response from daemonDockerError
- MySQL 5.7.17 安裝報錯SSL error: Unable to get private key fromMySqlError
- 【GoldenGate】Oracle GoldenGate(四) 壓力測試同步百萬行資料GoOracle
- 【ERROR】Oracle列印錯誤程式碼解釋ErrorOracle
- Fatal NI connect error 12170 錯誤Error
- android 打包出現錯誤Error： ResourceNameAndroidError