How to Use HANDLECOLLISIONS in Oracle GoldenGate

HANDLECOLLISIONS in Oracle GoldenGate is a replicat process parameter, which is mainly used in initial load. When this parameter is used in the replicat process, even if there is a data integrity problem in the target database environment (for example, the update and delete operations encounter missing rows or repeated insert operations ), the replicat process can still process data in the trail. If the HANDLECOLLISIONS parameter is used, the replicat process processes the data as follows: Missing updates are ignored. missing deletes are ignored. duplicate inserts are turned into updates. to use the HANDLECOLLISIONS parameter, you only need to add this parameter to the replicat process parameter file and restart the replicat process to take effect. You can use the HANDLECOLLISIONS parameter for a specific table, or use this parameter globally. Using the send command, we can also dynamically enable the HANDLECOLLISIONS parameter without stopping the replicat process. The command is as follows: GGSCI> send replicat <NAME>, HANDLECOLLISIONS [<owner. table_name>] run the following send command to check whether the HANDLECOLLISIONS parameter is enabled: send replicat <NAME>, report handlecollisions [<owner. table_name>] Example 1 The following enables HANDLECOLLISIONS for all MAP statements in the parameter file. HANDLECOLLISIONSMAP hr. emp, TARGET hr. emp; MAP hr. job_hist, TAR GET hr. job_hist; MAP hr. dep, TARGET hr. dep; MAP hr. country, TARGET hr. country; Example 2 The following enables HANDLECOLLISIONS for some MAP statements while disabling it forothers. HANDLECOLLISIONSMAP hr. emp, TARGET hr. emp; MAP hr. job_hist, TARGET hr. job_hist; NOHANDLECOLLISIONSMAP hr. dep, TARGET hr. dep; MAP hr. country, TARGET hr. country; Example 3 The following shows a combination of global and MAP-le Vel use. the MAP specificationoverrides the global specification for the specified tables. HANDLECOLLISIONSMAP 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; it is not recommended that the HANDLECOLLISIONS parameter be enabled all the time in Oracle, because this may cause data inconsistency in the target database. Even if data is missing or duplicated, The replicat process continues to process data in the trail. HANDLECOLLISIONS is mainly used to start replicat when the source database application is still running and GoldenGate is still capturing data changes of the application after initial load. HANDLECOLLISIONS is also used when we need to roll back the trail file to solve some difficult problems for specific purposes. In GoldenGate 8.0.2.1 and later versions, when the replicat process is stopped, we can see the number of conflict in repeated insert operations in replicat statistics or report file. HANDLECOLLISIONS and KEYCOLS are used in combination to perform DML operations on tables without primary keys or unique indexes, even if KEYCOLS is specified for the table, goldenGate also treats DML in a different way than a table with a primary key or a unique index, because KEYCOLS cannot directly replace the primary key. When a record with the same KEYCOLS value is inserted, goldenGate does not conflict with these records, and the HANDLECOLLISIONS rule does not apply to these records. HANDLECOLLISIONS and PKUPDATES are used in combination. If the Missing Update is a primary key update, replicat will try to insert this record instead of ignoring this operation. However, when replicat tries to insert data, the record to be inserted is not a complete image in the trail file. To obtain a complete image of the primary key update, you must add the following parameters to the extract process, and restart the extract process to make it take effect: fetchoptions fetchpkupdatecols Source http://blog.csdn.net/xiangsir/article/details/8728637