This chapter is a supplement to the basic configuration requirements that are documented in Configuring Oracle GoldenGate Apply.
9.1 Disabling Triggers and Referential Cascade Constraints on Target Tables
Triggers and cascade constraints must be disabled on Oracle target tables when Replicat is in nonintegrated mode.
Oracle GoldenGate provides some options to handle triggers or cascade constraints automatically, depending on the Oracle version:
For Oracle 220.127.116.11 and later 11gR2 versions, Replicat automatically disables the work performed by triggers during its session. It does not disable a trigger, but instead prevents the trigger body from executing. The
WHENportion of the trigger must still compile and execute correctly to avoid database errors. To enable triggers to fire, or to disable them manually, use the
DBOPTIONSand place the statement after the
USERIDALIASparameter. To allow a specific trigger to fire, you can use the following database procedure, where
trigger_owneris the owner of the trigger and
trigger_nameis the name of the trigger. Once the procedure is called with
FALSEfor a particular trigger, it remains set until the procedure is called with
For Oracle 18.104.22.168 and later 11gR2 versions, you can use the
DBOPTIONSparameter with the
DEFERREFCONSToption to delay the checking and enforcement of cascade update and cascade delete constraints until the Replicat transaction commits.
For other Oracle versions, you must disable triggers and integrity constraints or alter them manually to ignore the Replicat database user.
Constraints must be disabled in nonintegrated Replicat mode because Oracle GoldenGate replicates DML that results from the firing of a trigger or a cascade constraint. If the same trigger or constraint gets activated on the target table, it becomes redundant because of the replicated version, and the database returns an error. Consider the following example, where the source tables are
salary_src and the target tables are
A delete is issued for
It cascades a delete to
Oracle GoldenGate sends both deletes to the target.
The parent delete arrives first and is applied to
The parent delete cascades a delete to
The cascaded delete from
salary_srcis applied to
The row cannot be located because it was already deleted in step 5.
9.2 Deferring Constraint Checking on Target Tables
When Replicat is in nonintegrated mode, you may need to defer constraint checking on the target.
Perform the following steps to defer the constraints:
- If constraints are
DEFERRABLEon the source, the constraints on the target must also be
DEFERRABLE. You can use one of the following parameter statements to defer constraint checking until a Replicat transaction commits:
SQLEXECat the root level of the Replicat parameter file to defer the constraints for an entire Replicat session.
SQLEXEC ("alter session set constraint deferred")
Use the Replicat parameter
DEFERREFCONSToption to delay constraint checking for each Replicat transaction.
- You might need to configure Replicat to overcome integrity errors caused by transient primary-key duplicates. Transient primary-key duplicates are duplicates that occur temporarily during the execution of a transaction, but are resolved by transaction commit time. This kind of operation typically uses a
SET x = x+nformula or some other manipulation that shifts values so that a new value equals an existing one.
The following illustrates a sequence of value changes that can cause a transient primary-key duplicate if constraints are not deferred. The example assumes the primary key column is
CODEand the current key values (before the updates) are 1, 2, and 3.
update item set code = 2 where code = 1; update item set code = 3 where code = 2; update item set code = 4 where code = 3;
In this example, when Replicat applies the first update to the target, there is an ORA-00001 (unique constraint) error because the key value of 2 already exists in the table. The Replicat transaction returns constraint violation errors. By default, Replicat does not handle these violations and abends.
9.2.1 Handling Transient Primary-key Duplicates in Versions Earlier than 22.214.171.124
To handle transient primary-key duplicates in versions earlier than 126.96.36.199, use the Replicat parameter
HANDLETPKUPDATE. In this configuration, a nonintegrated Replicat handles transient primary-key updates by temporarily deferring constraints. To support this functionality, you must create or alter the constraints as
DEFERRABLE INITIALLY IMMEDIATE on the target tables. If the constraints are not
DEFERRABLE, Replicat handles the errors according to rules that are specified with the
REPERROR parameters, if they exist, or else it abends.
9.2.2 Handling Transient Primary-key Duplicates in Version 188.8.131.52 or Later
For versions later than 184.108.40.206, a nonintegrated Replicat by default tries to resolve transient primary-key duplicates automatically by using a workspace in Oracle Workspace Manager. In this configuration, Replicat can defer the constraint checking until commit time without requiring the constraints to be explicitly defined as deferrable.
The requirements for automatic handling of transient primary-key duplicates are:
Grant the Replicat database user access to the following Oracle function:
The target tables cannot have deferrable constraints; otherwise Replicat returns an error and abends.
To handle tables with deferrable constraints, make certain the constraints are
DEFERRABLE INITIALLY IMMEDIATE, and use the
HANDLETPKUPDATE parameter in the
MAP statement that maps that table. The
HANDLETPKUPDATE parameter overrides the default of handling the duplicates automatically.The use of a workspace affects the following Oracle GoldenGate error handling parameters:
When Replicat enables a workspace in Oracle Workspace Manager, it ignores the error handling that is specified by Oracle GoldenGate parameters such as
REPERROR. Instead, Replicat aborts its grouped transaction (if
BATCHSQL is enabled), and then retries the update in normal mode by using the active workspace. If ORA-00001 occurs again, Replicat rolls back the transaction and then retries the transaction with error-handling rules in effect again.
If Replicat encounters ORA-00001 for a non-update record, the error-handling parameters such as
REPERROR handle it.
A workspace cannot be used if the operation that contains a transient primary-key duplicate also has updates to any out-of-line columns, such as LOB and XMLType. Therefore, these cases are not supported, and any such cases can result in undetected data corruption on the target. An example of this is:
update T set PK = PK + 1, C_LOB = 'ABC';