This is in continuation to my series of findings and I recommend that you start from the first post
http://gauthamc.blogspot.com/2014/08/goldengate-supplemental-logging-keycols.html to understand my environment much better.
TEST CASE 2
==========
The test case is pretty similar to test case 1 except that I have modified to extract parameter to add
LOGALLSUPCOLS.
Otherwise the setup is exactly the same as test case 1.
You might want to read more about LOGALLSUPCOLS (extract parameter) which is GG 12c feature and seems to be mandatory if you are using INTEGRATED replicat (again a 12c feature) with parallelism >1 (which should be true in most cases). Here we are going to look at what changes in the trail file when this parameter is set .
So here it goes again, the same steps as in test case 1.
--extract parameter file
..
..
DDL include Mapped
LOGALLSUPCOLS
table SCOTT.junk;
sequence SCOTT.*;
--end of extract parameter file
--create table in source with no PK/UK defined
SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb
er,col6 number);
Table created.
--make sure the table DDL has moved to target
SQL> select * from junk;
no rows selected
--set supplemental logging for col1,col2 and col3
GGSCI (titan.gautham.com) 140> add trandata scott.junk, cols(col1,col2,col3),nokey
2014-08-09 15:52:09 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.
Logging of supplemental redo data enabled for table SCOTT.JUNK.
TRANDATA for scheduling columns has been added on table 'SCOTT.JUNK'.
--verify
GGSCI (titan.gautham.com) 142> info trandata scott.junk
Logging of supplemental redo log data is enabled for table SCOTT.JUNK.
Columns supplementally logged for table SCOTT.JUNK: COL1, COL2, COL3.
--perform an insert on source
SQL> insert into junk values(100,100,100,100,100,100);
1 row created.
SQL> commit;
Commit complete.
--verify insert has travelled to target
SQL> select * from junk;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 100 100 100
--perform an update on the source of col4 .Remember only col1,col2 and col3 is added for supplemental logging
SQL> update junk set col4=500 where col1=100;
1 row updated.
SQL> commit;
Commit complete.
Look at the trailfile from logdump for the update
--------------------------------------------------------------------
The interesting thing is you will see 2 records in the trail file for the above update.
You will see a FieldComp (before image) followed by a GGSPKUpdate(before/after image).
It does look like the LOGALLSUPCOLS is responsible for the 1st FieldComp record .
With my interactions with Oracle Support and update operation always appears as either a FieldComp or GGSPKUpdate(when there is a PK update).In most practical situations where a table always has a PK and logging is setup either on the PK or all columns, I have only seen a FieldComp when an update operation takes place.
I am assuming that if the source had a PK or KEYCOLS defined in parameter file(assuming supplemental logging covered the PK or the KEYCOLS), we would see the GGSPKUpdate replaced with a FieldComp .That will be my test case 3.
Logdump 752 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 44 (x002c) IO Time : 2014/08/09 16:17:18.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 243605008
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 16:17:18.000.000 FieldComp Len 44 RBA 8711
Name: SCOTT.JUNK
Before Image: Partition 4 G b
0000 0007 0000 0003 3130 3000 0100 0700 0000 0331 | ........100........1
3030 0002 0007 0000 0003 3130 3000 0300 0700 0000 | 00........100.......
0331 3030 | .100
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 3130 30 | ....100
GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 4763 4141 4641 4141 414b 4641 4141 0001 | AAAUGcAAFAAAAKFAAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3131 3038 3032 37 | 591108027
TokenID x36 '6' TRANID Info x00 Length 12
3130 2e32 392e 3637 3134 3533 | 10.29.671453
Important findings from the above record -
As expected we see col1,col2,col3 records because of supplemental logging.
Looks like since we updated col4 (4th column of the table or column 3 in GG(as GG numbers column from 0),
it stores the before image of col4 which is 100
Logdump 753 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 90 (x005a) IO Time : 2014/08/09 16:17:18.000.000
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 243605008
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 16:17:18.000.000 GGSPKUpdate Len 90 RBA 8873
Name: SCOTT.JUNK
After Image: Partition 4 G e
002c 0000 0007 0000 0003 3130 3000 0100 0700 0000 | .,........100.......
0331 3030 0002 0007 0000 0003 3130 3000 0300 0700 | .100........100.....
0000 0331 3030 0000 0007 0000 0003 3130 3000 0100 | ...100........100...
0700 0000 0331 3030 0002 0007 0000 0003 3130 3000 | .....100........100.
0300 0700 0000 0335 3030 | .......500
Before Image Len 46 (x0000002e)
KeyLen 44 (x0000002c)
KeyCol 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 3 (x0003), Len 7 (x0007)
0000 0003 3130 30 | ....100
After Image Len 44 (x0000002c)
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 4763 4141 4641 4141 414b 4641 4141 0001 | AAAUGcAAFAAAAKFAAA..
As with testcase 1, the replicat was broken here too -
REPLICAT ABENDED RPHVN1D 00:00:00 00:02:35
report file
---------------
...
...
2014-08-09 16:16:54 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.
..
..
It does seem like we had the same abort as testcase 1 and we can fix this issue either by putting KEYCOLS on the replicat parameter file or just adding a PK(on target table) on one of the columns of col1, col2,col3 and col4.
Just for curiousity, instead of adding a PK on col1 or col2 or col3 (unchanged), let me add the PK on the changed column col4.
SQL> alter table junk add constraint pk_junk primary key(col4);
Table altered.
--restart the replicat and check the target
SQL> select * from junk;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 500 100 100
http://gauthamc.blogspot.com/2014/08/goldengate-supplemental-logging-keycols.html to understand my environment much better.
TEST CASE 2
==========
The test case is pretty similar to test case 1 except that I have modified to extract parameter to add
LOGALLSUPCOLS.
Otherwise the setup is exactly the same as test case 1.
You might want to read more about LOGALLSUPCOLS (extract parameter) which is GG 12c feature and seems to be mandatory if you are using INTEGRATED replicat (again a 12c feature) with parallelism >1 (which should be true in most cases). Here we are going to look at what changes in the trail file when this parameter is set .
So here it goes again, the same steps as in test case 1.
--extract parameter file
..
..
DDL include Mapped
LOGALLSUPCOLS
table SCOTT.junk;
sequence SCOTT.*;
--end of extract parameter file
--create table in source with no PK/UK defined
SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb
er,col6 number);
Table created.
--make sure the table DDL has moved to target
SQL> select * from junk;
no rows selected
--set supplemental logging for col1,col2 and col3
GGSCI (titan.gautham.com) 140> add trandata scott.junk, cols(col1,col2,col3),nokey
2014-08-09 15:52:09 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.
Logging of supplemental redo data enabled for table SCOTT.JUNK.
TRANDATA for scheduling columns has been added on table 'SCOTT.JUNK'.
--verify
GGSCI (titan.gautham.com) 142> info trandata scott.junk
Logging of supplemental redo log data is enabled for table SCOTT.JUNK.
Columns supplementally logged for table SCOTT.JUNK: COL1, COL2, COL3.
--perform an insert on source
SQL> insert into junk values(100,100,100,100,100,100);
1 row created.
SQL> commit;
Commit complete.
--verify insert has travelled to target
SQL> select * from junk;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 100 100 100
--perform an update on the source of col4 .Remember only col1,col2 and col3 is added for supplemental logging
SQL> update junk set col4=500 where col1=100;
1 row updated.
SQL> commit;
Commit complete.
Look at the trailfile from logdump for the update
--------------------------------------------------------------------
The interesting thing is you will see 2 records in the trail file for the above update.
You will see a FieldComp (before image) followed by a GGSPKUpdate(before/after image).
It does look like the LOGALLSUPCOLS is responsible for the 1st FieldComp record .
With my interactions with Oracle Support and update operation always appears as either a FieldComp or GGSPKUpdate(when there is a PK update).In most practical situations where a table always has a PK and logging is setup either on the PK or all columns, I have only seen a FieldComp when an update operation takes place.
I am assuming that if the source had a PK or KEYCOLS defined in parameter file(assuming supplemental logging covered the PK or the KEYCOLS), we would see the GGSPKUpdate replaced with a FieldComp .That will be my test case 3.
Logdump 752 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 44 (x002c) IO Time : 2014/08/09 16:17:18.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 243605008
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 16:17:18.000.000 FieldComp Len 44 RBA 8711
Name: SCOTT.JUNK
Before Image: Partition 4 G b
0000 0007 0000 0003 3130 3000 0100 0700 0000 0331 | ........100........1
3030 0002 0007 0000 0003 3130 3000 0300 0700 0000 | 00........100.......
0331 3030 | .100
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 3130 30 | ....100
GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4155 4763 4141 4641 4141 414b 4641 4141 0001 | AAAUGcAAFAAAAKFAAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3131 3038 3032 37 | 591108027
TokenID x36 '6' TRANID Info x00 Length 12
3130 2e32 392e 3637 3134 3533 | 10.29.671453
Important findings from the above record -
As expected we see col1,col2,col3 records because of supplemental logging.
Looks like since we updated col4 (4th column of the table or column 3 in GG(as GG numbers column from 0),
it stores the before image of col4 which is 100
Logdump 753 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 90 (x005a) IO Time : 2014/08/09 16:17:18.000.000
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 243605008
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 16:17:18.000.000 GGSPKUpdate Len 90 RBA 8873
Name: SCOTT.JUNK
After Image: Partition 4 G e
002c 0000 0007 0000 0003 3130 3000 0100 0700 0000 | .,........100.......
0331 3030 0002 0007 0000 0003 3130 3000 0300 0700 | .100........100.....
0000 0331 3030 0000 0007 0000 0003 3130 3000 0100 | ...100........100...
0700 0000 0331 3030 0002 0007 0000 0003 3130 3000 | .....100........100.
0300 0700 0000 0335 3030 | .......500
Before Image Len 46 (x0000002e)
KeyLen 44 (x0000002c)
KeyCol 0 (x0000), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 1 (x0001), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 2 (x0002), Len 7 (x0007)
0000 0003 3130 30 | ....100
KeyCol 3 (x0003), Len 7 (x0007)
0000 0003 3130 30 | ....100
After Image Len 44 (x0000002c)
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 4763 4141 4641 4141 414b 4641 4141 0001 | AAAUGcAAFAAAAKFAAA..
As with testcase 1, the replicat was broken here too -
REPLICAT ABENDED RPHVN1D 00:00:00 00:02:35
report file
---------------
...
...
2014-08-09 16:16:54 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.
..
..
It does seem like we had the same abort as testcase 1 and we can fix this issue either by putting KEYCOLS on the replicat parameter file or just adding a PK(on target table) on one of the columns of col1, col2,col3 and col4.
Just for curiousity, instead of adding a PK on col1 or col2 or col3 (unchanged), let me add the PK on the changed column col4.
SQL> alter table junk add constraint pk_junk primary key(col4);
Table altered.
--restart the replicat and check the target
SQL> select * from junk;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 500 100 100
Looks like we are all good now.
No comments:
Post a Comment