Oracle GoldenGate Exception Handling
Author: Bobby Curtis, MBA
Date: October 3, 2023
Updated: January 23, 2025
Organization: RheoData
Table of Contents
Exception handling is one of the basic yet advance features that Oracle GoldenGate can do. This allows the Oracle GoldenGate processes to keep running when errors happen and time for administrators to evaluate the errors.
The exception table is one of two things:
1. A matching table to the table being replicated.
2. A master exception table that is used to track where the exception happens.
For ICON, this will use a master exception table approach to handle exceptions within their bi-directional configuration.
This version of the exception table will capture details of an error based on the information that is passed in the trail file when the error occurred. This information can be used to identify where and what trail file should be reviewed to identify the data that may not have been processed.
accept ggate_user char prompt 'GoldenGate User Name: '
drop table &&ggate_user..exceptions
/
create table
&&ggate_user..exceptions (
EXCEPTION_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100
, EXCEPTION_TS TIMESTAMP(6) default systimestamp
, EXCEPTION_STATUS VARCHAR2(15)
, REP_NAME VARCHAR2(8)
, TABLE_NAME VARCHAR2(61)
, BEFORE_AFTER VARCHAR2(32)
, OPTYPE VARCHAR2(20)
, TRANSIND VARCHAR2(20)
, LOGCSN NUMBER
, FILESEQNO NUMBER
, FILERBA NUMBER
, LOGRBA NUMBER
, LOGPOSITION NUMBER
, COMMITTIMESTAMP TIMESTAMP(6)
, ERRTYPE VARCHAR2(20)
, ERRNO NUMBER
, DBERRMSG VARCHAR2(4000)
,CONSTRAINT exception_pk PRIMARY KEY (EXCEPTION_ID)
)
/
Show Errors
The exceptions macro will be processed by the replicat and used to populate the exceptions table. Within an Oracle GoldenGate (Microservices) deployment (non-OCI), a macro should be placed in a directory where it can be referenced by the replicat.
In this instance, the $OGG_ETC_HOME/conf/ogg directory can be used. Simply add a sub-directory called mac or dirmac. Within this directory, add the file “exceptions.mac” and the contents should be as follows:
MACRO #exception_handler
PARAMS(#ggate_user)
BEGIN
, TARGET #ggate_user.exceptions
, COLMAP ( exception_id = 0
, exception_ts = ""
, exception_status = ""
, rep_name = @GETENV ("GGENVIRONMENT", "GROUPNAME")
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, before_after = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR")
, optype = @GETENV ("LASTERR", "OPTYPE")
, transind = @GETENV ( "GGHEADER", "TRANSACTIONINDICATOR")
, logcsn = @GETENV ("TRANSACTION", "CSN")
, fileseqno = @GETENV ("RECORD", "FILESEQNO")
, filerba = @GETENV ("RECORD", "FILERBA")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
)
, INSERTALLRECORDS
, EXCEPTIONSONLY
END;
Once the exception macro is in place; the replicat needs to be updated to reflect the location and how errors should be handled. This is done with the INCLUDE and REPERROR parameters. In the parameter file as example below, this will configure exceptions for all schemas/tables in replication. The last thing that needs to be done is to write corresponding map statements that will use the mac.
REPLICAT REPPDB2
USERIDALIAS TargetPDB DOMAIN OracleGoldenGate
INCLUDE mac/exceptions.mac
REPERROR(DEFAULT, EXCEPTION)
REPERROR(DEFAULT2, ABEND)
DDLERROR DEFAULT IGNORE
DDL
DDLOPTIONS UPDATEMETADATA
MAP DEVDB_PDB1.TPC.*, TARGET TPC.*;
MAP DEVDB_PDB1.TPC.*, #exception_handler(ggate);
MAP DEVDB_PDB1.TPC1.*, TARGET TPC1.*;
MAP DEVDB_PDB1.TPC1.*, #exception_handler(ggate);
Congratulations! You have successfully added a exception handling to your replication process.