11gR2GoldenGateOracle

OGG replication failing with "ORA-12899: value too large for column“

Posted Anand

Recently while working on Oracle GoldenGate, when i started the replicat it failed with “ORA-12899: value too large for column”

2011-09-25 22:19:52  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rep3.prm:  SQL error 12899 mapping anand.tab1 to andy.tab2 OCI Error ORA-12899: value too large for column "ANDY"."TAB2"."CITY" (actual: 68, maximum: 50) (status = 12899), SQL <UPDATE "ANDY"."TAB2" SET .......
2011-09-25 22:19:52  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rep3.prm:  Repositioning to rba 21289165 in seqno 0.
2011-09-25 22:19:52  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rep3.prm:  Error mapping from anand.tab1 to andy.tab2.
2011-09-25 22:19:52  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep3.prm:  PROCESS ABENDING.

To resolve the issue set the database character set inside the parameter file suing the SETENV command.

 select * from v$nls_parameters where parameter like '%NLS_CHARACTERSET%';
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_CHARACTERSET                                                 AL32UTF8
1 row selected.

In the extract/replicat parameter file –

setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
USERID [email protected]_gg PASSWORD "AACAAAAAAAAAAAIAIJCEMFLRG", ENCRYPTKEY default

2 thoughts on “OGG replication failing with "ORA-12899: value too large for column“”

    1. Its the script provided by Tanel Poder. Thanks to him. Seems like his link isn’t working so pasting the script —
      col sw_event head EVENT for a40 truncate
      col sw_p1transl head P1TRANSL for a42
      col sw_sid head SID for 999999
      col sw_p1 head P1 for a18 justify right word_wrap
      col sw_p2 head P2 for a18 justify right word_wrap
      col sw_p3 head P3 for a18 justify right word_wrap
      select
      sid sw_sid,
      CASE WHEN state != ‘WAITING’ THEN ‘WORKING’
      ELSE ‘WAITING’
      END AS state,
      CASE WHEN state != ‘WAITING’ THEN ‘On CPU / runqueue’
      ELSE event
      END AS sw_event,
      seq#,
      seconds_in_wait sec_in_wait,
      NVL2(p1text,p1text||’= ‘,null)||CASE WHEN P1 < 536870912 THEN to_char(P1) ELSE '0x'||rawtohex(P1RAW) END SW_P1,
      NVL2(p2text,p2text||'= ',null)||CASE WHEN P2 < 536870912 THEN to_char(P2) ELSE '0x'||rawtohex(P2RAW) END SW_P2,
      NVL2(p3text,p3text||'= ',null)||CASE WHEN P3 < 536870912 THEN to_char(P3) ELSE '0x'||rawtohex(P3RAW) END SW_P3,
      CASE
      WHEN event like 'cursor:%' THEN
      '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))
      WHEN event like 'enq%' AND state = 'WAITING' THEN
      '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||
      chr(bitand(p1, -16777216)/16777215)||
      chr(bitand(p1,16711680)/65535)||
      ' mode '||bitand(p1, power(2,14)-1)
      WHEN event like 'latch%' AND state = 'WAITING' THEN
      '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||(
      select name||'[par'
      from v$latch_parent
      where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))
      union all
      select name||'[c'||child#||']'
      from v$latch_children
      where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))
      )
      WHEN event like 'library cache pin' THEN
      '0x'||RAWTOHEX(p1raw)
      ELSE NULL END AS sw_p1transl
      FROM
      v$session_wait
      WHERE
      sid IN (&1)
      ORDER BY
      state,
      sw_event,
      p1,
      p2,
      p3
      /

      Reply

Leave a Reply