了解GoldenGate Replicat的HANDLECOLLISIONS参数

HANDLECOLLISIONS是我们使用goldengate过程中常有的一个REPLICAT参数,该参数依赖于主键或唯一索引处理冲突数据,常用于初始化阶段。对于无主键或唯一索引的表无法处理冲突,且可能导致重复记录。注意打开此参数则所有数据错误不管reperror如何配置均不再写discard文件,即所有数据冲突信息被默认规则处理,没有任何日志(则会忽略error mapping数据错误,而且不会报告到discard文件),因此日常复制不建议使用该参数;可予以考虑的特殊场景为只需新增数据,无需复制历史数据。

 

使用HANDLECOLLISIONS的几个场景:

  1. target丢失delete记录(missing delete),忽略该问题并不记录到discardfile
  2. target丢失update记录(missing update)
    • 更新的键值是主键=》 update转换成INSERT ,默认情况下插入记录不完整
    • 更新的键值是非主键=》 忽略该问题并不记录到discardfile
  3. 重复插入已存在的主键值到target表中,这将被replicat转换为UPDATE现有主键值的行的其他非主键列

情景1 target丢失delete记录(missing delete) :

C:\Users\ML>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 18 13:38:03 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn sender/oracle
Connected.
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          2
target :
SQL> conn receiver/oracle
Connected.
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          2
SQL>
GGSCI (XIANGBLI-CN) 1> alter extract load2 , begin now
EXTRACT altered.
GGSCI (XIANGBLI-CN) 4> alter replicat rep2, begin now
REPLICAT 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 mgr
MGR is already running.
GGSCI (XIANGBLI-CN) 15> start er *
Sending START request to MANAGER ...
EXTRACT LOAD2 starting
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (XIANGBLI-CN) 16> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     LOAD2       00:00:00      00:00:01
REPLICAT    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 ABORT
2012-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\ze000003
Hdr-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   : 3337232
Continued  :     N  (x00)     RecCount   :     1  (x01)
2012-09-18 13:45:38.000000 Delete             Len     9 RBA 1091
Name: SENDER.HANDLEC
___________________________________________________________________
Reading D:\ogg\V34342-01\ex\ze000003, current RBA 1091, 0 records
Report 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:         1
Last 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:   0
2012-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 statistics
CACHE MANAGER VM USAGE
vm current     =      0    vm anon queues =      0 
vm anon in use =      0    vm file        =      0 
vm used max    =      0    ==> CACHE BALANCED
CACHE CONFIGURATION
cache size       =   2G   cache force paging = 3.41G
buffer min       =  64K   buffer highwater   =   8M
pageout eligible size =   8M
================================================================================
使用skiptransaction跳过上述失败事务
GGSCI (XIANGBLI-CN) 18> start rep2 skiptransaction
Sending 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-01296
2012-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 <UPDATE "RECEIVER"."HANDLEC" SET "T1" = :a1 WHERE "T1" = :b0>).
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 rep2
replicat rep2
userid receiver , password oracle
trace ./rep_trace1.trc
trace2 ./rep_trace2.trc
ASSUMETARGETDEFS
HANDLECOLLISIONS
map sender.*, target receiver.*;
GGSCI (XIANGBLI-CN) 18> start rep2
SQL> 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/oracle
Connected.
SQL> select * from handlec;
T1 T2
---------- ----------
1 2
10 100
5
20 200
SQL> delete handlec where t1=5;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from handlec;
T1 T2
---------- ----------
1 2
10 100
20 200
SQL> conn sender/oracle
Connected.
SQL> update handlec set t1=t1+1000 where t1=5;
1 row updated.
SQL> commit;
Commit complete.
SQL> conn receiver/oracle
Connected.
SQL>
SQL>
SQL> select * from handlec;
T1 T2
---------- ----------
1 2
10 100
20 200
1005 2

 

 

 

如上述实验验证FETCHOPTIONS FETCHPKUPDATECOLS将捕获完整的redo image镜像到trail中,这保证把primary key的更新通过HANDLECOLLISIONS转换为对target的一个完整记录的插入。

 

情景3 重复插入已存在的主键值到target表中,这将被replicat转换为UPDATE现有主键值的行的其他非主键列:

 

 

 

*** TARGET 
SQL> conn receiver/oracle
Connected.
SQL> select * from handlec;
T1         T2
---------- ----------
1          2
10          9
5
target中已经存在 t1=10 t2=9的记录 ,此时再在source中插入(10,100)的记录
>>SOURCE
SQL> insert into handlec values(10,100);
1 row created.
SQL> commit;
>>TARGET
SQL> select * from handlec;
T1         T2
---------- ----------
1          2
10        100
5
上面可以看到在source的insert操作,因为在target中已有对应的主键记录所以被启用HANDLECOLLISIONS的REPLICAT转换为UPDATE非主键的其他COLUMNS

 

 

总结

 

HANDLECOLLISIONS是我们使用goldengate过程中常有的一个REPLICAT参数,该参数依赖于主键或唯一索引处理冲突数据,常用于初始化阶段。对于无主键或唯一索引的表无法处理冲突,且可能导致重复记录。注意打开此参数则所有数据错误不管reperror如何配置均不再写discard文件,即所有数据冲突信息被默认规则处理,没有任何日志,因此日常复制不建议使用该参数;可予以考虑的特殊场景为只需新增数据,无需复制历史数据。

 

使用HANDLECOLLISIONS的几个场景:

  1. target丢失delete记录(missing delete),忽略该问题并不记录到discardfile
  2. target丢失update记录(missing update)
    • 更新的键值是主键=》 update转换成INSERT ,默认情况下插入记录不完整
    • 更新的键值是非主键=》 忽略该问题并不记录到discardfile
  3. 重复插入已存在的主键值到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, NOHANDLECOLLISIONS
Sending NOHANDLECOLLISIONS request to REPLICAT REP2 ...
REP2 NOHANDLECOLLISIONS set for 1 tables and 0 wildcard entries

Trackbacks

  1. […] update转换成INSERT ,默认情况下插入记录不完整,详见《了解GoldenGate Replicat的HANDLECOLLISIONS参数》)对于丢失的PK […]

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号