ALLSTARS_ORACLE 发表于 2013-10-18 13:37:15

OGG-00665 OCI Error writing LOB (small) for column BODY (2), stmt INSERT INTO...


test用户的mail表的body字段是clob类型，然后报错
ERROR   OGG-00665  Oracle GoldenGate Delivery for Oracle, rep1.prm:  OCI Error writing LOB (small) for column BODY (2), stmt INSERT INTO "TEST"."MAIL"  (status = 24801-ORA-24801: illegal parameter value in OCI lob function)

Maclean Liu(刘相兵 发表于 2013-10-18 13:41:50

Oracle GoldenGate Replicat fails with "OGG-00665 OCI Error Writing LOB (small) For Column <column name>"

Oracle GoldenGate - Version 11.2.1.0.0 and later
Information in this document applies to any platform.
Symptoms

Replicat fails with following error

2012-06-12 13:37:22 ERROR OGG-00665 OCI Error writing LOB (small) for column ANSWER (6), stmt INSERT INTO "TLC"."LC$LOAN_QA"
(status = 24801-ORA-24801: illegal parameter value in OCI lob function), SQL.




Cause

1) This is due to incorrect NLS_LANG character set value used by the Replicat.

2) Bug 15923407
Solution

1) Check your replicat report file to look for following section.

Database Language and Character Set:
NLS_LANG = ".US7ASCII" <-- Denotes the value used by the replicat
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8" <-- Denotes the value of the target db setting

The NLS_LANG in the replicat parameter file should match the SOURCE database/ Extract NLS_LANG character set value.

Source extract was using AL32UTF8 correctly matching the databse NLS_LANG character set
Replicat was using the incorrect NLS_LANG character set causing the issue. This can be resolved by using
SETENV (NLS_LANG="<value>") in the replicat parameter

The position of SETENV parameter does matter. It should be BEFORE the USERID like below

replicat <replicat name>
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
userid <id> password <pw>

2) If replicat is correctly using the source NLS_LANG then this is due to bug 15923407 .

Fix is included in 11.2.1.0.6. Please check with OGG support team if the fix is needed on earlier versions of OGG.

Maclean Liu(刘相兵 发表于 2013-10-18 13:42:41

该问题 可能与 SETENV  的位置有关 应当放在 userid 之前

replicat <replicat name>
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
userid <id> password <pw>
页: [1]
查看完整版本: OGG-00665 OCI Error writing LOB (small) for column BODY (2), stmt INSERT INTO...