REPERROR 1403

[Roshan]

Oracle DB 12.1.0.2
Solaris 11.4
OGG 18c

Hi Erman,

I am getting ORA-01403: no data found where the ACCOUNT_LINK_CODE_N =23247680 is present on both source and target but with different start and end times from source and target.

OCI Error ORA-01403: no data found, SQL <UPDATE "CBSOGG"."CB_AUTO_DEBIT" x SET x."VALUE_N" = :a4,x."AUTHORISATION_NUM_V" = :a12,x."AUTHORISATION_DATE_D" = :a13,x."CVV_NUMBER_V" = :a17,x."CARD_CODE_V" = :a20,x."REASON_CODE_FROM_BANK_V" = :a21,x."AUTHO_REQ_TYPE_V" = :a22,x."AUTHO_STATUS_V" = :a23,x."SENT_TO_BANK_V" = :a24,x."CONF_FROM_BANK_V" = :a25,x."AUTO_DEBIT_TYPE_V" = :a27,x."START_DATE_D" = :a28,x."END_DATE_D" = :a29,x."UPPER_LIMIT_N" = :a30,x."CARD#ACC_HOLDER_NAME_V" = :a32,x."BANK_CODE_V" = :a33,x."BRANCH_CODE_V" = :a34,x."CARD_TYPE_V" = :a35,x."EXPIRY_DATE_D" = :a36,x."REMARKS_V" = :a37,x."BANK_ACC_TYPE_V" = :a38,x."NEGOTIATING_BANK_CODE_V" = :a39,x."CIN_NUMBER_V" = :a40,x."REASON_CODE_FOR_BANK_V" = :a41 WHERE x."ACCOUNT_LINK_CODE_N" = :b0 AND x."CARD#ACCOUNT_NUMBER_V" = :b1 AND ROWNUM = 1>
Operation failed at seqno 1080 rba 148377014
Discarding record on action DISCARD on error 1403
Problem replicating CBS_CORE.CB_AUTO_DEBIT to CBSOGG.CB_AUTO_DEBIT.
Record not found
Mapping problem with unified update record (target format) SCN:41.0.144565...
*
ACCOUNT_LINK_CODE_N = 23247680
000000: 32 33 32 34 37 36 38 30                         |23247680        |

AUTO_DEBIT_TYPE_V = D
000000: 44                                              |D               |

START_DATE_D = 2019-11-21 00:00:00.000000000 Indian/Mauritius
000000: 32 30 31 39 2d 31 31 2d 32 31 20 30 30 3a 30 30 |2019-11-21 00:00|
000010: 3a 30 30 2e 30 30 30 30 30 30 30 30 30 20 49 6e |:00.000000000 In|
000020: 64 69 61 6e 2f 4d 61 75 72 69 74 69 75 73       |dian/Mauritius  |


Given that the initial load was performed correctly, could you please advise if implementing the below would be a good idea

https://www.vitalsofttech.com/troubleshoot-oracle-goldengate-using-exception-handler/

In this way we can only correct the record which is causing issue, in this case ACCOUNT_LINK_CODE_N = 23247680.

Please advise.

Roshan

[ErmanArslansOracleBlog]

1)Do you see that record when you query using the following conditions (by adding the required bind variables)

x."ACCOUNT_LINK_CODE_N" = :b0 AND x."CARD#ACCOUNT_NUMBER_V" = :b1

2)check if supplemental logging is enabled for your table.

GGSCI> info trandata your_schema.your_table_name

[Roshan]

On source yes

GGSCI (dware1 as extractogg@cbs2) 4> info trandata cbs_core.CB_AUTO_DEBIT

Logging of supplemental redo log data is enabled for table CBS_CORE.CB_AUTO_DEBIT.

Columns supplementally logged for table CBS_CORE.CB_AUTO_DEBIT: "ACCOUNT_LINK_CODE_N", "AUTHORISATION_DATE_D", "AUTHORISATION_NUM_V", "AUTHO_REQ_TYPE_V", "AUTHO_STATUS_V", "AUTO_DEBIT_TYPE_V", "BANK_ACC_TYPE_V", "BANK_CODE_V", "BRANCH_CODE_V", "CARD#ACCOUNT_NUMBER_V", "CARD#ACC_HOLDER_NAME_V", "CARD_CODE_V", "CARD_TYPE_V", "CIN_NUMBER_V", "CONF_FROM_BANK_V", "CVV_NUMBER_V", "END_DATE_D", "EXPIRY_DATE_D", "NEGOTIATING_BANK_CODE_V", "REASON_CODE_FOR_BANK_V", "REASON_CODE_FROM_BANK_V", "REMARKS_V", "SENT_TO_BANK_V", "START_DATE_D", "UPPER_LIMIT_N", "VALUE_N".

Prepared CSN for table CBS_CORE.CB_AUTO_DEBIT: 265466905795

[Roshan]

1) No the record does not exist on target. Why is the rowcount same?
select * from CB_AUTO_DEBIT where ACCOUNT_LINK_CODE_N = 23247680 and CARD#ACCOUNT_NUMBER_V = 44631496706;

Why is the rowcount same for that table on both source and target?
select count(*) from cb_auto_debit;
68835
select count(*) from cb_auto_debit@coreprd;
68835

For
CARD#ACCOUNT_NUMBER_V =44631496704 record is missing on source.

[ErmanArslansOracleBlog]

So the error is an expected one then..

Yes, you can add that record manually to the target as a workaround.
Also, you have the opportunity to resync that table(only that table) -> How to Resync a Single Table with Minimum Impact to Other Tables' Replication? (Doc ID 966211.1)

[Roshan]

OK thanks I will try it and let you know.

But it is inconvenient to reload the table each time there is a ORA-1403 error especially for big tables.

This is why I wish to implement the exceptions table and MACRO.

[ErmanArslansOracleBlog]

Okay.. Do that way, and let me know the outcome Roshan..