9 Additional Configuration Steps For Using Nonintegrated Replicat
This chapter is a supplement to the basic configuration requirements that are documented in Configuring Oracle GoldenGate Apply.
Topics:
- 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. - Deferring Constraint Checking on Target Tables
When Replicat is in nonintegrated mode, you may need to defer constraint checking on the target.
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 11.2.0.2 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
WHEN
portion of the trigger must still compile and execute correctly to avoid database errors. To enable triggers to fire, or to disable them manually, use theNOSUPPRESSTRIGGERS
option ofDBOPTIONS
and place the statement after theUSERIDALIAS
parameter. To allow a specific trigger to fire, you can use the following database procedure, wheretrigger_owner
is the owner of the trigger andtrigger_name
is the name of the trigger. Once the procedure is called withFALSE
for a particular trigger, it remains set until the procedure is called withTRUE
. dbms_ddl.set_trigger_firing_property(
trigger_owner
"trigger_name
", FALSE)-
For Oracle 11.2.0.2 and later 11gR2 versions, you can use the
DBOPTIONS
parameter with theDEFERREFCONST
option 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 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.
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:
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 11.2.0.2
To handle transient primary-key duplicates in versions earlier than 11.2.0.2, 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 HANDLECOLLISIONS
and REPERROR
parameters, if they exist, or else it abends.
Parent topic: Deferring Constraint Checking on Target Tables
9.2.2 Handling Transient Primary-key Duplicates in Version 11.2.0.2 or Later
For versions later than 11.2.0.2, 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:
DBMS_XSTREAM_GG.ENABLE_TDUP_WORKSPACE()
-
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:
-
HANDLECOLLISIONS
-
REPERROR
When Replicat enables a workspace in Oracle Workspace Manager, it ignores the error handling that is specified by Oracle GoldenGate parameters such as HANDLECOLLISIONS
and 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.
Note:
If Replicat encounters ORA-00001 for a non-update record, the error-handling parameters such as HANDLECOLLISIONS
and 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';
Parent topic: Deferring Constraint Checking on Target Tables