2017-10-25 11:25:35 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, repyylis.prm: REPLICAT REPYYLIS started.
2017-10-25 11:25:35 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, repyylis.prm:
OCI Error ORA-01847: day of month must be between 1 and last day of month (status = 1847). INSERT INTO "TXYY_LIS"."L_PATIENTINFO"("DOCTADVISENO","REQUESTTIME","REQUESTER","EXECUTETIME","EXECUTOR","RECEIVETIME","RECEIVER","CANCELTIME","CANCELER","STAYHOSPITALMODE","PATIENTID","INFANTFLAG","SECTION","DEPART_BED","PATIENTNAME","SEX","BIRTHDAY","NL","NLDW","CYCLE","DIAGNOSTIC","SAMPLETYPE","TOPONYMY","EXAMINAIMCODE","EXAMINAIM","REQUESTMODE","NOTES","FEE","FEESTATUS","SAMPLENO","RESULTCOMMENT","RESULTSTATUS","CHECKOPERATOR","CHECKEROPINION","CHECKTIME","LABDEPARTMENT","CISBORDER","EDITSTATUS","BRLB","BRXZ","PRINTTIME","PRINTFLAG","BMXH","FPHM","COMPUTERNAME","SFBZ","CHECKER2","SJDW","YBID","REQUESTID","CHECKUPNO","FZCS","DYCS","MFPB","AUTOPRINTED","BGSJ","TXSJ","GZJS","ADRESS","SCBZ","YEWYH","YEPB","UPLOAD","TX_JLSJ","SFZH","WSWLH") 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,:a39,: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).
2017-10-25 11:25:35 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, repyylis.prm: Aborted grouped transaction on 'TXYY_LIS.L_PATIENTINFO', Database error 1847 (OCI Error ORA-01847: day of month must be between 1 and last day of month (status = 1847). INSERT INTO "TXYY_LIS"."L_PATIENTINFO" ("DOCTADVISENO","REQUESTTIME","REQUESTER","EXECUTETIME","EXECUTOR","RECEIVETIME","RECEIVER","CANCELTIME","CANCELER","STAYHOSPITALMODE","PATIENTID","INFANTFLAG","SECTION","DEPART_BED","PATIENTNAME","SEX","BIRTHDAY","NL","NLDW","CYCLE","DIAGNOSTIC","SAMPLETYPE","TOPONYMY","EXAMINAIMCODE","EXAMINAIM","REQUESTMODE","NOTES","FEE","FEESTATUS","SAMPLENO","RESULTCOMMENT","RESULTSTATUS","CHECKOPERATOR","CHECKEROPINION","CHECKTIME","LABDEPARTMENT","CISBORDER","EDITSTATUS","BRLB","BRXZ","PRINTTIME","PRINTFLAG","BMXH","FPHM","COMPUTERNAME","SFBZ","CHECKER2","SJDW","YBID","REQUESTID","CHECKUPNO","FZCS","DYCS","MFPB","AUTOPRINTED","BGSJ","TXSJ","GZJS","ADRESS","SCBZ","YEWYH","YEPB","UPLOAD","TX_JLSJ","SFZH","WSWLH") 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,:a39,: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)).
2017-10-25 11:25:35 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repyylis.prm:
Repositioning to rba 54903443 in seqno 594.
2017-10-25 11:25:35 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repyylis.prm:
SQL error 1847 mapping LIS.L_PATIENTINFO to TXYY_LIS.L_PATIENTINFO OCI Error ORA-01847: day of month must be between 1 and last day of month (status = 1847). INSERT INTO
"TXYY_LIS"."L_PATIENTINFO"("DOCTADVISENO","REQUESTTIME","REQUESTER","EXECUTETIME","EXECUTOR","RECEIVETIME","RECEIVER","CANCELTIME","CANCELER","STAYHOSPITALMODE","PATIENTID","INFANTFLAG","SECTION","DEPART_BED","PATIENTNAME","SEX","BIRTHDAY","NL","NLDW","CYCLE","DIAGNOSTIC","SAMPLETYPE","TOPONYMY","EXAMINAIMCODE","EXAMINAIM","REQUESTMODE","NOTES","FEE","FEESTATUS","SAMPLENO","RESULTCOMMENT","RESULTSTATUS","CHECKOPERATOR","CHECKEROPINION","CHECKTIME","LABDEPARTMENT","CISBORDER","EDITSTATUS","BRLB","BRXZ","PRINTTIME","PRINTFLAG","BMXH","FPHM","COMPUTERNAME","SFBZ","CHECKER2","SJDW","YBID","REQUESTID","CHECKUPNO","FZCS","DYCS","MFPB","AUTOPRINTED","BGSJ","TXSJ","GZJS","ADRESS","SCBZ","YEWYH","YEPB","UPLOAD","TX_JLSJ","SFZH","WSWLH") 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,:a39,: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).
2017-10-25 11:25:35 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repyylis.prm: Repositioning to rba 54903443 in seqno 594.
2017-10-25 11:25:35 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repyylis.prm: Error mapping from LIS.L_PATIENTINFO to TXYY_LIS.L_PATIENTINFO.
2017-10-25 11:25:35 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repyylis.prm: PROCESS ABENDING.
Oracle GoldenGate - Version 11.2.1.0.20 and later Information in this document applies to any platform.
GOAL
Randomly experiencing an update occurring in source where one of the dates will get captured by GG as 0001-01-00 00:00:00. When the replicat attempts to insert into target, it of course abends due to the month being captured as 00. A select on source displays this: EVENT_END_DT_TM -------------------- 00-JAN-0001 00:00:00
We are trying to determine what is updating the source row to have this date but would like to be able to add a reperr to the replicat for the table so we can either set the field to match the date from another field or make it a valid date.
O
CI Error ORA-01847: day of month must be between 1 and last day of month (status = 1847). INSERT INTO "PCLADB"."CLINICAL_EVENT" ("CLINICAL_EVENT_ID","ENCNTR_ID","PERSON_ID","EVENT_START_DT_TM","EVENT_ID","VALID_UNTIL_DT_TM","EVENT_TITLE_TEXT","VIEW_LEVEL","ORDER_ID","CATALOG_CD","ACCESSION_NBR","REFERENCE_NBR","PARENT_EVENT_ID","EVENT_RELTN_CD","VALID_FROM_DT_TM","EVENT_CLASS_CD","EVENT_CD","EVENT_TAG","EVENT_END_DT_TM","RESULT_VAL","RESULT_UNITS_CD","TASK_ASSAY_CD","RECORD_STATUS_CD","RESULT_STATUS_CD","PUBLISH_FLAG","NORMALCY_CD","RESOURCE_CD","COLLATING_SEQ","VERIFIED_DT_TM","PERFORMED_DT_TM","PERFORMED_PRSNL_ID","UPDT_DT_TM","NORMAL_LOW","NORMAL_HIGH","CLINSIG_UPDT_DT_TM","ORDER_ACTION_SEQUENCE","ENTRY_MODE_CD","EVENT_END_TZ","VERIFIED_TZ","LOC_NURSE_UNIT_CD","LOC_FACILITY_CD","OP_LOC_BUILDING_CD","OP_LOC_NURSE_UNIT_CD","PURGE_FLAG","IMMUNIZATION_IND","RES_ONLY_IND","BACKFILL_IND") 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
Aborting transaction on /home/oracle/ggs/dirdat/e1 beginning at seqno 27002 rba 120234022
SOLUTION
The data itself is bad and no issues with OGG.
You can try to convert the bad data, below is a sample, you may configure it to your situation accordingly:
Oracle GoldenGate - Version 12.1.2.1.5 and later
Information in this document applies to any platform.
SYMPTOMS
On : 12.1.2.1.5 version, 1 - Oracle DB backend
Integrated Replicat ABENDs with ERROR OGG-00665 OCI Error Flushing database inbound server, 'OGG$R_IKFL2' (status = 26815-ORA-26815: Error from Apply Network Receiver for Apply "OGG$R_IKFL2" and Capture "replicat". ORA-01847: day of month must be between 1 and last day of month) even after specifying REPERROR (1847, IGNORE)
CAUSE
This issue is caused due to invalid date and REPERROR did not work due to Bug 21820934 - APPLY SERVER DOESN'T HANDLE DATA CONVERSIONS ERRORS LIKE 'ORA-01843'.
According to Bug 21820934 - APPLY SERVER DOESN'T HANDLE DATA CONVERSIONS ERRORS LIKE 'ORA-01843'. The apply network receiver is not able to handle the conversion error.
SOLUTION
Classic replicat will ignore invalid dates. But integrated replicat does not ignore.
Apply fix for bug 21820934 - APPLY SERVER DOESN'T HANDLE DATA CONVERSIONS ERRORS LIKE 'ORA-01843' and restart the replicat