Resolving Oracle GoldenGate SQL Error 1403: No Data Found – Root Cause & Best Practices

Oracle GoldenGate (OGG) is a powerful tool for real-time data replication across heterogeneous environments. However, replication conflicts can arise due to differences in source and target database operations. One common issue encountered is SQL error 1403 – No Data Found, which occurs when GoldenGate tries to update or delete a record that no longer exists in the target database. This blog post analyzes a real-world scenario, its root cause, and recommended resolutions.

During an Oracle GoldenGate replication process, an update operation failed on the target database with the following error:

OGG-01154  Oracle GoldenGate Delivery for Oracle: SQL error 1403 mapping ESTPDB.SCHDR.ADDRESSES_TBL to INTPDB.ECMSTG.ADDRESSES_TBL No data found.

Further investigation into GoldenGate’s Per-Base Exception Table and Global Exception Table confirmed the failure:

Exception Table Output:

SELECT EXCP_DATE, REP_NAME, ERRNO, ERRTYPE, OPTYPE, RECCSN, RECSEQNO, RECRBA
FROM c##ggadmin.addresses_tbl_ex;
EXCP_DATE            REP_NAME    ERRNO  ERRTYPE       OPTYPE   RECCSN    RECSEQNO  RECRBA
-------------------- ----------  -----  -----------   ------   -------- ---------- --------
24-JUL-2024 19:44:02 DRRTR      1403   DB No Data Found 20533612 5        7687

Global Exception Table Output:

SELECT excp_date, table_name, rep_name, reccsn, RECSEQNO, RECRBA, LOGRBA, LOGPOSITION
FROM c##ggadmin.exceptions;
EXCP_DATE                     TABLE_NAME                     REP_NAME  RECCSN   RECSEQNO  RECRBA  LOGRBA  LOGPOSITION
------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -----------
24-JUL-24 07.44.02.897873 PM  ESTPDB.SCHDR.ADDRESSES_TBL  DRRTR    20533612         5       8399      248      27720208

Root Cause Analysis

The following sequence of events led to the exception:

1. Insert Operation on Source:
   • A new row was inserted into SCHDR.ADDRESSES_TBL.
   • This was successfully replicated to the target (ECMSTG.ADDRESSES_TBL).
2. Delete Operation on Target:
   • The row was deleted manually from the target (ECMSTG.ADDRESSES_TBL).
   • A commit was issued, permanently removing the record.
3. Update Operation on Source:
   • The row was updated on the source table (SCHDR.ADDRESSES_TBL).
   • GoldenGate attempted to replicate the update to the target, but the row was missing, triggering SQL Error 1403 – No Data Found.

1. Manually Reinsert the Missing Row

If the row was deleted unintentionally, manually reinserting it ensures data consistency.

INSERT INTO ECMSTG.ADDRESSES_TBL
SELECT * FROM SCHDR.ADDRESSES_TBL WHERE ADDRESS_ID = 140091867;

UPDATE ECMSTG.ADDRESSES_TBL SET CREATED_DATE = SYSDATE WHERE ADDRESS_ID = 140091867;

COMMIT;

2. Modify GoldenGate Configuration for Conflict Resolution

If rows are frequently deleted but updates may still occur, configure GoldenGate to handle such conflicts gracefully:

Option A: Ignore Updates for Missing Rows

REPERROR 1403 DISCARD

Conclusion

Replication errors such as SQL Error 1403 – No Data Found can cause data inconsistencies if not handled properly. By implementing conflict detection mechanisms, exception handling, and proper error handling configurations, GoldenGate users can ensure seamless replication and data integrity. Always monitor GoldenGate exception tables and logs to proactively detect and resolve such issues.

Have you encountered similar Oracle GoldenGate replication errors? Share your experiences and solutions in the comments!