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<(DBObjType)1> const&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> 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博客 ” ，链接：https://blog.itpub.net/25744374/viewspace-753896/ ，如需转载，请注明出处，否则将追究法律责任。
