WARNING OGG-00869 OCI Error ORA-01843: 无效的月份 (status = 1843)

salis

2014-06-03 17:11:17  WARNING OGG-00869  OCI Error ORA-01843: 无效的月份 (status = 1843). INSERT INTO "BSRUN"."ZY_BRRY" ("ZYH","ZYHM","BAHM","MZHM","BRXZ","GFZH","BRXM"
,"BRXB","CSNY","SFZH","HYZK","ZYDM","SFDM","JGDM","MZDM","GJDM","DWBH","GZDW","DWDH","DWYB","HKDZ","HKYB","LXRM","LXGX","LXDZ","LXDH","PZHM","SBHM","DBRM","DBGX","ZZTX
","DBBZ","BRKS","BRBQ","BRCH","DJRQ","RYRQ","CYRQ","CYPB","CYFS","CZGH","RYQK","BRQK","HLJB","YSDM","BRXX","HZKS","JCKS","MZYS","ZYYS","ZSYS","ZZYS","QZRQ","KSRQ","JSR
Q","JSCS","JZRQ","HZRQ","XGPB","BAPB","BRGL","BRKL","BZ","BRQM","JTDH","YBKH","JZKH","SZYS","ZLXZ","SPJE","SJZY","DJBZ","DJID","DJJE","YSJS","ZKZT","BRID","YBHM","YBXZ
","ZKKZBZ","YGYS","FAMILYSYSNO","RECCODE","NHJSLX","ZJJE","ZFJE","JYRQ","ZHZF","BOOKNO","YWLSH","JSSH","SHSJ","SHGH","TOTALCOSTS","TCJE") VALUES (:a0,:a1,:a2,:a3,:a4,:
a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a17,:a18,:a19,:a20,:a21,:a22,:a23,:a24,:a25,:a26,:a27,:a28,:a29,:a30,:a31,:a32,:a33,:a34,:a35,:a36,:a37,:a38,:a3
9,:a40,:a41,:a42,:a43,:a44,:a45,:a46,:a47,:a48,:a49,:a50,:a51,:a52,:a53,:a54,:a55,:a56,:a57,:a58,:a59,:a60,:a61,:a62,:a63,:a64,:a65,:a66,:a67,:a68,:a69,:a70,:a71,:a72,
:a73,:a74,:a75,:a76,:a77,:a78,:a79,:a80,:a81,:a82,:a83,:a84,:a85,:a86,:a87,:a88,:a89,:a90,:a91,:a92,:a93,:a94).

salis

之前一直好好的，没问题，今天一查看就报这个错误

seven

您好，我今天也遇到这个错了，不知道您后来是怎么处理的？我到现在还没找着原因

thomas_lhb

ORA-01843:
not a valid month
Cause:
A date specified an valid month.
Valid month are: January-December, for format code MONTH, and Jan-Dec, for format code MON.
Action:
Enter a valid month value in the correct format.

检查下源端和目标端的这两个参数是否一样呢：
NLS_DATE_LANGUAGE 和 NLS_DATE_FORMAT

SQL> show parameters NLS_DATE_LANGUAGE
NAME                           TYPE        VALUE
--------------------------- ----------- -----------------------------
nls_date_language             string      

SQL> show parameters NLS_DATE_FORMAT
NAME                           TYPE        VALUE
--------------------------- ----------- -----------------------------
nls_date_format               string

yhd_my

Replication Fails with "Error ORA-01843: not a valid month" (Doc ID 1299383.1)

OCI Error ORA-01843: not a valid month (status = 1843), SQL <INSERT INTO "MAC"."AMX_SETTLE_FILE_NOTIFICATION" ("PROC_BATCH","BRIC","NUMBER_OF_RECORDS","NUMBER_OF_CREDITS","NUMBER_OF_DEBITS","NUMBER_OF_REJECTS","FILE_REF_NUMBER","FILE_CREATION_DATE","FILE_RECEIP>
Operation failed at seqno 114 rba 68317228
Discarding record on action DISCARD on error 1843
Problem replicating MAC.AMX_SETTLE_FILE_NOTIFICATION to MAC.AMX_SETTLE_FILE_NOTIFICATION
Mapping problem with insert record (target format)...
*
PROC_BATCH = 564096
BRIC = NULL
NUMBER_OF_RECORDS = 43
NUMBER_OF_CREDITS = 0
NUMBER_OF_DEBITS = 18
NUMBER_OF_REJECTS = 0
FILE_REF_NUMBER = 1138AE73
FILE_CREATION_DATE = 0000-00-00 00:00:00
FILE_RECEIPT_DATE = 0000-00-00 00:00:00
HASH_TOTAL_AMOUNT = 88001
HASH_DEBIT_AMOUNT = 88001
HASH_CREDIT_AMOUNT = 0
ID = 47278
FILE_STATUS = Accepted with Errors


For the two Date fields "FILE_CREATION_DATE" and "FILE_RECEIPT_DATE",  the value is invalid as "0000-00-00 00:00:00"

Cause

This is because Oracle does NOT check the content of DATE values when data are directly loaded from a client to the server in some cases. So on the source instances there are invalid values. When GG tries to insert them on the target instances through a normal "Insert" command, Oracle checks these values and returns the error.

Detailed explanations can be found in "Date Field Contains All Zeros (Doc ID 312973.1)"

Solution

OGG has a function to convert the invalid value to something acceptable to Oracle Database.


1.Add a convert function in the colmap for the Date column. Here is an example for the replicat param file.


REPLICAT rep02
sourcedefs dirdef/eora02.def
USERID gg11, PASSWORD gg11
map gg11.test_date, target sh.test_date,
colmap (usedefaults,
FILE_CREATION_DATE = @IF ( @STRCMP ( FILE_CREATION_DATE, "0000-00-00:00:00:00") = 0, "", FILE_CREATION_DATE)
FILE_RECEIPT_DATE = @IF ( @STRCMP ( FILE_RECEIPT_DATE, "0000-00-00:00:00:00") = 0, "", FILE_RECEIPT_DATE)
);

-- Sometimes the string compare does not work because of other characters in the column.
-- Use this alternate colmap test instead
colmap (usedefaults,
FILE_CREATION_DATE = @IF (( @STRFIND( FILE_CREATION_DATE, "0000-00-00",1) = 1, "", FILE_CREATION_DATE)
FILE_RECEIPT_DATE  = @IF (( @STRFIND( FILE_RECEIPT_DATE,  "0000-00-00",1) = 1, "", FILE_RECEIPT_DATE)
);

2.Save the changes and restart replicat.


References
NOTE:312973.1 - Date Field Contains All Zeros


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.