Handling GoldenGate Exceptions and Errors with REPERROR

We can use the REPERROR parameter in the Replicat parameter file to control the way that the replication process responds to or handles any errors encountered in any of the DML statements which it is trying to process.

We can use the keyword DEFAULT to set a global response for all errors except those for which explicit REPERROR statements have been specified.

In the example we will see how we are handling the ORA-00001: unique constraint violated error using an exception handler specified via the REPERROR (-1, EXCEPTION) clause of the Replicat parameter file.

By default, if the replicat process encounters any error condition it will abend.

The example shows how by using an exception handler, replicat process does not abend, but handles the exceptions appropriately and continues processing.

If we have a primary key defined on both the source and target tables and if a unique key violation does happen, then neither the Extract or Replicat process gets affected and processing is not halted on either end of the Goldengate environment.

But if we have a case where say there are duplicate key values on the source table, but on the target table there is a primary or unique constraint in place. When these duplicate rows get propagated to the target server via the extract trail and when the Replicat process does encounter these row violations of the primary key constraint in place on the target table, the replicat process will abend.

However, we can use the REPERROR parameter to specify how we will handle this specific error (or any other error or all errors).

In our example, we have created an ‘exception’ table and the exception handler is to write information about any such rows which are violating the unique constraint to this exception table AND continue processing without abending the replicat process.

Without the exception handler in place, we will first see that the Replicat process terminates or abends.

SQL> /
Enter value for 1: 4001
Enter value for 2: KERRY
old   3:  (&1,'&2')
new   3:  (4001,'KERRY')
insert into emp
*
ERROR at line 1:
ORA-00001: unique constraint (IDIT_PRD.PK_EMP) violated




GGSCI (indb02) 5> info replicat myrep

REPLICAT   MYREP     Last Started 2011-04-15 12:18   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:00:17 ago)
Log Read Checkpoint  File ./dirdat/gg000016
                     2011-04-15 12:19:30.219092  RBA 1825

Using the VIEW REPORT command, we can see why the process has abended.

2011-04-15 12:22:27  WARNING OGG-01004  Aborted grouped transaction on 'IDIT_PRD.EMP', Database error 1 (ORA-00001: unique constraint (IDIT_PRD.PK_EMP) violated).

2011-04-15 12:22:27  WARNING OGG-01003  Repositioning to rba 1825 in seqno 16.

2011-04-15 12:22:27  WARNING OGG-01154  SQL error 1 mapping IDIT_PRD.EMP to IDIT_PRD.EMP OCI Error ORA-00001: unique constraint (IDIT_PRD.PK_EMP) violated (status = 1), SQL .

Now let us put in an exception handler into our replicat parameter file.

REPLICAT myrep
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1")
SETENV (ORACLE_SID=GGDB2)
ASSUMETARGETDEFS
USERID idit_prd,PASSWORD idit_prd
REPERROR (-1, EXCEPTION)
MAP idit_prd.emp, TARGET idit_prd.emp;
INSERTALLRECORDS
MAP idit_prd.emp, TARGET idit_prd.emp_exception,
EXCEPTIONSONLY,
COLMAP (USEDEFAULTS,
optype = @GETENV ("lasterr", "optype"),
dberr = @GETENV ("lasterr", "dberrnum"),
dberrmsg = @GETENV ("lasterr", "dberrmsg"));

So the REPERROR (-1, EXCEPTION) means that when we encounter the ORA-00001 error, the exception handler will kick in.

The EXCEPTIONSONLY clause defines that such exceptions will be logged in the EMP_EXCEPTIONS table, which has all the columns of the EMP table, plus additional columns which will log information about row violations in the OPTYPE, DBERR and DBERRMSG columns.

So now we go and insert some duplicate rows on the source table.

SQL> insert into emp
  2   values
  3   (&1,'&2');
Enter value for 1: 1001
Enter value for 2: GREG
old   3:  (&1,'&2')
new   3:  (1001,'GREG')

1 row created.

SQL> commit;

Commit complete.

We see that on the target, this time the replicat process has not abended and continues to do the processing.

GGSCI (indb02) 26> info replicat myrep

REPLICAT   MYREP     Last Started 2011-04-15 12:26   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  File ./dirdat/gg000016
                     2011-04-15 12:26:57.214525  RBA 2216

But the table EMP_EXCEPTIONS has been populated with the information about the duplicate rows which has caused the ORA-00001 error.

SQL> select * from emp_exception;


     EMPNO ENAME      OPTYPE               DBERR                DBERRMSG
---------- ---------- -------------------- -------------------- --------------------
      1001 GREG       INSERT               1                    OCI Error ORA-00001:

Have a look at this link which has another good example of error handling and also describes how to create and use a macro in GoldenGate.

Creating a GoldenGate Exception Handler to trap and log Oracle Errors