• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
    Posts: 78
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
     
    reply
      Bookmark Topic Watch Topic
    • New Topic