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.