4 Replies Latest reply on Nov 17, 2019 8:01 AM by ORASCN

    HANDLECOLLISIONS Impact

    3415172
    3415172 Nov 6, 2019 2:26 PM

      Hello Experts,

       

      There could be some scenarios where we may need to use HANDLEOLLISIONS. Example: may be to suppress collisions between cdc and full load, or when DR (Disaster Reovery) switch on source and alter extract to use begin at some particular time(may be to start extract on source 2 with lil time lag while specifying with BEGIN as not certain on time DR happened).

       

      That moment there could be transactions already replicated to target and need of using HANDLECOLLISIONS parameter on replicat, but per docs need to remove this parameter after the missing records are replicated. Would like to know the impact in case if we keep the parameter forever in replicat parameters. Any adverse affects as i see some of blogs/docs says not to use HANDLECOLLISION but not much details on why not to keep it forever. Your inputs will be helpful and appreciate.

       

      -- Cheers, KK.

      • 109 Views
      • Tags:
        • 1. Re: HANDLECOLLISIONS Impact
          ORASCN
          ORASCN Nov 7, 2019 5:57 AM (in response to 3415172)

          Hi KK,

           

          HANDLECOLLISIONS should be used only during the course of Initial Load and not during the Normal or Online replication. Using this parameter will lead to a data integrity issue or data mismatch issue between the source and target.

           

          When the HANDLECOLLISIONS parameter is set, data is processed as follows : -

           

          Missing updates are ignored.

          Missing deletes are ignored.

          Duplicate inserts are turned into updates.

          PK updates will be inserted

           

          Regards,

          Veera

          1 person found this helpful
          • 2. Re: HANDLECOLLISIONS Impact
            3415172
            3415172 Nov 15, 2019 6:19 AM (in response to ORASCN)

            Hi Veera, Can you please have some examples to understand above 4 categories when its active continuously. -- Regards, KK

            • 3. Re: HANDLECOLLISIONS Impact
              3415172
              3415172 Nov 15, 2019 7:23 AM (in response to ORASCN)

              Hi veera,

               

              Few examples i can think of at this moment are:

               

              Missing Updates - Source has data from inception(like from 10 yrs) but replication only has data from past 3 years(as per requirement for example). And, now any update that happened on data beyond 3 yrs will be missing update as 3years beyond data won't exists in target;

               

              Missing Deletes - Same as above but source operation is delete;

               

              Duplicate inserts - any tables doesn't have keys at source;

               

              PK updates will be inserted - any example on this please..

               

              -- Regards,

              KK

              • 4. Re: HANDLECOLLISIONS Impact
                ORASCN
                ORASCN Nov 17, 2019 8:01 AM (in response to 3415172)

                Hi KK,

                 

                Duplicate inserts - any tables doesn't have keys at source;

                 

                                    -----When initial load is not done correctly. If you have the record in the Target and not in the Source and when you try to insert that record in the source, you hit this error.

                 

                PK updates will be inserted - any example on this please..

                 

                               ------- When PK column is getting updated for a record in the source and that particular record is not available in the target, then you will hit this scenario.

                 

                Also, when you don't have PK or UI for a table, then OGG will consider all the columns as PK columns. In this case, you will hit the above.

                 

                Regards,

                Veera