9 Additional Configuration Steps For Using Nonintegrated Replicat

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 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 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.

1. A delete is issued for emp_src.

2. It cascades a delete to salary_src.

3. Oracle GoldenGate sends both deletes to the target.

4. The parent delete arrives first and is applied to emp_targ.

5. The parent delete cascades a delete to salary_targ.

6. The cascaded delete from salary_src is applied to salary_targ.

7. 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:

1. 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:

   • Use 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.

2. 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.

• Handling Transient Primary-key Duplicates in Versions Earlier than 11.2.0.2
  
• Handling Transient Primary-key Duplicates in Version 11.2.0.2 or Later
  

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.

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';