Manual CDR Exception Handling in Goldengate Bi-Directional Setup

In Golden Gate Bi-directional setup, the industry facing the main challenge is addressing the conflicts. Even though Auto CDR is introduced to resolve by Oracle itself, still this is feature is not acceptable to many customers.

Because sometimes the record should win in slave, and sometime the record should win in primary since it is purely business logic. In such cases, Auto CDR is ruled out, and the customer should consider implementing exception handling, and then if any exception happens, those should be discarded. The discarded rows should be taken care of manually.

We will see how we have to create and integrate with replciat process.

1. Create Exception Tables and provide necessary grants.

CREATE TABLE GGADMIN.EXCEPTIONS
(
REP_NAME VARCHAR2(8) NULL,
TABLE_NAME VARCHAR2(61) NULL,
DML_DATE TIMESTAMP(6) NULL,
ERRNO NUMBER NULL,
DBERRMSG VARCHAR2(4000) NULL,
OPTYPE VARCHAR2(20) NULL,
ERRTYPE VARCHAR2(20) NULL,
LOGRBA NUMBER NOT NULL,
LOGPOSITION NUMBER NOT NULL,
COMMITTIMESTAMP TIMESTAMP(6) NOT NULL,
GGADMIN_FILENAME VARCHAR2(256) NULL,
CDRFAIL NUMBER NULL,
CDRSUC NUMBER NULL,
CDRDETECT NUMBER NULL,
CONSTRAINT EXCEPTIONS_PK
PRIMARY KEY (LOGRBA,LOGPOSITION,COMMITTIMESTAMP)
USING INDEX TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
ENABLE
VALIDATE
)
ORGANIZATION HEAP
TABLESPACE USERS
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
NOROWDEPENDENCIES
/

CREATE UNIQUE INDEX GGADMIN.EXCEPTIONS_PK ON GGADMIN.EXCEPTIONS
(LOGRBA, LOGPOSITION, COMMITTIMESTAMP)
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;ALTER TABLE GGADMIN.EXCEPTIONS ADD (
CONSTRAINT EXCEPTIONS_PK
PRIMARY KEY
(LOGRBA, LOGPOSITION, COMMITTIMESTAMP)
USING INDEX GGUSER.EXCEPTIONS_PK);

GRANT DELETE ON GGADMIN.EXCEPTIONS TO GGADMIN
/
GRANT INSERT ON GGADMIN.EXCEPTIONS TO GGADMIN
/
GRANT SELECT ON GGADMIN.EXCEPTIONS TO GGADMIN
/
GRANT UPDATE ON GGADMIN.EXCEPTIONS TO GGADMIN
/

2. Update Replicat to use Macros functions and map the tables to exception tables.

Replicat inrep1
userid ggadmin@PRODS, password Welcome1
--dboptions integratedparams (parallelism 4, max_parallelism 8)
dboptions settag 00112233445566778899AABBCCDDEEFF
assumetargetdefs
--batchsql
statoptions resetreportstats
report at 00:01
reportrollover at 00:03
reportcount every 1 minutes, rate
discardrollover at 11:30
dboptions suppresstriggers
-- write operations that could not be processed to discard file
RepError (DEFAULT, EXCEPTION)
RepError (DEFAULT2, DISCARD)
include ./dirprm/exception.prm
include ./dirprm/map_statements.inc

3. Update exceptions.prm file

$cat dirprm/exception.prm 
MACRO #exception_handler
BEGIN
-- Use the same Golden Gate Exceptions Table for all exception records
, TARGET GGADMIN.EXCEPTIONS
, COLMAP ( REP_GROUP = @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;

-- Mapping tables

MAP SCOTT.DRESSE #exception_handler();

4. Update the map_statements.inc file for each of the table

Map SCOTT.DRESSE, target SCOTT.DRESSE,
CompareCols (ON UPDATE ALL, ON DELETE ALL),
ResolveConflict (UPDATEROWMISSING, (DEFAULT, OVERWRITE)) ,
Resolveconflict (UPDATEROWEXISTS, (DEFAULT, OVERWRITE)) ,
Resolveconflict (DELETEROWMISSING, (DEFAULT, DISCARD)) ;
Map PRODUCTION_SOLERE_TAIGA.*, Target PRODUCTION_SOLERE_TAIGA.*, 
MAPEXCEPTION (TARGET PRODUCTION_SOLERE_TAIGA.TRXEXCEPTIONS,
INSERTALLRECORDS,
COLMAP(
REP_GROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME')
, TABLE NAME = @GETENV ('GGHEADER','TABLENAME')
, OPTYPE = @GETENV ('LASTERR','OPTYPE')
, ERRTYPE = @GETENV ('LASTERR','ERRTYPE')
, LOGPOSITION = @GETENV ('GGHEADER', 'LOGPOSITION')
, COMMITTIMESTAMP = @GETENV ('GGHEADER' , 'COMMITTIMESTAMP')
, TRAIL FILE = @GETENV ( 'GGFILEHEADER','FILENAME')
, CDRFAIL = @GETENV ('DELTASTATS', 'CDR_RESOLUTIONS_FAILED')
, CDRSUC = @GETENV ( 'DELTASTATS','CDR_RESOLUTIONS_SUCCEEDED')
, CDRDETECT = @GETENV ('DELTASTATS' , 'CDR_CONFLICTS')
, ERRNO = @GETENV ('LASTERR','DBERRNUM')
, DBERRMSG = @GETENV ('LASTERR' , 'DBERRMSG')
)
);

5. Stop the replicat and start. Ensure the replicat is running without any syntax issues.

6. Enforce the exception

Source:

DELETE scott.DRESSE WHERE SAL=7902;
COMMIT;

Target: 

UPDATE scott.DRESSE SET SAL=SAL+100 where EMPNO=7902;
COMMIT;

7. Verify the replicat status  (or) view the report

GGSCI> stats inrep1

GGSCI> view report inrep1

8. Select the rows from the exception table

SQL> SELECT * FROM GGADMIN.EXCEPTIONS;