General bi-directional (2 way) replication considerations

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

There are general considerations for any bi-directional replication, which include things such as:

  1. If the data is to be modified from both sides it is best to impose some business rule for ownership of the records. E.g. one record should not be modified from both sites at the same time.

  2. For sequences a general recommendation is to use non-overlapping sequences. Either use different number ranges (different start), or different modulo (in case of 2 sites, use odd/even). Even if the replication would somehow magically update the sequences on the other side, it will never be fast enough, as the sequence numbers can be requested from Oracle from both sites at the very same time. And/or it would require NOCACHE on sequences to be used, further impacting performance. So it's much better option to ensure that the numbers don't overlap by design.) Oracle documentation has a word on this, too:  http://docs.oracle.com/cd/E11882_01/server.112/e10705/conflict.htm#i1006279 (this is for Streams, but it's really valid for any replication when both sites are being modified at the same time.)

  3. Dbvisit Replicate has features to handle conflicts if they occur; however, it's much better to prevent them in the first place. Both in terms of replication performance and data integrity issues.

Notes on DELETES and conflict handlers within a bi-directional environment:

The NEWER handler on a DELETE does not make best sense, as there is no timestamp associated with the delete operation, so it's impossible to properly evaluate NEWER/OLDER.

Technical explanation as to why it does not work: we build the 'resolving SQL' by including columns from PK and new values. So we compare whether the new data should be put into the table or not (ordering by the column specified in the handler).

So our recommendation is this: do not do deletes in 2-way replication if you want to use NEWER/OLDER. Use so-called logical deletes = add a flag column and set it to 'Active'/'Delete'. Because if node one deletes a row and the second does an update later, you cannot get the row on node 1 back and your data won't be in sync. (And as pointed at the top, we cannot even know which of them were actually first.)

As a general rule Oracle actually advises avoiding delete conflicts in shared data environments, so that within a shared ownership data model apps should not delete rows using DELETE statements. Instead of this apps should mark rows for deletion and then configure the system to purge logically deleted rows periodically.

Mike Donovan December 23, 2013 23:10