What does “HANDLECOLLISIONS” do in GoldenGate?

HANDLECOLLISIONS is a parameter in Oracle GoldenGate designed to help manage data conflicts that arise during data replication. It's especially useful during initial data loads or when a specific subset of tables needs to be reloaded on the target database.

How HANDLECOLLISIONS Works: This parameter directs GoldenGate on how to handle specific types of data conflicts that can occur:

Insert Collision: When an insert operation is attempted, but the row already exists on the target.
Update Collision: When an update is attempted, but the target row is missing.
Delete Collision: When a delete operation is attempted, but the row is not found on the target.

HANDLECOLLISIONS takes the following actions:

For insert collisions, GoldenGate will skip the error if the row already exists.
For update collisions, it will convert the update to an insert if no row is found.
For delete collisions, it will skip the error if the row doesn't exist.

This parameter should only be used temporarily during initial loads or first-time synchronization, as keeping it on long-term can mask real data consistency issues. Many users mistakenly leave it on and later face data integrity concerns.

While enabling this parameter can prevent the replicat process from stopping unexpectedly, it’s important to understand that it does not provide alerts about underlying issues in the system. This lack of visibility can mean that critical issues go unnoticed until they escalate. Therefore, you should carefully consider the trade-off between stability and the potential risk of unresolved issues.

Prerequisites for using HANDLECOLLISIONS:

HANDLECOLLISIONS requires supplemental logging of all columns for each table, which can add significant overhead to the database. Once HANDLECOLLISIONS is disabled, revert supplemental logging to default settings (SCHEDULINGCOLS).
It requires the full "before" image of every column in the trail file, which is enabled through LOGALLSUPCOLS in the Extract.
HANDLECOLLISIONS only works with tables that have a primary or unique key, which GoldenGate can use. If used on tables without such a key, it may result in duplicate data during update or delete collisions.

Be sure to turn off HANDLECOLLISIONS once the initial load or synchronization is complete. Keeping it enabled beyond this can hide data consistency issues in production. Disabling HANDLECOLLISIONS after the initial synchronization helps detect and address data problems promptly, preserving system and data integrity and preventing performance and reliability issues in the long term.

## INFO     INFO MANAGER                                            Provides details of the Manager process
INFO MGR                                                Also provides details of the Manager process
STATUS MANAGER                                          This command also display the info of manager
INFO ER *                                               To info all of the process
INFO EXTTRAIL /u01/app/OGGH/DIRDAT/SE                   Display info for specific exttrails
INFO EXTTRAIL *                                         Display info for all exttrails
INFO RMTTRAIL /u01/app/OGGH/DIRDAT/TE                   Display info for specific rmttrails
INFO RMTTRAIL *                                         Display info for all rmttrails

## REFRESH  REFRESH MANAGER                                         Reloads from the Manager Parameter file
REFRESH MGR                                             Reloads from the Manager Parameter file

# SEND      SEND MANAGER CHILDSTATUS                                Displays status of processes, started by Manager.
SEND MANAGER CHILDSTATUS DEBUG                          Return the ports numbers allocated by the Manager
SEND MANAGER GETPORTINFO                                Displays the list of currently allocated ports by MGR process
SEND MANAGER GETPORTINFO DETAIL                         Provides info on ports and process assigned to them.
SEND MANAGER GETPURGEOLDEXTRACTS                        Retrieves trail purge retention info.

# START     START MANAGER                                           Starts the Manager Process
START MGR                                               Starts the Manager Process

# STOP      STOP MGR                                                Stops the Manager Process
STOP MANAGER !                                          Stops Manager without asking for user confirmation.
STOP MGR !                                              Stops Manager without asking for user confirmation.

# Add CommandADD EXTRACT EXT_SR, Tranlog, Begin Now                  Used to specify transaction logs as data source for extract.

ADD EXTRACT EXT_SR, Begin Now, Passive                  Specifies the extract to be run in passive mode.

ADD EXTRACT EXT_SR, Extseqno 3 Extrba 1056, Begin Now   Specifies the extract process starting position

ADD EXTRACT EXT_SR, SOURCEISTABLE                       Extracts data from data tables for initial loading.

ADD REPLICAT INITLOAD, SPECIALRUN                       It will create a special run replicat as task.

ADD REPLICAT REPL_TR1, EXTTRAIL                         Create the replicat with the trail
/u01/app/OGGH/DIRDAT/AA

ADD REPLICAT REPL_TR1, EXTTRAIL                                     Create the replicat with the trail and the 
/u01/app/OGGH/DIRDAT/AA, CHECKPOINTTABLE GGSUSER.OGG_CHECKPOINT     Checkpoint info like the DB table used to save checkpoint info.

ADD REPLICAT REPL_TR1, EXTTRAIL                           Create the replicat with the trail and specifying that this 
/u01/app/OGGH/DIRDAT/AA, NODBCHECKPOINT                   replicat didn’t write info to DB table info to DB table

ADD EXTTRAIL                                              It is used to create the local trail file for extract process on local system

ADD EXTTRAIL /u01/app/OGGH/DIRDAT/SE, EXTRACT EXT_SR,     Create EXTTRAIL with the Prefix”SE”, and the size of 100 mb
MEGABYTES 100

ADD EXTTRAIL /u01/app/OGGH/DIRDAT/SE000008                To create the EXTTRAIL with specific sequence number

ADD RMTTRAIL                                              It is used to create the remote trail files for the extract or pump processes on remote systems

ADD RMTTRAIL /u01/app/OGGH/DIRDAT/TE, EXTRACT PXT_SR,     Create RMTTRAIL with the Prefix”TE”, and the size of 100 mb
MEGABYTES 100

ADD RMTTRAIL /u01/app/OGGH/DIRDAT/SE000010                To create the RMTTRAIL with specific sequence number

# ALTER Command   ALTER EXTRACT EXT_SR, BEGIN NOW                           Instructs extract to start processing
ALTER EXTRACT EXT_SR, BEGIN 20241009                      Instructs extract to start processing from specific date
ALTER EXTRACT EXT_SR, ETROLLOVER                          Extract rolls over to next trail file
ALTER EXTRACT EXT_SR, EXTSEQNO 3, EXTRBA 1056             Alters extract to start from the specific locaton in the trail
ALTER EXTRACT EXT_SR, THREAD 4, BEGIN 20241009            Alters extract thread & start date for RAC
ALTER REPLICAT REPL_TR1, BEGIN NOW                        Alter replicat to start processing from now
ALTER REPLICAT REPL_TR1, BEGIN 20241009                   Alter replicat to start processing from specific date
ALTER REPLICAT REPL_TR1, BEGIN 20241009 10:00:10          Alter replicat to start processing from specific date and time
ALTER REPLICAT REPL_TR1, EXTSEQNO 8                       Alter replicat to start from the specific trail file
ALTER REPLICAT REPL_TR1, EXTRBA 254998                    Alter replicat to start from the specific location in the trail
ALTER EXTTRAIL ./DIRDAT/SE, EXTRACT EXT_SR, MEGABYTES 50  It is used to change the options of the existing EXTTRAIL file for extract process on local system
ALTER RMTTRAIL ./DIRDAT/TE, EXTRACT PXT_SR, MEGABYTES 50  It is used to change the options of the existing RMTTRAIL file of extract or pump processes on remote systems

# CLEANUP Command CLEANUP EXTRACT EXT_SR                                    It purges all history of records except last
CLEANUP EXTRACT EXT_SR, SAVE 10                           It saves last 10 records and deletes all other
CLEANUP REPLICAT REPL_TR1                                 It purges all history of records except last
CLEANUP REPLICAT REPL_TR1, SAVE 10                        It saves last 10 records and deletes all other

# DELETE Command  DELETE EXTRACT EXT_SR                                    Deletes the extract process
DELETE EXTRACT e*                                        Deletes all extract process whose name starts with e
DELETE EXTRACT e* !                                      Deletes all extract process whose name starts with e without prompting
DELETE REPLICAT REPL_TR1                                 Deletes the replicat process
DELETE EXTRACT r*                                        Deletes all replicat process whose name starts with r
DELETE EXTRACT r* !                                      Deletes all replicat process whose name starts with r without prompting
DELETE EXTTRAIL /u01/app/OGGH/DIRDAT/SE                  It is used to delete the exttrail assigned to the extract on local system 
DELETE RMTTRAIL /u01/app/OGGH/DIRDAT/TE                  It is used to delete the exttrail for the extract or pump on remote system 

# INFO Command    INFO EXTRACT EXT_SR, SHOWCH                              Display checkpoint info of extract
INFO EXTRACT EXT_SR, DETAIL                              Display trail info, run history
INFO EXTRACT EXT_SR, TASKS                               Display extract tasks
INFO REPLICAT *, TASKS                                   It is used to display replicat tasks only.
INFO REPLICAT REPL_TR1                                   It is used to display the information of replicat.
INFO REPLICAT REPL_TR1, DETAIL                           It display the detailed information of replicat.
INFO REPLICAT REPL_TR1, SHOWCH                           It displays the checkpoint table information, from checkpoint file and checkpoint table.

# KILL Command    KILL EXTRACT EXT_SR     
KILL REPLICAT REPL_TR1  
KILL ER *                                                To kill all of the process

# LAG Command      LAG EXTRACT EXT_SR                                       To find lag for extract  EXT_SR”
LAG EXTRACT *                                            To find lag for all of the extract processes
LAG REPLICAT REPL_TR1                                    To find lag for replicat “REPL_TR1”
LAG REPLICAT *                                           To find lag for all of the replicat processes
LAG ER *                                                 To get lag info of all the process

# REGISTER Command      REGISTER EXTRACT EXT_SR LOGRETENTION                     To register extract  EXT_SR”

# UNREGISTER Command                UNREGISTER EXTRACT EXT_SR LOGRETENTION                   To unregister the extract EXT_SR””

# SEND Command    SEND EXTRACT SRC_EXT1, ROLLOVER                                  To increment the extract to next file in trail
SEND EXTRACT SRC_EXT1, STOP                                      To stop the extract process
SEND EXTRACT SRC_EXT1, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20   For the Oracle RAC, specify the time after which the OGG scan and delete the orphan transactions
SEND EXTRACT SRC_EXT1, SKIPTRANS 1.11.1111 THREAD 2              For skipping the transaction in the Oracle RAC environment
SEND EXTRACT EXT_SR, SHOWTRANS                                   Display the info about the open transactions like checkpoint, extract group name, SCN, Redo log and RB, status etc
SEND EXTRACT EXT_SR, SHOWTRANS COUNT 2                           Display the info for two transactions only
SEND REPLICAT REPL_TR1, HANDLECOLLISIONS                         Enable the handlecollisions option of OGG used for error handling
SEND REPLICAT REPL_TR1, REPORT HANDLECOLLISIONS r_*              Generate statical report to replicat report file
SEND REPLICAT REPL_TR1, GETLAG                                   Get the lag info in seconds
SEND ER *                                                        To use send command on all of the process
SEND EXTRACT SRC_EXT1, BR BRCHECKPOINT IMMEDIATE                 This command forces a BR checkpoint after skipping any old running transactions

# START Command   START EXTRACT EXT_SR    
START REPLICAT REPL_TR1 
START REPLICAT REPL_TR1, ATCSN 2549980                           To start the replicat process from the oraclespecific CSN number including the CSN no transaction
START REPLICAT REPL_TR1, AFTERCSN 25472359                       To start the replicat process from the oraclespecific CSN number, but ignoring that CSN no transaction
START ER *                                                       To start all of the process

# STATS Command   STATS EXT_SR      
STATS EXTRACT EXT_SR                                             Stats will be displayed for the extract EXT_SR””
STATS EXTRACT EXT_SR REPORTRATE SEC                              Display the stats for the fetch operations per sec
STATS EXTRACT EXT_SR, TOTAL, DAILY                               The total stats is shown since the start of the day
STATS EXTRACT EXT_SR, TOTAL, HOURLY, REPORTRATE MIN,             By using comma between the keywords multiple options can be used for the stats command
RESET, REPORTFETCH 
STATS REPL_TR1    
STATS REPLICAT REPL_TR1                                          Stats will be displayed for the REPLICAT REPL_TR1””
STATS REPLICAT REPL_TR1 REPORTDETAIL SEC                         Display the stats for the opertaion that were not replicated due to errors
STATS REPLICAT REPL_TR1, TOTALSONLY *.*                          Total of all transactions since start
STATS REPLICAT REPL_TR1, TOTAL, DAILY                            The total stats is shown since the start of the day
STATS REPLICAT REPL_TR1, TOTAL, HOURLY, REPORTRATE MIN,          By using comma between the keywords multiple options can be used for the stats command
RESET, NOREPORTDETAIL
STATS ER *                                                       To check the stats all of the process

# STATUS Command  STATUS EXTRACT EXT_SR                                           To check the status for extract  EXT_SR”
STATUS EXTRACT e*                                               To check the status for all extracts starting with “e”
STATUS REPLICAT REPL_TR1                                        To check the status for replicat “REPL_TR1”
STATUS REPLICAT r*                                              To check the status for all replicat starting with “r”
STATUS ER *                                                     To find status of all the process

# STOP Command    STOP EXTRACT EXT_SR                                             To stop the extract  EXT_SR”
STOP EXTRACT e*                                                 To stop all of the running extract process whose name start with “e”
STOP EXTRACT *                                                  To stop all of the running extract processes
STOP REPLICAT REPL_TR1                                          To stop the replicat “REPL_TR1”
STOP REPLICAT r*                                                To stop all of the running replicat process whose name start with “r”
STOP REPLICAT *                                                 To stop all of the running replicat processes
STOP ER *                                                       To stop all of the process

Zaaker's Oracle DBA Blog

Saturday, November 9, 2024