Challenges using replicate for DR

For a successful implementation of Bi-directional Replication a careful analysis is required. You must understand what the supported and unsupported objects are, business requirements and more importantly how to identify and resolve conflicts.

Dbvisit Replicate is an Asynchronous solution and the movement of data from one server to the other via plog files is completely independent of movement the other direction. In order to actually keep them in sync and manage conflicts, you need to set up conflict resolution. It is also possible that there may be LAG at times depending on the transaction volume or network issue or when processes are down. This LAG can cause data inconsistencies. To avoid this situation make sure that there is very little or no LAG or have proper SLAs. 

• Why is it important to design for bidirectional replication?

If an application can be sharded or partitioned across databases involved in replication we can mostly eliminate conflicts because each site will operate on their own set of data. For example, one record should not be modified on both sites at the same time.

If this is an existing application or 3^rd party software and cannot be controlled then data is going to be modified on both sides it is best to impose some business rules for ownership of records to automate the resolve of conflicts as described below.

Below there is a full section dedicated to this topic.

• What about half-duplex configurations, one site active and the second warm site?

Half-duplex bidirectional configuration where the replication processes is configured in both directions but are only active in one direction at a time. This can be useful in situations where a fallback position is required during a migration or upgrade. The reverse replication can be enabled after the migration/upgrade to keep the old system in sync with the new should the need arise to revert back to the old system after a period of time.

The important point here is that all traffic must be drained from active site and passive site should be caught up (no lag) from former active site before enabling traffic on second site from customers. If this is not possible then conditions as described above can occur and automated handlers/logic must be put in place to handle these.

There are special considerations with triggers as well. With an active/ passive configuration they can be set to ENABLED on source side and DISABLED on target side. If target becomes the active site then triggers need to be DISABLED on former active site & ENABLED on new active site. If not triggers can fire and corrupt data. Another option is described below as well in a separate section for Triggers below.

• What about Sequences in Bidirectional Replication?

Sequences are not supported in Bi-Directional Replication. You must deploy one of the methods below to overcome it.

• • ODD/EVEN

  • RANGE

  • CONCATENATE

  You may also choose to customize the sequence definition at each replication site such that the values retrieved are unique across all sites. It is critical that table primary keys and unique indexes are unique for each database participating in an active-active replication environment.

• 
  

• 
  

• • What about Triggers?

   Dbvisit Replicate does not replicate triggers. However, triggers on target tables of any replication deserve special consideration. The reason being is that the trigger on the source that makes changes on the source will be replicated over to the target database so there is no reason for the triggers to fire again on the target.  Therefore, in bi-directional replication, triggers should not be fired if the DML is being executed by APPLY or APPLY1 process.  

   

• Configuration variable APPLY_SET_TRIGGER_FIRE_ONCE or boolean variable DBVREP.DBRSAPPLY_PKG.IS_DBREPLICATE_SESSION can be used to control trigger execution depending upon the Oracle version.

  1. Oracle 10.2.0.5, 11.2.0.2 and 11.2.0.3: APPLY_SET_TRIGGER_FIRE_ONCE

  In Oracle 10.2.0.5, 11.2.0.2 and 11.2.0.3, if APPLY_SET_TRIGGER_FIRE_ONCE variable is set to YES (Default) then the triggers do not fire for changes done by the apply process.  Therefore, APPLY.APPLY_SET_TRIGGER_FIRE_ONCE and APPLY1.APPLY_SET_TRIGGER_FIRE_ONCE are set to YES in bi-direction replication.

           Note: DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY must be set to TRUE (Default) in Oracle. 

  
  

       2. Oracle versions 11.2.0.4 and above : DBRSAPPLY_PKG.IS_DBREPLICATE_SESSION

  For Oracle versions 11.2.0.4 and above the APPLY_SET_TRIGGER_FIRE_ONCE will not work, as Oracle has changed the licensing model. The workaround is to add following condition in the trigger so the trigger will not be executed when APPLY or APPLY1 process executes DMLs.

• Example:

   

  CREATE OR REPLACE TRIGGER TRG_TEMP_SCHEMA_TABLE

  BEFORE UPDATE ON SCHEMA.TABLE

  FOR EACH ROW

  BEGIN

         IF DBVREP.DBRSAPPLY_PKG.IS_DBREPLICATE_SESSION=FALSE

         THEN :new.last_updated:=systimestamp;

  END IF;

  END;

  /

• What are types of conflicts can I encounter with bi-directional & multi-master replication?

The whole next section in this document dedicated to this topic.

Conflicts in Bi-directional Transactional Replication

When you make changes on a server that is participating in replication, the changes are replicated to all other participating servers. During this replication, conflicts may occur and replication may fail. The following list describes the possible conflicts and the ways that you can avoid these conflicts. These are a few basic examples.

1. Uniqueness conflict — This conflict results from an attempt from two different sites to insert records with the same primary key

• Suggested Action To avoid this problem, make sure that you use different keys on each server that participates in the replication. To do so, allocate a predetermined range of keys to each server that participates in the replication. You can also use a composite key on each server.

2. Delete conflict — This type of conflict occurs when one transaction deletes a row that another transaction updates (before the delete is propagated). When you update a record that has been deleted on another server, the UPDATE statement affects zero rows on the server where the record has been deleted, and the replication fails with an error. :

• Suggested Action To avoid this problem, perform one of the following steps do not allow any actual deletes but instead logically delete record by setting a deactivation flag in the record.
  -or-
• Look for a record before the DELETE statement ie. Delete_check stored procedure to check other sites for record. If no record exists, bypass the UPDATE statement, and the record is deleted on all the subscribers. (May be slow & also what if a db is down, how long do we wait for check to respond?)

3. Update conflict — This conflict is caused by simultaneous update operations on the same record. This can occur when you update a column in a record that is updated at the same time on another server, the data may be different on the two servers.

• Suggested Action To avoid this problem, determine if the different columns in the same record are updated at the same time, and then take any necessary action.:

a)     Compare the current values of all the columns against their values before the update in a stored procedure (may be slow)

b)     Check/compare using a trigger to check the values on your other site(s) but things to consider is 1) what if a db needing to be checked is down or slow to respond, 2) how long do we wait for check to respond? time out?)

-or-

c) Add a column to the table represent the row version and to compare its current value with its value before the update

-or-

d) Add a timestamp to the record itself and define business rules for handling this type of conflict with a handler

• Latest Timestamp Value. With this simple technique, you apply updates as they are received. Based on timestamp value, the most recent updates overlays prior updates. This approach can result in situations where one user’s update gets overlaid by a more recent update.
• Earliest Timestamp Value. This mechanism is the opposite of the latest timestamp value, in that the first update overlays subsequent updates. As you’d expect, not many shops use this method, but it is an option.
• Minimum and Maximum Value. This mechanism may be used when the advanced replication facility detects a conflict with a column group. The advanced replication facility calls the minimum value conflict resolution method and then compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate that column when you select the minimum value conflict resolution method.
• Groups priority Value. Using this method, some groups have priority (a higher rank) over other groups. Therefore, the update associated with the highest-ranked group gets the update.
• Site Priority Value. In this method, all master sites are NOT created equal. Some remote sites will have priority over other sites.

*** Note Each deployment may require a different approach to resolve these conflicts, depending on business requirements. These conflicts are easier to resolve when only two servers are involved. When more than two servers are involved, you may be able to use stored procedures to determine which server originated the changes. Then an update stored procedure can be used to update the records in Server C does not know if the change originated in Server A or in Server B. Unlike merge replication, transactional replication is not designed to resolve conflicts. 

Additional considerations can also be found at https://dbvisitsupport.zendesk.com/knowledge/articles/228341107/en-us?brand_id=1808736