Oracle GoldenGate is a powerful tool for real-time data replication, but errors such as ORA-01043 “No Data Found” can disrupt replication processes. In this blog post, we’ll explore how to manage ORA-01043 errors using the REPERROR parameter in Oracle GoldenGate configurations, ensuring smooth replication operations even when data retrieval issues occur.
ORA-01043 errors occur when an SQL statement within Oracle GoldenGate fails to find any data during execution. In the context of GoldenGate, this error may occur during the Replicat process when changes to the target database are not applied if the required data is not found.
Using REPERROR Parameter: The REPERROR parameter in Oracle GoldenGate configurations allows users to define custom actions to handle specific error codes encountered during replication. By specifying how to handle ORA-01043 errors, administrators can ensure that GoldenGate continues processing without halting replication operations.
The REPERROR will educate the replicat on how to discard the ORA-01403 upon occurrence. We will infuse this parameter in the replicat file. This change requires restarting the replicat process.
| SOURCE | TARGET | ERROR MESSAGE |
|---|---|---|
| Updated in source but row not present at target | Send to discard file when it comes across – REPERROR (1403 Discard) | No data found |
| Deleted in source but row not present at target | Send to discard file when it comes across – REPERROR (1403 Discard) | No data found |
PARAMETER SYNTAX: REPERROR (ERROR NUMBER, ABEND/DISCARD/IGNORE)
REPERROR (1403, DISCARD) → This will add an entry to discard the file in which a matching error occurs skip that particular transaction, and move to the next transaction
REPERROR (1403, ABEND) → This is a default behavior that will abend the replicate process
REPERROR (1403, IGNORE) → This will ignore the matching error, skip that particular transaction, and move to the next transaction, but will never know which transaction is being skipped as no entry is being added. USE IT WITH CAUTION
Usage: If we are applying to all the map statements or globally
REPERROR (1403, DISCARD)
MAP SCOTT.EMP TARGET SCOTT.EMP;
MAP SCOTT.DEPT TARGET SCOTT.DEPT;Similarly, if we intend to apply only specific Map statements, the below syntax can be used
MAP SCOTT.EMP TARGET SCOTT.EMP;
MAP SCOTT.DEPT TARGET SCOTT.DEPT;
REPERROR (1403, DISCARD)
MAP SCOTT.TEST TARGET SCOTT.TEST; If you would like to specify only one table, the syntax below can be used.
MAP SCOTT.MSC TARGET MSC, REPERROR (1403, DISCARD);By leveraging the REPERROR parameter in Oracle GoldenGate configurations, organizations can effectively manage ORA-01043 “No Data Found” errors encountered during replication processes. By defining appropriate handling actions and conducting thorough testing, administrators can ensure uninterrupted data replication operations and maintain the reliability of their GoldenGate environments.
