Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 27, 2002

Oracle Replication Solutions

By Prakash Chandani

The management of copies of tables across a distributed database environment is replication. Replication may implicitly involve maintenance of all or some of the objects related to a table; e.g. Triggers, Indexes, Referential Integrities, and so on, so as to maintain database consistency at any point of time for all the databases.

Replication can be of many types. It can be a complete replica of all the objects in a given database, or it can be only a partial replication. The type of replication to be adopted for a system depends on the type application requirement. Also, it depends on the type of platform used and the type of Oracle License one wants to purchase. For instance, Oracle Enterprise Edition 8i provides for Advanced Replication Management Module, whereas Oracle Standard Edition does not. (But the Enterprise Edition is costlier by $20,000 USD per license.)

As we shall see, all types of replications can be possible in both the editions; however, each has different types of configurations and time requirements to set-up the replication. Thus, by careful study of the replication requirement for your application and the features available in Oracle, and after taking into consideration other factors like time, cost, etc., you should be able to choose the best set-up for your requirements.

Now, let's look at the different terminologies used in replications. Also, we will need to cover how the replication is actually set-up under different environments.

Basic Replication

When your requirement is such that the copies of the tables are needed for read-only purposes, replication can be implemented with the help of read-only snapshots. Snapshot, as the name suggests, is merely a copy of the table, and as the table updates with the new transactions the snapshot updates at regular intervals as well. Read-only snapshot will not allow you to make any updates on itself. (This is as opposed to Updatable snapshots which allow you to make updates on the snapshot itself. Updatable snapshots are not required in the Basic Replication. These are available only in Enterprise Edition of Oracle.)

Now, read-only (RO) snapshots can be created on a remote database also, depending on your application requirement (e.g. you have a distributed database setup). Thus, whatever table is needed to be replicated to have a read-only copy at some other database site, local or remote, and for only read-only purposes, we just need to create a snapshot for the purpose. The snapshot can also possibly be created to have only a subset of the original table.

There are certain issues which should be addressed in this type of replication. For example, what if there is a long network or database outage. The snapshot will attempt to refresh 16 times and after that it will be marked as broken. In this case, a manual start of the refresh has to be done. Alternatively, the snapshot can be created with a complete refresh option. But complete refresh has an obvious disadvantage if the table is very large in size. Maintenance of the snapshot sites, thus, is one of the inherent problems in snapshot replication.

(Querying DBA_REFRESH and using >execute DBMS_JOB.RUN(JOB#)
-- one can start/re-run the snapshot refresh for a broken snapshot)

Further, sizing of the snapshot should be based on the parent table. For example, for a very dynamic table you may like to create the snapshot having PCTFREE which matches with the table.

Finally, snapshot should be added to the refresh group so as to maintain referential integrity. You can create the refresh group using the DBMS_REFRESH package or using schema manager.

Replication Conflicts

Uniqueness conflict: When a replicated row attempts to validate Entity Integrity (Primary/Unique key violation), conflict will arise. Suppose there is a table A1 at site A. There is a corresponding table A1 at site B. Now consider the requirement that both the instances of table A1 have to be replicated to site C in a single merged table. Uniqueness conflict can easily arise.

Update conflict: This conflict may never arise for Basic Replication, but can frequently occur in advanced replication. Let us understand the meaning of this conflict. Suppose our table A1 at site A needs to be replicated to the same table A1 at site B. Also, table A1 at site B needs to be replicated at site A. At a given time let us assume that both have identical rows. Now let us assume that row r1 is updated with some data at site A, and the same row r1 is updated with some other data at B. Now, imagine both these transactions have to be replicated. Clearly there is a conflict--update conflict.

Deletion conflict: In our aforementioned example, let us assume that same row r1 is deleted at both the sites. Clearly this is a deletion conflict, as this delete transaction has also got to be replicated. We will see how to avoid/prevent such type of conflicts.

Advanced Replication

Consider the following scenario. There are two databases at geographically disparate locations P & Q. At each of the sites there are two tables A1 and B1. Thus, there is table A1 and B1 at site P and there is table A1 and B1 at site Q. Consider further that table B1 references table A1. (B1 has Fkey referencing the Pkey of A1). Now, all the transactions (insert, update, delete) at P(A1,B1) have to replicated at Q(A1,B1) and vice versa. This sort of replication is called Master-Master Replication, where sites P and Q act as peer sites, and transactions originating at each site, replicate to the other.

Further, any object creation at P or Q, e.g. table, trigger, integrity constraint, etc. also has to be replicated to the other site. We should also consider the permissible time delay in the replication. In some applications, time delay needs to be minimal so as to make the replications near real-time. An alternative to this sort of replication can be a trigger-based replication. A transaction then is saved at all the sites simultaneously. But, this makes transaction size very large, but of course it may be suitable for some applications. One advantage of this type of replication is that it preempts the chances of occurrence of conflicts. (Which is not very difficult to understand). This sort of replication can be implemented even in the standard edition. This trigger-based replication, which is also an example of synchronous replication, can be unsuitable for certain applications (e.g.. if two sites are geographically distant and bandwidth of the network is low and transaction size is high).

Propagation Types

Apart from synchronous propagation there is also asynchronous propagation. This is also called store-and-forward propagation. Reords are propagated in batches and at predetermined regular intervals. It is best suited when replication is over a WAN and transaction size is high.

Serial Propagation: Transactions are propagated in the same order of commit and one at a time.

Parallel Propagation: When configured, Oracle asynchronously propagates transactions using multiple parallel transit streams for higher throughput. When necessary, Oracle executes the dependant transactions to ensure global database integrity.

Oracle purges the deferred transaction queue at regular intervals. (Oracle maintains transaction queues for Async propagation).

Advanced Replication and Oracle Replication Manager

Advanced Replication environments that support an update-anywhere data model can be challenging to configure and manage. To help administer advanced replication environments, Oracle provides a sophisticated management tool, Oracle Replication Manager, with Oracle Enterprise Edition. The following terms are necessary to understand in AR:

Replication Objects: All db objects which need to be replicated.

Replication Groups: As discussed for simple replication, all the related objects should be clubbed in the single Replication Group.

Replication Catalog: Every site, particularly in AR, maintains a data dictionary for the objects to be replicated.

From Oracle 8i onwards, Oracle uses a set of internalized triggers to capture the data to be replicated. Also, the procedure which actually propagates the data, and performs much of the conflict resolution, have been internalized.

Updatable Snapshots: Available with Oracle Enterprise Edition, these can be used where only certain tables are necessary to be replicated. Thus any changes made on the snapshots can also be reflected on the table. However, they have the same typical inherent problems as the read-only snapshot and may not be very suitable for many environments. In other words, it may require a lot of maintenance work to be done. There are also some restrictions on the subquery. Updatable snapshots, when Query rewrite is enabled, can be particularly useful in OLAP Applications. This obviously makes the response times very fast, as the result set is readily available and may even be cached for frequently run identical queries.

Implementing Multi-Master Replication with Replication Manager (RM)

We can take the help of the wizard to quickly set-up replication. Here's the procedure for setting up Advanced Replication through the Resource Manager:

  1. Let there be 2 databases with service names Test1 & Test2. Further, let there be 2 schemas each, e.g.. s1 and s2, which require replication. Test1 and Test2 are the two peers.

  2. Invoke the wizard and select the multi-master option.

  3. In the default users, choose default Replication Administration schema as "REPADMIN" with password as "REPADMIN" (default).

  4. Add the names of the schemas at the Master Site Schema.

  5. Choose the default scheduling or change it as required. This is the interval for refresh.

  6. Select the default Purge Job scheduling. It tells the system how to purge the queue of deferred transactions after successful propagation.

  7. You can further customize the settings for a particular master site before choosing "FINISH" and completing the set-up work.

  8. You can further configure your replication environment using the Replication Manager (e.g.. add/delete site objects, refresh groups, destinations, etc.)

One thing worth noting that before incorporating further changes to your replication environment, replication itself should be quiesced. (Replication Manager by default prompts you to quiesce the Replication.) After incorporating the changes, you need to resume the replication again.

Simulating Multi-master Replication in Standard Edition

If replication is the only extra need, and the rest of the application requirement can be catered to by the Standard Edition of Oracle, then it does make sense to opt for Standard Edition as it costs you around $20K USD less than the Enterprise Edition per license. Of course, you need to do some PL/SQL programming and use some standard Oracle Supplied Packages. One way of simulating replication for multi-master replications is to create a schema (e.g. REPADMIN at each site). This schema should contain an instance of each object to be replicated at that site. You can use DBMS_JOB package to run a procedure which simply checks any update in the number of objects in the schemas to be replicated. If there is any update, it makes the corresponding update in REPADMIN schema and propagates the update to the destination schemas.

Further, each insert, delete or update transaction in the REPADMIN schema, which is having the instance of all the objects to be replicated, can be updated with the corresponding insert, update or delete with the help of triggers (insert, update or delete trigger).

A back-end procedure needs to be written which runs at regular intervals specified by DBMS_JOB. This procedure propagates those transactions to the destination sites. Thus, we can build-up our own logic for store and forward. Once successfully propagated to all the sites, the transactions can be deleted from the REPADMIN schema (this is similar to purge job settings in Replication manager).

By giving the exceptions, sufficient logic can also be given to sense the unsuccessful propagation, temporary network outage, database outage, conflict resolutions, etc. The remedy which has to be taken during an exception can be coded in the PL/SQL procedure. If this procedure is successfully coded for one object, the logic for replication of other objects is quite similar.

A free sample code of such a procedure and the triggers used is available with the author.


Sample Code

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM