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
OPTYPE ERRTYPE LOGRBA LOGPOSITION COMMITTIMESTAMP
------ ------- ------ ----------- -------------------------
INSERT DB 988 171211460 02-APR-10 12.41.42.999468
Myself ( http://www.vitalsofttech.com/troubleshoot-oracle-goldengate-using-exception-handler/)
Create Exception table on Target server :
01) Create Table :
CREATE TABLE GGS_OWNER.GGS_EXCEPTIONS
(
REP_NAME VARCHAR2(8 BYTE),
TABLE_NAME VARCHAR2(61 BYTE),
DML_DATE TIMESTAMP(6),
ERRNO NUMBER,
DBERRMSG VARCHAR2(4000 BYTE),
OPTYPE VARCHAR2(20 BYTE),
ERRTYPE VARCHAR2(20 BYTE),
LOGRBA NUMBER(20) NOT NULL,
LOGPOSITION NUMBER(20) NOT NULL,
COMMITTIMESTAMP TIMESTAMP(6) NOT NULL,
GGS_FILENAME VARCHAR2(500 BYTE),
CDRFAIL NUMBER,
CDRSUC NUMBER,
CDRDETECT NUMBER
)
TABLESPACE GGS_DATA;
02 ) create unque Index :
CREATE UNIQUE INDEX GGS_OWNER.EXCEPTIONS_PK ON GGS_OWNER.GGS_EXCEPTIONS
(LOGRBA, LOGPOSITION, COMMITTIMESTAMP)
03)
ALTER TABLE GGS_OWNER.GGS_EXCEPTIONS ADD (
CONSTRAINT EXCEPTIONS_PK
PRIMARY KEY
(LOGRBA, LOGPOSITION, COMMITTIMESTAMP)
USING INDEX GGS_OWNER.EXCEPTIONS_PK);
04) Replicate process should be :
REPLICAT rep3
ASSUMETARGETDEFS
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = "RNDT")
USERID ggs_owner, PASSWORD ggs_owner
--EOFDELAY 60
DISCARDFILE /u01/oracle/ggs/dirrpt/discard.txt, APPEND, MEGABYTES 100
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
ASSUMETARGETDEFS
MAP SCOTT.*, TARGET SCOTT.*;
MAP ULTIMUSISL.*, TARGET ULTIMUSISL.*;
---new exception handler
MACRO #exception_handler
BEGIN
-- Use the same Golden Gate Exceptions Table for all exception records
, TARGET GGS_OWNER.GGS_EXCEPTIONS
, COLMAP ( rep_name = @GETENV("GGENVIRONMENT", "GROUPNAME")
, 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")
, GGS_FILENAME = @GETENV("GGFILEHEADER", "FILENAME")
, CDRFAIL = @GETENV("DELTASTATS","CDR_RESOLUTIONS_FAILED")
, CDRSUC = @GETENV("DELTASTATS","CDR_RESOLUTIONS_SUCCEEDED")
, CDRDETECT = @GETENV("DELTASTATS","CDR_CONFLICTS"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
--REPERROR (DEFAULT, EXCEPTION)
--REPERROR (DEFAULT2, ABEND)
--REPERROR (-1, EXCEPTION)
MAP ULTIMUSISL.* #exception_handler();
MAP SCOTT.* #exception_handler();
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
OPTYPE ERRTYPE LOGRBA LOGPOSITION COMMITTIMESTAMP
------ ------- ------ ----------- -------------------------
INSERT DB 988 171211460 02-APR-10 12.41.42.999468
Myself ( http://www.vitalsofttech.com/troubleshoot-oracle-goldengate-using-exception-handler/)
Create Exception table on Target server :
01) Create Table :
CREATE TABLE GGS_OWNER.GGS_EXCEPTIONS
(
REP_NAME VARCHAR2(8 BYTE),
TABLE_NAME VARCHAR2(61 BYTE),
DML_DATE TIMESTAMP(6),
ERRNO NUMBER,
DBERRMSG VARCHAR2(4000 BYTE),
OPTYPE VARCHAR2(20 BYTE),
ERRTYPE VARCHAR2(20 BYTE),
LOGRBA NUMBER(20) NOT NULL,
LOGPOSITION NUMBER(20) NOT NULL,
COMMITTIMESTAMP TIMESTAMP(6) NOT NULL,
GGS_FILENAME VARCHAR2(500 BYTE),
CDRFAIL NUMBER,
CDRSUC NUMBER,
CDRDETECT NUMBER
)
TABLESPACE GGS_DATA;
02 ) create unque Index :
CREATE UNIQUE INDEX GGS_OWNER.EXCEPTIONS_PK ON GGS_OWNER.GGS_EXCEPTIONS
(LOGRBA, LOGPOSITION, COMMITTIMESTAMP)
03)
ALTER TABLE GGS_OWNER.GGS_EXCEPTIONS ADD (
CONSTRAINT EXCEPTIONS_PK
PRIMARY KEY
(LOGRBA, LOGPOSITION, COMMITTIMESTAMP)
USING INDEX GGS_OWNER.EXCEPTIONS_PK);
04) Replicate process should be :
REPLICAT rep3
ASSUMETARGETDEFS
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = "RNDT")
USERID ggs_owner, PASSWORD ggs_owner
--EOFDELAY 60
DISCARDFILE /u01/oracle/ggs/dirrpt/discard.txt, APPEND, MEGABYTES 100
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
ASSUMETARGETDEFS
MAP SCOTT.*, TARGET SCOTT.*;
MAP ULTIMUSISL.*, TARGET ULTIMUSISL.*;
---new exception handler
MACRO #exception_handler
BEGIN
-- Use the same Golden Gate Exceptions Table for all exception records
, TARGET GGS_OWNER.GGS_EXCEPTIONS
, COLMAP ( rep_name = @GETENV("GGENVIRONMENT", "GROUPNAME")
, 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")
, GGS_FILENAME = @GETENV("GGFILEHEADER", "FILENAME")
, CDRFAIL = @GETENV("DELTASTATS","CDR_RESOLUTIONS_FAILED")
, CDRSUC = @GETENV("DELTASTATS","CDR_RESOLUTIONS_SUCCEEDED")
, CDRDETECT = @GETENV("DELTASTATS","CDR_CONFLICTS"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
--REPERROR (DEFAULT, EXCEPTION)
--REPERROR (DEFAULT2, ABEND)
--REPERROR (-1, EXCEPTION)
MAP ULTIMUSISL.* #exception_handler();
MAP SCOTT.* #exception_handler();
No comments:
Post a Comment