HANDLECOLLISIONS是我们使用goldengate过程中常有的一个REPLICAT参数，该参数依赖于主键或唯一索引处理冲突数据，常用于初始化阶段。对于无主键或唯一索引的表无法处理冲突，且可能导致重复记录。注意打开此参数则所有数据错误不管reperror如何配置均不再写discard文件，即所有数据冲突信息被默认规则处理，没有任何日志(则会忽略error mapping数据错误，而且不会报告到discard文件)，因此日常复制不建议使用该参数；可予以考虑的特殊场景为只需新增数据，无需复制历史数据。 使用HANDLECOLLISIONS的几个场景：
- target丢失delete记录(missing delete)，忽略该问题并不记录到discardfile
- target丢失update记录(missing update)
- 更新的键值是主键=》 update转换成INSERT ，默认情况下插入记录不完整
- 更新的键值是非主键=》 忽略该问题并不记录到discardfile
- 重复插入已存在的主键值到target表中，这将被replicat转换为UPDATE现有主键值的行的其他非主键列
情景1 target丢失delete记录(missing delete) ：
C:\Users\ML>sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 18 13:38:03 2012Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> conn sender/oracleConnected.SQL> create table handlec(t1 int primary key,t2 int);Table created.SQL> insert into handlec values(1,2);1 row created.SQL> insert into handlec values(3,2);1 row created.SQL> insert into handlec values(4,2);1 row created.SQL> commit;Commit complete.SQL> select * from handlec; T1 T2---------- ---------- 1 2 3 2 4 2target :SQL> conn receiver/oracleConnected.SQL> create table handlec(t1 int primary key,t2 int);Table created.SQL> insert into handlec values(1,2);1 row created.SQL> commit;SQL> select * from handlec; T1 T2---------- ---------- 1 2SQL>GGSCI (XIANGBLI-CN) 1> alter extract load2 , begin nowEXTRACT altered.GGSCI (XIANGBLI-CN) 4> alter replicat rep2, begin nowREPLICAT altered.GGSCI (XIANGBLI-CN) 13> add trandata sender.*Logging of supplemental redo data enabled for table SENDER.HANDLEC.Logging of supplemental redo log data is already enabled for table SENDER.TV.GGSCI (XIANGBLI-CN) 14> start mgrMGR is already running.GGSCI (XIANGBLI-CN) 15> start er *Sending START request to MANAGER ...EXTRACT LOAD2 startingSending START request to MANAGER ...REPLICAT REP2 startingGGSCI (XIANGBLI-CN) 16> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING LOAD2 00:00:00 00:00:01REPLICAT RUNNING REP2 00:00:00 00:00:08***SOURCE端删除一条TARGET没有的数据SQL> delete handlec where t1=3;1 row deleted.SQL> commit;Commit complete.出现SQL error 1403错误，REPLICAT ABORT2012-09-18 13:45:48 WARNING OGG-01004 Aborted grouped transaction on 'RECEIVER.HANDLEC', Database error 1403 (OCI Error ORA-01403: no data found, SQL ).2012-09-18 13:45:48 WARNING OGG-01003 Repositioning to rba 1091 in seqno 3.2012-09-18 13:45:48 WARNING OGG-01154 SQL error 1403 mapping SENDER.HANDLEC to RECEIVER.HANDLEC OCI Error ORA-01403: no data found, SQL .2012-09-18 13:45:48 WARNING OGG-01003 Repositioning to rba 1091 in seqno 3.Source Context : SourceModule : [er.errors] SourceID : [er/errors.cpp] SourceFunction : [take_rep_err_action] SourceLine : [623] ThreadBacktrace : [8] elements : [D:\ogg\V34342-01\gglog.dll(??1CContextItem@@UEAA@XZ+0x3272) [0x000000018010BDD2]] : [D:\ogg\V34342-01\gglog.dll(?_MSG_ERR_MAP_TO_TANDEM_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjName@$00@ggapp@gglib@ggs@@1W4MessageDisposition@CMessageFactory@@@Z+0x138) [0x00000001800AD508]] : [D:\ogg\V34342-01\replicat.exe(ERCALLBACK+0x6e1e) [0x0000000140099D5E]] : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x4411) [0x00000001400C9BE1]] : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x289cd) [0x00000001400EE19D]] : [D:\ogg\V34342-01\replicat.exe(CommonLexerNewSSD+0x9440) [0x00000001402AE980]] : [C:\windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x000000007733652D]] : [C:\windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x000000007746C521]]2012-09-18 13:45:48 ERROR OGG-01296 Error mapping from SENDER.HANDLEC to RECEIVER.HANDLEC.************************************************************************ ** Run Time Statistics ** ************************************************************************Last record for the last committed transaction is the following: ___________________________________________________________________Trail name : D:\ogg\V34342-01\ex\ze000003Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 9 (x0009) IO Time : 2012-09-18 13:45:38.000000 IOType : 3 (x03) OrigNode : 255 (xff)TransInd : . (x03) FormatType : R (x52)SyskeyLen : 0 (x00) Incomplete : . (x00)AuditRBA : 44 AuditPos : 3337232Continued : N (x00) RecCount : 1 (x01)2012-09-18 13:45:38.000000 Delete Len 9 RBA 1091Name: SENDER.HANDLEC___________________________________________________________________Reading D:\ogg\V34342-01\ex\ze000003, current RBA 1091, 0 recordsReport at 2012-09-18 13:45:48 (activity since 2012-09-18 13:45:48)From Table SENDER.HANDLEC to RECEIVER.HANDLEC: # inserts: 0 # updates: 0 # deletes: 0 # discards: 1Last log location read: FILE: D:\ogg\V34342-01\ex\ze000003 SEQNO: 3 RBA: 1091 TIMESTAMP: 2012-09-18 13:45:38.000000 EOF: NO READERR: 02012-09-18 13:45:48 ERROR OGG-01668 PROCESS ABENDING.2012-09-18 13:45:48 INFO OGG-01237 Trace file D:\ogg\V34342-01\REP_TRACE1.TRC closed.2012-09-18 13:45:48 INFO OGG-01237 Trace file D:\ogg\V34342-01\REP_TRACE2.TRC closed.CACHE OBJECT MANAGER statisticsCACHE MANAGER VM USAGEvm current = 0 vm anon queues = 0 vm anon in use = 0 vm file = 0 vm used max = 0 ==> CACHE BALANCEDCACHE CONFIGURATIONcache size = 2G cache force paging = 3.41Gbuffer min = 64K buffer highwater = 8Mpageout eligible size = 8M================================================================================使用skiptransaction跳过上述失败事务GGSCI (XIANGBLI-CN) 18> start rep2 skiptransactionSending START request to MANAGER ...REPLICAT REP2 starting
情景2 target丢失update记录(missing update)，更新的键值是主键 ：
继续我们的测试, 针对source的某条记录进行更新SQL> update handlec set t1=5 where t1=4;1 row updated.SQL> commit;Commit complete.对于在target 丢失更新(miss update)的情况也会造成 Database error 1403+OGG-012962012-09-18 13:49:30 WARNING OGG-01004 Aborted grouped transaction on 'RECEIVER.HANDLEC', Database error 1403 (OCI Error ORA-01403: no data found, SQL ).2012-09-18 13:49:30 WARNING OGG-01003 Repositioning to rba 1218 in seqno 3.2012-09-18 13:49:30 WARNING OGG-01003 Repositioning to rba 1218 in seqno 3.Source Context : SourceModule : [er.errors] SourceID : [er/errors.cpp] SourceFunction : [take_rep_err_action] SourceLine : [623] ThreadBacktrace : [8] elements : [D:\ogg\V34342-01\gglog.dll(??1CContextItem@@UEAA@XZ+0x3272) [0x000000018010BDD2]] : [D:\ogg\V34342-01\gglog.dll(?_MSG_ERR_MAP_TO_TANDEM_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjName@$00@ggapp@gglib@ggs@@1W4MessageDisposition@CMessageFactory@@@Z+0x138) [0x00000001800AD508]] : [D:\ogg\V34342-01\replicat.exe(ERCALLBACK+0x6e1e) [0x0000000140099D5E]] : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x4411) [0x00000001400C9BE1]] : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x289cd) [0x00000001400EE19D]] : [D:\ogg\V34342-01\replicat.exe(CommonLexerNewSSD+0x9440) [0x00000001402AE980]] : [C:\windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x000000007733652D]] : [C:\windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x000000007746C521]]2012-09-18 13:49:30 ERROR OGG-01296 Error mapping from SENDER.HANDLEC to RECEIVER.HANDLEC.加入HANDLECOLLISIONS后，rep可以继续工作且不生成discard记录GGSCI (XIANGBLI-CN) 23> view params rep2replicat rep2userid receiver , password oracletrace ./rep_trace1.trctrace2 ./rep_trace2.trcASSUMETARGETDEFSHANDLECOLLISIONSmap sender.*, target receiver.*;GGSCI (XIANGBLI-CN) 18> start rep2SQL> select * from handlec; T1 T2---------- ---------- 1 2 5
这里出现T1=5 T2 NULL记录的原因是 ，丢失update的更新操作是针对主键的更新，此时replicat会尝试插入一条记录而非忽略该update。 注意插入的记录可能不是完整的行，如上例中的T2 为NULL ，若要求完整的行记录则要求EXTRACT使用PKUPDATE选项。 需要加入的选项是FETCHOPTIONS FETCHPKUPDATECOLS 将以上选项加入到EXTRACT参数文件中，并重启EXTRACT。 这将引起extract捕获完整的主键更新镜像。 如以下的例子：
SQL> conn receiver/oracleConnected.SQL> select * from handlec;T1 T2---------- ----------1 210 100520 200SQL> delete handlec where t1=5;1 row deleted.SQL> commit;Commit complete.SQL> select * from handlec;T1 T2---------- ----------1 210 10020 200SQL> conn sender/oracleConnected.SQL> update handlec set t1=t1+1000 where t1=5;1 row updated.SQL> commit;Commit complete.SQL> conn receiver/oracleConnected.SQL>SQL>SQL> select * from handlec;T1 T2---------- ----------1 210 10020 2001005 2
如上述实验验证FETCHOPTIONS FETCHPKUPDATECOLS将捕获完整的redo image镜像到trail中，这保证把primary key的更新通过HANDLECOLLISIONS转换为对target的一个完整记录的插入。 情景3 重复插入已存在的主键值到target表中，这将被replicat转换为UPDATE现有主键值的行的其他非主键列：
*** TARGET SQL> conn receiver/oracleConnected.SQL> select * from handlec; T1 T2---------- ---------- 1 2 10 9 5target中已经存在 t1=10 t2=9的记录 ，此时再在source中插入(10,100)的记录>>SOURCESQL> insert into handlec values(10,100);1 row created.SQL> commit;>>TARGETSQL> select * from handlec; T1 T2---------- ---------- 1 2 10 100 5上面可以看到在source的insert操作，因为在target中已有对应的主键记录所以被启用HANDLECOLLISIONS的REPLICAT转换为UPDATE非主键的其他COLUMNS
总结 HANDLECOLLISIONS是我们使用goldengate过程中常有的一个REPLICAT参数，该参数依赖于主键或唯一索引处理冲突数据，常用于初始化阶段。对于无主键或唯一索引的表无法处理冲突，且可能导致重复记录。注意打开此参数则所有数据错误不管reperror如何配置均不再写discard文件，即所有数据冲突信息被默认规则处理，没有任何日志，因此日常复制不建议使用该参数；可予以考虑的特殊场景为只需新增数据，无需复制历史数据。 使用HANDLECOLLISIONS的几个场景：
- target丢失delete记录(missing delete)，忽略该问题并不记录到discardfile
- target丢失update记录(missing update)
- 更新的键值是主键=》 update转换成INSERT ，默认情况下插入记录不完整
- 更新的键值是非主键=》 忽略该问题并不记录到discardfile
- 重复插入已存在的主键值到target表中，这将被replicat转换为UPDATE现有主键值的行的其他非主键列
另：该参数仅处理数据本身的Insert/Delete冲突，如果出现两端映射或其它结构性问题Replicat进程依然会abend，不能被忽略 此外对于主键的更新操作，若在target使用HANDLECOLLISIONS且该update丢失，在会转换为INSERT该主键的操作，注意默认情况下插入的记录不完整，FETCHOPTIONS FETCHPKUPDATECOLS将捕获完整的redo image镜像到trail中，这保证把primary key的更新通过HANDLECOLLISIONS转换为对target的一个完整记录的插入。 我们可以通过send 命令动态取消HANDLECOLLISIONS
GGSCI (XIANGBLI-CN) 29> send rep2, NOHANDLECOLLISIONSSending NOHANDLECOLLISIONS request to REPLICAT REP2 ...REP2 NOHANDLECOLLISIONS set for 1 tables and 0 wildcard entries