Oracle7 Server Distributed Systems Manual, Vol. 2 Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Understanding Replication

This chapter introduces the features that support advanced distributed applications:

Note: This manual describes features that are available to users who are interested in using the distributed and advanced replication options. Basic replication requires the distributed option and PL/SQL. Symmetric replication requires the distributed option, PL/SQL, and the advanced replication option.


The Oracle Server provides a variety of methods to replicate your data. This chapter begins with a discussion of store-and-forward, or asynchronous, replication. For asynchronous replication you can select the method that best suits your needs, from basic primary site replication to advanced dynamic and shared ownership models. Real-time (synchronous) and procedural replication, which have specialized uses, are described later in this chapter. To help you understand these replication options, this chapter discusses supporting mechanisms.

Basic Replication

Basic replication uses read-only snapshots to enforce a form of a primary site replication. As shown in Figure 1 - 1, a read-only snapshot is a full copy of a table, or a subset of a table, that reflects a recent state of the master table. A snapshot is defined by a distributed query that references one or more master tables, views, or other snapshots. A database that contains a master table is referred to as the master database.

Each replica (or copy) of the master table is called a snapshot because the information captured at a moment in time can be periodically refreshed to reflect a more recent transaction-consistent state of the master table.

A simple snapshot is based on a single remote table and has none of the following: distinct or aggregate functions; GROUP BY or CONNECT BY clauses; subqueries; joins; or set operations. If a snapshot's defining query contains any of these clauses or operations, it is a complex snapshot.

Advantages of Read-Only Snapshots

Maintaining read-only snapshots of a master table among the nodes of a distributed database is often a useful feature for the following reasons:

Groups of snapshots can be refreshed to a single point in time, allowing you to maintain transactional consistency between copies of several related master tables.

Advanced Replication

The advanced replication option supports a symmetric, update-anywhere replication model; that is, all copies of data can potentially be updated, and ultimately all sites converge on the same data.

Replication Groups

A replicated object is a database object that is copied to multiple sites in a distributed system. Replication groups are

When you issue a data manipulation language (DML) or data-level statement against a replicated table, that update is ultimately propagated to every other replica of the table. Oracle applies DML changes to each replica in a transactionally consistent manner to ensure data consistency and referential integrity between tables.

Oracle also applies DDL (data definition language or schema-level) changes to each replica, such as adding a column to a table.

Oracle allows you to replicate

Supporting objects are replicated because their SQL Data Definition Language (DDL) statements are replicated. For example, supporting objects are created by propagating and executing the same CREATE statement at each site.

Oracle allows you to define, replicate, and manage groups of replicated objects (replication groups) as a unit. Please note the following:

Replication Sites

A replication group can be replicated (copied) to one or more replication sites. There are two basic kinds of replication sites: master sites and snapshot sites.

A master site must receive a full copy of all of the objects in the replication group. Each master site propagates, or pushes, its changes to every other master site for the replication group.

A snapshot site can receive a subset of the objects in the replication group. For example, you may choose to replicate only selected tables to a snapshot site, or even selected portions of a table. Table-level information is replicated at snapshot sites in the form of read-only or updatable snapshots.

Read-only snapshots can be used only for queries and only the master table can be updated (see the following table). Updatable snapshots provide a local, updatable copy of a remote master table and can be defined to contain a full copy of a master table or a defined subset of rows in the master table that satisfy value-based selection criteria.

Read-Only Snapshots Updatable Snapshots
for queries only for queries and updates
can be simple or complex (derived from a single master table or more than one master table) must be simple (derived from a single master table)
Snapshot sites must have an associated master site (although this master site can change if necessary), and unlike master sites, snapshot sites only push their changes to their associated master site. Snapshot sites can also pull down changes from their associated master site. The propagation mechanisms used by master and snapshot replication sites are explained in greater detail [*].

A replicated environment consists of a replication group, the replicated objects in the group, and the snapshot and master sites containing replicas of the group. Every replication group must have one and only one master definition site. The master definition site is used as the control point for performing administrative activities. Although data-level changes can be made at any site participating in a replicated environment, schema-level changes must be performed at the master definition site. However, if you experience a network outage, the master definition site can be relocated to another master site from any master site in the system.

As shown in Figure 1 - 2, a replication site can participate in multiple replication groups. For example, Site A in Figure 1 - 2 is a snapshot site for schema 3, the master definition site for schema 2, and the master site for schema 1.

Replication Catalog

The symmetric replication facility uses a replication catalog to maintain information, such as which objects are being replicated, where they are being replicated, and how updates need to be propagated to these replicas. This replication catalog consists of a set of database tables that can be backed up and recovered (like any other tables).

Additionally, these tables are themselves replicated to each master replication site, ensuring that there is no single point of failure in your replicated environment.

Once you have configured your replicated environment, any data-level changes that you make will be propagated to all of the associated replication sites. No special commands or interfaces are required.

Schema-level changes, such as those used to configure and administer a replicated environment, must be made using a replication interface. Oracle provides a number of packaged procedures that you can call to administer your replicated environment. These procedures are described[*], as well as in other parts of this document.

Oracle Replication Manager

Oracle Replication Manager, which can be launched as an applet from Oracle Enterprise Manager or run as a stand-alone product, is a graphical tool that lets you configure, schedule, and administer your replicated environment from a single location. Replication Manager's point-and-click interface lets you create replication groups consisting of tables as well as their supporting objects, such as indexes, triggers, views, and conflict resolution procedures. You can drag and drop a group onto other databases to add new replication sites to your environment. If you add or remove objects from a replication group, the changes are automatically deployed at every site.

Replication Manager also helps you troubleshoot and resolve error conditions. You can view the deferred transaction queue at each location, and reschedule or force immediate execution of these transactions as needed. You can also view outstanding administrative requests for each location. Additionally, you can take advantage of Enterprise Manager's event management capabilities, which provide a proactive monitoring capability of replication status across multiple site.

Additional Information: To learn more about this tool and its graphical user-interface (GUI), consult the Oracle Replication Manager online help system.

Note: The rest of this manual refers to the procedural, rather than the Replication Manager GUI, interface for replication administration.


The symmetric replication facility supports both full-table replication and replication of subsets of tables. The following mechanisms are supported:

Read-Only Snapshot Sites

Multiple read-only snapshot sites can be used to provide local access to remote master tables. Having a local snapshot of the data improves query response time. Updates can only be issued against the remote master table.

A read-only snapshot is refreshed from its associated master table in a transactionally consistent manner at a time-based interval or on demand.

Read-only snapshots are easily maintained, and do not require the advanced replication option. Examples of read-only snapshot site uses are included [*].

Multi-Master Replication

As shown in Figure 1 - 3, multi-master replication supports full table, peer-to-peer replication between master tables. All master tables at all sites can be updated.

Changes applied to any master table are propagated and applied directly to all other master tables. These changes can be propagated either synchronously or asynchronously.

Updatable Snapshot Sites

A single master site can be used to consolidate information provided from multiple updatable snapshot sites. In the example shown in Figure 1 - 4, orders can be entered at each of your sales offices, but all orders are processed at the corporate headquarters site.

You can think of the ORDERS snapshot at each of the sales offices as a writeable subset of the ORDERS table at headquarters.

An updatable snapshot is refreshed from its associated master table in a transactionally consistent manner at a time-based interval or on demand. Changes from the updatable snapshot can be forwarded to its master table either synchronously or asynchronously.

Figure 1 - 4. Using Updatable Snapshot Sites for Information Consolidation

Hybrid Configurations

Multi-master table replication and updatable snapshots can be combined in hybrid (mixed) configurations to meet different needs. Specifically, snapshot masters can be n-way replicated (multi-mastered). N-way snapshot master replication means that

For example, as shown in Figure 1 - 5 n-way replication between two snapshot masters can support full-table replication between two master sites supporting two geographic regions. Snapshots can be defined on the snapshot masters to replicate full tables or table subsets to sites within each region.

An added benefit of this n-way replication is that snapshots can be remastered from the other master sites to improve availability. If one master site fails, its snapshots can refresh themselves from the surviving master site and continue processing. This configuration, in which a master site resembles a "hub" with "spokes" to snapshot sites, also allows the two n-way connected master sites to function as fail-over sites for each other (see the "Survivability" section [*]).

Some of the key differences between updatable snapshots and replicated masters include the following:

Propagating Changes Between Replicas

When you update an object in a replicated environment, this change must ultimately be propagated to all master sites, as well as to any appropriate snapshot sites.

Asynchronously Propagating Data-Level Changes

Oracle uses two primary mechanisms for asynchronously propagating data-level (DML) changes between replication sites:

Deferred Transactions

For multiple master replication and replication from updatable snapshots to masters, Oracle generates a trigger and stored procedure for that table to support the replication of data-level changes. When you perform a change locally, Oracle fires a generated trigger that builds a remote procedure call (RPC) to a packaged procedure at the remote site.

The arguments to these procedures contain the information necessary to apply the change at the remote site. The RPC is stored in the local deferred transaction queue. The local deferred transaction queue is stored in a database table, which can be viewed using the DefTran view.

As shown in Figure 1 - 6, when you commit an update to a table, Oracle inserts the necessary remote procedure calls into the deferred transaction queue. Oracle uses a second table, which can be viewed using the RepSites view, to store the destinations of each of the sites to which to push this queue. When you add another master site, its location is added to this destinations view. There is only one entry in the queue for each deferred transaction, regardless of how many destinations are listed in the replication sites view.

In separate transactions, the entries in the deferred transaction queue are propagated to each site listed in the replication site's destination view. Oracle does not remove a transaction from the local deferred transaction queue until it has been successfully propagated to all of the destinations listed in the replication sites view.

Figure 1 - 6. Propagating Data Level Changes

Snapshot Refresh

Snapshot sites use the deferred transaction mechanism described in the previous section to propagate data-level changes from updatable snapshots to their associated master table.

To propagate changes from the master tables to the associated read-only and updatable snapshots, Oracle uses the snapshot refresh mechanism,instead of the row-level replication mechanism. During a snapshot refresh,

Snapshot Refresh Groups

Because snapshot refresh is set-based, if you require that two or more snapshots be refreshed to a single point in time, create a snapshot refresh group.

For example, to preserve a master-detail relationship between a pair of snapshots, place the related snapshots in the same refresh group.

Alternative Replication Mechanisms

In addition to deferred transactions and snapshot refreshes, Oracle provides two alternative mechanisms for propagating data-level changes among replicas:

As described below, these mechanisms have specialized uses.

Synchronous replication uses the same row-level replication mechanism to propagate data-level changes as deferred transactions, but does not use a deferred transaction queue.

As shown in Figure 1 - 7, when you make a change to a replicated table, Oracle fires a trigger which calls a packaged procedure at each master site that applies the change.

Figure 1 - 7. Propagating Data-Level Changes Synchronously

Your change must be successfully applied at both the local table and at any replicated copies of the table, or rollback occurs. Synchronous replication is most useful in situations where you have a stable network and require that your replicated sites remain continuously synchronized.

You can choose to create a replicated environment in which some sites propagate changes synchronously while others use asynchronous propagation (deferred transactions).

Note: The advantages of synchronous propagation (no conflicting changes, never out of date) can only be fully realized if all sites are both sending and receiving changes synchronously.

Procedural replication only replicates the call to a stored procedure that is used to update a table. Procedural replication does not replicate the update itself.

After you replicate a procedure, you can generate a wrapper for this procedure at each site. When you call the procedure at the local site, the wrapper ensures that a call is ultimately made to the same procedure at all other sites in the replicated environment. This call can be made either synchronously or asynchronously (using the deferred transaction queue described [*]).

Note: Do not confuse this wrapper with the PL/SQL Wrapper.

Suggestion: Procedural replication is often used for large batch-oriented operations that can be run serially (such as purging data that was "logically" deleted), and can be used in conjunction with row-level replication. For more information on using procedural replication, refer to page 8 - 13.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index