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.
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.
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:
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.
Disable 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.
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:
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.
To avoid fetches, use HANDLECOLLISIONS with _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 NOT NULL.
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 f1 and f2 as the key.
ADD TRANDATA was issued accordingly, to log column f2. 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:
Only columns f1 and 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.
The missing 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.
Messages from _ALLOWPKMISSINGROWCOLLISIONS
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 more information about Oracle GoldenGate initial load methods.
Default
NOHANDLECOLLISIONS
Syntax
HANDLECOLLISIONS | NOHANDLECOLLISIONS [_ALLOWPKMISSINGROWCOLLISIONS] [THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])]
HANDLECOLLISIONSEnables collision handling.
_ALLOWPKMISSINGROWCOLLISIONSUse 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.
NOHANDLECOLLISIONSTurns 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
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 statement.
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 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;
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.*;