Key column <column-name> (1) is missing from delete on table <schema.table-name>, table has no PK but has UI with a DESC column

(Doc ID 2080963.1)

Last updated on JANUARY 12, 2017

Applies to:

Oracle GoldenGate - Version 11.1.1.0.0 to 12.1.3.0.0 [Release 11.1.1 to 12.1]
Information in this document applies to any platform.

Goal

When replicating using GoldenGate, the Discard file shows messages similar to:

Key column column-name (1) is missing from delete on table schema.table-name
Missing 1 key columns in delete for table schema.table-name.
...
Aborting transaction on /u01/goldengate/ET beginning at seqno 415 rba 69903534
  error at seqno 415 rba 69903534
Problem replicating schema.table-name to schema.table-name
Mapping problem with delete record (target format)...
*
COLUMN-0 = FCARC
000000: 46 43 41 52 43 |FCARC |

COLUMN-2 = 4
000000: 34 |4 |

COLUMN-3 = 1
000000: 31 |1 |

*

What is specific about this situation, is that the table has no keys, but has a Unique Index.
(NOTE: The GoldenGate Discard file reference COLUMN-0 equates to the Oracle database column COLUMN-1, et cetera...)

CREATE UNIQUE INDEX "<SCHEMA>"."<INDEX-NAME>" ON "<SCHEMA>"."<TABLE-NAME>" ("<COLUMN-1>", "<COLUMN-2>" DESC, "<COLUMN-3>", "<COLUMN-4>");

To confirm the columns are indexed:

SQL> select COLUMN_NAME, DESCEND from DBA_IND_COLUMNS where INDEX_NAME='<INDEX-NAME>' and TABLE_OWNER='<SCHEMA>';
COLUMN_NAME    DESCEND
-------------  --------
<COLUMN-1>     ASC
SYS_NC00008$   DESC
<COLUMN-3>     ASC
<COLUMN-4>     ASC

Note that the Unique Index DESC'ending column has a system generated name, rather than the actual column name.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms