??GoldenGate Replicat?HANDLECOLLISIONS??
- by Liu Maclean(???)
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 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?????:
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, NOHANDLECOLLISIONS
Sending NOHANDLECOLLISIONS request to REPLICAT REP2 ...
REP2 NOHANDLECOLLISIONS set for 1 tables and 0 wildcard entries
