How to force OGG to use ENABLED Invalid or Non-Validated Keys

Introduction

If a table has a PK constraint or a Unique Constraint that is Non-Validated or Invalid, Oracle GoldenGate will by default not use those keys to uniquely identify a row.  It will use all columns to uniquely identify a row.  This may become an issue due to the increased space required for both trails and redo logs.  It will also force all updates to become PK Updates.  This could become a performance issue on the target because it may not use an index to apply the data.

Main Article

To force OGG to use those keys in Extract/Replicat/GGSCI , the GLOBALS parameter ALLOWNONVALIDATEDKEYS may be used.  In order to use this approach the PK or Key must be ENABLED.  If the PK or Key is not enabled, OGG will still use all columns to uniquely identify the row.

 

To enable ALLOWNONVALIDATEDKEYS functionality when only DML support is required

1.      Stop all processes including manager.  Make sure all replicats and pumps have consumed all current trail files before stopping them.

2.      Add ALLOWNONVALIDATEDKEYS to the GLOBALS parameter file

3.      Remove any KEYCOLS that were added to the parameter files for tables with non-validated PKs or Keys that will now be utilized.

4.      Re-start all processes, starting with Manager. From this point on, Oracle GoldenGate will select non-validated or non-valid primary keys as a unique identifier.

 

To enable ALLOWNONVALIDATEDKEYS functionality when DDLsupport is required

1.      Follow the steps (1-3) above to enable functionality with DML support is required

2.      Update the GGS_SETUP table in the DDL schema by using the following SQL.

UPDATE <owner>.GGS_SETUP SET value=’1′ WHERE

property=’ALLOWNONVALIDATEDKEYS';

COMMIT;

3.      Re-start all processes, starting with Manager. From this point on, Oracle GoldenGate will select non-validated or non-valid primary keys as a unique identifier.

 

Optionally, Supplemental logging maybe changed to use the non-validated PK.  This should be done when no activity is on the database and all data has been processed by extract and replicat. Or before the extract is added when following Instantiation Best Practices.  To change supplemental logging while activity is occurring on the database without re-instantiating is risky and will most likely cause replication errors. ALLOWNONVALIDATEDKEYS must exist in the GLOBALS file.

1.      Delete Current Supplemental Logging

GGSCI> DELETE TRANDATA <SCHEMA>.<TABLENAME>

Or

GGSCI> DELETE TRANDATA <SCHEMA>.*

Or

GGSCI> DELETE SCHEMATRANDATA <SCHEMA>

2.      Re-create Supplemental Logging to use key

GGSCI> ADD TRANDATA <SCHEMA>.<TABLENAME>

Or

GGSCI> ADD TRANDATA <SCHEMA>.*

Or

GGSCI> ADD SCHEMATRANDATA <SCHEMA>

3.      Verify Correct Key is being Utilized

The INFO TRANDATA command can be used in 12c and above.  For lower versions, the dictionary tables can be utilized to verify supplemental logging on a table.

 

Caution:

When using ALLOWNONVALIDATEDKEYS, whether in testing or in production, you accept the risk that the target data may not be maintained accurately through replication: If a key proves to be truly non-valid and the table on which it is defined contains more than one record with the same key value, Oracle GoldenGate might choose the wrong target row to update.

 

Summary

This approach allows OGG to utilize Non-Validated Keys that may exist for any number of valid reasons.  This approach should not be used if the tables are known to contain duplicate data.  Care should also be taken when modifying supplemental logging on an active implementation of OGG.   This parameter will impact Extract, Replicat, and GGSCI commands (Add Trandata and Add Schematrandata).

Add Your Comment