Valid For

Replicat

Description

Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. These errors, called collisions, occur during an initial load, when data from source tables is being loaded to target tables while Oracle GoldenGate is replicating transactional changes that are being made to those tables. When Oracle GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS provides Replicat with error-handling logic for these collisions.

You can use HANDLECOLLISIONS and NOHANDLECOLLISIONS in the following ways:

• You can enable HANDLECOLLISIONS and NOHANDLECOLLISIONS in a global manner by specifying them at the root level of the parameter file. One parameter remains enabled for all subsequent MAP statements in the parameter file, until the opposing parameter is encountered.

• You can enable HANDLECOLLISIONS or NOHANDLECOLLISIONS within a specific MAP parameter to enable or disable error handling only for that source-target mapping.

How HANDLECOLLISIONS Works

The following example explains how HANDLECOLLISIONS works:

• When Replicat encounters an update to a column that Oracle GoldenGate is using as a key, the handling is as follows:

  • If the row with the old key is not found in the target, the change record in the trail is converted to an insert.

  • If a row with the new key exists in the target, Replicat deletes the row that has the old key (it would not exist if the update had executed successfully), and then the row with the new key is updated as an overlay where the trail values replace the current values.

  This logic requires all of the columns in the table (not just the ones that changed) to be logged to the transaction log, either by default or by force, such as by using the COLS option of ADD TRANDATA for an Oracle database. See Possible Solutions to Avoid Missing Column Values.

• When Replicat encounters a duplicate-record error, the static record that was applied by the initial load is overwritten by the change record in the trail. Overlaying the change is safer from an operational standpoint than ignoring the duplicate-record error.

• Replicat with HANDLECOLLISIONS doesn't discard the change record in the trail even if update or delete operation doesn’t affect a key column in the source and Replicat encounters a missing-record error in the target. These errors happen when a record is changed on the source system and then the record is deleted before the table data is extracted by the initial-load process. For example:

  1. The application updates record A in source table1.

  2. Extract extracts the update.

  3. The application deletes record A in source table1.

  4. Extract extracts the delete.

  5. Oracle GoldenGate extracts initial-load data from source table1, without record A.

  6. Oracle GoldenGate applies the initial load, without record A.

  7. Replicat attempts to apply the update of record A.

  8. The database returns a "record missing" error.

  9. Replicat attempts to apply the delete of record A.

  10. The database returns a "record missing" error.

Disable HANDLECOLLISIONS after the transactional changes captured during the initial load are applied to the target tables, so that Replicat does not automatically handle subsequent errors. Errors generated after initial synchronization indicate an abnormal condition and should be evaluated by someone who can determine how to resolve them. For example, a missing-record error could indicate that a record which exists on the source system was inadvertently deleted from the target system.

You can turn off HANDLECOLLISIONS in the following ways:

• Stop Replicat and remove HANDLECOLLISIONS from the Replicat parameter file (can cause target latency). Alternatively, you can edit the parameter file to add NOHANDLECOLLISIONS before the MAP statements for which you want to disable the error handling.

• While Replicat is running, run GGSCI and then use the SEND REPLICAT command with the NOHANDLECOLLISIONS option for the tables that you want to affect.

  Note:

  If using SEND REPLICAT, make certain to remove HANDLECOLLISIONS from the parameter file or add a NOHANDLECOLLISIONS parameter before starting another Replicat run, so that HANDLECOLLISIONS does not activate again.

Possible Solutions to Avoid Missing Column Values

When a database does not log all of the column values of a source table by default, there can be errors if the target table has NOT NULL constraints when Replicat attempts to convert a primary-key update to an insert. You can work around this scenario in the following ways:

• HANDLECOLLISIONS requires that the table have a NOT NULL primary key or NOT NULL unique constraint on the target table.

• Use the NOCOMPRESSUPDATES parameter in the Extract parameter file to send all of the columns of the table to the trail, and configure the database to log all column values. By default, Extract only writes the primary key and the columns that changed to the trail. This is the safest method, because it writes the current values at the time when the operation is performed and eliminates the need for fetching.

• Use the FETCHOPTIONS parameter with the FETCHPKUPDATECOLS option in the Extract parameter file. This configuration causes Extract to fetch unavailable columns when a key column is updated on the source. A fetch is the current value, not necessarily the value at the time of a particular update, so there can be data integrity issues. See "FETCHOPTIONS" for more information and additional fetch options to handle unsuccessful fetches.

If the database includes all columns by default, then you must use NOCOMPRESSUPDATES and NOCOMPRESSDELETES for HANDLECOLLISIONS to work properly. If the database does not support NOCOMPRESSDELETES, you must use FETCHOPTIONS MISSINGCOLS.

See About Instantiating with Initial Load Extract for more information about Oracle GoldenGate initial load methods.

Default

NOHANDLECOLLISIONS

Syntax

HANDLECOLLISIONS | NOHANDLECOLLISIONS [_ALLOWPKMISSINGROWCOLLISIONS]
[THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])]

HANDLECOLLISIONS

Enables collision handling.

_ALLOWPKMISSINGROWCOLLISIONS

Use HANDLECOLLISIONS with _ALLOWPKMISSINGROWCOLLISIONS to skip primary-key UPDATE operations if the corresponding target row does not exist.

Note:

Skipping operations can cause data corruption. See the Description in this topic.

NOHANDLECOLLISIONS

Turns off collision handling.

THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])

Enables HANDLECOLLISIONS for the specified threads. When used in a global HANDLECOLLISIONS statement at the root level of the parameter file, HANDLECOLLISIONS is enabled for the specified threads wherever they are in all MAP statements where . When used in a HANDLECOLLISIONS clause of a MAP statement, HANDLECOLLISIONS is enabled only for that MAP statement.

threadID[, threadID][, ...]

Specifies a thread ID or a comma-delimited list of threads in the format of threadID, threadID, threadID.

thread_range[, thread_range][, ...]

Specifies a range of threads in the form of threadIDlow-threadIDhigh or a comma-delimted list of ranges in the format of threadIDlow-threadIDhigh, threadIDlow-threadIDhigh.

A combination of these formats is permitted, such as threadID, threadID, threadIDlow-threadIDhigh.

Examples

Example 1   

This example enables HANDLECOLLISIONS for all MAP statements in the parameter file.

HANDLECOLLISIONS
MAP hr.emp, TARGET hr.emp;
MAP hr.job_hist, TARGET hr.job_hist;
MAP hr.dep, TARGET hr.dep;
MAP hr.country, TARGET hr.country;

Example 2   

This example enables HANDLECOLLISIONS for some MAP statements while disabling it for others.

HANDLECOLLISIONS
MAP hr.emp, TARGET hr.emp;
MAP hr.job_hist, TARGET hr.job_hist;
NOHANDLECOLLISIONS
MAP hr.dep, TARGET hr.dep;
MAP hr.country, TARGET hr.country;

Example 3   

This example shows the basic use of HANDLECOLLISIONS within a MAP statement.

MAP dbo.tcust, TARGET dbo.tcust, HANDLECOLLISIONS;

Example 4   

This example shows a combination of global and MAP-level use. The MAP specification overrides the global specification for the specified tables.

HANDLECOLLISIONS
MAP hr.emp, TARGET hr.emp;
MAP hr.job_hist, TARGET hr.job_hist;
MAP hr.dep, TARGET hr.dep, NOHANDLECOLLISIONS;
MAP hr.country, TARGET hr.country, NOHANDLECOLLISIONS;

Example 5   

In the following example, HANDLECOLLISIONS is enabled globally for all MAP statements, except for default thread 0 in the first MAP statement and for thread 3 in the second MAP statement.

HANDLECOLLISIONS
MAP fin.*, TARGET fin.*;
MAP sales.*, TARGET sales.*;
MAP orders.*, TARGET orders.*;
MAP scott.cust, TARGET scott.cust, NOHANDLECOLLISIONS;
MAP amy.cust, TARGET amy.cust, THREAD(3), NOHANDLECOLLISIONS;

Example 6   

In this example, HANDLECOLLISIONS is enabled globally, but turned off for thread 3. The remaining threads 1, 2, and 4 will handle collisions.

HANDLECOLLISIONS
NOHANDLECOLLISIONS THREAD(3)
MAP scott.emplyees, TARGET scott.employees, THREADRANGE(1,4, OID);
MAP scott.inventory, TARGET scott.inventory, THREADRANGE(1,4, OID);
MAP scott.cust, TARGET scott.cust, THREADRANGE(1,4, OID);

Example 7   

In this example, HANDLECOLLISIONS is enabled globally, then disabled globally for threads 5 through 7. In the first map statement, all threads will handle collisions, since the HANDLECOLLISIONS parameter does not specify a thread or a range. In the second map statement, only threads 4, 8, and 9 will handle collisions, because the global NOHANDLECOLLISIONS applies to threads 5-7.

HANDLECOLLISIONS
NOHANDLECOLLISIONS THREADRANGE(5-7)
MAP scott.cust, TARGET scott.cust, THREADRANGE(4,9,OID), HANDLECOLLISIONS; 
MAP scott.offices, TARGET scott.offices, THREADRANGE(4,9,OID);
MAP scott.emp, TARGET scott.emp, THREADRANGE(4,9,OID);
MAP scott.ord, TARGET scott.ord, THREADRANGE(4,9,OID);
MAP acct.*, TARGET acct.*;
MAP admin.*, TARGET admin.*;