Creating a GoldenGate Exception Handler to Trap and Log Oracle Errors

GoldenGate does not provide a standard exceptions handler. By default, a Replicat process will abend should any operational failure occur, and will rollback the transaction to the last known checkpoint. This may not be ideal in a production environment.

The HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters can be used to control whether or not Replicat tries to resolve duplicate-record and missing-record errors, but should these errors be ignored?

The way to determine what error has occurred, by which Replicat, caused by what data, create an Exceptions handler.

Steps

The steps below create an Exceptions handler that will trap and log the specified Oracle error(s), but allow the Replicat to continue to process data:

1. The first step is to create an Exceptions table, similar to the example DDL below:

create table ggs_admin.exceptions

( rep_name varchar2(8)

, table_name varchar2(61)

, errno number

, dberrmsg varchar2(4000)

, optype varchar2(20)

, errtype varchar2(20)

, logrba number

, logposition number

, committimestamp timestamp

);

ALTER TABLE ggs_admin.exceptions ADD (

  CONSTRAINT PK_CTS

 PRIMARY KEY

 (logrba, logposition, committimestamp) USING INDEX PCTFREE 0 TABLESPACE MY_INDEXES);

The Exceptions table must be created in the GoldenGate Admin user schema. It can log exception data for all Replicat processes.

2. Edit each Replicat process parameter file and add the exception handler Macro code block.

[oracle@linuxserver1 ggs]$ ggsci

GGSCI (linuxserver1) 1> edit params RTARGET1

-- This starts the macro

MACRO #exception_handler

BEGIN

, TARGET ggs_admin.exceptions

, COLMAP ( rep_name = "RTARGET1"

, table_name = @GETENV ("GGHEADER", "TABLENAME")

, errno = @GETENV ("LASTERR", "DBERRNUM")

, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")

, optype = @GETENV ("LASTERR", "OPTYPE")

, errtype = @GETENV ("LASTERR", "ERRTYPE")

, logrba = @GETENV ("GGHEADER", "LOGRBA")

, logposition = @GETENV ("GGHEADER", "LOGPOSITION")

, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))

, INSERTALLRECORDS

, EXCEPTIONSONLY;

END;

-- This ends the macro

3. Remaining within the editor (vi), edit the MAP statements to include the #exception_handler(). Also add the REPERROR parameter to reference to the Oracle error(s) you wish to trap.

REPERROR (DEFAULT, EXCEPTION)

REPERROR (DEFAULT2, ABEND)

REPERROR (-1, EXCEPTION)

MAP SRC.ORDERS, TARGET TGT.ORDERS;

MAP SRC.ORDERS #exception_handler()

MAP SRC.ORDER_ITEMS, TARGET TGT.ORDER_ITEMS;

MAP SRC.ORDER_ITEMS #exception_handler()

MAP SRC.PRODUCTS, TARGET TGT.PRODUCTS;

MAP SRC.PRODUCTS #exception_handler()

• The REPERROR parameter controls how the Replicat process responds to errors when executing the MAP statement.
• The DEFAULT argument sets a global response to all errors except those for which explicit REPERROR statements are specified.

E.g. A MAP statement to trap ORA-01403: "no data found"  error.

MAP SRC.ORDERS, TARGET TGT.ORDERS, REPERROR (-1403, EXCEPTION);

• The DEFAULT2 argument specifies a "catch all" action for any unanticipated Oracle errors that may occur. In the example in step 3, the Replicat process will Abend.

4. Stop and start the Replicat process.

GGSCI (linuxserver1) 3> stop REPLICAT RTARGET1

Sending STOP request to REPLICAT RTARGET1 ...

Request processed.

GGSCI (linuxserver1) 4> start replicat RTARGET1

Sending START request to MANAGER ...

REPLICAT RTARGET1 starting

5. Check Replicat process is running.

GGSCI (linuxserver1) 5> info all

Program     Status Group       Lag           Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING RTARGET1    00:00:00      00:00:22

6. Start your application and begin replicating data.

Viewing Exceptions

Below is an example of the data collected following an ORA-00001: "unique constraint violated"

SQL> select * from ggs_admin.exceptions where rownum <= 1;

REP_NAME TABLE_NAME ERRNO DBERRMSG

-------- ---------- ----- --------

RTARGET1 SRC.ORDERS     1 OCI Error ORA-00001: unique constraint (TGT.PK_ORD) violated (status = 1), SQL

                          <INSERT INTO "TGT"."ORDERS" ("ORDER_ID","CUST_ID","PRODUCT_ID" ..

OPTYPE ERRTYPE LOGRBA LOGPOSITION COMMITTIMESTAMP

------ ------- ------ ----------- -------------------------

INSERT DB         988   171211460 02-APR-10 12.41.42.999468

Tip: The DBERRMSG column will store the error, the error description and the complete SQL up to 4000 bytes.

The exception handler can be modified to also include the before and after images of an UPDATE operation. This information is valuable for conflict resolution.