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)
1. Red Hat Linux 5 servers with Oracle 11 G, one primary and another DR.
2. with Golden Gate (Version 18.104.22.168.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"
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.
Joined: Jul 03, 2007
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.