Preparing Tables for Processing
The table attributes in the following sections must be addressed in your Oracle GoldenGate environment.
Replicat Consideration for Target Identity Columns, Triggers, and Constraints
When replicating data to a target SQL Server database that has identity columns, triggers, and cascade and check constraints, consider the following:
-
For columns that have an identity column, Replicat sets the
IDENTITY_INSERT ON
for the table, which may reduce delivery performance. -
For tables that have triggers or cascade constraints, execution of the trigger or cascade operation may result in a Replicat error if the Replicat is configured to deliver the same data that a trigger will insert or cascade constraint will update or delete.
For example,
TableA
on the source has a trigger that inserts a record intoTableB
. The Extract is configured to capture records for bothTableA
andTableB
. On the target, the Replicat will first insert a record forTableA
, then the trigger forTableA
fires and inserts intoTableB
, followed by the Replicat attempting to insert the same record intoTableB
, which will result in a Replicat error. -
Check any foreign key constraints are also enforced, which may reduce delivery performance.
-
For tables on the target database that have triggers, set the
SET XACT_ABORT
parameter to off. This ensures that execution of the trigger operation does not result in missing transactions.
To overcome these situations, there are several options that can be implemented based on the replication use case.
-
For unidirectional implementations where a Replicat is the only process writing data to the target tables, consider the following options for Identity columns, triggers and constraints on the target tables.
-
Disable or drop the Identity property, triggers and constraints on the target tables.
-
Modify the identity property, triggers and constraints and set the
NOT FOR REPLICATION
option on for each and ensure that the Microsoft ODBC driver is at least version 17.8.1.
-
-
For multi-directional implementations where both a Replicat and application write data to the target tables, and triggers and constraints are enabled, modify the Identity property, triggers and constraints and set the
NOT FOR REPLICATION
option on for each and ensure that the Microsoft ODBC driver is at least version 17.8.1.Additionally, to use
IDENTITY
columns in a multi-directional replication configuration, define theIDENTITY
columns to have an increment value equal to the number of servers in the configuration, with a different seed value for each one.For example, a three-database configuration would be as follows:
Database1 set the seed value at 0 with an increment of 3.
Database2 set the seed value at 1 with an increment of 3.
Database3 set the seed value at 2 with an increment of 3.
Improving IDENTITY Replication with Array Processing
Because only one table per session can have IDENTITY_INSERT
set to ON
, Replicat must continuously toggle IDENTITY_INSERT
when it applies IDENTITY
data to multiple tables in a session. To improve the performance of Replicat in this situation, use the BATCHSQL
parameter. BATCHSQL
causes Replicat to use array processing instead of applying SQL statements one at a time.
Ensuring Row Uniqueness in Source and Target Table
Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.
Unless a KEYCOLS
clause is used in the TABLE
or MAP
statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:
-
Primary key
-
First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
-
If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration.
Note:
If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient
WHERE
clause. -
If a table does not have an appropriate key, or if you prefer that the existing key(s) are not used, you can define a substitute key, if the table has columns that always contain unique values. You define this substitute key by including a
KEYCOLS
clause within the ExtractTABLE
parameter and the ReplicatMAP
parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.
Using KEYCOLS to Specify a Custom Key
If a table does not have an applicable row identifier, or if you prefer that identifiers are not used, you can define a substitute key, providing that the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS
clause within the Extract TABLE
parameter and the Replicat MAP
parameter. The specified key overrides any existing primary or unique key that Oracle GoldenGate finds.