Bidirectional transactional replication, also known as two-way transactional replication, permits a server to be both a publisher and a subscriber to the same data. Servers that participate in the replication will replicate any changes to the other servers. It is important that the transactions are not then again replicated back to their source.
For example, if you have two servers (Server A and Server B), the servers are said to be in bidirectional transactional replication if both of the following conditions are true:
Therefore, if a change originates from Server A, the change is replicated to Server B, but Server B does not propagate the same change back to Server A. Dbvisit Replicate uses a loopback detection mechanism that the distributor uses to determine whether to send the changes back to originating server.
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.
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 3rd 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.
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.
Sequences are not supported in Bi-Directional Replication. You must deploy one of the methods below to overcome it.
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.
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.
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;
/
The whole next section in this document dedicated to this topic.
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
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. :
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.
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
*** 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