Preparing Tables for Processing
The following table attributes must be addressed in an Oracle GoldenGate environment for Teradata.
Disabling Triggers and Cascade Constraints
Disable triggers, cascade delete constraints, and cascade update constraints on target
Teradata tables if Oracle GoldenGate replicates DML from a source that occured from
a trigger or cascade constraint. If the same trigger or constraint gets activated on
the target table, it becomes redundant due to the replicated source data, and the
database will return an error. Consider the following example, where the source
tables are emp_src
and salary_src
and the target
tables are emp_targ
and salary_targ
.
- A delete is issued for
emp_src
. - It cascades a delete to
salary_src
. - Oracle GoldenGate sends both deletes to the target.
- The parent delete arrives first and is applied to
emp_targ
. - The parent delete cascades a delete to
salary_targ
. - The cascaded delete from
salary_src
is applied tosalary_targ
. - The row cannot be located because it was already deleted in step 5.
Ensuring Row Uniqueness for Tables
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.
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 efficientWHERE
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 Extract
TABLE
parameter and the ReplicatMAP
parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Parameters and Functions 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.
Handling Massive Update and Delete Operations
Operations that update or delete a large number of rows generate discrete updates and deletes for each row on the subscriber database. This could cause a lock manager overflow on the Teradata subscriber system, and thus terminate the Replicat process.
To avoid these errors, temporarily suspend replication for these operations and then perform them manually on the source and target systems. To suspend replication, use the following command, which suspends replication for that session only. The operations of other sessions on that table are replicated normally.
set session override replication on;
commit;