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“”
Hi,
I’m looking for sw.sql script used here :
“local write wait” event
December 16th, 2010Posted in Oracle
Thank for help.
Guy WOLF.
[email protected]
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
/