Author: Prakash

Replication:

Replication is the management of copies of tables across a distributed database environment.

Types of Replication:

Replication can be of many types.

• Replication can be a complete copy of all the objects in a given database
• Partial Replication

Choosing the Type of Oracle Replication:

One must choose the type of replication based on

• Application requirement
• Platform Type
• The Oracle License one wants to purchase.
• Time
• Cost

Read-only snapshots for Oracle Replication:

The replication can be implemented with the help of read-only snapshots if the copies of the tables are needed for read-only purposes. Snapshot is just 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.

Creating RO-Snapshots for Oracle Replication:

Depending on the requirement the RO-Snapshots can be created on a remote database. 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.

Oracle Replication Issues in RO-Snapshots:

If there is a long network or database outage then 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. On the other hand, 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 intrinsic problems in snapshot replication.

Oracle Replication Snapshots for maintaining Referential Integrity:

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.

Uniqueness Conflict in Oracle Replication:

When a replicated row attempts to validate Entity Integrity like 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. Uniqueness conflict can easily arise if both the instances of table A1 have to be replicated to site C in a single merged table.

Update conflict in Oracle Replication:

Update conflict can frequently occur in advanced replication. Consider 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. If row r1 is updated with some data at site A, and the same row r1 is updated with some other data at B then there is an update conflict if both these transactions have to be replicated.

Deletion conflict in Oracle Replication:

There is a deletion conflict if the same row r1 is deleted at both the sites as this delete transaction has also got to be replicated.

Master-Master Oracle Replication:

There are two databases at geographically disparate locations P & Q. There is table A1 and B1 at site P and there is table A1 and B1 at site Q and table B1 references table A1. Now, all the transactions (insert, update, delete) at P(A1,B1) have to replicated at Q(A1,B1) and vice versa. Thus 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.

Trigger Based oracle replication:

A transaction is saved at all the sites simultaneously. One advantage of this type of replication is that it preempts the chances of occurrence of conflicts. 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.

Asynchronous Propagation for oracle replication:

This is also called store-and-forward propagation. Records 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 for oracle replication:

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

Parallel Propagation for oracle oracle replication:

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 Replication Manager:

Oracle provides Oracle Replication Manager, with Oracle Enterprise Edition to help administer advanced replication environments.

Simulating Multi-master Oracle Replication:

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. Each insert, delete or update transaction in the REPADMIN schema can be updated with the corresponding insert, update or delete with the help of triggers.

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. Once successfully propagated to all the sites, the transactions can be deleted from the REPADMIN schema.

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.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips: Handling Recordsets in Oracle Procedures!!
   Oracle – SQL: Performance Boost with Collections
   DBA Tips for Re-sequencing using Oracle Analytic Functions!!
   Great Tips on Reusing Space after deletion of database data!!