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
NOHANDLECOLLISIONS in the following ways:
You can enable
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
NOHANDLECOLLISIONS within a specific
MAP parameter to enable or disable error handling only for that source-target mapping.
The preceding methods can be combined. You can specify a global collisions-handling rule and then override that rule with different collisions-handling rules in the
MAP statements. A
MAP specification always overrides the global specification.
The following example explains how
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.
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:
The application updates record A in source table1.
Extract extracts the update.
The application deletes record A in source table1.
Extract extracts the delete.
Oracle GoldenGate extracts initial-load data from source table1, without record A.
Oracle GoldenGate applies the initial load, without record A.
Replicat attempts to apply the update of record A.
The database returns a "record missing" error.
Replicat attempts to apply the delete of record A.
The database returns a "record missing" error.
HANDLECOLLLIONS 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.
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:
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.
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.
To avoid fetches, use
_ALLOWPKMISSINGROWCOLLISIONS to skip the update instead of converting it to an
INSERT. This configuration can also cause data integrity issues under certain conditions. See "Preventing Conversion of Key Updates to Inserts" for more information.
Preventing Conversion of Key Updates to Inserts
In some cases, it is not appropriate to convert an operation that updates a key column to an
INSERT if the target row does not exist. In these cases, you can use the
_ALLOWPKMISSINGROWCOLLISIONS option to force Replicat to skip the operation, instead of applying it as an insert.
The following example illustrates such a case. This scenario performs an instantiation of Oracle GoldenGate replication, using the default
HANDLECOLLISIONS logic, to show what happens if column values are missing when Replicat tries to convert the update to an insert.
Source and Target tables:
Both tables are named sample.
f1 f2 f3 f4 1 10-01-2011 11:30:45 1 1 2 10-02-2011 14:15:20 2 2 3 10-03-2011 15:12:55 3 3
All columns are
f1 is the primary key.
f2 is a date field that automatically updates whenever the record is changed.
KEYCOLS is used in the parameter files to instruct Oracle GoldenGate to use
f2 as the key.
ADD TRANDATA was issued accordingly, to log column
f1 is automatically logged because it is a primary key.
DML sequence of events:
Start Extract to capture ongoing transactions.
UPDATE the table as follows:
update sample set f3=3 where f1=2;
In this operation, column
f2 updates automatically with the current date and time. Oracle GoldenGate considers this to be a key update.
The row now looks like this:
2 10-20-2011 08:01:32 3 3
DELETE the same row.
delete sample where f1=2;
Now the table contains the following rows:
f1 f2 f3 f4 1 10-01-2011 11:30:45 1 1 3 10-03-2011 15:12:55 3 3
Perform an export/import of the source data to the target, using
HANDLECOLLISIONS to handle missing or duplicate rows.
The replicated update (
update sample, set f3=3 where f1=2) is the first operation to be applied from the trail by Replicat. It fails because the row was deleted from the source before the import/export was performed.
Replicat converts the
UPDATE to an
INSERT according to
HANDLECOLLISIONS logic for operations that update a key column (the
f2 date-time column).
In a case where all of the column values are available in the trail, the new
INSERT succeeds. Moreover, it does not cause inconsistency, even though the row was deleted on the source, because the replicated delete (
delete sample where f1=2) removes it again. However, in this example, there are two problems:
f2, plus the changed value of
f3, are logged. The value for
f4 is not logged and the value is not available for the insert operation.
All columns have a
NOT NULL constraint.
f4 value causes the
INSERT to fail. By using
_ALLOWPKMISSINGROWCOLLISIONS, Replicat skips the
UPDATE instead of converting it to an
INSERT. This causes the subsequent
DELETE to fail because the row does not exist, so Replicat skips the
DELETE record as part of the default
HANDLECOLLISIONS logic. The data now is consistent with that of the source.
Because of the risk of data loss associated with
_ALLOWPKMISSINGROWCOLLISIONS, a warning is issued when it is used. The warning is similar to the following text:
Using _ALLOWPKMISSINGROWCOLLISIONS may cause data corruption under certain conditions.
A warning message also is issued for when an
UPDATE to a key does not contain a full after image for conversion to an insert:
A complete after image is not available in SOURCE.x, at RBA 123, in file .\dirdat\aa00000000, while inserting a row into TARGET.x due to a missing target row for a key update operation. NOCOMPRESSUPDATES or FETCHOPTIONS FETCHPKUPDATECOLS may be specified in the EXTRACT parameter file to include a complete image for key update operations.
Getting More Information about Initial Loads
See Administering Oracle GoldenGate for Windows and UNIX for more information about Oracle GoldenGate initial load methods.
HANDLECOLLISIONS | NOHANDLECOLLISIONS [_ALLOWPKMISSINGROWCOLLISIONS] [THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])]
Enables collision handling.
HANDLECOLLISIONS with _
ALLOWPKMISSINGROWCOLLISIONS to skip primary-key
UPDATE operations if the corresponding target row does not exist.
Skipping operations can cause data corruption. See the Description in this topic.
Turns off collision handling.
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
HANDLECOLLISIONS is enabled only for that
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
A combination of these formats is permitted, such as
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;
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;
This example shows the basic use of
HANDLECOLLISIONS within a
MAP dbo.tcust, TARGET dbo.tcust, HANDLECOLLISIONS;
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;
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
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;
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);
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.*;