This chapter is a supplement to the basic configuration requirements that are documented in Configuring Oracle GoldenGate Apply.
Topics:
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 the NOSUPPRESSTRIGGERS
option of DBOPTIONS
and place the statement after the USERIDALIAS
parameter. To allow a specific trigger to fire, you can use the following database procedure, where trigger_owner
is the owner of the trigger and trigger_name
is the name of the trigger. Once the procedure is called with FALSE
for a particular trigger, it remains set until the procedure is called with TRUE
.
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 the DEFERREFCONST
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 to salary_targ
.
The row cannot be located because it was already deleted in step 5.
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.
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
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