Basic error handling with Exception Table

Introduction

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. This blog post is meant to show the basics of exception handling by using a macro within the Oracle GoldenGate (Microservices) environment.

A word of caution needs to be given though.  This approach will not work on Oracle GoldenGate Service (GGS) within OCI.  This is due to not having access to the underlying host where the parameter files and trail files are stored.  Hopefully, in the coming time, the Oracle GoldenGate Product Team will allow this.  Yet, please remember that GGS is a “service”.

Exception Table(s) Info

An 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.

Exception Table

This version of the exception table (master table approach) 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

Exception Macro

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;

Update Replicat

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);

Now you can capture errors while processing is on-going without a replicat abending.