golden gate index issue

[Roshan]

Oracle Golden Gate 18c
Oracle DB 12.1.0.2
Solaris 11

Hello Erman,

the lag checkpoint for replication is increasing and RBA is not moving.

I enabled trace and the following output was captured.

trace file:
old obj name = CB_INVOICE
nullflags_knglxr is 0Xe1
 Bind variable count 0
knalfGenRowHashValues: ERR.: 26688
Missing key in lcr
Table: "CBSOGG"."CB_INVOICE", Index# 1253950, Columns: ("ACCOUNT_LINK_CODE_N", "TRANS_NUM_V") is missing: "ACCOUNT_LINK_CODE_N"
Dumping lcr:
Dumping lcr a04775a40 : type 3
 Dumping external row LCR
 (xid,scn,instnum,scnseq,flags,time,compat,compat_num)
=( 0x002a.02b.0008239c,0x0000.00000000,844,0,0x801, 06/04/2021 10:12:59,9.2.0.0.0,0)
header null flags = 0Xfdfc
  gdbnm=a047758e8, NULL
 tag is null
 uba is null
 tde key id=0
 Message tracking: null
 Header extra: nullflags=0xfc
   nos_xid=a04775378, 42.43.533404
   pos=a04775960, length=12, isnull=N, 333237323036383635343130
   flags=0
   execusr=0
    logon_user (null)
   rba (null)

I deleted all indexes with ACCOUNT_LINK_CODE_N but still replication hangs.

target table DDL is shown below
cbinvoice.txt

If I disable all contraints on target table replication will not start becausing structure of source and target will be different.
Kindly advise why dropping index is not working?

Thanks,

Roshan

[Roshan]

I also enabled LOGALLSUPCOLS in extract but still I am facing same issue.

[ErmanArslansOracleBlog]

This is documented Roshan : Goldengate Integrated Replicat Running Slow When Hitting ORA-26688: missing key in LCR (Doc ID 2125301.1)

LOGALLSUPCOLS is for the new records.. Droppinng indexes is the for the old ones..

You may see that error unless you drop the index(es) at target's table(s) that are referencing a missing column for the LCR. You should consider dropping index with # 1253950. (on the target..)

[Roshan]

Hi Erman,

can you please advise how I can find the index number?
index.PNG

Thanks,

Roshan

[ErmanArslansOracleBlog]

lets check it from dba_objects

object_id=1253950

what do you get?

[Roshan]

Hi,

I got the issue on a new table

Missing key in lcr
Table: "CBSOGG"."CB_SUBS_PROVISIONING", Index# 1463145, Columns: ("ACCOUNT_LINK_CODE_N", "ACTION_DATE_DT", "CHRONO_NUM_N", "INSTANCE_ID_N", "ORDER_REF_NO_V", "PROCESS_ID_N", "PROCESS_NO_N", "SERVICE_ID_V", "USER_CODE_N") is missing: "ACCOUNT_LINK_CODE_N"
Dumping lcr:
Dumping lcr 991cbbdf8 : type 3

Please find below output.
export.xlsx

Thanks,

Roshan

[ErmanArslansOracleBlog]

Interesting.. That index# is weird.. It is not the object id it seems. No need to force it.. that number may have a meaning only in GG's world..

Check the target table. Check the index that is indexing the following columns -> ACCOUNT_LINK_CODE_N", "ACTION_DATE_DT", "CHRONO_NUM_N", "INSTANCE_ID_N", "ORDER_REF_NO_V", "PROCESS_ID_N", "PROCESS_NO_N", "SERVICE_ID_V", "USER_CODE_N

That is the index you need to focus.

[Roshan]

Hi,

I noticed whenever I remove the unique key index, the error is gone.

Regards,

Roshan

[ErmanArslansOracleBlog]

So it is consistent with what I said earlier about this subject.

--> reminder : This is documented Roshan : Goldengate Integrated Replicat Running Slow When Hitting ORA-26688: missing key in LCR (Doc ID 2125301.1)

LOGALLSUPCOLS is for the new records.. Droppinng indexes is the for the old ones..

You may see that error unless you drop the index(es) at target's table(s) that are referencing a missing column for the LCR. You should consider dropping index with # 1253950. (on the target..)