OGG-01169

[Roshan]

Oracle Database 12.1.0.2
Solaris 11.4

Hello Erman,

replicat is failing for 1 table despite unique keys being defined on both source and target.

2019-10-04 10:51:51 INFO OGG-06511 Using following columns in default map by name: TRANS_NUM_V, TRANS_DATE_D, TRANS_TYPE_V, TRANS_SUB_TYPE_V, DB_CR_V, TRANS_REF_NO_V, ENTITY_TYPE_V, ACCOUNT_LINK_CODE_N, SAP_SUB_AC_CODE_N, TRANS_AMT_N, DESCRIPTION_V, USER_CODE_N, POSTED_TO_ACCOUNTS_N, POSTED_TRANS_NUM_V, EXTERNAL_REFERENCE_V, AUTO_DEBIT_FLG_N, MAIN_CRDB_REF_N, POSTED_FROM_SAGE_V, UNITS_N, WALLET_ID_V, LAST_MODIFIED_DATE_D.

2019-10-04 10:51:51 INFO OGG-06510 Using the following key columns for target table CBSOGG.CB_CR_DB_NOTES: TRANS_NUM_V, TRANS_DATE_D, TRANS_TYPE_V, TRANS_SUB_TYPE_V, DB_CR_V, TRANS_REF_NO_V, ENTITY_TYPE_V, ACCOUNT_LINK_CODE_N, SAP_SUB_AC_CODE_N, TRANS_AMT_N, DESCRIPTION_V, USER_CODE_N, POSTED_TO_ACCOUNTS_N, POSTED_TRANS_NUM_V, EXTERNAL_REFERENCE_V, AUTO_DEBIT_FLG_N, MAIN_CRDB_REF_N, POSTED_FROM_SAGE_V, UNITS_N, WALLET_ID_V, LAST_MODIFIED_DATE_D.


Source Context :
SourceModule : [er.replicat.utiity]
SourceID : [/scratch/jkrath/view_storage/jkrath_ogg1810rc4vw/oggcore/OpenSys/src/app/er/replicat/reputility.cpp]
SourceMethod : [validateUpdate]
SourceLine : [428]

2019-10-04 10:51:51 ERROR OGG-01169 Encountered an update where all key columns for target table CBSOGG.CB_CR_DB_NOTES are not present.

Could you please advise why?

DDL_SOURCE.txt
DDL_TARGET.txt
REPLOGGT0.rpt

Thanks

[ErmanArslansOracleBlog]

Did you add all the keycols into the parameter file and retest?

[Roshan]

APplication team say it is difficult to identify a set of columns for use as keycols. I do not know which set of fields to place in KEYCOLS.

Functional unique index exists for some tables.

Can I proceed with steps from:

2355832.1

[Roshan]

I tried the steps from Doc ID 2355832.1

I see the warning message below

2019-10-05T04:59:57.803+0400 INFO OGG-06441 Oracle GoldenGate Delivery for Oracle, reploggt.prm: Unique key OGG_CB_CR_DB_NOTES2#$1 for table CB_CR_DB_NOTES cannot be used due to the inclusion of virtual columns, or user-defined datatypes, or extended long varchar columns, or function-based index columns.
2019-10-05T04:59:57.803+0400 WARNING OGG-06440 Oracle GoldenGate Delivery for Oracle, reploggt.prm: No viable unique key is defined for table CB_CR_DB_NOTES. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

[ErmanArslansOracleBlog]

If the symptoms given in that note are similar to yours, yes why not..
It says: we have function-based index on top of the key cols and we can't use a function-based index to find unique rows.
That's why the suggest you to create unique index in the target.
That note is based on the KEYCOLS principle, which can be read in the note ->

-------------------------------
https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters160.htm

The following column-types are not supported in KEYCOLS:

Oracle column types not supported by KEYCOLS:

Virtual columns, UDTs, function-based columns, and any columns that are explicitly excluded from the Oracle GoldenGate configuration.

SQL Server, DB2 LUW, DB2 z/OS, MySQL, SQL/MX, Teradata, TimesTen column types not supported by KEYCOLS:

Columns that contain a timestamp or non-materialized computed column, and any columns excluded from the Oracle GoldenGate configuration. For SQL Server Oracle GoldenGate enforces the total length of data in rows for target tables without a primary key to be below 8000 bytes.

Sybase column types not supported by KEYCOLS:

Computed columns, function-based columns, and any columns that are explicitly excluded from the GoldenGate configuration.
----------------

[Roshan]

OK I created a unique index using the columns from function index and set it as invisible as per the doc
since function based index is not supported.
 

CREATE UNIQUE INDEX OGG_CB_CR_DB_NOTES2#$1 ON CB_CR_DB_NOTES (TRANS_NUM_V,TRANS_DATE_D) tablespace INDX;

[ErmanArslansOracleBlog]

So, what is the current situation?

[Roshan]

I am still getting warning:

2019-10-05T22:32:42.090+0400  INFO    OGG-06441  Oracle GoldenGate Delivery for Oracle, reploggt.prm:  Unique key OGG_CB_CR_DB_NOTES2#$1 for table CB_CR_DB_NOTES cannot be used due to the inclusion of virtual columns, or user-defined datatypes, or extended long varchar columns, or function-based index columns.



CB_CR_DB+NOTES_INDEX.PNG

[Roshan]

[Roshan]

Maybe I will need to check with application team for another set of columns for KEYCOLS. The timezone datatype is causing the error.

[Roshan]

I managed to get the keycols columns from application team

CREATE UNIQUE INDEX CB_CR_DB_NOTES#trans_accl on cb_cr_db_notes(trans_num_v,account_link_code_n);

[ErmanArslansOracleBlog]

Good for you Roshan :)