Turning ORA-01403 into Evidence: Diagnosing GoldenGate Apply Errors with an Exceptions Table

Introduction

Anyone who has run Oracle GoldenGate for long has seen it: a Replicat abends with ORA-01403: no data found. An update or delete arrived for a row that isn’t on the target. The duplicate-key twin, ORA-00001: unique constraint violated, is not far behind.

The error itself tells you very little. It names the table and, at best, the key. It does not tell you why the row was missing. Was it never delivered? Deleted by someone else on the target? Lost in an initial-load gap? Matched on the wrong key? Finding out usually means reading report files, digging through the trail with Logdump, and querying source and target history while that history still exists. It is slow, and it is often abandoned once the Replicat is restarted and the lag is cleared.

In many environments the “fix” ends up being a parameter that hides the problem rather than explains it. HANDLECOLLISIONS makes the errors disappear, REPERROR (1403, DISCARD) moves them into a text file nobody reads.

This post shows a different approach, tested end to end in a lab: capture every ORA-01403 and ORA-00001 into a queryable exceptions table using REPERROR (…, EXCEPTION) and MAPEXCEPTION, while the Replicat keeps running. Each exception row carries the key, the full row the source intended, the source transaction and commit time, the trail position and the key type on both sides. From that one row we walk, step by step, to a proven root cause without opening the trail.

We also show how the same technique gives you a safe, evidence-driven path to retire HANDLECOLLISIONS from ongoing change data capture (CDC) replication, which is where this work started for us.

What you will find here

• Why ORA-01403 and ORA-00001 happen in CDC replication, and what the usual options really do
• A complete setup: exceptions table, a reusable macro in an include file, Replicat changes and REST-based deployment
• Real outputs from a GoldenGate 23.26 lab, including the mistakes we made and how we fixed them
• A worked investigation from exception row to the session that deleted the row
• Queries to find patterns across exceptions, and how to operate this at scale

Where ORA-01403 and ORA-00001 come from in CDC replication

GoldenGate applies each captured change to the target by key. Two outcomes break that model:

Error	Raised when	Integrated Replicat reports it first as
ORA-01403 no data found	An update or delete finds no row with that key on the target	ORA-26787: “The row to update or delete does not exist in the table.”
ORA-00001 unique constraint violated	An insert finds a row with that key already there	ORA-26799: “The row to update or insert violated the unique constraint.”

With Integrated Replicat, the inbound server raises the error first. GoldenGate then logs OGG-02544, “REPLICAT will retry in Direct mode”, and the documented action is to “use the REPERROR parameter and/or Conflict Detection Resolution rules to control how Replicat should handle a specific error or conflict.” In our lab, the direct-mode retry surfaced the classic ORA-00001 and ORA-01403, and rules on errors 1 and 1403 handled them.

It is clear that these errors are not normal once initial synchronization is complete: “Errors generated after initial synchronization indicate an abnormal condition and should be evaluated by someone who can determine how to resolve them” (HANDLECOLLISIONS reference).

In practice, the usual causes are:

Cause	What happened
Another writer on the target	A job, application or person changed or deleted a replicated row outside GoldenGate
Initial-load or instantiation gap	A row was created before the load point but not included in the load
Reprocessing or repositioning	The Replicat replayed changes it, or a load, had already applied
Cascade	One earlier failure (a missed insert) causes every later update and delete on that key to fail
Key mismatch or keyless table	Source and target keys differ, or the target has no key and is matched on every column
Filter or mapping gap	The insert was filtered out, but later updates pass the filter
Duplicate mapping	Two Replicats, or overlapping wildcards, apply the same table
Source capture gap	The change never made it into the trail

The error message tells you which table and, at best, which key. It cannot tell you which of these happened. That takes evidence, and that is what this post is about.

The usual options and their limits

REPERROR controls how Replicat responds to a database error. The default is “TRANSABORT for deadlocks, ABEND for all others”. It can be set globally, “or you can use it within a MAP statement to affect the tables specified in that statement”. The responses most often reached for with ORA-01403 and ORA-00001 are:

Approach	Replicat keeps running?	Failed operation applied?	Evidence you get	Effort to find the cause
Default (ABEND)	No. Stops on the first error	No	Report file: error, table, trail position	High: Logdump, then manual history queries, under pressure to restart
REPERROR (1403, IGNORE)	Yes	No	None	Not possible: the failure is silently dropped
REPERROR (1403, DISCARD)	Yes	No	Discard file: “Logs the offending operation to the discard file but continue processing”	High: text files to collect, parse and keep before they roll over or are purged
HANDLECOLLISIONS	Yes	Overwrites or converts (see next section)	Collision counts in report statistics	Not possible per row: the data is changed and the cause is hidden
REPERROR (1403, EXCEPTION) + MAPEXCEPTION	Yes	No	A row per failure in a table: key, full row, source commit time and log position, trail position, key types	Low: SQL queries first, Logdump only as fallback

Why the discard file alone falls short

The discard file does record the failed operation, and keeping it configured is good practice. As the only source of evidence, though, it has practical limits:

• It can be lost. By default “at startup, if a discard file exists, it is purged before new data is written” (Using the discard file). APPEND prevents that, but aged files from DISCARDROLLOVER are named with a sequence number (DISCARDROLLOVER) and only a limited set is kept.
• It can stop the Replicat. If the file reaches its maximum size, “the process abends” (DISCARDFILE). The documented default size differs between pages (3 MB for MEGABYTES, 50 MB in the default description), so always set MEGABYTES explicitly.
• It is text on the deployment. Counting failures per table, spotting repeat keys or bursts, and correlating with source transactions means building something to pull, parse, deduplicate and archive the files.

What exception capture adds

EXCEPTION is documented for exactly this: “Use this option in conjunction with an exceptions MAP statement or to work with the MAPEXCEPTION option of MAP” (REPERROR). With MAPEXCEPTION, “INSERTALLRECORDS causes all operation types to be applied to the exceptions table as INSERT operations. This is required to keep an accurate record of the exceptions and to prevent integrity errors on the exceptions table” (Using MAPEXCEPTION).

The result is evidence that is structured, queryable, durable, and created at the moment of failure, when the trail record still holds everything about the source change.

Why HANDLECOLLISIONS should not stay on in CDC replication

HANDLECOLLISIONS exists for a specific, temporary window. Oracle describes the errors it handles as ones that “occur during an initial load, when data from source tables is being loaded to target tables while Oracle GoldenGate is replicating transactional changes”, and instructs: “Disable HANDLECOLLISIONS after the transactional changes captured during the initial load are applied to the target tables, so that Replicat does not automatically handle subsequent errors” (HANDLECOLLISIONS reference, 23).

What it does to the data while it is on:

• Duplicate insert: “the static record that was applied by the initial load is overwritten by the change record in the trail” (HANDLECOLLISIONS reference, 26).
• Missing row on a primary-key update: “If the row with the old key is not found in the target, the change record in the trail is converted to an insert.” This requires all columns to be logged.
• Missing row on a delete: Replicat skips the delete (12.3 reference).

In every case the Replicat keeps running and the error disappears. That is exactly right during an initial-load overlap, and exactly wrong afterwards. Left on for ongoing CDC, it means:

• A row deleted on the target by someone else is never noticed.
• A duplicate caused by a second writer is silently overwritten with whatever arrived last.
• The causes in the previous section are never investigated, because nothing ever fails.
• Downstream reports can be wrong with no signal that anything happened.

Oracle also cautions specifically: “Error Handling of Integrated Replicat is not appropriate with HANDLECOLLISIONS. Oracle recommends that you use precise instantiation methods instead of using HANDLECOLLISIONS” (same 23 reference).

That is our situation. Our CDC Replicats had HANDLECOLLISIONS left on long after instantiation, and collision reports showed it handling collisions on many tables, every day. Simply removing it would turn every hidden collision into an abend. Exception capture lets us remove it and see every collision, table by table, without stopping replication.

How exception capture works

The failed operation is still not applied to the target. The difference is that it is recorded, with everything needed to explain it, and replication carries on. Every value in the exception row comes from the trail record the Replicat was holding when the error came back, or from the error itself. Nothing is looked up after the fact.

Only the errors you name are routed. Anything else, such as a foreign key violation or a value too large for a column, still abends the Replicat, which is what you want.

Lab environment

Everything below was run in a private lab. Credentials and host addresses are omitted.

Item	Value
GoldenGate	23.26.1.0.0 Microservices Architecture
Deployment	LAB, managed through the Administration Service REST API and Admin Client
Source	Oracle PDB SRCPDB, schema APPUSER
Target	Oracle PDB TGTPDB, schema APPUSER
Extract	EXT1, Integrated, LOGALLSUPCOLS, UPDATERECORDFORMAT COMPACT
Replicat	REP1, Integrated
Apply user	GGADMIN (credential alias TGTGG)
Test tables	HC_DUPLICATE and HC_MISSING_UPDATE: ID NUMBER primary key, DESCRIPTION, STATUS, UPDATED_AT TIMESTAMP
Other mapped tables	CUSTOMERS, HC_MISSING_DELETE, HC_NO_COLLISION (left unchanged)
Supplemental logging	All columns on the test tables

The Replicat started from this baseline, with no collision handling at all. Any duplicate or missing row would abend it:

REPLICAT REP1
USERIDALIAS TGTGG
STATOPTIONS REPORTDETAIL, RESETREPORTSTATS
REPORT AT 23:55
REPORTROLLOVER AT 00:05
REPORTCOUNT EVERY 60 MINUTES, RATE
DISCARDROLLOVER AT 00:00
MAP SRCPDB.APPUSER.CUSTOMERS,         TARGET APPUSER.CUSTOMERS;
MAP SRCPDB.APPUSER.HC_DUPLICATE,      TARGET APPUSER.HC_DUPLICATE;
MAP SRCPDB.APPUSER.HC_MISSING_UPDATE, TARGET APPUSER.HC_MISSING_UPDATE;
MAP SRCPDB.APPUSER.HC_MISSING_DELETE, TARGET APPUSER.HC_MISSING_DELETE;
MAP SRCPDB.APPUSER.HC_NO_COLLISION,   TARGET APPUSER.HC_NO_COLLISION;

The Extract was already capturing full row images:

EXTRACT EXT1
USERIDALIAS SRCCDB
EXTTRAIL ea
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE SRCPDB.APPUSER.CUSTOMERS;
TABLE SRCPDB.APPUSER.HC_DUPLICATE;
TABLE SRCPDB.APPUSER.HC_MISSING_UPDATE;
TABLE SRCPDB.APPUSER.HC_MISSING_DELETE;
TABLE SRCPDB.APPUSER.HC_NO_COLLISION;

INFO TRANDATA SRCPDB.APPUSER.HC_MISSING_UPDATE
Logging of supplemental transaction log data is enabled for table SRCPDB.APPUSER.HC_MISSING_UPDATE.
All columns supplementally logged for table SRCPDB.APPUSER.HC_MISSING_UPDATE.

The lab is Oracle to Oracle. The same pattern applies to other sources, with one difference noted where it matters: how much of an update’s row image the source captures.

Step-by-step setup

Four pieces: one logging table, one include file with a macro per table, a few Replicat parameter lines, and a REST-based deployment. No business table is changed.

Step 1: The exceptions table

One shared table for every table and Replicat on the target. In the lab it lives in APPUSER. In production we recommend the GoldenGate schema (for example GGADMIN), alongside GoldenGate’s own objects such as the heartbeat table, so no application schema is touched.

CREATE TABLE APPUSER.GG_EXCEPTIONS (
  EXC_ID        NUMBER GENERATED ALWAYS AS IDENTITY,
  REPLICAT      VARCHAR2(30),
  SRC_TABLE     VARCHAR2(256),
  TGT_TABLE     VARCHAR2(256),
  OP_TYPE       VARCHAR2(30),
  ERR_TYPE      VARCHAR2(30),
  ERR_NUM       NUMBER,
  ERR_MSG       VARCHAR2(4000),
  SRC_KEY_TYPE  VARCHAR2(10),
  TGT_KEY_TYPE  VARCHAR2(10),
  KEY_VALUES    VARCHAR2(4000),
  ROW_VALUES    VARCHAR2(4000),
  COMMIT_TS     VARCHAR2(40),
  SRC_CSN       VARCHAR2(100),
  SRC_XID       VARCHAR2(100),
  SRC_LOGPOS    VARCHAR2(100),
  TRAIL_SEQNO   NUMBER,
  TRAIL_RBA     NUMBER,
  LOGGED_TS     TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Non-unique on purpose, matches KEYCOLS in the macro
CREATE INDEX APPUSER.GG_EXCEPTIONS_IX1 ON APPUSER.GG_EXCEPTIONS (SRC_TABLE, TRAIL_SEQNO, TRAIL_RBA);

-- Needed only when the apply user does not own the table
GRANT INSERT, SELECT ON APPUSER.GG_EXCEPTIONS TO GGADMIN;

Three rules, each learned the hard way:

• No primary key or unique constraint other than the identity. A restart can reprocess a failed record, and any constraint violation on this table abends the Replicat.
• Every column nullable except the identity, so a partial record can never make the insert fail.
• A non-unique index on the KEYCOLS columns. The index is optional for capture (GoldenGate only inserts here) but recommended, and useful for queries.

Step 2: One macro per table, in an include file

The key and row expressions must name each table’s own columns, so each in-scope table gets its own macro. Everything else is identical, and the macros live in one include file per Replicat, so the Replicat parameter file grows by a single short call per table.

-- hc_exc.prm
-- Per-table exception capture into shared APPUSER.GG_EXCEPTIONS

MACRO #exc_HC_DUPLICATE
BEGIN
  NOHANDLECOLLISIONS,
  REPERROR (1, EXCEPTION),
  REPERROR (1403, EXCEPTION),
  MAPEXCEPTION (TARGET APPUSER.GG_EXCEPTIONS, INSERTALLRECORDS,
    KEYCOLS (SRC_TABLE, TRAIL_SEQNO, TRAIL_RBA),
    COLMAP (REPLICAT     = @GETENV('GGENVIRONMENT', 'GROUPNAME'),
            SRC_TABLE    = @GETENV('GGHEADER', 'TABLENAME'),
            TGT_TABLE    = 'APPUSER.HC_DUPLICATE',
            OP_TYPE      = @GETENV('GGHEADER', 'OPTYPE'),
            ERR_TYPE     = @GETENV('LASTERR', 'ERRTYPE'),
            ERR_NUM      = @GETENV('LASTERR', 'DBERRNUM'),
            ERR_MSG      = @GETENV('LASTERR', 'DBERRMSG'),
            SRC_KEY_TYPE = 'PK',
            TGT_KEY_TYPE = 'PK',
            KEY_VALUES   = @STRCAT('ID=', @STRNUM(ID)),
            ROW_VALUES   = @STRCAT('ID=', @STRNUM(ID),
                             '|DESCRIPTION=', @IF(@COLTEST(DESCRIPTION, MISSING), @IF(@COLTEST(BEFORE.DESCRIPTION, PRESENT), @BEFORE(DESCRIPTION), '<missing>'), @IF(@COLTEST(DESCRIPTION, NULL), '<null>', DESCRIPTION)),
                             '|STATUS=',      @IF(@COLTEST(STATUS, MISSING), @IF(@COLTEST(BEFORE.STATUS, PRESENT), @BEFORE(STATUS), '<missing>'), @IF(@COLTEST(STATUS, NULL), '<null>', STATUS)),
                             '|UPDATED_AT=',  @IF(@COLTEST(UPDATED_AT, MISSING), @IF(@COLTEST(BEFORE.UPDATED_AT, PRESENT), @BEFORE(UPDATED_AT), '<missing>'), @IF(@COLTEST(UPDATED_AT, NULL), '<null>', UPDATED_AT))),
            COMMIT_TS    = @GETENV('GGHEADER', 'COMMITTIMESTAMP'),
            SRC_CSN      = @GETENV('TRANSACTION', 'CSN'),
            SRC_XID      = @GETENV('TRANSACTION', 'XID'),
            SRC_LOGPOS   = @GETENV('GGHEADER', 'LOGPOSITION'),
            TRAIL_SEQNO  = @GETENV('RECORD', 'FILESEQNO'),
            TRAIL_RBA    = @GETENV('RECORD', 'FILERBA'),
            LOGGED_TS    = @DATENOW()))
END;

MACRO #exc_HC_MISSING_UPDATE
BEGIN
  NOHANDLECOLLISIONS,
  REPERROR (1, EXCEPTION),
  REPERROR (1403, EXCEPTION),
  MAPEXCEPTION (TARGET APPUSER.GG_EXCEPTIONS, INSERTALLRECORDS,
    KEYCOLS (SRC_TABLE, TRAIL_SEQNO, TRAIL_RBA),
    COLMAP (REPLICAT     = @GETENV('GGENVIRONMENT', 'GROUPNAME'),
            SRC_TABLE    = @GETENV('GGHEADER', 'TABLENAME'),
            TGT_TABLE    = 'APPUSER.HC_MISSING_UPDATE',
            OP_TYPE      = @GETENV('GGHEADER', 'OPTYPE'),
            ERR_TYPE     = @GETENV('LASTERR', 'ERRTYPE'),
            ERR_NUM      = @GETENV('LASTERR', 'DBERRNUM'),
            ERR_MSG      = @GETENV('LASTERR', 'DBERRMSG'),
            SRC_KEY_TYPE = 'PK',
            TGT_KEY_TYPE = 'PK',
            KEY_VALUES   = @STRCAT('ID=', @STRNUM(ID)),
            ROW_VALUES   = @STRCAT('ID=', @STRNUM(ID),
                             '|DESCRIPTION=', @IF(@COLTEST(DESCRIPTION, MISSING), @IF(@COLTEST(BEFORE.DESCRIPTION, PRESENT), @BEFORE(DESCRIPTION), '<missing>'), @IF(@COLTEST(DESCRIPTION, NULL), '<null>', DESCRIPTION)),
                             '|STATUS=',      @IF(@COLTEST(STATUS, MISSING), @IF(@COLTEST(BEFORE.STATUS, PRESENT), @BEFORE(STATUS), '<missing>'), @IF(@COLTEST(STATUS, NULL), '<null>', STATUS)),
                             '|UPDATED_AT=',  @IF(@COLTEST(UPDATED_AT, MISSING), @IF(@COLTEST(BEFORE.UPDATED_AT, PRESENT), @BEFORE(UPDATED_AT), '<missing>'), @IF(@COLTEST(UPDATED_AT, NULL), '<null>', UPDATED_AT))),
            COMMIT_TS    = @GETENV('GGHEADER', 'COMMITTIMESTAMP'),
            SRC_CSN      = @GETENV('TRANSACTION', 'CSN'),
            SRC_XID      = @GETENV('TRANSACTION', 'XID'),
            SRC_LOGPOS   = @GETENV('GGHEADER', 'LOGPOSITION'),
            TRAIL_SEQNO  = @GETENV('RECORD', 'FILESEQNO'),
            TRAIL_RBA    = @GETENV('RECORD', 'FILERBA'),
            LOGGED_TS    = @DATENOW()))
END;

What each part does:

Part	Purpose
NOHANDLECOLLISIONS	HANDLECOLLISIONS off for this MAP only, even if it is set globally
REPERROR (1, EXCEPTION), REPERROR (1403, EXCEPTION)	Send duplicate and missing-row errors to the exceptions mapping. Every other error still abends
MAPEXCEPTION (TARGET …, INSERTALLRECORDS, …)	Write each failed operation as an insert into the exceptions table
KEYCOLS (SRC_TABLE, TRAIL_SEQNO, TRAIL_RBA)	Key the exceptions table on columns the mapping always fills
@GETENV(‘GGHEADER’, …)	Table name, operation type, commit time and source log position from the trail record header
@GETENV(‘TRANSACTION’, …)	Source commit SCN/LSN and transaction ID (documented for Extract, observed in our 23.26 Replicat, validate availability on your version)
@GETENV(‘RECORD’, …)	Trail sequence number and RBA
@GETENV(‘LASTERR’, …)	The database error that was just raised
@COLTEST, @BEFORE, @IF, @STRCAT, @STRNUM	Build key and row text, with before-image fallback and explicit <null> / <missing> markers

For a fleet, nobody writes these by hand. We generate the include file from the target dictionary (columns, key columns and key type) and the source dictionary (source key type), one file per Replicat.

Step 3: Replicat parameters

Three lines above the MAPs, and one call on each in-scope MAP. Nothing else changes:

REPLICAT REP1
USERIDALIAS TGTGG
STATOPTIONS REPORTDETAIL, RESETREPORTSTATS
REPORT AT 23:55
REPORTROLLOVER AT 00:05
REPORTCOUNT EVERY 60 MINUTES, RATE
DISCARDFILE REP1.dsc, APPEND, MEGABYTES 500
DISCARDROLLOVER AT 00:00
INCLUDE <deployment_home>/etc/conf/ogg/hc_exc.prm

MAP SRCPDB.APPUSER.CUSTOMERS,         TARGET APPUSER.CUSTOMERS;
MAP SRCPDB.APPUSER.HC_DUPLICATE,      TARGET APPUSER.HC_DUPLICATE,      #exc_HC_DUPLICATE();
MAP SRCPDB.APPUSER.HC_MISSING_UPDATE, TARGET APPUSER.HC_MISSING_UPDATE, #exc_HC_MISSING_UPDATE();
MAP SRCPDB.APPUSER.HC_MISSING_DELETE, TARGET APPUSER.HC_MISSING_DELETE;
MAP SRCPDB.APPUSER.HC_NO_COLLISION,   TARGET APPUSER.HC_NO_COLLISION;

The discard file is kept as a safety net. With exception capture in place it stays essentially empty.

On startup the report shows each macro expanded in place, which is the quickest way to confirm the include worked:

INFO OGG-06505 MAP resolved (entry SRCPDB.APPUSER.HC_DUPLICATE): MAP "SRCPDB"."APPUSER"."HC_DUPLICATE", TARGET "TGTPDB".APPUSER.HC_DUPLICATE, NOHANDLECOLLISIONS, REPERROR (1, EXCEPTION), REPERROR (1403, EXCEPTION), MAPEXCEPTION (TARGET APPUSER.GG_EXCEPTIONS, INSERTALLRECORDS, KEYCOLS (SRC_TABLE, TRAIL_SEQNO, TRAIL_RBA), COLMAP (...)).

Step 4: Deploy with the REST API

We made every change through the Administration Service REST API, which is how we intend to roll it out across deployments.

ADM="http://<host>:<admin-port>"        # Administration Service of the deployment
H='-H Content-Type:application/json -H Accept:application/json'
# credentials in ~/.netrc (chmod 600), used with curl -n

# 1. Back up the current parameters (rollback copy)
curl -s -n $H $ADM/services/v2/replicats/REP1 > rep1_backup.json

# 2. Create the include file (POST worked on 23.26, PUT is the documented call to modify an existing file)
python3 -c "import json; print(json.dumps({'lines':open('hc_exc.prm').read().splitlines()}))" > hc_exc.json
curl -s -n $H -X POST $ADM/services/v2/config/files/hc_exc.prm -d @hc_exc.json

# 3. Stop, push the new parameters, start
curl -s -n $H -X PATCH $ADM/services/v2/replicats/REP1 -d '{"status":"stopped"}'
python3 -c "import json; print(json.dumps({'config':open('rep1.prm').read().splitlines()}))" > rep1.json
curl -s -n $H -X PATCH $ADM/services/v2/replicats/REP1 -d @rep1.json
curl -s -n $H -X PATCH $ADM/services/v2/replicats/REP1 -d '{"status":"running"}'

The parameter push is validated by the server before it is saved:

OGG-10183  Parameter file REP1.prm passed validity check.
OGG-00975  Replicat group REP1 starting.
OGG-15445  Replicat group REP1 started.

That check catches syntax errors. It does not catch apply-time problems such as an exceptions mapping with an unsuitable key, which only show up on the first real collision.

The include file is read only at startup, so any change to it needs a Replicat stop and start. In the lab we referenced it by its full path in the deployment’s parameter directory, which is where the config/files endpoint created it. On managed OCI GoldenGate there is no operating-system access: create the file through the REST API or the console’s Parameter Files page, and confirm on the first Replicat that the report shows the expanded MAPs. If an include file is not an option, the macros can be placed directly at the top of the Replicat parameter file instead.

Testing: forcing ORA-00001 and ORA-01403

We staged both anomalies the way they happen in real life: something changes the target behind replication’s back.

Test 1: Duplicate insert (ORA-00001)

A row is planted on the target, then the source inserts the same key.

-- TGTPDB
INSERT INTO APPUSER.HC_DUPLICATE (id, description, status, updated_at)
VALUES (9004, 'target-only row', 'TGT', SYSTIMESTAMP);
COMMIT;

-- SRCPDB
INSERT INTO APPUSER.HC_DUPLICATE (id, description, status, updated_at)
VALUES (9004, 'source row', 'SRC', SYSTIMESTAMP);
COMMIT;

Test 2: Update of a missing row (ORA-01403)

A row replicates normally, is deleted on the target, and is then updated on the source. The order matters: if the source update runs first, it applies normally and no error is raised.

-- 1. SRCPDB
INSERT INTO APPUSER.HC_MISSING_UPDATE (id, description, status, updated_at)
VALUES (9005, 'full row test', 'NEW', SYSTIMESTAMP);
COMMIT;

-- 2. TGTPDB, after the row arrives
DELETE FROM APPUSER.HC_MISSING_UPDATE WHERE id = 9005;
COMMIT;

-- 3. SRCPDB
UPDATE APPUSER.HC_MISSING_UPDATE SET status = 'UPDATED', updated_at = SYSTIMESTAMP WHERE id = 9005;
COMMIT;

What the Replicat report shows

These excerpts are from the first run of the same tests (keys 9001 and 9002). Integrated Replicat first reports each anomaly with an inbound-server error, then retries the operation in direct mode, where the classic error is raised and handled by our REPERROR rule:

WARNING OGG-02544  Unhandled error (ORA-26799: unique constraint APPUSER.SYS_C007580 violated for table APPUSER.HC_DUPLICATE with column values ("ID") = (9001)
ORA-00001: unique constraint (APPUSER.SYS_C007580) violated) while processing the record at SEQNO 7, RBA 3032 in Integrated mode. REPLICAT will retry in Direct mode.
WARNING OGG-01154  SQL error 1 mapping source table SRCPDB.APPUSER.HC_DUPLICATE to target table TGTPDB.APPUSER.HC_DUPLICATE. Database error: OCI Error ORA-00001: unique constraint (APPUSER.SYS_C007580) violated

WARNING OGG-02544  Unhandled error (ORA-26787: The row with key ("ID") = (9002) does not exist in table APPUSER.HC_MISSING_UPDATE
ORA-01403: no data found) while processing the record at SEQNO 7, RBA 3699 in Integrated mode. REPLICAT will retry in Direct mode.
WARNING OGG-01154  SQL error 1403 mapping source table SRCPDB.APPUSER.HC_MISSING_UPDATE to target table TGTPDB.APPUSER.HC_MISSING_UPDATE. Database error: No data found.

Rules on errors 1 and 1403 were enough, no separate rule for ORA-26787 or ORA-26799 was needed in this test.

What lands in the exceptions table

SELECT exc_id, tgt_table, op_type, err_num, key_values,
       src_key_type || '/' || tgt_key_type keys, src_csn, row_values
FROM   APPUSER.GG_EXCEPTIONS
ORDER  BY exc_id;

EXC_ID TGT_TABLE                 OP_TYPE         ERR_NUM KEY_VALUES KEYS  SRC_CSN
     3 APPUSER.HC_DUPLICATE      INSERT                1 ID=9004    PK/PK 3818868
     5 APPUSER.HC_MISSING_UPDATE SQL COMPUPDATE     1403 ID=9005    PK/PK 3820378

EXC_ID ROW_VALUES
     3 ID=9004|DESCRIPTION=source row|STATUS=SRC|UPDATED_AT=2026-10-28:22:24:39
     5 ID=9005|DESCRIPTION=full row test|STATUS=UPDATED|UPDATED_AT=2026-10-28:22:31:37

The Replicat stayed RUNNING throughout. On the target, row 9004 kept its own values (target-only row): the failed insert was not applied. The exception row is now the only place the source’s intended values survive, and that is exactly what we want to investigate.

One exception, every field:

SELECT x.field, x.value
FROM   XMLTABLE('/ROWSET/ROW/*'
         PASSING XMLTYPE(DBMS_XMLGEN.GETXML(
           'SELECT * FROM APPUSER.GG_EXCEPTIONS WHERE exc_id = 5'))
         COLUMNS field VARCHAR2(30) PATH 'name()', value VARCHAR2(4000) PATH '.') x;

FIELD           VALUE
EXC_ID          5
REPLICAT        REP1
SRC_TABLE       SRCPDB.APPUSER.HC_MISSING_UPDATE
TGT_TABLE       APPUSER.HC_MISSING_UPDATE
OP_TYPE         SQL COMPUPDATE
ERR_NUM         1403
ERR_MSG         No data found
KEY_VALUES      ID=9005
ROW_VALUES      ID=9005|DESCRIPTION=full row test|STATUS=UPDATED|UPDATED_AT=2026-10-28:22:31:37.929216000
TRAIL_SEQNO     7
TRAIL_RBA       5310
COMMIT_TS       2026-10-28 22:31:38.000000
LOGGED_TS       28-OCT-26 10.31.48.000000 PM
ERR_TYPE        DB
SRC_CSN         3820378
SRC_XID         2.14.650
SRC_LOGPOS      166797328
SRC_KEY_TYPE    PK
TGT_KEY_TYPE    PK

One row answers: where (Replicat, tables), what (operation, error), which record (key), what it should be (row), which source event (commit time, SCN, transaction) and whether the table is properly keyed on both sides.

Capturing the full row on compressed updates

Our first version of the macro produced this for a missing-row update:

EXC_ID OP_TYPE         ERR_NUM KEY_VALUES ROW_VALUES
     4 SQL COMPUPDATE     1403 ID=9003    ID=9003|DESCRIPTION=<missing>|STATUS=UPDATED|UPDATED_AT=2026-10-28:22:25:20.859015000

DESCRIPTION shows <missing> because the update only changed STATUS and UPDATED_AT. With UPDATERECORDFORMAT COMPACT, the update is written as a single trail record holding the before and after images. The after image carries the changed columns. Because the source logs all columns and the Extract uses LOGALLSUPCOLS, the unchanged columns are in the record too, in the before image.

For an unchanged column, the before value is the current value. So for every non-key column, the macro reads the after image first and falls back to the before image:

'|DESCRIPTION=', @IF(@COLTEST(DESCRIPTION, MISSING),
                     @IF(@COLTEST(BEFORE.DESCRIPTION, PRESENT), @BEFORE(DESCRIPTION), '<missing>'),
                     @IF(@COLTEST(DESCRIPTION, NULL), '<null>', DESCRIPTION))

The next missing-row update produced the complete row:

EXC_ID OP_TYPE         ERR_NUM KEY_VALUES ROW_VALUES
     5 SQL COMPUPDATE     1403 ID=9005    ID=9005|DESCRIPTION=full row test|STATUS=UPDATED|UPDATED_AT=2026-10-28:22:31:37.929216000

That is the row as the source has it after the update, which is exactly what the target should contain.

The markers are deliberate: <missing> means the trail did not carry that column at all, <null> means it was genuinely null. A table full of <missing> values is itself a finding: the source is not capturing full images for that table.

From exception row to root cause: a worked investigation

Exception 5 tells us the target did not have row 9005 when the source updated it at 22:31:38. The question every 1403 raises is the same: was the row ever there, and if so, what removed it? We answered it in five queries, without reading the trail.

Step 1: Has this key failed before?

SELECT exc_id, op_type, err_num, commit_ts, src_csn
FROM   APPUSER.GG_EXCEPTIONS
WHERE  tgt_table = 'APPUSER.HC_MISSING_UPDATE'
AND    key_values = 'ID=9005'
ORDER  BY src_csn;

Only exception 5 came back. Had an earlier insert for the same key failed, the 1403 would simply be a knock-on effect, and the investigation would move to that first failure.

Step 2: What did the source do, and when?

The exception row carries the source commit SCN (SRC_CSN), so the exact source change can be recovered with a Flashback Version Query. Here is the same lookup for an earlier exception from our first test run (key 9002), using nothing but its SRC_CSN:

-- SRCPDB
SELECT versions_startscn, versions_operation, t.*
FROM   APPUSER.HC_MISSING_UPDATE VERSIONS BETWEEN SCN 3812000 AND MAXVALUE t
WHERE  versions_startscn = 3812515;

VERSIONS_STARTSCN V   ID DESCRIPTION    STATUS
          3812515 U 9002 before update  UPDATED

For a SQL Server source, the equivalent is the CDC change table for the table, filtered by key or by LSN.

Step 3: Did the row reach the target?

-- TGTPDB
SELECT versions_startscn, versions_starttime, versions_endtime,
       versions_operation, versions_xid, t.*
FROM   APPUSER.HC_MISSING_UPDATE VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE t
WHERE  id = 9005
ORDER  BY versions_startscn NULLS FIRST;

VERSIONS_STARTSCN START_TIME   END_TIME     V  ID    STATUS
          3686462 22:31:12     22:31:30     I  9005  NEW
          3686502 22:31:30                  D  9005  NEW

Version XIDs: insert 0A00180088020000, delete 08001800C2020000.

The row did arrive at 22:31:12, and was deleted at 22:31:30, eight seconds before the source update committed. That rules out a load gap, a reposition, or an earlier failure.

Our first attempt used a one-day window and failed with ORA-30052: invalid lower limit snapshot expression. Switching the lower bound to SCN MINVALUE fixed it, returning whatever history was available.

Step 4: Who changed it?

SELECT xid, start_scn, logon_user, operation, undo_sql
FROM   flashback_transaction_query
WHERE  xid = HEXTORAW('08001800C2020000');

XID              START_SCN LOGON_USER OPERATION UNDO_SQL
08001800C2020000   3686500 SYS        DELETE    insert into "APPUSER"."HC_MISSING_UPDATE"("ID","DESCRIPTION","STATUS","UPDATED_AT") values ('9005','full row test','NEW',TO_TIMESTAMP('28-OCT-26 10.31.08.675103 PM')),
08001800C2020000   3686500 SYS        BEGIN

And for the insert, for comparison:

XID              LOGON_USER OPERATION
0A00180088020000 GGADMIN    INSERT
0A00180088020000 GGADMIN    BEGIN

Step 5: Conclude

Replication delivered the row (GGADMIN). Another session (SYS) deleted it on the target. The ORA-01403 was caused by a second writer on the target, proven with transaction, user and time.

Two cautions from this step:

• UNDO_SQL restores the row as it was before the delete (STATUS = ‘NEW’), not as the source now has it (STATUS = ‘UPDATED’). It is evidence, not a repair. Repair from the current source row after checking for subsequent changes. The exception row records the source values at the time of the failed operation.
• LOGON_USER names a database user, not a program. If several tools share one service account, you need auditing to tell them apart (covered in the Operating section).

When target history has already aged out, the trail position in the exception row (TRAIL_SEQNO, TRAIL_RBA) takes you straight to the record in Logdump. That is the fallback, not the starting point.

Finding patterns across exceptions

A single exception explains a single failure. Patterns across many exceptions explain the system, and they usually point at the cause before you open a single row.

Pattern	What to look at	Usually points to
Problem tables	Count by table, operation and error, distinct keys, first and last seen	Where to start
Bursts in time	Count per table per hour	Batch jobs, reloads, restarts or repositioning
Repeat keys	Keys with more than one failure, in source SCN order	A cascade from one missed insert
Multi-table transactions	One source transaction failing on several tables	One upstream event, not many problems
Key type	Source or target key type other than PK, or the two differ	Keyless or mismatched keys producing false 1403s
Lag gap	LOGGED_TS minus COMMIT_TS	The Replicat was down or lagging when it happened
Missing columns	Count of <missing> per table	Source not capturing full row images

-- Problem tables
SELECT tgt_table, op_type, err_num,
       COUNT(*)                   AS occurrences,
       COUNT(DISTINCT key_values) AS distinct_keys,
       MIN(logged_ts)             AS first_seen,
       MAX(logged_ts)             AS last_seen
FROM   APPUSER.GG_EXCEPTIONS
GROUP  BY tgt_table, op_type, err_num
ORDER  BY occurrences DESC;

-- When it happens
SELECT tgt_table, TRUNC(logged_ts, 'HH24') AS hour, COUNT(*) AS occurrences
FROM   APPUSER.GG_EXCEPTIONS
GROUP  BY tgt_table, TRUNC(logged_ts, 'HH24')
ORDER  BY hour, tgt_table;

-- Repeat keys
SELECT tgt_table, key_values, COUNT(*) AS failures,
       LISTAGG(op_type || ':' || err_num, ', ') WITHIN GROUP (ORDER BY src_csn) AS sequence
FROM   APPUSER.GG_EXCEPTIONS
GROUP  BY tgt_table, key_values
HAVING COUNT(*) > 1
ORDER  BY failures DESC;

-- One source transaction, many tables
SELECT src_xid, MIN(commit_ts) AS commit_ts, COUNT(*) AS failures,
       COUNT(DISTINCT tgt_table) AS tables
FROM   APPUSER.GG_EXCEPTIONS
GROUP  BY src_xid
HAVING COUNT(DISTINCT tgt_table) > 1;

-- Weak keys
SELECT DISTINCT tgt_table, src_key_type, tgt_key_type
FROM   APPUSER.GG_EXCEPTIONS
WHERE  src_key_type <> 'PK' OR tgt_key_type <> 'PK' OR src_key_type <> tgt_key_type;

To split ROW_VALUES into one line per column for a given exception:

SELECT REGEXP_SUBSTR(p.pair, '^[^=]+')                AS column_name,
       REGEXP_SUBSTR(p.pair, '=(.*)$', 1, 1, NULL, 1) AS column_value
FROM   APPUSER.GG_EXCEPTIONS e,
       LATERAL (SELECT LEVEL AS pos, REGEXP_SUBSTR(e.row_values, '[^|]+', 1, LEVEL) AS pair
                FROM   dual
                CONNECT BY LEVEL <= REGEXP_COUNT(e.row_values, '\|') + 1) p
WHERE  e.exc_id = 5
ORDER  BY p.pos;

The LATERAL matters. Our first version used CONNECT BY directly against the table and returned 66 jumbled rows, because the hierarchy is built across every row before WHERE filters it.

The blind spot. A target table with no primary or unique key never raises ORA-00001: duplicates are simply inserted. Its anomalies never reach the exceptions table at all, and its updates and deletes are matched on every column, which produces false 1403s. Keep a dictionary-based list of key types for every replicated table, not just the ones that fail.

Using exception capture to retire HANDLECOLLISIONS

The goal is not a quiet Replicat. It is proven root causes, fixed at the source, and Replicats running with neither HANDLECOLLISIONS nor any collision suppression. Exception capture is the temporary bridge.

Scope it per table

HANDLECOLLISIONS can be turned off for individual tables: “A MAP specification always overrides the global specification” (HANDLECOLLISIONS reference). That is why the macro starts with NOHANDLECOLLISIONS. Each in-scope table moves to exception capture while everything else keeps its current behavior, and the global setting is removed later in a separate, deliberate change.

Start from evidence. In our environment, collision reports built from Replicat statistics showed which tables actually had collisions. Only those tables need exception capture, the rest can lose HANDLECOLLISIONS directly.

Roll out in waves

1. Pilot: one low-volume, lower-impact environment.
2. Measure: exceptions per table per hour, Replicat lag, exceptions table growth.
3. Investigate: every exception gets a cause, proven or suspected, recorded with its evidence.
4. Fix at the source: the right place for each fix depends on the cause.
5. Expand: similar environments next, then higher-volume ones, each starting with every fix already proven.

Cause	Fix once in	Effect
Load gap, reprocessing, duplicate mapping	Instantiation and restart procedures, mapping standards	Every future environment
Key mismatch, keyless tables, filter gaps	The standard Replicat template (KEYCOLS, keys, filters)	Every Replicat built from it
Another writer on the target	That environment’s jobs and access	That environment
Source capture gap	That source’s capture configuration	That source

Per table, in order

1. Table identified as having collisions
2. HANDLECOLLISIONS off, exception capture on
3. Cause proven from the exception rows
4. Cause fixed at its source
5. Zero exceptions over representative business cycles, with collection proven
6. Exception rule removed: the table now abends on any collision, as it should

End state

• No HANDLECOLLISIONS, globally or per MAP
• No REPERROR (…, EXCEPTION) or DISCARD for collisions
• A named discard file kept as a safety net
• Every replicated table has a usable key on both sides
• Parameter drift checks that flag HANDLECOLLISIONS if it ever returns

Operating it

Scale

Only tables with collisions need exception capture. Macros are generated per Replicat from the dictionaries: target columns, key columns and key type, plus the source key type. Each Replicat gets one include file, and its parameter file grows by one short call per table. Two documented constraints shape the generator:

• Config file names follow the pattern ^(GLOBALS|ENCKEYS|[a-zA-Z0-9_$]*[.](conf|prm|prm[.]backup|inc|properties))$, 5 to 255 characters (REST: Retrieve Configuration File). So names like exc_rep1.prm work and hyphens do not.
• Each parameter line pushed through the REST API is limited to 4,095 characters (REST: Update Replicat). Very wide tables need their ROW_VALUES expression split across lines.

If a table has thousands of collisions a day, measure before switching it: the cost is less the exception insert and more the failed operation and its direct-mode retry. Start with the lower-volume tables.

Keep the evidence, then purge it

Rows accumulate only on failure, but they do accumulate. Archive evidence according to your retention policy before purging it. The following example only deletes rows older than 30 days, it does not archive them:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'GG_EXCEPTIONS_PURGE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DELETE FROM APPUSER.GG_EXCEPTIONS WHERE logged_ts < SYSTIMESTAMP - INTERVAL ''30'' DAY; COMMIT; END;',
    repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
    enabled         => TRUE);
END;
/

Make sure someone looks

The Replicat no longer stops, so a person has to be told. A scheduled query for new rows, sent to whatever alerting you already use, is enough:

SELECT tgt_table, op_type, err_num, COUNT(*) occurrences
FROM   APPUSER.GG_EXCEPTIONS
WHERE  logged_ts > SYSTIMESTAMP - INTERVAL '15' MINUTE
GROUP  BY tgt_table, op_type, err_num;

For tables where a single wrong row matters more than lag, you may prefer to capture the exception and then stop. Test that pattern in your own environment before relying on it.

Investigate within the retention window

The exception row is permanent until purged. The history around it is not:

• Target history depends on undo, which “is retained for the time specified by the undo_retention parameter” (Using Oracle Flashback Technology). The transaction query also needs SELECT ANY TRANSACTION and supplemental logging (ALTER DATABASE ADD SUPPLEMENTAL LOG DATA) on the target, per the same page.
• Source history: Flashback on Oracle sources, CDC change tables (with their own cleanup retention) on SQL Server sources.
• Trail files, kept per your retention policy.

Investigate within the shortest of the three, which in practice means every shift, not every week.

Name the second writer

LOGON_USER identifies a database user. When tools share service accounts, a unified audit policy on the replicated tables records every change made by anyone other than the GoldenGate user (Unified auditing conditions):

CREATE AUDIT POLICY gg_other_writers
  ACTIONS DELETE ON APPUSER.HC_MISSING_UPDATE,
          INSERT ON APPUSER.HC_MISSING_UPDATE,
          UPDATE ON APPUSER.HC_MISSING_UPDATE
  WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') <> ''GGADMIN'''
  EVALUATE PER STATEMENT;

AUDIT POLICY gg_other_writers;

Security

ROW_VALUES holds real row data. Restrict who can read the exceptions table, apply the same handling as the source tables, and include it in your retention policy.

Conclusion

ORA-01403 and ORA-00001 are symptoms. The causes behind them, such as a second writer, a load gap, a key that doesn’t identify the row, or a replayed change, stay invisible as long as the only choices are to abend, ignore, discard to a file, or let HANDLECOLLISIONS paper over them.

Routing those errors into an exceptions table with REPERROR (…, EXCEPTION) and MAPEXCEPTION changes the question from “why did the Replicat stop?” to “what does the evidence say?”. Each failure arrives with its key, its intended row, its source transaction and its trail position. From there, a short chain of queries proves the cause, in our lab down to the session that deleted the row, without opening a trail file.

It also gives a safe way out of HANDLECOLLISIONS for CDC replication: turn it off table by table, see every collision, fix what causes it, and finish with Replicats that abend only when something genuinely needs attention.

Build it once, test it with forced collisions, and generate it for the rest.

References

Oracle GoldenGate

• HANDLECOLLISIONS | NOHANDLECOLLISIONS (23)
• HANDLECOLLISIONS | NOHANDLECOLLISIONS (26)
• REPERROR (23)
• Using MAPEXCEPTION (26)
• @GETENV (19c)
• Column conversion functions summary (26)
• UPDATERECORDFORMAT (26)
• LOGALLSUPCOLS (21.3)
• ADD TRANDATA (23)
• Defining a macro (21.3)
• INCLUDE (26)
• Using the discard file (26)
• DISCARDFILE (21.3)
• DISCARDROLLOVER (21.3)
• OGG-02544 and OGG-06439
• REST API: Update Replicat (19c)
• REST API: Retrieve Configuration File (23)
• REST API: Modify Configuration File (12.3)
• Administration Service parameter files (26)
• Using the REST API with OCI GoldenGate

Oracle Database

• ORA-26787 and ORA-26799
• Using Oracle Flashback Technology (23)
• Unified auditing: configurable conditions (19c)