Oracle GoldenGate is a powerful replication solution, but certain scenarios can trigger exceptions that require investigation. This test case examines how GoldenGate handles a no data found error when a record is deleted at the target but subsequently updated at the source.
Scenario Overview
The replication involves two databases:
- Source database: src_db.user_details
- Target database: tgt_db.customer_info
A record is inserted at the source, deleted at the target, and later updated at the source. This sequence triggers an exception in Oracle GoldenGate.
Step 1: Insert a Row at the Source
A new record is added to user_details.
SET DEFINE OFF;
INSERT INTO src_db.user_details
(user_id, first_name, last_name, email, phone, created_date, modified_date)
VALUES
(1001, 'John', 'Doe', '[email protected]', '1234567890',
TO_DATE('07/23/2024 03:49:15', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('07/23/2024 03:49:52', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
Step 2: Delete the Row at the Target
The same record is removed from customer_info.
DELETE FROM tgt_db.customer_info WHERE user_id = 1001;
COMMIT;
Step 3: Update the Row at the Source
An update occurs on user_details.
UPDATE src_db.user_details SET modified_date = SYSDATE WHERE user_id = 1001;
COMMIT;
Step 4: Oracle GoldenGate Encounters an Exception
Since the record no longer exists at the target, GoldenGate fails to apply the update.
GoldenGate Error Log (ggserr.log)
2024-07-24T19:44:02.886+0530 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, replicat.prm: SQL error 1403 mapping src_db.user_details to tgt_db.customer_info No data found.
Per-Base Exception Table
SELECT excp_date, rep_name, errno, errtype, optype, reccsn, recseqno, recrba
FROM ggadmin.user_details_exceptions;
EXCP_DATE REP_NAME ERRNO ERRTYPE OPTYPE RECCSN RECSEQNO RECRBA
-------------------- ---------- ------ ----------- ------------ ---------- ---------- ----------
24-JUL-2024 19:44:02 REPLICAT 1403 DB No data found 20533612 5 7687Global Exception Table
SELECT excp_date, table_name, rep_name, reccsn, recseqno, recrba, logrba, logposition
FROM ggadmin.global_exceptions;
EXCP_DATE TABLE_NAME REP_NAME RECCSN RECSEQNO RECRBA LOGRBA LOGPOSITION
------------------------------ ---------------- ---------- ---------- ---------- ---------- ---------- -----------
24-JUL-24 07.44.02.897873 PM src_db.user_details REPLICAT 20533612 5 8399 248 27720208
Why Did This Happen?
The update from the source could not find a matching record at the target. Since the row was deleted earlier, GoldenGate encountered a no data found error.
How to Handle This in Oracle GoldenGate?
1. Use REPERROR to Ignore the Error
If replication should continue without stopping, configure the replicat process to discard the error:
REPERROR 1403, DISCARD
This prevents the process from failing due to missing rows.
2. Use HANDLECOLLISIONS for Initial Load
If this occurs during an initial data load, temporarily enable:
HANDLECOLLISIONS
After the initial sync, remove this setting to avoid unnecessary conflict handling.
3. Use a Mapping Option to Insert Missing Records
Modify the MAP statement to allow missing rows to be inserted:
MAP src_db.user_details, TARGET tgt_db.customer_info, insertallrecords;
4. Investigate and Manually Resolve Data Issues
If data integrity is crucial, manually reinsert missing records.
INSERT INTO tgt_db.customer_info
SELECT * FROM src_db.user_details WHERE user_id = 1001;
Conclusion
This test case demonstrates how missing records at the target can lead to no data found exceptions in Oracle GoldenGate. Using strategies like REPERROR, HANDLECOLLISIONS, and appropriate mapping options ensures smooth replication. Regularly reviewing exception tables helps diagnose and resolve issues effectively.
