Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

0

积分

0

好友

2

主题
发表于 2014-6-3 21:33:44 | 查看: 5171| 回复: 4

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).
QQ截图20140603213130.png
发表于 2014-6-3 21:42:33
之前一直好好的,没问题,今天一查看就报这个错误

回复 显示全部楼层 道具 举报

发表于 2014-11-6 16:22:28
您好,我今天也遇到这个错了,不知道您后来是怎么处理的?我到现在还没找着原因

回复 显示全部楼层 道具 举报

发表于 2014-11-17 16:20:58
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

回复 显示全部楼层 道具 举报

发表于 2014-11-18 16:20:30
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.

回复 显示全部楼层 道具 举报

您需要登录后才可以回帖 登录 | 注册

扫码加入微信Oracle小密圈


QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

GMT+8, 2018-7-22 12:57 , Processed in 0.071133 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569