Oracle GoldenGate is a powerful tool for real-time data replication. However, during replication setups, encountering errors like OGG-00918: Key Column <Column Name> is Missing from Map can be a challenge. This blog will walk you through diagnosing and resolving this error with a practical example.
Problem
During a GoldenGate replication process, the following error logs were encountered:
2024-06-13T16:49:04.406+0530 INFO OGG-06604 Oracle GoldenGate Delivery for Oracle: Database INTPDB CPU info: CPU Count 1, CPU Core Count 1, CPU Socket Count 1.
2024-06-13T16:49:04.411+0530 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle: REPLICAT repuser started.
2024-06-13T16:49:04.413+0530 INFO OGG-02911 Oracle GoldenGate Capture for Oracle, extuser.prm: Processing table ESTPDB.AVUKWEB.USERS_TBL.
2024-06-13T16:49:05.502+0530 INFO OGG-06441 Oracle GoldenGate Delivery for Oracle: Unique key USERS_BRAND_ID_REGION_ID_PC_EMAIL_ADDRESS_UK for table USERS_TBL cannot be used due to the inclusion of virtual columns, or user-defined datatypes, or extended long varchar columns, or function-based index columns.
2024-06-13T16:49:05.502+0530 INFO OGG-06441 Oracle GoldenGate Delivery for Oracle: Unique key USERS_PK for table USERS_TBL cannot be used due to the inclusion of virtual columns, or user-defined datatypes, or extended long varchar columns, or function-based index columns.
2024-06-13T16:49:05.509+0530 ERROR OGG-00918 Oracle GoldenGate Delivery for Oracle: Key column USER_ID is missing from map.
2024-06-13T16:49:05.515+0530 WARNING OGG-01194 Oracle GoldenGate Capture for Oracle, extuser.prm: EXTRACT task repuser abended: Initial data load error reported by REPLICAT.
2024-06-13T16:49:05.526+0530 ERROR OGG-01203 Oracle GoldenGate Capture for Oracle, extuser.prm: EXTRACT abending.
2024-06-13T16:49:10.516+0530 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle: PROCESS ABENDING.
Additionally, the following query confirmed the presence of virtual columns in the table:
SELECT table_name, column_name
FROM dba_tab_cols
WHERE table_name = 'USERS_TBL' AND virtual_column = 'YES';
TABLE_NAME COLUMN_NAME
--------------- ---------------
USERS_TBL SYS_NC00130$
USERS_TBL SYS_NC00131$
Resolving Oracle GoldenGate Replicat Abends with Error OGG-00918: Key Column Missing from Map
Oracle GoldenGate is a robust tool for real-time data replication, but even experienced professionals may encounter challenges during setup. One such issue is OGG-00918: Key Column <Column Name> is Missing from Map, which can lead to replication abends. This blog details the problem, its root cause, and the solution using a real-world example.
Problem
During a GoldenGate replication process, the following error logs were encountered:
yamlCopy code2024-06-13T16:49:04.406+0530 INFO OGG-06604 Oracle GoldenGate Delivery for Oracle: Database INTPDB CPU info: CPU Count 1, CPU Core Count 1, CPU Socket Count 1.
2024-06-13T16:49:04.411+0530 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle: REPLICAT repuser started.
2024-06-13T16:49:04.413+0530 INFO OGG-02911 Oracle GoldenGate Capture for Oracle, extuser.prm: Processing table ESTPDB.AVUKWEB.USERS_TBL.
2024-06-13T16:49:05.502+0530 INFO OGG-06441 Oracle GoldenGate Delivery for Oracle: Unique key USERS_BRAND_ID_REGION_ID_PC_EMAIL_ADDRESS_UK for table USERS_TBL cannot be used due to the inclusion of virtual columns, or user-defined datatypes, or extended long varchar columns, or function-based index columns.
2024-06-13T16:49:05.502+0530 INFO OGG-06441 Oracle GoldenGate Delivery for Oracle: Unique key USERS_PK for table USERS_TBL cannot be used due to the inclusion of virtual columns, or user-defined datatypes, or extended long varchar columns, or function-based index columns.
2024-06-13T16:49:05.509+0530 ERROR OGG-00918 Oracle GoldenGate Delivery for Oracle: Key column USER_ID is missing from map.
2024-06-13T16:49:05.515+0530 WARNING OGG-01194 Oracle GoldenGate Capture for Oracle, extuser.prm: EXTRACT task repuser abended: Initial data load error reported by REPLICAT.
2024-06-13T16:49:05.526+0530 ERROR OGG-01203 Oracle GoldenGate Capture for Oracle, extuser.prm: EXTRACT abending.
2024-06-13T16:49:10.516+0530 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle: PROCESS ABENDING.
From the error log, the following issues were identified:
- Unique Keys Not Usable:
- Multiple unique keys (
USERS_BRAND_ID_REGION_ID_PC_EMAIL_ADDRESS_UK,USERS_PK, etc.) could not be used due to the inclusion of virtual columns.
- Multiple unique keys (
- Key Column Missing:
- The column
USER_ID, crucial for mapping, was missing from the GoldenGate configuration.
- The column
- Abended Processes:
- Both the Extract and Replicat processes failed, causing replication to stop.
Additionally, the following query confirmed the presence of virtual columns in the table:
sqlCopy codeSELECT table_name, column_name
FROM dba_tab_cols
WHERE table_name = 'USERS_TBL' AND virtual_column = 'YES';
Result:
rubyCopy codeTABLE_NAME COLUMN_NAME
--------------- ---------------
USERS_TBL SYS_NC00130$
USERS_TBL SYS_NC00131$
Root Cause Analysis
- Virtual Columns: System-generated virtual columns (
SYS_NC$) are often created for function-based indexes or constraints. These columns are not physically stored in the database and are incompatible with GoldenGate for unique key or primary key resolution. - Missing Key Column: The primary key (
USER_ID) was excluded from the GoldenGate mapping or trail file, leading to the abend.
1. Recreate Primary Key Without Virtual Columns
Recreate the primary key using only physical columns to make it compatible with GoldenGate:
SQL> ALTER TABLE USERS_TBL DROP CONSTRAINT USERS_PK;
SQL> ALTER TABLE USERS_TBL ADD CONSTRAINT USERS_PK PRIMARY KEY (USER_ID);
This ensures the primary key does not include virtual columns.
2. Verify Table Constraints
Check the table constraints to confirm the primary key is properly defined:
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = 'USERS_TBL';
3. Drop Unnecessary Virtual Columns
If the virtual columns are not required, they can be dropped:
SQL> ALTER TABLE USERS_TBL DROP COLUMN SYS_NC00130$;
SQL> ALTER TABLE USERS_TBL DROP COLUMN SYS_NC00131$;
4. Restart the Processes
After applying these changes, restart the GoldenGate Extract and Replicat processes to resume replication.
