OGG-01161 Bad column index (3)

ALLSTARS_ORACLE

Source

Discard -> Process Abending  : 2012-11-22 09:53:07

Source  Context :      SourceModule            : [ggstd.conv.endian]      SourceID                : [/scratch/pradshar/view_storage/pradshar_bugdbrh40_12927937/oggcore/OpenSys/src/gglib/ggstd/lecnv.c]      SourceFunction          : [convCompSQL]      SourceLine              : [530]      ThreadBacktrace         : [9] elements                              : [/ggs/replicat(CMessageContext::AddThreadContext()+0x26)  [0x5f1e56]]                              : [/ggs/replicat(CMessageFactory::CreateMessage(CSourceContext*,  unsigned int,  ...)+0x7b2) [0x5e88f2]]                              : [/ggs/replicat(_MSG_ERR_MAP_COL_INDEX_INVALID(CSourceContext*,  DBString<777>  const&,  int,  int,  CMessageFactory::MessageDisposition)+0xa6)  [0x5bee16]]                              : [/ggs/replicat  [0x900e6c]]                              : [/ggs/replicat(ggConvRecLE(char*,  file_def*,  int,  char, char)+0x6c) [0x900fac]]                              : [/ggs/replicat  [0x8f4d6e]]                              : [/ggs/replicat(main+0x84b)  [0x507a0b]]                              :  [/lib64/libc.so.6(__libc_start_main+0xf4) [0x34cd81d994]]                              : [/ggs/replicat(__gxx_personality_v0+0x1e2)  [0x4e3ffa]]       2012-11-22 09:53:07  ERROR    OGG-01161  Bad column index (3)  specified for table FEXLINK.LUKE, max columns = 3.

ALLSTARS_ORACLE

Start the Replicat.
(OGG_Target) GGSCI> START rep_5d
 View the Replicat report.
(OGG_Target) GGSCI> VIEW REPORT rep_5d
 Look for the following message:
2006-10-04 16:08:51 GGS ERROR 160 Bad column index
24144) specifed for table SOURCE.EMP5D, max columns = 8.
This message indicates that there is something wrong with the trail.
 In Logdump, issue the NEXTTRAIL command to open the next file in the sequence, the
one you created with the rollover.
(OGG_Target) GGSCI> sh logdump
Logdump> OPEN ./dirdat/5d000000
Logdump> NEXTTRAIL
 Issue the NEXT command to view the first record.
Logdump> n
 Note the data portion of the record, the area on the right-hand side. It is gibberish,
indicating encryption.
 Enable decryption in Logdump.
Logdump> DECRYPT ON
 Issue the NEXT command to view the next record.
Logdump> n

Now you will see that the data has been decrypted so you can read it.
 Exit Logdump
 In GGSCI, edit the Replicat parameter file to uncomment the DECRYPTTRAIL
parameter and start the replicat
(OGG_Target) GGSCI> EDIT PARAMS rep_5d
(OGG_Target) GGSCI> START rep_5d
 From SQL*Plus, rerun the following script to insert more data into the source tables.
SQL> @Class_labs/lab_5_insert_more_data_d
 Use the INFO command to verify that Replicat is RUNNING.
(OGG_Target) GGSCI> INFO rep_5d

ALLSTARS_ORACLE

        What Causes the GGS ERROR 160 Bad Column Index(xxxx) Error in Replicat (Doc ID 972954.1)


APPLIES TO:

Oracle GoldenGate - Version 4.0.0 and later
Information in this document applies to any platform.
***Checked for relevance on 10-Jun-2013***
GOAL

What causes the GGS ERROR 160  Bad Column Index(xxxx) error in Oracle GoldenGate (OGG) Replicat?

SOLUTION

There are some potential reasons and workarounds for this error:

1) Column mismatch between source and target

The GGS ERROR 160 Bad Column Index(xxxx) error in Replicat is caused by a Source Column Index "xxxx" greater than the number of columns in the Source Table Definition File Input to the Replicat process or if the ASSUMETARGETDEFS parameter is used and the Source Table and Target Table do not have the same structure, the Source Table has more columns than the Target Table.

Example
GGS ERROR 160 Bad column index(129) specified for table {table name}, max columns = 127

Explanation
The source table trail record has an index and data for column number 129 but only 127 columns are defined in the Source Table Definition File or when the ASSUMETARGETDEFS parameter is used in the Replicat Parameter File the Target Table contains 127 columns.

This is generally caused by changes in the Source Table or Target Table(i.e. columns have been added or deleted and a new Source Definition File has not been created to reflect the Source Table structure to match the Trail Records that Replicat is trying to process.

To resolve this error, run DEFGEN on the Source System for the Table causing the Replicat abend, copy that Definition File to the Target system.  Add this SOURCEDEFS file to the Replicat Parameter file and restart the Replicat process.

Note:  This applies to all Open Systems platforms except z/OS(IBM mainframe)


2) Encryption and decrypttrail

Check if the trail source is encrypted and if it is, then add the decrypttail parameter to the Replicat to eliminate the error.
If the extract does not use encryption, and the replicat has decrypttrail enabled, replicat will read an un-encrypted number and translate it to a column index that does not conform to the proper index number. In that case remove the decrypttail parameter from the Replicat.
Note: DECRYPT/ENCRYPT parameters must be set before RMTTRAIL parameter for a correct behavior of encryption/decription



There are different issues reported via GGS ERROR 160, for those check the error description (e.g. "Bad column length" and follow the related KM note in the reference section

ALLSTARS_ORACLE

        GoldenGate Replicat Characterset Conversion: Error 160 Bad column length (Doc ID 1199561.1)

APPLIES TO:

Oracle GoldenGate - Version 10.4.0.12 and later
Information in this document applies to any platform.
SYMPTOMS

When replicaing from multi-byte source column to single byte target column, the replicat may abend with error:
GGS ERROR 160 Bad column length.

There are four known causes and solutions.

Example 1:


1. source: AL32UTF8
create table s1 (a number, b varchar2(1) CHAR);
insert into s1 values (2,unistr('\00c4'));
insert into s1 values (1,unistr('\2019'));
select a,dump(b,16) from s1;
A DUMP(B,16)
---------- ------------------------------
2 Typ=1 Len=2: c3,84
1 Typ=1 Len=3: e2,80,99

2. target: WE8ISO8859P1
create table r1 (a number, b varchar2(1) CHAR);

AMERICAN_AMERICA.AL32UTF8 is set in both extract and replicat.
Replicat abended with
"2010-08-03 22:27:12 GGS ERROR 160 Bad column length (2) specified
for column B in table GGUSER.S1, maximum allowable length is 1."

EXAMPLE 2:

Source and target have same characterset.  The problem column was added with a default value after table creation. Database version is 11.2.0.1 or 11.1.0.7.
e.g.,  alter table test_table add test_c4 char(10) default 'A' not null;

EXAMPLE 3:


Source columns is varchar2(1 byte). The extract record should be 1 byte like:
Column 2 (x0002), Len 5 (x0005)
0000 0001 64 | ....d

Instead, we see 2-byte value in an update trail record, which caused replicat abend:
Column 2 (x0002), Len 6 (x0006)
0000 0002 0164 | .....d





Example 4




2011-11-15 14:26:50  ERROR   OGG-01163  Bad column length (143) specified for
column ACCOUNT_PHONE in table RGOVINDA.AGP_MAIN_TICKET
, maximum allowable length is 25.

As a work-around alter the target column to VARCHAR2(200 CHAR) from VARCHAR2(25 CHAR)

CAUSE

Cause for example 1:
This is a limitation of current version of OGG when handling characterset conversion.  The replicat only check the maximum length in target, and does not know the characterset of the data in trail. Therefore, it abends before converting the multi-byte character to single-byte character.

Cause for example 2:
This is due to database bug 8769746. The bug causes wrong data type code to be returned through OCI call, although it returns right value through sqlplus.

For the same error, there may be other configuration causes, which should be ruled out first.

In this issue, the  source and target databases have the same characterset.

and

The problem column was added with a default value after table creation.

and

Database version is 11.2.0.1 or 11.1.0.7

To verify: Generate a sourcedefs file for both the source and target table in question:

sample:

Run defgen on source and target like below,

create a file named defgen.prm in OGG home/dirprm with following contents

defsfile ./dirdef/ogg_test.def, purge
userid <id> password <pw>
table STERPRD.URBN_ISD_DTL ;
table YFS_ORDER_HEADER.ALL_ADDRESSES_VERIFIED ;

2. $ ./defgen paramfile ./dirprm/defgen.prm



Compare the datatype definitions for the row in question in source and target. Here is an example of the difference that may be seen.

LOCK in table X.Y, maximum allowable length is 3.

On source the OCI call returns the following datatype to OGG

LOCK                        64     50      794  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2

On target the OCI call returns the following datatype to OGG

LOCK                       134      8      794  0  0 1 0      8      8      8 0 0 0 0 1    0 0 3

Thus the replicat fails since the datatype (64 vs 134) returned by the OCI calls do not match







Cause for example 3:
This was observed when supplemental log was not enabled.



Cause for example 4:

bug 13393339, where extract mis-parse the record that result in garbage column data.



SOLUTION

Example 1:

This is bug 9969804 and fixed in ogg version 11.2.1.

For earlier version (e.g., v11.1), following workarounds may be considered:
1. change semantics from CHAR to BYTE in both source and target.
Or
2. increase the target column length. as varchar2 is limited to 4000 bytes with either CHAR or BYTE semantics, the target column will have the capacity.

Example 2:
The database bug is fixed in 11.2.0.2 or 11.1.0.7 patch 23.  (please see Doc 8769746.8 for more detail).
Alternatively, a workaround is to recreate source and target table.

Example 3:
Enable the supplemental log.



Example 4:

bug 13393339 is fixed in 11.2.1.0.1 and backported to 11.1.1.1.4

REFERENCES

ALLSTARS_ORACLE

        ERROR OGG-01163 Bad column length (nnn) specified for column XXXXX in table XXXX.XXXX, maximum allowable length is nnnn (Doc ID 1319000.1)


APPLIES TO:

Oracle GoldenGate - Version 10.4.0.0 and later
Information in this document applies to any platform.
SYMPTOMS

ERROR   OGG-01163  Bad column length (nnn) specified for column XXXXX in table XXXX.XXXX, maximum allowable length is nnnn


CAUSE

- The definition file as provided to replicat (sourcedefs) is incorrect.

- Prior to 10.4.0.110.001 a bug cause this message to be displayed
after an alter table (on source and target) to add DEFAULT NOT NULL numeric column.


SOLUTION

- Regen the definition file using defgen on the source server and use this instead.

- Upgrade to at least 10.4.0.110.001.