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.