This chapter contains instructions that are specific only to Replicat when operating in nonintegrated mode. When Replicat operates in nonintegrated mode, triggers, cascade constraints, and unique identifiers must be properly configured in an Oracle GoldenGate environment.
This chapter includes the following sections:
This chapter is a supplement to the basic configuration requirements that are documented in Chapter 9, "Configuring Oracle GoldenGate Apply".
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
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
dbms_ddl.set_trigger_firing_property(trigger_owner "trigger_name", FALSE)
For Oracle 18.104.22.168 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
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_src is applied to
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.
If constraints are
DEFERRABLE on 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:
SQLEXEC at 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
DBOPTIONS with the
DEFERREFCONST option 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+n formula 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
CODE and 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.
To handle transient primary-key duplicates in versions earlier than 22.214.171.124, 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.
For versions later than 126.96.36.199, 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.
Note:If Replicat encounters ORA-00001 for a non-update record, the error-handling parameters such as
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';