Preparing Tables for Processing
The following table attributes must be addressed in an Oracle GoldenGate environment for TimesTen.
Removing ON DELETE CASCADE Contraints
If a target table in Oracle TimesTen has a foreign key, which specifies the
ON DELETE CASCADE
clause, and if the table that is the target
of that foreign key is also a target for Oracle GoldenGate replication then you must
remove the ON DELETE CASCADE
clause from the foreign key definition
to avoid errors.
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.
Oracle TimesTen does not support the disabling of foreign key constraints or on
delete cascade constraints. To remove the ON DELETE CASCADE
, you
must either drop the table and recreate it without the ON DELETE
CASCADE
clause or you can use ALTER TABLE
to remove
the foreign key constraint and recreate it without the ON DELETE
CASCADE
clause.
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.