Same as in testcase 1 i.e. NO PK's on source or target, no LOGALLSUPCOLS in extract, no KEYCOLS in replicat except that extract now has KEYCOLS defined on col1 .
GETTRUNCATES
table SCOTT.junk, keycols(col1);
Please don't get confused with the mapexception part as it is the exception handling routine I always add in dev/qa to trap unique key violated and no data found.
This has no meaning in this experiment.
DDL INCLUDE MAPPED &
EXCLUDE OBJNAME GGADM.EXCEPTIONS
..
ASSUMETARGETDEFS
GETTRUNCATES
REPERROR (-1, EXCEPTION)
REPERROR (-1403, EXCEPTION)
map scott.junk, target scott1.junk, mapexception (TARGET ggadm.exceptions
, INSERTALLRECORDS
-- , EXCEPTIONSONLY
, colmap ( rep_name = 'xxxxx'
, table_name = @GETENV ('GGHEADER', 'TABLENAME')
, errno = @GETENV ('LASTERR', 'DBERRNUM')
, dberrmsg = @GETENV ( 'LASTERR', 'DBERRMSG')
, optype = @GETENV ( 'LASTERR', 'OPTYPE')
, errtype = @GETENV ( 'LASTERR', 'ERRTYPE')
, logrba = @GETENV ( 'GGHEADER', 'LOGRBA')
, logposition = @GETENV ( 'GGHEADER', 'LOGPOSITION')
, TRAILRBA = @GETENV('RECORD', 'FILERBA')
, TRAILSEQUENCE = @GETENV('RECORD', 'FILESEQNO')
, committimestamp = @GETENV ( 'GGHEADER', 'COMMITTIMESTAMP')
) );
As always create table, add supplemental logging on col1,col2,col3 and insert a row on source and ensure that travelled to target.
Please see this relevant portion from test case 1.
Now the important update
SQL> update junk set col4=500 where col1=100;
1 row updated.
SQL> commit;
Logdump 781 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 44 (x002c) IO Time : 2014/08/09 16:57:33.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 245809680
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 16:57:33.000.000 FieldComp Len 44 RBA 5876
Name: SCOTT.JUNK
After Image: Partition 4 G s
0000 0007 0000 0003 3130 3000 0100 0700 0000 0331 | ........100........1
3030 0002 0007 0000 0003 3130 3000 0300 0700 0000 | 00........100.......
0335 3030 | .500
Column 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 3 (x0003), Len 7 (x0007)
0000 0003 3530 30 | ....500
GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 476a 4141 4641 4141 414b 3141 4141 0001 | AAAUGjAAFAAAAK1AAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3131 3131 3537 31 | 591111571
TokenID x36 '6' TRANID Info x00 Length 12
3231 2e33 332e 3138 3830 3833 | 21.33.188083
See the huge difference that KEYCOL made ?
In test case 1, we saw a GGSPKUpdate whereas now we are seeing a FieldComp.
remember supplemental logging was done on col1,col2 and col3 and KEYCOLS had col1.
Replicat has the same abort issue .
2014-08-09 16:57:14 WARNING OGG-06439 No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
COL1, COL2, COL3, COL4, COL5, COL6
Using the following key columns for target table SCOTT1.JUNK: COL1, COL2, COL3, COL4, COL5, COL6.
This is because since the target has no PK/UK or KEYCOLS , the replicat wants to set all the columns in the where condition of the update but it sees only col1, col2, col3, col4. therefore it fails.
The resolving is simple, just add primary key on either or all of columns - col1, col2,col3 and col4 and you are set.
SQL> alter table junk add constraint pk_junk primary key(col2);
Table altered.
logdump as always
Extract Parameter file--------------------------------
--LOGALLSUPCOLS -->commented outGETTRUNCATES
table SCOTT.junk, keycols(col1);
Replicat Paramer file------------------------------
Please don't get confused with the mapexception part as it is the exception handling routine I always add in dev/qa to trap unique key violated and no data found.This has no meaning in this experiment.
DDL INCLUDE MAPPED &
EXCLUDE OBJNAME GGADM.EXCEPTIONS
..
ASSUMETARGETDEFS
GETTRUNCATES
REPERROR (-1, EXCEPTION)
REPERROR (-1403, EXCEPTION)
map scott.junk, target scott1.junk, mapexception (TARGET ggadm.exceptions
, INSERTALLRECORDS
-- , EXCEPTIONSONLY
, colmap ( rep_name = 'xxxxx'
, table_name = @GETENV ('GGHEADER', 'TABLENAME')
, errno = @GETENV ('LASTERR', 'DBERRNUM')
, dberrmsg = @GETENV ( 'LASTERR', 'DBERRMSG')
, optype = @GETENV ( 'LASTERR', 'OPTYPE')
, errtype = @GETENV ( 'LASTERR', 'ERRTYPE')
, logrba = @GETENV ( 'GGHEADER', 'LOGRBA')
, logposition = @GETENV ( 'GGHEADER', 'LOGPOSITION')
, TRAILRBA = @GETENV('RECORD', 'FILERBA')
, TRAILSEQUENCE = @GETENV('RECORD', 'FILESEQNO')
, committimestamp = @GETENV ( 'GGHEADER', 'COMMITTIMESTAMP')
) );
As always create table, add supplemental logging on col1,col2,col3 and insert a row on source and ensure that travelled to target.
Please see this relevant portion from test case 1.
Now the important update
SQL> update junk set col4=500 where col1=100;
1 row updated.
SQL> commit;
And here is the record from logdump in trail file
------------------------------------------------
Logdump 781 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 44 (x002c) IO Time : 2014/08/09 16:57:33.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 245809680
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 16:57:33.000.000 FieldComp Len 44 RBA 5876
Name: SCOTT.JUNK
After Image: Partition 4 G s
0000 0007 0000 0003 3130 3000 0100 0700 0000 0331 | ........100........1
3030 0002 0007 0000 0003 3130 3000 0300 0700 0000 | 00........100.......
0335 3030 | .500
Column 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 3 (x0003), Len 7 (x0007)
0000 0003 3530 30 | ....500
GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 476a 4141 4641 4141 414b 3141 4141 0001 | AAAUGjAAFAAAAK1AAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3131 3131 3537 31 | 591111571
TokenID x36 '6' TRANID Info x00 Length 12
3231 2e33 332e 3138 3830 3833 | 21.33.188083
See the huge difference that KEYCOL made ?
In test case 1, we saw a GGSPKUpdate whereas now we are seeing a FieldComp.
remember supplemental logging was done on col1,col2 and col3 and KEYCOLS had col1.
Replicat has the same abort issue .
2014-08-09 16:57:14 WARNING OGG-06439 No unique key is defined for table JUNK. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
COL1, COL2, COL3, COL4, COL5, COL6
Using the following key columns for target table SCOTT1.JUNK: COL1, COL2, COL3, COL4, COL5, COL6.
This is because since the target has no PK/UK or KEYCOLS , the replicat wants to set all the columns in the where condition of the update but it sees only col1, col2, col3, col4. therefore it fails.
The resolving is simple, just add primary key on either or all of columns - col1, col2,col3 and col4 and you are set.
SQL> alter table junk add constraint pk_junk primary key(col2);
Table altered.
--restart replicat and see the magic
SQL> /
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 500 100 100
I am going to redo the same experiment above but I am going to set KEYCOLS to a column which is not logged by supplemental logging and then going to update an totally different column.
1.Set supplemental logging on col1, col2, col3
2.Set KEYCOLS on col4 on extract side
i.e. table SCOTT.junk, keycols(col4);
3.Update a column say col5 not defined in 1,2.
Update junk set col5=500 where col1=100;
SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb
er,col6 number);
Table created.
add trandata scott.junk, cols(col1,col2,col3),nokey
SQL> insert into junk values(100,100,100,100,100,100);
1 row created.
SQL> commit;
Commit complete.
SQL> update junk set col5=500 where col1=100;
1 row updated.
SQL> commit;
Commit complete.
logdump as always
---------------------
Logdump 794 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 44 (x002c) IO Time : 2014/08/09 17:17:47.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 248909840
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 17:17:47.000.000 FieldComp Len 44 RBA 3031
Name: SCOTT.JUNK
After Image: Partition 4 G s
0000 0007 0000 0003 3130 3000 0100 0700 0000 0331 | ........100........1
3030 0002 0007 0000 0003 3130 3000 0400 0700 0000 | 00........100.......
0335 3030 | .500
Column 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
Column 4 (x0004), Len 7 (x0007)
0000 0003 3530 30 | ....500
GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 476e 4141 4641 4141 414c 4e41 4141 0001 | AAAUGnAAFAAAALNAAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3131 3133 3834 32 | 591113842
TokenID x36 '6' TRANID Info x00 Length 12
3231 2e33 302e 3138 3735 3938 | 21.30.187598
Very Interesting !!!!
Because KEYCOLs is mentioned in the parameter file , that is why we see a FieldComp instead of GGSPKUpdate operation (otherwise I am sure we would see a GGSPKUpdate in the absence of KEYCOLS) but unfortunately col4 which is set in KEYCOLS is missing in the
trail file as the supplemental logging only covered col1, col2 and col3 and offcourse we see col5 as that was the row updated.
So please, please ensure that your supplemental logging covers the KEYCOLS.
Also KEYCOLS or PK/UK decide whether the update would be a FieldComp vs GGSPKUpdate.
As always we see REPLICAT abended in target and i guess you all know what to do (if you had been reading my previous testcases).
Here are the golden rules on how the target system constructs the where condition of the target
based on whether it encounters a FieldComp or GGSPKUpdate
GGSPKUpdate has both before and after images in same record.
It is created only if source has no PK/UK nor does it have KEYCOLS defined on extract.
Rules - (1)If The target should have PK/UK or KEYCOLS defined in replicat - it will use those columns
for where condition(If both of them are available I am assuming KEYCOLS takes precedence over PK/UK)
Then the before image in the GGSPKUpdate should have those columns set. Otherwise replicat will bomb as it does not have enough information in trail file to construct the where clause.
(2)If the target has no PK/UK nor does it have KEYCOLS defined in replicat -
The before image of GGSPKUpdate should have all the table columns . Otherwise replicat will bomb as it does not have enough information in trail file to construct the where clause.
If both of them are available I am assuming KEYCOLS takes precedence over PK/UK.
Now the afterimage should cover those columns.If not it will bomb.
If it covers, then it will use them in the where condition and use the rest of the columns in set.
(2)If the target has no PK/UK nor does it have KEYCOLS defined in replicat -
The after image of FieldComp should have all the table columns . Otherwise replicat will bomb.
Assuming after image has all columns , then it uses all those columns for where condition and since it cannot reuse the columns in set, the update effectively becomes
update <table name> set <nothing> where <column list from goldengate>;
When the above case is encountered, we get the APPLYNOOPUPDATES error.
So please, please ensure that your supplemental logging covers the KEYCOLS.
Also KEYCOLS or PK/UK decide whether the update would be a FieldComp vs GGSPKUpdate.
As always we see REPLICAT abended in target and i guess you all know what to do (if you had been reading my previous testcases).
Here are the golden rules on how the target system constructs the where condition of the target
based on whether it encounters a FieldComp or GGSPKUpdate
GGSPKUpdate
GGSPKUpdate has both before and after images in same record.
It is created only if source has no PK/UK nor does it have KEYCOLS defined on extract.
Rules - (1)If The target should have PK/UK or KEYCOLS defined in replicat - it will use those columns
for where condition(If both of them are available I am assuming KEYCOLS takes precedence over PK/UK)
Then the before image in the GGSPKUpdate should have those columns set. Otherwise replicat will bomb as it does not have enough information in trail file to construct the where clause.
(2)If the target has no PK/UK nor does it have KEYCOLS defined in replicat -
The before image of GGSPKUpdate should have all the table columns . Otherwise replicat will bomb as it does not have enough information in trail file to construct the where clause.
FieldComp
FieldComp has no before image. It has only after image as basic assumption is PK is not changed.It is created only if source has a PK/UK or has KEYCOLS defined in extract parameter file.
Rules -
(1)If The target should have PK/UK or KEYCOLS defined in replicat - it will use those columns for where condition.If both of them are available I am assuming KEYCOLS takes precedence over PK/UK.
Now the afterimage should cover those columns.If not it will bomb.
If it covers, then it will use them in the where condition and use the rest of the columns in set.
(2)If the target has no PK/UK nor does it have KEYCOLS defined in replicat -
The after image of FieldComp should have all the table columns . Otherwise replicat will bomb.
Assuming after image has all columns , then it uses all those columns for where condition and since it cannot reuse the columns in set, the update effectively becomes
update <table name> set <nothing> where <column list from goldengate>;
When the above case is encountered, we get the APPLYNOOPUPDATES error.
2014-08-09 20:58:36 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2014-08-09 20:58:58 ERROR OGG-01168 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Encountered an update for target table SCOTT1.JUNK, which has no unique key defined. KEYCOLS can be used to define a key. Use ALLOWNOOPUPDATES to process the update without applying it to the target database. Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.
2014-08-09 20:59:08 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: PROCESS ABENDING.
2014-08-09 20:58:58 ERROR OGG-01168 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Encountered an update for target table SCOTT1.JUNK, which has no unique key defined. KEYCOLS can be used to define a key. Use ALLOWNOOPUPDATES to process the update without applying it to the target database. Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.
2014-08-09 20:59:08 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: PROCESS ABENDING.
No comments:
Post a Comment