My goldengate set up is a 11.2.0.4 oracle database on a goldengate 12c .
I am running a series of tests to understand how goldengate writes data into the trail file and how replicat behaves based on supplemental logging (all columns vs few columns), no PK/UK defined on the tables, effect of keycols in the extract and replicat parameter file, LOGALLSUPCOLS(a 12c GG parameter).
I hope to document a bunch of them and will be writing multiple testcases.
TEST CASE 1
=============
Table has no PK or unique key defined on both source and target and table structure is identical.
Supplemental logging is done for 3 columns out of 6 columns.
Extract has no KEYCOLS defined.
EXTRACT has no LOGALLSUPCOLS.
replicat has no KEYCOLS defined.
SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb
er,col6 number)
2 /
Table created.
GGSCI (titan.gautham.com) 119> add trandata scott.junk, cols(col1,col2,col3),nokey
2014-08-09 13:07:44 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'.
GGSCI (titan.gautham.com) 120> 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.
--LOGALLSUPCOLS -->commented out
table SCOTT.junk; -->no keycols mentioned here
Perform the following from sqlplus at the source-
SQL> insert into junk values(100,100,100,100,100,100);
1 row created.
SQL> commit;
Commit complete.
SQL> update junk set col4=500 where col1=100;
1 row updated.
SQL> commit;
Commit complete.
The below logdump portion is relevant only to the update statement .I have not included the logdump portion for the insert as it does not serve us any purpose for this discussion.
1.Has info on col1, col2,col3 (as they are added in add trandata)
2.Has info on col4 as it was changed
3.shows GGSPKUpdate (for the update) with before and after image
Logdump 692 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 90 (x005a) IO Time : 2014/08/09 13:23:00.000.000
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 230433296
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 13:23:00.000.000 GGSPKUpdate Len 90 RBA 4260
Name: SCOTT.JUNK
After Image: Partition 4 G s
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 4751 4141 4641 4141 4149 7641 4141 0001 | AAAUGQAAFAAAAIvAAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3130 3932 3935 36 | 591092956
TokenID x36 '6' TRANID Info x00 Length 11
352e 3132 2e32 3138 3937 35 | 5.12.218975
replicat parameter file
=========================
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 = 'rphvn1d'
, 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')
) );
EFFECT ON REPLICAT
==================
REPLICAT CRASHES
ggserr.log
------------
2014-08-09 13:23:05 WARNING OGG-01151 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Error mapping from SCOTT.JUNK to SCOTT1.JUNK.
2014-08-09 13:23:05 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Repositioning to rba 4260 in seqno 14.
2014-08-09 13:23:05 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Error mapping from SCOTT.JUNK to SCOTT1.JUNK.
2014-08-09 13:23:14 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: PROCESS ABENDING.
report file
-----------
2014-08-09 13:40:18 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.
From above error message , it is clear that at replicat side since it does not see any PK (on the target)or KEYCOL defined (in replicat parameter file)
, it needs all the 6 columns in the trail file. But the trail file has before and after image for only first 4 columns (col1,col2.col3 as it is defined by supplemental logging and col4 as it was the changed column).
So to remediate, temporarily add a PK to target-
SQL> alter table junk add constraint pk_junk primary key(col1);
Table altered.
And restart the replicat
GGSCI (titan.gautham.com) 137> start rphvn1d
Sending START request to MANAGER ...
REPLICAT RPHVN1D starting
GGSCI (titan.gautham.com) 138> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
JAGENT STOPPED
EXTRACT RUNNING XTHVN1D 00:00:09 00:00:07
REPLICAT RUNNING RPHVN1D 02:18:58 00:00:02
--Check the data on the target
SQL> select * from junk;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 500 100 100
Now since the testing is complete, get the source and target ready for the next testcase by dropping junk table at the source and make sure the DDL got replicated to target -
source
--------
SQL> drop table junk;
Table dropped.
target
------
SQL> select * from junk;
select * from junk
*
ERROR at line 1:
ORA-00942: table or view does not exist
I am running a series of tests to understand how goldengate writes data into the trail file and how replicat behaves based on supplemental logging (all columns vs few columns), no PK/UK defined on the tables, effect of keycols in the extract and replicat parameter file, LOGALLSUPCOLS(a 12c GG parameter).
I hope to document a bunch of them and will be writing multiple testcases.
TEST CASE 1
=============
Table has no PK or unique key defined on both source and target and table structure is identical.
Supplemental logging is done for 3 columns out of 6 columns.
Extract has no KEYCOLS defined.
EXTRACT has no LOGALLSUPCOLS.
replicat has no KEYCOLS defined.
SQL> create table junk(col1 number,col2 number,col3 number,col4 number,col5 numb
er,col6 number)
2 /
Table created.
GGSCI (titan.gautham.com) 119> add trandata scott.junk, cols(col1,col2,col3),nokey
2014-08-09 13:07:44 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'.
GGSCI (titan.gautham.com) 120> 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.
Extract parameter file
..--LOGALLSUPCOLS -->commented out
table SCOTT.junk; -->no keycols mentioned here
Perform the following from sqlplus at the source-
SQL> insert into junk values(100,100,100,100,100,100);
1 row created.
SQL> commit;
Commit complete.
SQL> update junk set col4=500 where col1=100;
1 row updated.
SQL> commit;
Commit complete.
The below logdump portion is relevant only to the update statement .I have not included the logdump portion for the insert as it does not serve us any purpose for this discussion.
logdump of trail file
1.Has info on col1, col2,col3 (as they are added in add trandata)
2.Has info on col4 as it was changed
3.shows GGSPKUpdate (for the update) with before and after image
Logdump 692 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 90 (x005a) IO Time : 2014/08/09 13:23:00.000.000
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 10578 AuditPos : 230433296
Continued : N (x00) RecCount : 1 (x01)
2014/08/09 13:23:00.000.000 GGSPKUpdate Len 90 RBA 4260
Name: SCOTT.JUNK
After Image: Partition 4 G s
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 4751 4141 4641 4141 4149 7641 4141 0001 | AAAUGQAAFAAAAIvAAA..
TokenID x4c 'L' LOGCSN Info x00 Length 9
3539 3130 3932 3935 36 | 591092956
TokenID x36 '6' TRANID Info x00 Length 11
352e 3132 2e32 3138 3937 35 | 5.12.218975
replicat parameter file
=========================
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 = 'rphvn1d'
, 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')
) );
EFFECT ON REPLICAT
==================
REPLICAT CRASHES
ggserr.log
------------
2014-08-09 13:23:05 WARNING OGG-01151 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Error mapping from SCOTT.JUNK to SCOTT1.JUNK.
2014-08-09 13:23:05 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Repositioning to rba 4260 in seqno 14.
2014-08-09 13:23:05 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: Error mapping from SCOTT.JUNK to SCOTT1.JUNK.
2014-08-09 13:23:14 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rphvn1d.prm: PROCESS ABENDING.
report file
-----------
2014-08-09 13:40:18 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.
From above error message , it is clear that at replicat side since it does not see any PK (on the target)or KEYCOL defined (in replicat parameter file)
, it needs all the 6 columns in the trail file. But the trail file has before and after image for only first 4 columns (col1,col2.col3 as it is defined by supplemental logging and col4 as it was the changed column).
So to remediate, temporarily add a PK to target-
SQL> alter table junk add constraint pk_junk primary key(col1);
Table altered.
And restart the replicat
GGSCI (titan.gautham.com) 137> start rphvn1d
Sending START request to MANAGER ...
REPLICAT RPHVN1D starting
GGSCI (titan.gautham.com) 138> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
JAGENT STOPPED
EXTRACT RUNNING XTHVN1D 00:00:09 00:00:07
REPLICAT RUNNING RPHVN1D 02:18:58 00:00:02
--Check the data on the target
SQL> select * from junk;
COL1 COL2 COL3 COL4 COL5 COL6
---------- ---------- ---------- ---------- ---------- ----------
100 100 100 500 100 100
Now since the testing is complete, get the source and target ready for the next testcase by dropping junk table at the source and make sure the DDL got replicated to target -
source
--------
SQL> drop table junk;
Table dropped.
target
------
SQL> select * from junk;
select * from junk
*
ERROR at line 1:
ORA-00942: table or view does not exist
No comments:
Post a Comment