wood burning stoves 2.0*
The moose likes Oracle/OAS and the fly likes ERROR   OGG-01163  Bad column length, and CHAR data type Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "ERROR   OGG-01163  Bad column length, and CHAR data type" Watch "ERROR   OGG-01163  Bad column length, and CHAR data type" New topic
Author

ERROR OGG-01163 Bad column length, and CHAR data type

Jeevan Sunkersett
Ranch Hand

Joined: Jul 03, 2007
Posts: 77
Hi,

We are experiencing an odd issue in production; related to Golden gate replication. Request feedback to debug and find the root cuase.
(please excuse the formatting as this is the best I could achieve and make it more readable)

Production environment:
  • 1. Red Hat Linux 5 servers with Oracle 11 G, one primary and another DR.
  • 2. with Golden Gate (Version 11.1.1.0.0 Build 078) for real time replication between the two.



  • In the primary database,
    We have a table "COUNTER" in schema 'DPS', in which column 'PHOTOCAPTURED' has data type CHAR(1)
    from our change request history, we know this column was added using the ALTER table command




    This ALTER table command was perfectly replicated by GG (as determined by doing a DESC on DR database)

    But when data is added to this column GG gives error, when replicating;

    The REPLICAT job is in ABENDED state; error

    ERROR OGG-01163 Bad column length (5) specified for column PHOTOCAPTURED in table DPS.COUNTER, maximum allowable length is 3."




    This error occurs only when data is added to the above mentioned COUNTER table.

    After a bit of exploration it was found that data in DR database
    table 'COUNTER' column PHOTOCAPTURED (with CHAR data type) was having 2 extra non-printable characters.

    The same non-printable characters were missing in the primary database.

    To resolve, we executed a ALTER table on primary database and increased CHAR(1) to CHAR(3) and inserted data in COUNTER table again.

    The GG error repeated, but this time on primary database;

    ERROR OGG-01163 Bad column length (7) specified for column PHOTOCAPTURED in table DPS.COUNTER, maximum allowable length is 5"



    Further;

    In the primary database,
    We have other tables in the same (and other schemas) which have column data type as CHAR(1)

    GG replication works fine for these columns and we have not faced the above issue.

    (only difference seems to be:: the columns were added using the CREATE table command and not ALTER table)


    for now, the only work around seems to be
    whenever the error occurs,
    manually execute ALTER table and increase the column size on the affected database.

    The DPS.COUNTER table is a very infrequently updated table so the work around suffices.

    Any clues to debug further and find the root cuase, will be appreciated.

    thank you
    ~g1

    Jeevan Sunkersett
    Ranch Hand

    Joined: Jul 03, 2007
    Posts: 77
    Hi,

    for the benefit of all,

    the root cause was identified as bug in Oracle

    Bug 8769746 - Describe returns wrong type for column added by ALTER TABLE [ID 8769746.8] . You can see the details in the support.oracle.com

    To avoid this thing in future never add a new column to an existing table with CHAR datatype with a default value.

    The issue is fixed in :
  • 11.2.0.2 (Server Patch Set)
  • 11.1.0.7 Patch 23 on Windows Platforms


  • thank you.
    ~g1
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: ERROR OGG-01163 Bad column length, and CHAR data type
     
    Similar Threads
    stuck up in the problem
    problem creating a foreignkey relation in compositekeys
    something interesting
    Hibernate Mapping
    Regarding Foreign Key Restricts when Alter data type in sql table