OGG: How to create UNIQUE index for OGG REPLICAT that is not used by DATABASE APPLICATION SQL (Doc ID 2355832.1)

Last updated on FEBRUARY 19, 2020

Applies to:

Symptoms

1) Integrated replicat abends with OGG-01169

ERROR OGG-01169 Oracle GoldenGate Delivery for Oracle, <replicat>.prm: Encountered an update where all key columns for target table <USER>.<TABLE> are not present.

2) No index defined that can be used.

Example:

Available Index is a Unique function-based index:

CREATE UNIQUE INDEX "<USER>"."<TABLE>" ON "<USER>"."<TABLE>" ("EMPLID", "EMPL_RCD", "EFFDT" DESC, "EFFSEQ" DESC)....

Function based Index:

<USER> <TABLE> <USER> <TABLE> EMPLID
<USER> <TABLE> <USER> <TABLE> EMPL_RCD
<USER> <TABLE> <USER> <TABLE> SYS_NC00163$
<USER> <TABLE> <USER> <TABLE> SYS_NC00164$

select owner, index_name, index_type, table_owner, table_name, uniqueness, visibility, status
from dba_indexes where table_owner='<USER>'
and table_name='<TABLE>';

OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENES VISIBILIT STATUS
<USER> <TABLE> FUNCTION-BASED NORMAL <USER> <TABLE> UNIQUE VISIBLE VALID

3) KEYCOLS defined in replicat parameter file

MAP SCHEMA.<TABLE>, KEYCOLS(EMPLID, EMPL_RCD, EFFDT, EFFSEQ), TARGET <USER>.<TABLE>;

4) KEYCOLS supplimentally logged at the source.

Columns supplementally logged;

<USER>.<TABLE> <TABLE> EMPLID 1 LOG
<USER>.<TABLE> <TABLE> EMPL_RCD 2 LOG
<USER>.<TABLE> <TABLE> EFFDT 3 LOG
<USER>.<TABLE> <TABLE> EFFSEQ 4 LOG

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.