Oracle GoldenGate - Exception Handling

Oracle GoldenGate - Exception Handling

Oracle GoldenGate Exception Handling

 

Author: Bobby Curtis, MBA

 

Date: October 3, 2023

 

Updated: January 23, 2025

 

Organization: RheoData

Table of Contents

INTRODUCTION:

EXCEPTION TABLE(S) INFO

Exception Table

Exception Macro

Update Replicat

CONCLUSION:

 


 

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. 

Exception Table(s) Info

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.

Exception Table

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

 

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

 

Conclusion:

Congratulations! You have successfully added a exception handling to your replication process.

 

 

    • Related Articles

    • Oracle GoldenGate - Installing Oracle GoldenGate binaries

      Installing Oracle GoldenGate binaries Author: Bobby Curtis, MBA Date: August 4, 2023 Organization: RheoData Table of Contents INTRODUCTION: Prerequisites: STEP 1: DOWNLOAD ORACLE GOLDENGATE 21.10 STEP 2: TRANSFER INSTALLATION FILES STEP 3: EXTRACT ...
    • Oracle GoldenGate - Backup Oracle GoldenGate Deployments

      Backup Oracle GoldenGate Deployments Author: Bobby Curtis, MBA Date: January 23, 2025 Updated: October 13, 2023 Organization: RheoData Table of Contents INTRODUCTION: Assumption: STEP 1: ACCESS THE ORACLE GOLDENGATE SERVER STEP 2: IDENTIFY THE ...
    • Installing Oracle GoldenGate deployments

      Installing Oracle GoldenGate deployments Author: Bobby Curtis, MBA Date: August 8, 2023 Organization: RheoData Table of Contents INTRODUCTION: Prerequisites: STEP 1: DOWNLOAD ORACLE GOLDENGATE 21.10 STEP 2: TRANSFER INSTALLATION FILES STEP 3: EXTRACT ...
    • Oracle GoldenGate - Deployments: Switchover

      Oracle GoldenGate Deployments: Switchover Author: Bobby Curtis, MBA Date: August 17, 2023 Organization: RheoData Table of Contents INTRODUCTION: PREREQUISITES STEP 1: EXPORT DEPLOYMENT VAR AND ETC HOMES STEP 2: STOP SERVICEMANAGER STEP 3: VERIFY ...
    • Oracle GoldenGate - Out-of-Place Patching

      Oracle GoldenGate Out-of-Place Patching Author: Bobby Curtis, MBA Date: October 17, 2023 Updated: January 23, 2025 Organization: RheoData Table of Contents INTRODUCTION: PLATFORM PATCHING SECOND HOME PATCHSET LIST DEPLOYMENTS PATCH SERVICEMANAGER VIA ...