Replicat Abending with Mapping Error and Discard File Shows Missing Key Columns
Source table :
create table ggtest.demo3 (empid number not null PRIMARY KEY, empname varchar2(10), dept varchar2(10));
SQL> desc ggtest.demo3
Name Null? Type
—————————————– ——– —————————-
EMPID NOT NULL NUMBER
EMPNAME VARCHAR2(10)
DEPT VARCHAR2(10)
Target table :
create table ggtest.demo3 (empid number not null, empname varchar2(10), dept varchar2(10));
SQL> desc ggtest.demo3
Name Null? Type
—————————————– ——– —————————-
EMPID NOT NULL NUMBER
EMPNAME VARCHAR2(10)
DEPT VARCHAR2(10)
In this case extract will consider empid as key column. There are no primary or unique key defined on target table, so replicat will consider all the columns as key columns.
2019-08-04 22:09:13 INFO OGG-06505 MAP resolved (entry ggtest.demo3): MAP “GGTEST”.”DEMO3″, TARGET ggtest.demo3.
2019-08-04 22:09:13 WARNING OGG-06439 No unique key is defined for table DEMO3. All viable columns will be used to represent the key, but may not guarantee uniqueness
. KEYCOLS may be used to define the key.
2019-08-04 22:09:13 INFO OGG-02756 The definition for table GGTEST.DEMO3 is obtained from the trail file.
2019-08-04 22:09:13 INFO OGG-06511 Using following columns in default map by name: EMPID, EMPNAME, DEPT.
2019-08-04 22:09:13 INFO OGG-06510 Using the following key columns for target table GGTEST.DEMO3: EMPID, EMPNAME, DEPT.
If an update operation occurs on source, Extract will log only the changed column and the primary key column provided trandata was enabled on source table.
For example in source side we will perform one update operation againts demo3 table then lets see what will happen in target replicat side.
SQL> update ggtest.demo3 set EMPNAME=’HARI’ where EMPID=15;
1 row updated.
Replicat when processing the update operation will abend with error in mapping and discard file will show like the following
key column empname (1) is missing
key column dept (2) is missing
This is because if there are no primary key or unique key or PK constraint may be disabled replicat will consider all the columns as key columns.
My best advice is to add same set of primary key or unique key on both source and target tables.
However, as a workaround we can force the extract/replicat to use same columns as key columns using KEYCOLS parameter in the TABLE or MAP statement.
MAP ggtest.demo3, TARGET ggtest.demo3;, keycols (empid);
