Quickly Troubleshoot Oracle GoldenGate Using Exception Handler

By default, a Replicat process ABENDS when the data being modified doesn’t exist in the target database. Learn how to troubleshoot Oracle GoldenGate Using Exception Handler mechanism, identify if there are any missing prequisites commands like “add trandata” on the source.

When this happens you need to troubleshoot the issue and correct it before the Replicat process can proceed. But this wouldn’t be easy, as sometimes it can take a long time to identify the cause and resolve it.

As a result, the data continues to accumulate and the LAG continues to increase until this is resolved.

In such cases we may instead want to allow the Replicat to continue applying the rest of the records while keeping track of the failed records and error messages.

Oracle GoldenGate provides an excellent way of managing such failed records and errors message by saving them to an exception table.

Here let’s look at how we can map the failed record information into an exception table for a one-way replication configuration.

Environment Details

Info Source/Primary Target/Secondary
DB Name orcl orcl
DB Version 11.2.0.3  11.2.0.3
Schema HR  HR
Table Name  COUNTRIES  COUNTRIES
OGG Version 11.2.1.0.20 11.2.1.0.20

Enable Supplemental logging

Add the supplemental logging for the table.

GGSCI > dblogin userid ggs, password oracle
Successfully logged into database.

GGSCI> add trandata hr.countries

2014-05-17 02:09:16 WARNING OGG-00869 No unique key is defined for table 'COUNTRIES'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data using add trandata has been enabled for table HR.COUNTRIES.

Note: ADD SCHEMATRANDATA can be used instead of the ADD TRANDATA command when DDL replication is not enabled.

Configure Extract and Pump Process on Source System

Primary Extract

$ cd $GG_HOME
$./ggsci

GGSCI> Edit Params Ext1

EXTRACT Ext1
USERID ggs, PASSWORD oracle
EXTTRAIL ./dirdat/et
GETUPDATEBEFORES
TABLE HR.COUNTRIES;

xxx(Save and Exit the file)

GGSCI > Add Extract Ext1, TranLog, Begin Now
EXTRACT added.

GGSCI > Add extTrail ./dirdat/et, Extract Ext1, Megabytes 100
EXTTRAIL added.

GGSCI > Start Extract Ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI> Info Extract Ext1

EXTRACT         EXT1       Last Started 2014-05-17 02:10 Status RUNNING
Checkpoint      Lag          00:00:35 (updated 00:00:07 ago)
Log Read Checkpoint    Oracle Redo Logs
2014-05-17 02:09:57 Seqno 90, RBA 18089488 SCN 0.0 (0).

Pump Extract

GGSCI> EDIT PARAMS Pump1

EXTRACT Pump1
RMTHOST 192.168.1.30, MGRPORT 7809
RMTTRAIL ./dirdat/et
PASSTHRU
TABLE HR.COUNTRIES;

(Save and Exit the file)

GGSCI> Add Extract Pump1, exttrailsource ./dirdat/et
EXTRACT added.

GGSCI> Add rmtTrail ./dirdat/et, Extract Pump1, Megabytes 100
RMTTRAIL added.

GGSCI> Start Extract Pump1

START request to MANAGER ...
EXTRACT PUMP1 starting

GGSCI> Info Extract Pump1

EXTRACT PUMP1 Last Started 2014-05-17 02:16 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:04:36 ago)
Log Read Checkpoint File ./dirdat/et000000
First Record RBA 0

 Configure Exception Table and Replicat

You can create one exception table to track failed transaction information for each destination table that is part of replication. However this will add extra layer of complexity to managing multiple exception tables.

In this demonstration we will create one single exception table for all schema tables. This will allow us to manage failed records for all the tables in just one exception table.

Creating Exception Table

SQL> CREATE TABLE GGS.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  xxx    NOT NULL,
LOGPOSITION NUMBER NOT NULL,
COMMITTIMESTAMP TIMESTAMP(6) NOT NULL,
GGS_FILENAME VARCHAR2(256 BYTE),
CDRFAIL NUMBER,
CDRSUC NUMBER,
CDRDETECT NUMBER
);
Table created.

SQL> CREATE UNIQUE INDEX GGS.EXCEPTIONS_PK ON GGS.GGS_EXCEPTIONS
(LOGRBA, LOGPOSITION, COMMITTIMESTAMP);
Index created.

SQL> ALTER TABLE GGS.GGS_EXCEPTIONS ADD (
CONSTRAINT EXCEPTIONS_PK
PRIMARY KEY
(LOGRBA, LOGPOSITION, COMMITTIMESTAMP)
USING INDEX GGUSER.EXCEPTIONS_PK);
Table altered.

SQL> GRANT DELETE, INSERT, SELECT, UPDATE ON GGS.GGS_EXCEPTIONS TO GGS;
Grant succeeded.

Creating Replicat Process

GGSCI> EDIT PARAMS Rep1

REPLICAT Rep1
USERID GGS, PASSWORD oracle
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/Rep1.dsc, APPEND
REPERROR (DEFAULT, EXCEPTION)
MAP HR.COUNTRIES, TARGET HR.COUNTRIES;

---new exception handler
MACRO #exception_handler
BEGIN
-- Use the same Golden Gate Exceptions Table for all exception records
, TARGET GGUSER.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;

MAP HR.COUNTRIES #exception_handler();
(Save and Exit File)

GGSCI> Add Replicat Rep1, ExtTrail ./dirdat/et
REPLICAT added.

GGSCI> Info Replicate Rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI> Info Replicat Rep1
REPLICAT REP1 Last Started 2014-05-17 02:24 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint  File ./dirdat/et000000
2014-05-17 02:23:04.233833 RBA 1183

Verify that Exception Table Works

Insert a row in source HR.COUNTRIES table.

This row should flow to target table without any issues. There should not be any records inserted in Exception table.

Source Database
SQL> Insert into HR.COUNTRIES values (‘US’, ‘USA’,01);
1 row created.

SQL> Commit;
Commit complete.
SQL> select count(*) from HR.COUNTRIES;
COUNT(*)
----------
   1

Target Database
SQL> select count(*) from HR.COUNTRIES;
COUNT(*)
----------
1

SQL> select count(*) from GGUSER.EXCEPTIONS;
COUNT(*)
----------
0

There are no records in Exception table.

Scenario

Let’s delete the row on target table and update the same row on the source table. This will cause data inconsistency between source and target. The replicat will not found the data to be updated, so instead of ABENDING the replicat will write the failed record information into Exception table and continue with the replication process.

Target Database
SQL> delete from HR.COUNTRIES;
1 row deleted.

SQL> commit;
Commit complete.

Source Database
SQL> update HR.COUNTRIES set REGION_ID=02 where COUNTRY_ID='US';
1 row updated.

SQL> commit;
Commit complete.

GGSCI> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:09
EXTRACT RUNNING PUMP1 00:00:00 00:00:10

Target Database
SQL> select count(*) from HR.COUNTRIES;
COUNT(*)
----------
0

SQL> select count(*) from GGS.GGS_EXCEPTIONS;
COUNT(*)
----------
1

SQL> set lines 200
SQL> set pages 200
SQL> select * from ggs.ggs_exceptions;

REP_NAME TABLE_NAME DML_DATE ERRNO
-------- --------------------------------------------------
DBERRMSG
-------- --------------------------------------------------
OPTYPE ERRTYPE LOGRBA LOGPOSITION COMMITTIMESTAMP
-------- --------------------------------------------------
GGS_FILENAME
-------- --------------------------------------------------
CDRFAIL CDRSUC CDRDETECT
-------- --------------------------------------------------
REP1 HR.COUNTRIES 1403
OCI Error ORA-01403: no data found, SQL <UPDATE "HR"."COUNTRIES" SET "COUNTRY_ID" = :a3,"COUNTRY_NAME" = :a4,"REGION_ID" = :a5 WHERE "COUNTRY_ID" = :b0 AND "COUNTRY_NAME" is NULL AND "REGION_ID" = :b2
AND ROWNUM = 1>
PK UPDATE DB 0 18827792 17-MAY-14 02.38.24.068458 AM
./dirdat/et000000
0 0 0

GGSCI> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGERRUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:10

Comments

  1. sunilmethuku@gmail.com'Sunil says

    Hi,

    In goldengate i want all the replications to happen only when a certain condition satisfies else it should wait until the condition satisfies.

    Suppose I’m having a Table ‘T’ with only one row and a column ‘Flag’ in it. For every replication happening in the target database i want to make sure it happens only when the value of the ‘Flag’ is ‘A’ else the replication should wait until the value of the ‘Flag ‘ becomes ‘A’.

    I’m not sure if it is possible, can you please help me and tell if it is possible and how can i make it possible.

  2. andreejohan@gmail.com'andree says

    Hi,
    you have a very good topic.
    But please help me why
    , CDRFAIL = @GETENV(“DELTASTATS”,”CDR_RESOLUTIONS_FAILED”)
    , CDRSUC = @GETENV(“DELTASTATS”,”CDR_RESOLUTIONS_SUCCEEDED”)
    , CDRDETECT = @GETENV(“DELTASTATS”,”CDR_CONFLICTS”))
    values are 0, 0, 0.
    and logRBA also 0, this value is important also.

    Best Regards,
    Andree

Leave a Reply

Your email address will not be published.