Encountering errors during data replication can disrupt critical business processes and compromise data integrity. One such error, ORA-01403: No Data Found, is commonly encountered in Oracle GoldenGate environments and requires prompt resolution to ensure uninterrupted replication operations. In this blog post, we’ll explore the root causes of the ORA-01403 error in GoldenGate, analyze potential solutions, and provide actionable steps to mitigate its impact on data replication.
Understanding the ORA-01403 Error in GoldenGate: The ORA-01403 error occurs when a SELECT INTO statement or FETCH statement returns no rows from the target table or cursor. In GoldenGate, this error typically indicates that the Extract or Replicat process cannot locate the expected data during the replication process, leading to data inconsistency between the source and target databases.
The root causes for the ORA-01403 can be multiple reasons, such as Data Drift, Data Filtering, Referential Integrity constraints, Schema changes, etc.
The solution depends on context to context, if the ORA-01403 not due to the initial load then HANDLECOLLISION will be the perfect solution in this case.
The HANDLECOLLISION parameter in Oracle GoldenGate configurations allows users to specify actions to take when conflicts or errors, such as ORA-01043, occur during the initialization of the Extract or Replicat process. Administrators can ensure that replication processes continue seamlessly despite encountering errors by defining appropriate handling options for collision errors.
Without this parameter, Replicat will ABEND when it tries to process the SQL when the data does not exist in the target. It will also ABEND when it tries updating or deleting rows that are not present in the destination tables. To overcome this, normally, the RBA of the trail has to be moved forward one transaction before Replicat can be restarted and will stay running.
The following is the behavior of the Replicat process when the Goldengate HANDLECOLLISIONS parameter is enabled.
| ON SOURCE | ON TARGET | CONDITION | ACTION |
|---|---|---|---|
| INSERTS | INSERTS | Duplicate INSERTS | Converted to UPDATES |
| UPDATES | UPDATES | Updated in source, but row not present in target | Ignored |
| DELETES | DELETES | Deleted in source, but row not present in target | Ignored |
If this solution is used globally for all the MAP statements, as shown below, please restart the process after performing changes in the replicat file.
HANDLECOLLISIONS
MAP SCOTT.EMP TARGET SCOTT.EMP;
MAP SCOTT.DEPT TARGET SCOTT.DEPT;If this solution applies only specific MAP statements, we can differentiate them as follows:
HANDLECOLLISIONS
MAP SCOTT.EMP TARGET SCOTT.EMP;
MAP SCOTT.DEPT TARGET SCOTT.DEPT;
NOHANDLECOLLISIONS
MAP SCOTT.TEST TARGET SCOTT.TEST
We have learned how to handle ORA-01403 and how we can use the parameter in both cases as explained above.
