4 Replies Latest reply on Aug 7, 2019 4:49 AM by K.Gan

    replicat abends despite record present

    Robeen

      Oracle DB 12.2 (target)

      Oracle DB 12.1 (source)

       

      Hello Team,

       

      kindly note that replicat abends with error from discard file

       

      [oracle@RH-DATWHSE-DEV dirrpt]$ cat REPLOGGT5.dsc

      Oracle GoldenGate Delivery for Oracle process started, group REPLOGGT discard fi                                                                                                             le opened: 2019-08-01 14:48:48.144832

      Current time: 2019-08-01 14:48:58

      Discarded record from action ABEND on error 1403

       

       

      OCI Error ORA-01403: no data found, SQL <UPDATE "CBSOGG"."CB_SUB_INVOICE" x SET                                                                                                              x."TRANS_NUM_V" = :a24,x."TRANS_DATE_D" = :a25,x."SERVICE_CODE_V" = :a26,x."BILL                                                                                                             _CYCL_FULL_CODE_N" = :a27,x."BILLING_REGION_V" = :a28,x."CHRG_FROM_DATE_D" = :a2                                                                                                             9,x."CHRG_TILL_DATE_D" = :a30,x."INVOICE_AMT_N" = :a31,x."TAX_AMT_N" = :a32,x."O                                                                                                             VER_ALL_DISC_N" = :a33,x."INV_AMT_CLEARED_N" = :a34,x."ADJUSTMENT_AMT_N" = :a35,                                                                                                             x."TEMP_CLR_AMT_N" = :a36,x."DISPUTE_AMT_N" = :a37,x."EARLY_PAY_DISC_OPTN_N" = :                                                                                                             a38,x."EARLY_PAYMENT_DISC_AMT_N" = :a39,x."INV_CLEARED_FLG_V" = :a40,x."PRINT_CA                                                                                                             LL_DTLS_FLG_V" = :a41,x."ACCOUNT_CODE_N" = :a42,x."ITEMISED_TYPE_N" = :a43,x."CU                                                                                                             RRENCY_CODE_V" = :a44,x."GL_CODE_N" = :a45,x."PACKAGE_CODE_V" = :a46,x."SERV_ACC                                                                                                             _LINK_CODE_N" = :a47 WHERE x."TRANS_NUM_V" = :b0 AND x."TRANS_DATE_D" = :b1 AND                                                                                                              x."SERVICE_CODE_V" = :b2 AND x."BILL_CYCL_FULL_CODE_N" = :b3 AND x."BILLING_REGI                                                                                                             ON_V" = :b4 AND x."CHRG_FROM_DATE_D" = :b5 AND x."CHRG_TILL_DATE_D" = :b6 AND x.                                                                                                             "INVOICE_AMT_N" = :b7 AND x."TAX_AMT_N" = :b8 AND x."OVER_ALL_

      Aborting transaction on /u01/app/oracle/OGG/dirdat/cd beginning at seqno 120 rba                                                                                                              97,300,140

                               error at seqno 120 rba 97304529

      Problem replicating CBS_CORE.CB_SUB_INVOICE to CBSOGG.CB_SUB_INVOICE.

      Record not found

      Mapping problem with unified PK update record (target format) SCN:303.15.48975..                                                                                                             .

      *

      TRANS_NUM_V = FINV01900898-1905A

      000000: 46 49 4e 56 30 31 39 30 30 38 39 38 2d 31 39 30 |FINV01900898-190|

      000010: 35 41                                           |5A              |

       

       

      TRANS_DATE_D = 2019-05-31 23:59:59.000000000 +04:00

      000000: 32 30 31 39 2d 30 35 2d 33 31 20 32 33 3a 35 39 |2019-05-31 23:59|

      000010: 3a 35 39 2e 30 30 30 30 30 30 30 30 30 20 2b 30 |:59.000000000 +0|

      000020: 34 3a 30 30                                     |4:00            |

       

       

      SERVICE_CODE_V = FXL

      000000: 46 58 4c                                        |FXL             |

       

       

      BILL_CYCL_FULL_CODE_N = 1010000012019050

      000000: 31 30 31 30 30 30 30 30 31 32 30 31 39 30 35 30 |1010000012019050|

       

       

      BILLING_REGION_V = L001

      000000: 4c 30 30 31                                     |L001            |

       

      I have seen that record is found on both source and target for table CB_SUB_INVOICE. But still why does it say 'record not found' ?

       

      Source:

      Target:

       

      Regards,

       

      Joe

        • 1. Re: replicat abends despite record present
          ORASCN

          Hi Joe,

           

          If you pretty sure that the record is existing in both Source and Target, then I would request you to open an SR with Oracle as this might be a bug.

           

          Regards,

          Veera

          • 2. Re: replicat abends despite record present
            K.Gan

            The where clause is what the key that was used by replicat

            WHERE x."TRANS_NUM_V" = :b0 AND x."TRANS_DATE_D" = :b1 AND                                                                                                              x."SERVICE_CODE_V" = :b2 AND x."BILL_CYCL_FULL_CODE_N" = :b3 AND x."BILLING_REGI                                                                                                             ON_V" = :b4 AND x."CHRG_FROM_DATE_D" = :b5 AND x."CHRG_TILL_DATE_D" = :b6 AND x.                                                                                                             "INVOICE_AMT_N" = :b7 AND x."TAX_AMT_N" = :b8 AND x."OVER_ALL_...etc....

            Looks like the table has no keys so replicat is using every column. Check the discard file for the complete where clause and see that all the columns matched.

            Cheers

            Kee Gan

            • 3. Re: replicat abends despite record present
              Robeen

              Hi Kay,

               

              the complete select statement is shown below:

               

              2019-08-01 14:48:58  WARNING OGG-01154  SQL error 1403 mapping CBS_CORE.CB_SUB_INVOICE to CBSOGG.CB_SUB_INVOICE OCI Error ORA-01403: no data found, SQL <UPDATE "CBSOGG"."CB_SUB_INVOICE" x SET x."TRANS_NUM_V" = :a24,x."TRANS_DATE_D" = :a25,x."SERVICE_CODE_V" = :a26,x."BILL_CYCL_FULL_CODE_N" = :a27,x."BILLING_REGION_V" = :a28,x."CHRG_FROM_DATE_D" = :a29,x."CHRG_TILL_DATE_D" = :a30,x."INVOICE_AMT_N" = :a31,x."TAX_AMT_N" = :a32,x."OVER_ALL_DISC_N" = :a33,x."INV_AMT_CLEARED_N" = :a34,x."ADJUSTMENT_AMT_N" = :a35,x."TEMP_CLR_AMT_N" = :a36,x."DISPUTE_AMT_N" = :a37,x."EARLY_PAY_DISC_OPTN_N" = :a38,x."EARLY_PAYMENT_DISC_AMT_N" = :a39,x."INV_CLEARED_FLG_V" = :a40,x."PRINT_CALL_DTLS_FLG_V" = :a41,x."ACCOUNT_CODE_N" = :a42,x."ITEMISED_TYPE_N" = :a43,x."CURRENCY_CODE_V" = :a44,x."GL_CODE_N" = :a45,x."PACKAGE_CODE_V" = :a46,x."SERV_ACC_LINK_CODE_N" = :a47 WHERE x."TRANS_NUM_V" = :b0 AND x."TRANS_DATE_D" = :b1 AND x."SERVICE_CODE_V" = :b2 AND x."BILL_CYCL_FULL_CODE_N" = :b3 AND x."BILLING_REGION_V" = :b4 AND x."CHRG_FROM_DATE_D" = :b5 AND x."CHRG_TILL_DATE_D" = :b6 AND x."INVOICE_AMT_N" = :b7 AND x."TAX_AMT_N" = :b8 AND x."OVER_ALL_DISC_N" = :b9 AND x."INV_AMT_CLEARED_N" = :b10 AND x."ADJUSTMENT_AMT_N" = :b11 AND x."TEMP_CLR_AMT_N" = :b12 AND x."DISPUTE_AMT_N" = :b13 AND x."EARLY_PAY_DISC_OPTN_N" = :b14 AND x."EARLY_PAYMENT_DISC_AMT_N" = :b15 AND x."INV_CLEARED_FLG_V" = :b16 AND x."PRINT_CALL_DTLS_FLG_V" = :b17 AND x."ACCOUNT_CODE_N" = :b18 AND x."ITEMISED_TYPE_N" is NULL AND x."CURRENCY_CODE_V" = :b20 AND x."GL_CODE_N" = :b21 AND x."PACKAGE_CODE_V" = :b22 AND x."SERV_ACC_LINK_CODE_N" = :b23 AND ROWNUM = 1>.

               

              Regards,

               

              Joe

              • 4. Re: replicat abends despite record present
                K.Gan

                Ok, do all the columns after the where clause matched between source and target?

                WHERE x."TRANS_NUM_V" = :b0 AND x."TRANS_DATE_D" = :b1 AND x."SERVICE_CODE_V" = :b2 AND x."BILL_CYCL_FULL_CODE_N" = :b3 AND x."BILLING_REGION_V" = :b4 AND x."CHRG_FROM_DATE_D" = :b5 AND x."CHRG_TILL_DATE_D" = :b6 AND x."INVOICE_AMT_N" = :b7 AND x."TAX_AMT_N" = :b8 AND x."OVER_ALL_DISC_N" = :b9 AND x."INV_AMT_CLEARED_N" = :b10 AND x."ADJUSTMENT_AMT_N" = :b11 AND x."TEMP_CLR_AMT_N" = :b12 AND x."DISPUTE_AMT_N" = :b13 AND x."EARLY_PAY_DISC_OPTN_N" = :b14 AND x."EARLY_PAYMENT_DISC_AMT_N" = :b15 AND x."INV_CLEARED_FLG_V" = :b16 AND x."PRINT_CALL_DTLS_FLG_V" = :b17 AND x."ACCOUNT_CODE_N" = :b18 AND x."ITEMISED_TYPE_N" is NULL AND x."CURRENCY_CODE_V" = :b20 AND x."GL_CODE_N" = :b21 AND x."PACKAGE_CODE_V" = :b22 AND x."SERV_ACC_LINK_CODE_N" = :b23 AND ROWNUM = 1>.

                 

                That is a lot of columns. If you think there is a logical set of keys then you can use keycols in the MAP statement.

                Cheers

                Kee

                1 person found this helpful