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.

Page: 1 2 3

Topic: Replication Oracle Replication Postgres Replication MySQL Replication