Oracle Advanced Replication: A Definition in Plain English

What is Replication?

According to Webster’s Online, the definition we mean is: 3 a : COPY, REPRODUCTION b : the action or process of reproducing

Replication is simply copying data from one database to another. This could be a subset of the data or a complete copy.

I’m going to use insert for my examples but any DML can be replicated.

If you create an insert trigger on a table and push data across a database link every time a record gets inserted, you’ve performed replication. Call that the poor man’s SYNCHRONOUS replication. If in that trigger, you populate a local queue for a later push or pull to a different database, that’s ASYNCHRONOUS replication.

Oracle offers both types. The down side to synchronous replication is that the act of inserting a record is delayed until the data can be moved to all replicated databases. If a replicated database is down, the transaction cannot complete. The pro for synchronous is guaranteeing the transaction’s delivery to all databases and the elimination of conflicts (more about that later). The downside to asynchronous replication is that depending on database availability, network bandwidth and other factors, the delivery of the transaction may be delayed. This delay could impact reporting or other functional areas. It also increases the likelihood of conflicts in an active/active environment.

A master database is a database actively taking DML. Active/active means that multiple databases are running and accepting transactions and are replicating that data to each other. Oracle calls this multi-master replication. I’ll talk about that below.

So, what is Oracle Advanced Replication?

Oracle offers two types of replication: snapshot and multi-master. I’ll tackle snapshots first.

Oracle offers a feature called snapshots that is synonymous with materialized views. A snapshot at it’s most basic is a query that has it’s data materialized, or populated. When you create a snapshot, a table corresponding to the query’s column list is created (as is some other metadata not discussed here). When the snapshot is refreshed, that underlying table is populated with the results of the query. For replication, as data changes to a table in the master database, the snapshot refreshes as scheduled and moves that data to the replicated database. Snapshot replication is for read-only replication. There are some instances of updateable snapshots but I have never seen that feature used in the real world. If you have an example of real world use, post a reply and describe it. I’d like to hear about it.

For multi-master replication, an application can be installed on two (or more) oracle instances, the common example being one on the east coast and another on the west coast. Both may have people entering data and that data needs to be kept in synch. Hence, they are both master sites and the phrase multi-master. This obviously is more complex than snapshot replication. There are considerations to keep in mind when using multi-master replication. The three most important, from my point of view at least, are sequences (which cannot be replicated), triggers (which can turn recursive if you’re not careful) and conflict resolution.

Some people use multi-master replication as a failover device. That is NOT what replication is for and I would argue against it. Oracle provides other facilities specifically for failover. You can implement replication as a cheap psuedo failover but remember that you get what you pay for. By using replication in this manner, you will increase maintenance over time, both in conflict resolution and in system downtime.

How does it work in 8i, 9i, 10g? If definitely remember that replication was available in Oracle 7.3.4 but I’m not sure when it started exactly. In 7.3, replication was very basic, very limited and very manual. As far as I remember, synchronous replication has worked pretty much the same in all the versions: Triggers on the tables doing a push using db links. In later versions the triggers were hidden behind the scenes. Due to it’s limited use, the discussion from this point forward deals only with asynchronous replication.

I’m not sure of the exact facilities in versions prior to 8 but in 8.0, Oracle created triggers and packages and dropped replicated DML on a queue table (using Advanced Queuing or AQ. I’ll talk about AQ in the future). In 8i, Oracle hid the triggers and the queues. Later versions use streams and re-do logs. Streams is a topic for the future. The re-do logs keep a copy of everything done in a database. By reading the logs instead of using triggers, the overhead to the transaction, and to the database, is minimized.

And finally, 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.

Well, I guess this is my first real beginning on my Oracle Encyclopedia. I’ve used some words and phrases you may not be completely familiar with: insert, transactions, sequence, fail over, etc. How would you like me to handle these kinds of definitions? Should I include a glossary in the article? Should I provide a definition in parenthesis whenever I use a new word someone might not understand? Should I immediately follow an article with a definitions document? Any other ideas? Post a comment and let me know your thoughts and as always, if you agree, disagree, or just have some thoughts to share, please post a comment.



Technorati : , , , , , , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.


  • I bought this for my husband to help with a bathroom remodel project. He cannot stop talking about what a great produce this is. He has shown it to several of our neighbors and told several friends about it too. Makes some nasty work very easy!

  • Deepak says:

    Hi Lewis,Thanks for this nice blog.Would like to know why Oracle does not do replication of sequences. You have already mentioned that because of performance reasons it does not. But I would like to know more about it. 1> How it could have ben done?2> Why there will be a performance impact?Would be good if you can give examples of applications where Advance Queuing would be useful.ThanksDeepak