Hello,
I am implementing an Oracle GoldenGate (OGG) solution between two databases. A significant number of source tables (out of 10,000+) lack a Primary Key (PK) or Unique Key (UK). To handle this I am attempting to use the KEYCOLS clause to define a pseudo-key.
My Current Workflow:
- Supplemental Logging: Deleted existing TRANDATA and recreated it using:
ADD TRANDATA <schema>.<table_name>, COLS (<col1>, <col2>)
- Extract Configuration: Added
TABLE <schema>.<table_name>, KEYCOLS (<col1>, <col2>);
- Replicat Configuration: Added
MAP <schema>.<table_name>, TARGET <schema>.<table_name>, KEYCOLS (<col1>, <col2>);
- Instantiation: Imported a DP dump to the target and started the Replicat from the specific SCN.
The Issue: After resolving the lag I performed a row count validation. The target database has more than double the entries of the source database, indicating significant data duplication during replication.
I have three specific questions:
- Is KEYCOLS alone insufficient to prevent duplication? Even with KEYCOLS defined, is there a specific Replicat behavior or an underlying database requirement I am missing?
- TRANDATA vs. KEYCOLS requirements: Is explicitly adding
COLS (A, B) in ADD TRANDATA mandatory for this to work, or is the standard ADD TRANDATA <schema>.* sufficient if the Extract/Replicat params already define the KEYCOLS?
- Scalability: Since I am dealing with over 5,000 tables, manually adding KEYCOLS entries for every table in the parameter files is not feasible. Is there a more efficient way to globally handle tables without PKs, or a way to automate this identification/application within OGG?
Any insights or best practices on handling large scale replication for "keyless" tables would be greatly appreciated.
Thank you!