Replication

What is conflict resolution? There are 2 kinds of conflicts that will usually arise in any replicated environment, key conflicts and update conflicts.

A key conflict is where a primary key is inserted into one instance and the exact key is inserted into another instance before the first can be replicated. Now which one do you keep? Or do you keep both but change the key? If you use sequences, you can run into this because sequences are not replicated by oracle (mainly for performance reasons, post a comment if you would like a more detailed explanation of why this is so). A way to avoid this is to use sequences, but prepend a DB identifier to the sequence, i.e. database A would have an ID of A and database B would have an ID of B. When a sequence is generated in A, the A is prepended to that 00000001 would become A00000001. Any that come in on B would get a B prepended. That would remove key conflicts. You could as easily use a number instead of a character, keeping the column purely numeric.

An update conflict is more difficult to fix. What happens when a record is updated on one instance but deleted on another? Or it's updated on both but in different ways? Who wins? Update conflicts are very application dependent.

Here's a scenario from a real production environment. An application was set up with 4 way (4 instances) multi-master replication. It was decided to use replication in this way for fail over. One of the databases took a real-time update at exactly the same time that a batch process on a different instance updated about 50,000 transactions. The real-time update made it to the other 2 instances and committed. Because the original transactions in the other 3 databases were changes, the 50,000 transactions failed on replication. Unfortunately, the batch process was gospel so now the other 3 databases had to be brought back into synch. This application had to come down for 12 hours while the data was manually re-synched.

The moral here is not to stay away from replication. I'm saying don't use it for fail over and however you do use it, plan for conflicts. Advanced Replication does provide some facilities for conflict resolution. The provided functionality wouldn't have helped the above scenario but if you have the situation where the last transaction in wins, or the first, or a particular database is gospel, you can use those to automatically resolve the update conflict. IF you plan to use replication and design it into your application from the beginning, you can be much more successful with replication. With a cut and dried set of conflict rules or by implementing replication after the fact, you have to plan on downtime.

In a warehouse environment, you can use replication, snapshots are nice for this, to move data from your OLTP database to you staging and/or warehouse database. You're ensured that all data will make it because Oracle guarantees delivery. This would be the E in ETL with no coding at all. You wouldn't ever have to worry about conflicts since you aren't updating the warehouse copy of the tables. From there it's very easy to fire up Oracle Warehouse Builder (OWB) to finish off with the T&L.

Page: 1 2 3

Topic: Replication Oracle Replication Postgres Replication MySQL Replication