Oracle's Symmetric Replication Technology and Implications for Application Design.

Dean Daniels, Lip Boon Doo, Alan Downing, Curtis Elsbernd, Gary Hallmark, Sandeep Jain, Bob Jenkins, Peter Lim, Gordon Smith, Benny Souder, Jim Stamos: Oracle's Symmetric Replication Technology and Implications for Application Design. SIGMOD Conference 1994: 467
  author    = {Dean Daniels and
               Lip Boon Doo and
               Alan Downing and
               Curtis Elsbernd and
               Gary Hallmark and
               Sandeep Jain and
               Bob Jenkins and
               Peter Lim and
               Gordon Smith and
               Benny Souder and
               Jim Stamos},
  editor    = {Richard T. Snodgrass and
               Marianne Winslett},
  title     = {Oracle's Symmetric Replication Technology and Implications for
               Application Design},
  booktitle = {Proceedings of the 1994 ACM SIGMOD International Conference on
               Management of Data, Minneapolis, Minnesota, May 24-27, 1994},
  publisher = {ACM Press},
  year      = {1994},
  pages     = {467},
  ee        = {, db/conf/sigmod/DanielsDDEHJJLSSS94.html},
  crossref  = {DBLP:conf/sigmod/94},
  bibsource = {DBLP,}


Oracle Symmetric Replication extends the asynchronous snapshot replication capabilities of Oracle7 to eliminate primary site or row ownership restrictions imposed by the replication system. Any row of any replicated table can be updated at any time by any database storing the row. Data replication configurations can be based on read-only or updatable snapshots of subsets of master tables, peer to peer replication of complete master tables, or a combination of these two configurations. Symmetric Replication is implemented as a layered architecture that includes data transport, replication program generation, and distributed database administration components. The issues that Symmetric Replication raises for application design are being studied in the context of Oracle Applications products.

Configurations that replicate subsets of tables use snapshots, which are materialized views of remote tables, to select the data to be replicated. Snapshots of single tables without aggregation are refreshed incrementally, and more complex snapshots are refreshed by complete evaluation of the defining query. Symmetric Replication allows simple snapshots to be updatable. In Symmetric Replication, any copy of a table that is not a snapshot is called a master table because it can be used for snapshot refreshes. Modifications to a snapshot are propagated to its master table before the snapshot is refreshed.

Configurations that replicate entire tables can use snapshots, or they can use multiple master tables that exchange updates periodically or on demand. Snapshots can be defined on any master table. Fault tolerance is provided by allowing snapshots to refresh from any master table.

Symmetric Replication uses snapshot refresh and deferred remote procedure calls (RPCs) as its data transport mechanisms. Multiple snapshots can be refreshed as a transactionally consistent group. Deferred RPC is a mechanism for queuing transactions that consist of calls to stored procedures. The queued transactions are executed asynchronously at remote databases. The deferred RPC queues and the differential data used for incremental snapshot refresh are stored in the database and use standard database mechanisms for transactions, recovery, and administration.

In most configurations, the deferred RPC mechanisms are invoked by programs that are generated by the Symmetric Replication program generator. The program generator creates triggers that defer RPCs to propagate table updates to master sites, and procedures to apply the updates. Because Symmetric Replication allows concurrent updates to different copies of data. the generated replication programs contain logic to detect an update conflict. By default, a conflicting replicated transaction is not applied, and is logged in an error table. The program generators can incorporate application logic for conflict resolution.

Symmetric Replication is administered through a distributed replication catalog interface that consists of new data dictionary views and commands that manipulate the replication configuration and manage replicated database schemas. Replication catalog commands invoke replication program generators, create and refresh snapshots, create new master copies of tables, procedures and other database objects, and manage deferred RPC queues.

Symmetric replication builds on the snapshot technology provided by Oracle7 and many other distributed databases. The implementation uses synchronous distributed database technology including distributed SQL, synchronous RPCs, and distributed transactions. According to the taxonomy defined by Davidson et al., Symmetric Replication uses eventual mutual consistency as a correctness criterion. It provides support for simple pessimistic syntactic approaches that avoid conflicts, such as horizontal partitioning and primary site strategies. Simple optimistic semantic approaches that detect and resolve conflicts (e.g., use the most recent update) are also supported. More complex consistency strategies can be constructed.

The problems of designing applications that use update anywhere resolution and incur few conflicts are being studied using Oracle Applications. Oracle Applications is an integrated suite of business applications including Order Entry, Manufacturing, General Ledger, etc., that contains over 1600 tables or views and 42,500 SQL statements. Preliminary study indicates that Oracle Applications can use Symmetric Replication to share data among functional partitions of the application suite with few conflicts. A few general paradigms can be used to resolve conflicts. Horizontal partitioning of data is also feasible.

Experience with Symmetric Replication has suggested a number of topics for additional research, including incremental view maintenance and work-flow technologies.

Copyright © 1994 by the ACM, Inc., used by permission. Permission to make digital or hard copies is granted provided that copies are not made or distributed for profit or direct commercial advantage, and that copies show this notice on the first page or initial screen of a display along with the full citation.

ACM SIGMOD Anthology

Online Version (ACM WWW Account required): Full Text in PDF Format

CDROM Version: Load the CDROM "Volume 1 Issue 1, SIGMOD '93-'97" and ...

DVD Version: Load ACM SIGMOD Anthology DVD 1" and ... BibTeX

Printed Edition

Richard T. Snodgrass, Marianne Winslett (Eds.): Proceedings of the 1994 ACM SIGMOD International Conference on Management of Data, Minneapolis, Minnesota, May 24-27, 1994. ACM Press 1994 BibTeX , SIGMOD Record 23(2), June 1994

Online Edition: ACM Digital Library

[Index Terms]
[Abstract in PDF Format, 111 KB]


Susan B. Davidson, Hector Garcia-Molina, Dale Skeen: Consistency in Partitioned Networks. ACM Comput. Surv. 17(3): 341-370(1985) BibTeX

Referenced by

  1. Elena Baralis, Stefano Ceri, Stefano Paraboschi: Compile-Time and Runtime Analysis of Active Behaviors. IEEE Trans. Knowl. Data Eng. 10(3): 353-370(1998)
  2. Randall G. Bello, Karl Dias, Alan Downing, James J. Feenan Jr., James L. Finnerty, William D. Norcott, Harry Sun, Andrew Witkowski, Mohamed Ziauddin: Materialized Views in Oracle. VLDB 1998: 659-664
  3. Elena Baralis, Stefano Ceri, Stefano Paraboschi: Modularization Techniques for Active Rules Design. ACM Trans. Database Syst. 21(1): 1-29(1996)
ACM SIGMOD Anthology - DBLP: [Home | Search: Author, Title | Conferences | Journals]
ACM SIGMOD Anthology: Copyright © by ACM (, Corrections:
DBLP: Copyright © by Michael Ley (, last change: Sat May 16 23:40:22 2009