First, you need to report errors instead of letting the process abend. [1] You can use the REPTERROR parameter, which allows you to report the error and define how the errors should be handled. The followings are some examples.
- REPERROR (DEFAULT, EXCEPTION)
The DEFAULT argument sets a global response to all errors except those for which explicit REPERROR statements are specified. - REPERROR (DEFAULT2, ABEND)
The DEFAULT2 argument specifies a "catch all" action for any unanticipated Oracle errors that may occur. - REPERROR (-1, EXCEPTION)
ORA-00001: unique constraint violated error using an exception handler specified. The example ignores the duplicate error for Oracle database replication. - REPERROR(1062, IGNORE)
In MySQL, the duplicate record error is 1062. the example, will ignore the duplicate error for MySQL replication. - ORA-01403: "no data found" error.
REPERROR(-1403, EXCEPTION)
To handle errors, the best practices is to create an exception table in Oracle GoldenGate administration schedule to track them. The following is an example. The configuration takes several steps.
Step 1: Creating exception table
In the following example, we create an exception table under a ggadm user within a PDB so that application can manage the exceptions by itself. [2]
SQL> alter session set container=pdb1; Session altered. SQL> grant connect, resource to ggadm; Grant succeeded. SQL> connect ggadm/ggadm@pdb1 Connected. SQL> create table ggadm.exception_tbl ( process_name varchar2(8) , table_name varchar2(61) , errno number , dberrmsg varchar2(4000) , optype varchar2(20) , errtype varchar2(20) , logrba number , logposition number , committimestamp timestamp ); Table created.
MACRO #exception_handler BEGIN , TARGET ggadm.exception_tbl , COLMAP ( rep_name = "REPGDRDS" , 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;
replicat repgdrds MACRO #exception_handler BEGIN , TARGET ggs_admin.exceptions , COLMAP ( rep_name = "REPGDRDS" , 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; reperror(1413, exception) useridalias ggadmin_tgt dboptions integratedparams(parallelism 2) discardfile ./dirrpt/repgdrds.dsc,append megabytes 50 ddl include all map awsuser.member, TARGET pdb1.pmdemo.member;