Oracle8 Concepts Release 8.0 A58227-01 |
Library | Product | Contents | Index

# 31
Database Replication

Lady, you are the cruel'st she alive, If you will lead these graces to the grave And leave the world no copy.

Shakespeare: Twelfth-Night

This chapter explains the basic concepts and terminology related to the Oracle replication features.

- What Is Replication?
- Basic Replication Concepts
- Advanced Replication Concepts

  Attention: The Advanced Replication features described in this chapter are available only if you have purchased Oracle8 Enterprise Edition.

  Additional Information: Oracle8 Replication contains detailed information about database replication.

## What Is Replication?

Replication is the process of copying and maintaining database objects in multiple databases that make up a distributed database system. Replication can improve the performance and protect the availability of applications because alternate data access options exist. For example, an application might normally access a local database rather than a remote server to minimize network traffic and achieve maximum performance. Furthermore, the application can continue to function if the local server experiences a failure, but other servers with replicated data remain accessible.

Oracle supports two different forms of replication: basic and advanced replication.

### Basic Replication

With basic replication, data replicas provide read-only access to the table data that originates from a primary (master) site. Applications can query data from local data replicas to avoid network access regardless of network availability. However, applications throughout the system must access data at the primary site when updates are necessary.

Figure 31-1 illustrates basic replication.

Oracle can support basic, read-only replication environments using read-only table snapshots. To learn more about basic replication and read-only snapshots, see "Basic Replication Concepts" on page 31-4.

#### Figure 31-1 Basic, Read-Only Replication

### Advanced (Symmetric) Replication

The Oracle advanced replication features extend the capabilities of basic read-only replication by allowing applications to update table replicas throughout a replicated database system. With advanced replication, data replicas anywhere in the system can provide both read and update access to a table's data. Participating Oracle database servers automatically work to converge the data of all table replicas, and ensure global transaction consistency and data integrity.

Figure 31-2 illustrates advanced replication.

Oracle can support the requirements of advanced replication environments using several configurations. To learn more about advanced replication systems, see "Advanced Replication Concepts" on page 31-11.

#### Figure 31-2 Advanced Replication.

## Basic Replication Concepts

Basic replication environments support applications that require read-only access to the table data that originates from a primary site. The following sections explain the fundamental concepts of basic replication environments.

- Uses of Basic Replication
- Read-Only Table Snapshots
- Snapshot Refreshes

### Uses of Basic Replication

Basic, read-only data replication is useful for several types of applications.

#### Information Distribution

Basic replication is useful for information distribution. For example, consider the operation of a large consumer department store chain. With this type of business, it is critical to ensure that product price information is always available, relatively current, and consistent at all retail outlets. To achieve these goals, each retail store can have its own product price data that it refreshes nightly from a primary price table at corporate headquarters.

#### Figure 31-3 Information Distribution

#### Information Off-Loading

Basic replication is useful as a way to replicate entire databases or off-load information. For example, when the performance of high-volume transaction processing system is critical, it can be advantageous to maintain a duplicate database to isolate the demanding queries of decision support applications.

#### Figure 31-4 Information Off-Loading

#### Information Transport

Basic replication can be useful as an information transport mechanism. For example, basic replication can periodically move data from a production transaction processing database to a data warehouse.

### Read-Only Table Snapshots

A read-only table snapshot is a local copy of table data that originates from one or more remote master tables. An application can query the data in a read-only table snapshot, but cannot insert, update, or delete rows in the snapshot.

Figure 31-5 and the sections that follow explain more about read-only table snapshots and basic replication.

#### Figure 31-5 Read-only Snapshots, Master Tables, and Snapshot Logs

#### Read-Only Snapshot Architecture

Oracle supports basic data replication with its table snapshot mechanism. The following sections explain the architecture of simple read-only table snapshots.

Note: Oracle offers other basic replication features such as complex snapshots and ROWID snapshots for unique application requirements. To learn more about these special configurations, see "Other Basic Replication Options" on page 31-10.

##### A Snapshot's Defining Query

The logical data structure of a table snapshot is defined by a query that references data in one or more remote master tables. A snapshot's defining query determines what data the snapshot will contain.

A snapshot's defining query should be such that each row in the snapshot corresponds directly to a row or a part of a row in a single master table. Specifically, the defining query of a snapshot should not contain a distinct or aggregate function, a GROUP BY or CONNECT BY clause, join, restricted types of subqueries, or a set operation. The following example shows a simple table snapshot definition.

    CREATE SNAPSHOT sales.customers AS
        SELECT * FROM sales.customers@hq.acme.com

Note: In all cases, the defining query of the snapshot must reference all of the primary key columns in the master table.

A snapshot's defining query can include restricted types of subqueries that reference multiple tables to filter rows from the snapshot's master table. A subquery snapshot can be used to create snapshots that "walk up" the many-to-one references from child to parent tables that may involve multiple levels. The following example creates a simple subquery snapshot.

    CREATE SNAPSHOT sales.orders AS
        SELECT * FROM sales.orders@hq.acme.com o
            WHERE EXISTS
            ( SELECT c_id FROM sales.customers@hq.acme.com c
                  WHERE o.c_id = c.c_id AND zip = 19555);

### Snapshot Refreshes

The data that a snapshot presents does not necessarily match the current data of its master tables. A table snapshot is a transaction-consistent reflection of its master data as that data existed at a specific point in time. To keep a snapshot's data relatively current with the data of its master, Oracle must periodically refresh the snapshot. A snapshot refresh is an efficient batch operation that makes that snapshot reflect a more current state of its master.

You must decide how and when it is appropriate to refresh each snapshot to make it a more current representation of its master data. For example, snapshots stemming from master tables that applications often update usually require frequent refreshes. In contrast, snapshots that depend on relatively static master tables usually require infrequent refreshes. In summary, analyze application characteristics and requirements to help determine appropriate snapshot refresh intervals.

To refresh snapshots, Oracle supports different types of refreshes (complete and fast), snapshot refresh groups, and manual and automatic refreshes.

#### Complete and Fast Refreshes

Oracle can refresh an individual snapshot using either a complete refresh or a fast refresh.

##### Complete Refreshes

To perform a complete refresh of a snapshot, the server that manages the snapshot executes the snapshot's defining query. The result set of the query replaces the existing snapshot data to refresh the snapshot. Oracle can perform a complete refresh for any snapshot.

##### Fast Refreshes

To perform a fast refresh, the server that manages the snapshot first identifies the changes that took place in the master since the most recent refresh of the snapshot and then applies them to the snapshot. Fast refreshes are more efficient than complete refreshes when there are few changes to the master because participating servers and networks must replicate less data. Fast refreshes are available for snapshots only when the master table has a snapshot log.

#### Snapshot Logs

When a master table corresponds to one or more snapshots, create a snapshot log for the table so that fast refreshes of the snapshots are an option. A master table's snapshot log keeps track of fast refresh data for all corresponding snapshots - only one snapshot log is possible per master table. When a server performs a fast refresh for a snapshot, it uses the data in its master table's snapshot log to refresh the snapshot efficiently. Oracle automatically purges specific refresh data from a snapshot log after all snapshots perform refreshes such that the log data is no longer needed.

#### Snapshot Refresh Groups

To preserve referential integrity and transaction consistency among the table snapshots of several related master tables, Oracle organizes and refreshes each snapshot as part of a refresh group. Oracle refreshes all snapshots in a group as a single operation. After refreshing all of the snapshots in a refresh group, the data of all snapshots in the group corresponds to the same transaction consistent point in time.

#### Automatic Snapshot Refreshes

When creating a snapshot refresh group, administrators usually configure the group so that Oracle automatically refreshes its snapshots. Otherwise, administrators would have to manually refresh the group whenever necessary.

When configuring a refresh group for automatic refreshes, it is necessary to

- specify a refresh interval for the group
- configure the server that manages the snapshots with one or more SNPn background processes to wake up periodically and refresh any snapshots that are due for refreshing

##### Automatic Refresh Intervals

When you create a snapshot refresh group, you can specify an automatic refresh interval for the group. When setting a group's refresh interval, understand the following behaviors:

- The dates or date expressions that specify the refresh interval must evaluate to a future point in time.
- The refresh interval must be greater than the length of time necessary to perform a refresh.
- Relative date expressions evaluate to a point in time that is relative to the most recent refresh date. If a network or system failure should interfere with a scheduled group refresh, the evaluation of a relative date expression could change accordingly.
- Explicit date expressions evaluate to a specific point in time, regardless of the most recent refresh date.

##### Refresh Types

By default, Oracle attempts to perform a fast refresh of each snapshot in a refresh group. If, for some reason, Oracle cannot perform a fast refresh for an individual snapshot (for example, when a master table has no snapshot log), the server performs a complete refresh for the snapshot.

##### SNPn Background Processes

Oracle's automatic snapshot refresh facility functions by using job queues to schedule the periodic execution internal system procedures. Job queues require that at least one SNPn background process be running. An SNPn background process wakes up periodically, checks the job queue, and executes any outstanding jobs.

#### Manual Snapshot Refreshes

Scheduled, automatic snapshot refreshes may not always be adequate. For example, immediately following a bulk data load into a master table, dependent snapshots will no longer represent the master table's data. Rather than wait for the next scheduled automatic group refreshes, you might want to manually refresh dependent snapshot groups to immediately propagate the new rows of the master table to associated snapshots.

### Other Basic Replication Options

Oracle supports some additional basic replication features that can be useful in certain situations:

- Complex Snapshots
- ROWID Snapshots

#### Complex Snapshots

When the defining query of a snapshot contains a distinct or aggregate function, a GROUP BY or CONNECT BY clause, join, restricted types of subqueries, or a set operation, the snapshot is a complex snapshot.

The following example is a complex table snapshot definition.

    CREATE SNAPSHOT scott.emp AS
        SELECT ename, dname
            FROM scott.emp@hq.acme.com a, scott.dept@hq.acme.com b
                WHERE a.deptno = b.deptno
                SORT BY dname

The primary disadvantage of a complex snapshot is that Oracle cannot perform a fast refresh of the snapshot - Oracle can perform only complete refreshes of a complex snapshot. Consequently, the use of complex snapshots can affect network performance during complete snapshot refreshes.

#### ROWID Snapshots

Primary key snapshots (discussed implicitly in earlier sections of this chapter) are the default for Oracle. Oracle bases a primary key snapshot on the primary key of its master table. Because of this structure, you can:

- reorganize the master tables of a snapshot without having to complete a full refresh of the snapshot
- create a snapshot with a defining query that includes a restricted type of subquery

For backward compatibility only, Oracle also supports ROWID snapshots based on the physical row identifiers (ROWIDs) of rows in the master table. ROWID snapshots should only be used for snapshots of master tables in an Oracle Release 7.3 database, and should not be used when creating new snapshots of master tables in Oracle8 databases.

Note: To support a ROWID snapshot, Oracle creates an additional index on the snapshot's base table with the name I_SNAP$_snapshotname.

## Advanced Replication Concepts

In advanced replication environments, data replicas anywhere in the system can provide both read and update access to a table's data.

Attention: The information in this section applies only to the advanced replication feature of Oracle8 Enterprise Edition. See Getting to Know Oracle8 and the Oracle8 Enterprise Edition for more information about features available with Oracle8 Enterprise Edition.

This section explains the principal concepts of an advanced replication system, including the following topics.

- Uses for Advanced Replication
- Advanced Replication Configurations
- Replication Objects, Groups, Sites, and Catalogs
- Oracle's Advanced Replication Architecture
- Replication Administrators, Propagators, and Receivers
- Replication Conflicts
- Unique Advanced Replication Options

### Uses for Advanced Replication

Advanced, symmetric data replication is useful for many types of application systems with special requirements.

#### Disconnected Environments

Advanced replication is useful for the deployment of transaction processing applications that operate using disconnected components. For example, consider the typical sales force automation system for a life insurance company. Each salesperson must visit customers regularly with a laptop computer and record orders in a personal database while disconnected from the corporate computer network and centralized database system. Upon returning to the office, each salesperson must forward all orders to a centralized, corporate database.

#### Failover Site

Advanced replication can be useful to protect the availability of a mission critical database. For example, a symmetric replication system can replicate an entire database to establish a failover site should the primary site become unavailable due to a system or network outage. In contrast with Oracle's standby database feature, such a failover site can also serve as a fully functional database to support application access when the primary site is concurrently operational.

#### Distributing Application Loads

Advanced replication is useful for transaction processing applications that require multiple points of access to database information for the purposes of distributing a heavy application load, ensuring continuous availability, or providing more localized data access.

Applications that have such requirements commonly include customer service oriented applications, as shown in Figure 31-6.

#### Figure 31-6 Advanced Replication System with Multiple Points of Update Access

#### Information Transport

Advanced replication can be useful as an information transport mechanism. For example, an advanced replication system can periodically off-load data from an update-intensive operational database to a data warehouse or data mart.

### Advanced Replication Configurations

Oracle supports the requirements of advanced replication environments using multimaster replication as well as snapshot sites.

#### Multimaster Replication

Oracle's multimaster replication allows multiple sites, acting as equal peers, to manage groups of replicated database objects. Applications can update any replicated table at any site in a multimaster configuration.

Figure 31-7 illustrates a multimaster symmetric replication system.

#### Figure 31-7 Multimaster Replication System

#### Snapshot Sites and Updatable Snapshots

Master sites in an advanced replication system can consolidate the information that applications update at remote snapshot sites. Oracle's symmetric replication facility allows applications to insert, update, and delete table rows through updatable snapshots.

Figure 31-8 illustrates an advanced replication environment with updatable snapshots.

#### Figure 31-8 Advanced Replication System with Updatable Snapshots

Updatable snapshots have the following properties.

- Updatable snapshots are always simple, fast-refreshable table snapshots.
- Oracle propagates the changes made through an updatable snapshot to the snapshot's remote master table. If necessary, the updates then cascade to all other master sites.
- Oracle refreshes an updatable snapshot as part of a refresh group, identical to read-only snapshots.
- Updatable snapshots have the same underlying objects (base table, indexes, and views) as read-only snapshots. Additionally, Oracle creates a table USLOG$_snapshotname to support updatable snapshots.

#### Hybrid Configurations

Multimaster replication and updatable snapshots can be combined in hybrid (mixed) configurations to meet different application requirements. Mixed configurations can have any number of master sites and multiple snapshot sites for each master.

For example, as shown in Figure 31-9, n-way replication between two masters can support full-table replication between the databases that support two geographic regions. Snapshots can be defined on the masters to replicate full tables or table subsets to sites within each region.

#### Figure 31-9 Hybrid Configuration

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

- Replicated masters must contain data for the full table being replicated, whereas snapshots can replicate subsets of master table data.
- Multimaster replication allows you to replicate changes for each transaction as the changes occur. Snapshot refreshes are set oriented, propagating changes from multiple transactions in a more efficient, batch-oriented operation, but at less frequent intervals.
- If any conflicts occur as the result of changes being made to multiple copies of the same data, master sites detect and resolve the conflicts.

### Advanced Replication and the 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.

Additional Information: Oracle8 Replication contains information and examples for using Replication Manager.

### Replication Objects, Groups, Sites, and Catalogs

The following sections explain the basic components of an advanced replication system, including replication objects, groups, sites, and catalogs.

#### Replication Objects

A replication object is a database object that exists on multiple servers in a distributed database system. Oracle's advanced replication facility enables you to replicate tables and supporting objects such as views, database triggers, packages, indexes, and synonyms.

#### Replication Groups

In an advanced replication environment, Oracle manages replication objects using replication groups. By organizing related database objects within a replication group, it is easier to administer many objects together. Typically, you create and use a replication group to organize the schema objects necessary to support a particular database application. That is not to say that replication groups and schemas must correspond with one another - the objects in a replication group can originate from several database schemas, and a schema can contain objects that are members of different replication groups. The basic restriction is that a replication object can be a member of only one group.

#### Replication Sites

A replication group can exist at multiple replication sites. Advanced replication environments support two basic types of sites: master sites and snapshot sites.

- A master site maintains a complete copy of all objects in a replication group. All master sites in a multi-master, symmetric replication environment communicate directly with one another to propagate data and schema changes in the replication group. A replication group at a master site is more specifically referred to as a master group.
- Additionally, every replication group has one and only one master definition site. A replication group's master definition site is a master site that serves as the control point for managing the replication group and objects in the group.
- A snapshot site supports simple read-only and updatable snapshots of the table data at an associated master site. A snapshot site's table snapshots can contain all or just a subset of the table data within a replication group, but must be simple snapshots that have a one-to-one correspondence to tables at the master site. For example, a snapshot site may contain snapshots for only selected tables in a replication group, and a particular snapshot might be just a selected portion of a certain replicated table. A replication group at a snapshot site is more specifically referred to as a snapshot group. A snapshot group can also contain other replication objects.

#### Replication Catalog

Every master and snapshot site in an advanced replication environment has a replication catalog. A site's replication catalog is a distinct set of data dictionary tables and views that maintain administrative information about replication objects and replication groups at the site. Every server that participates in an advanced replication environment can automate the replication of objects in replication groups using the information in its replication catalog.

#### Replication Management API and Administration Requests

To configure and manage an advanced replication environment, each participating server uses Oracle's replication application programming interface (API). A server's replication management API is a set of PL/SQL packages that encapsulate procedures and functions that administrators can use to configure Oracle's advanced replication features. Oracle Replication Manager also uses the procedures and functions of each site's replication management API to perform work.

An administration request is a call to a procedure or function in Oracle's replication management API. For example, when you use Replication Manager to create a new master group, Replication Manager completes the task by making a call to the DBMS_REPCAT.CREATE_MASTER_REPGROUP procedure. Some administration requests generate additional replication management API calls to complete the request.

### Oracle's Advanced Replication Architecture

Oracle converges the data of typical advanced replication configurations using row-level replication with asynchronous data propagation. The following sections explain how these mechanisms function.

Note: Oracle offers other advanced replication features such as procedural replication and synchronous data propagation for unique application requirements. To learn more about these special configurations, see "Unique Advanced Replication Options" on page 31-26.

#### Row-Level Replication

Typical transaction processing applications modify small numbers of rows per transaction. Such applications at work in an advanced replication environment will usually depend on Oracle's row-level replication mechanism. With row-level replication, applications use standard DML statements to modify the data of local data replicas. When transactions change local data, the server automatically captures information about the modifications and queues corresponding deferred transactions to forward local changes to remote sites.

#### Asynchronous (Store-and-Forward) Data Propagation

Typical advanced replication configurations that rely on row-level replication propagate data level changes using asynchronous data replication. Asynchronous data replication occurs when an application updates a local replica of a table, stores replication information in a local queue, and then forwards the replication information to other replication sites at a later time. Consequently, asynchronous data replication is also called store-and-forward data replication.

As Figure 31-10 shows, Oracle uses its internal system of triggers, deferred transactions, deferred transaction queues, and job queues to propagate data-level changes asynchronously among master sites in an advanced replication system, as well as from an updatable snapshot to its master table.

- When applications work in an advanced replication environment, Oracle uses internal triggers to capture and store information about updates to replicated data. The internal triggers build remote procedure calls (RPCs) that will reproduce the data changes made at the local site to remote replication sites. The internal triggers that support data replication are essentially components within the Oracle server executable; therefore, Oracle can capture and store updates to replicated data very quickly with minimal use of system resources.

  #### Figure 31-10 Asynchronous Data Replication Mechanisms

- Oracle stores RPCs produced by the internal triggers in a site's deferred transaction queue for later propagation. Oracle also records information about initiating transactions so that all RPCs that make up a transaction can be propagated and applied remotely as a transaction as well. Oracle's advanced replication facility implements the deferred transaction queue using Oracle's advanced queueing mechanism.
- Oracle manages the propagation process using Oracle's job queue mechanism and deferred transactions. Each server that participates in an advanced replication system has a local job queue. A server's job queue is a database table that stores information about local jobs such as the PL/SQL call to execute for a job, when to run a job, and so forth. Typical jobs in an advanced replication environment include jobs to push deferred transactions to remote master sites, jobs to purge applied transactions from the deferred transaction queue, and jobs to refresh snapshot refresh groups.
- Oracle forwards data replication information by executing RPCs as part of deferred transactions. Oracle uses distributed transaction protocols to protect global database integrity automatically and ensure data survivability.

#### Serial Propagation

With serial propagation, Oracle asynchronously propagates replicated transactions, one at a time, in the same order of commit as on the originating site.

#### Parallel Propagation

With parallel propagation, Oracle asynchronously propagates replicated transactions using multiple, parallel transit streams for higher throughput. When necessary, Oracle orders the execution of dependent transactions to ensure global database integrity.

Parallel propagation uses the same execution mechanism that Oracle uses for parallel query, load, recovery, and other parallel operations. Each server process propagates transactions through a single stream. A parallel coordinator process controls these server processes. The coordinator tracks transaction dependencies, allocates work to the server processes, and tracks their progress.

#### Purging of the Deferred Transaction Queue

After a site pushes a deferred transaction to its destination, the transaction remains in the deferred transaction queue until another job purges the applied transaction from the queue.

#### Snapshots Propagation Mechanisms

Updatable snapshots in an advanced replication environment can both "push" and "pull" data to and from its master table, respectively.

##### Master Table Updates

Updates to an updatable snapshot are asynchronously pushed to its master table using Oracle's row-level, asynchronous data propagation mechanisms (RPCs, deferred transactions, and job queues).

##### Snapshot Refresh

Identical to basic replication environments, advanced replication systems use Oracle's snapshot refresh mechanism to pull changes asynchronously from a master table to associated updatable (and read-only) snapshots.

##### Other Considerations

An updatable snapshot's push and pull tasks are independent operations that you can configure associatively or separately.

- A snapshot site can configure a refresh group to automatically push all changes made to the member snapshots to the master site, and then refresh the snapshots.
- A snapshot site can configure updatable snapshots to push changes to the master site and refresh snapshots at different times and intervals.

For example, an advanced replication environment that consolidates information at a master site might configure updatable snapshots to push changes to the master site every hour but refresh updatable snapshots infrequently, if ever.

### Replication Administrators, Propagators, and Receivers

An Oracle symmetric replication environment requires several unique database user accounts to function properly, including replication administrators, propagators, and receivers.

- Every site in an Oracle symmetric replication system requires at least one replication administrator, a user responsible for configuring and maintaining replicated database objects.
- Each replication site in an Oracle symmetric replication system requires special user accounts to propagate and apply changes to replicated data.

#### Configuration Options

In most advanced replication configurations, just one account is used for all purposes - as a replication administrator, a replication propagator, and a replication receiver. However, Oracle also supports distinct accounts for unique configurations.

### Replication Conflicts

Advanced replication systems that support an update-anywhere model of data replicas must address the possibility of replication conflicts. The following sections explain the different types of replication conflicts, when they can occur, and how Oracle can detect and resolve replication conflicts.

#### Types of Replication Conflicts

Three types of conflicts can occur in an advanced replication environment: uniqueness conflicts, update conflicts, and delete conflicts.

##### Uniqueness Conflicts

A uniqueness conflict happens when the replication of a row attempts to violate entity integrity (a PRIMARY KEY or UNIQUE constraint). For example, consider what happens when two transactions that originate from two diffebent sites each insert a row into a respective table replica with the same primary key value - replication of the transactions will cause a uniqueness conflict.

##### Update Conflicts

An update conflict happens when the replication of an update to a row conflicts with another update to the same row. Update conflicts can happen when two different transactions, originating from different sites, update the same row at nearly the same time.

##### Delete Conflicts

A delete conflict happens when two transactions originate from different sites, with one transaction deleting a row that the other transaction updates or deletes.

#### Replicated Data Models and Conflicts

When designing applications that will work on top of a database system that uses advanced replication, you must consider the possibility of replication conflicts. In doing so, applications must choose to employ one of several different replicated data ownership models that will ensure global database integrity by avoiding or resolving replication conflicts.

##### Primary Site, Static Ownership

Primary ownership, also called static ownership, is the replicated data model that basic read-only replication environments support. Primary ownership prevents all replication conflicts, because only a single server permits update access to a set of replicated data.

Rather than control the ownership of data at the table level, applications can employ horizontal and vertical partitioning to establish more granular static ownership of data. For example, applications might have update access to specific columns or rows in a replicated table on a site-by-site basis.

##### Dynamic Ownership

The dynamic ownership replicated data model is less restrictive than primary site ownership. With dynamic ownership, the capability to update a data replica moves from site to site, still ensuring that only one site provides update access to specific data at any given point in time. A workflow system clearly illustrates the concept of a dynamic ownership. For example, related departmental applications can read the status code of a product order to determine when they can and cannot update the order.

Figure 31-11 illustrates an application that uses a dynamic ownership model.

#### Figure 31-11 Dynamic Ownership in an Order Processing System

##### Shared Ownership

Primary site ownership and dynamic ownership replication data models, which promote conflict avoidance, are often too restrictive or impossible to implement for some database applications. Some applications must operate using a shared ownership replicated data model in which applications can update the data of any table replica at any time.

When a shared data ownership system replicates changes asynchronously (store-and-forward replication), corresponding applications must be sure to avoid, or detect and resolve replication conflicts if and when they occur. For example:

- Delete conflicts are difficult to resolve in an asynchronous shared ownership model unless the replication system records historical information as transactions delete data. Consequently, applications that operate within an asynchronous, shared ownership data model should avoid delete conflicts by not using DELETE statements to delete rows. Instead, applications can mark rows for deletion and configure the system to periodically purge deleted rows using procedural replication.
- Coordinated sequence generation is a technique that applications can use to avoid uniqueness conflicts in a shared data ownership system. For example, typical applications use Oracle sequences to generate numerical primary keys. At each site in a shared data ownership system, create replica sequences so that each sequence generates a mutually exclusive set of sequence numbers.

#### Conflict Detection

When an application uses a shared ownership data model with asynchronous row-level replication, Oracle automatically detects uniqueness, update, and delete conflicts. To detect conflicts during replication, Oracle compares a minimal amount of row data from the originating site with the corresponding row information at the receiving site. When there are differences, Oracle detects the conflict.

To detect replication conflicts accurately, Oracle must be able to uniquely identify and match corresponding rows at different sites during data replication. Typically, Oracle's advanced replication facility uses the primary key of a table to uniquely identify rows in the table. When a table does not have a primary key, you must designate an alternate key - a column or set of columns that Oracle can use to identify rows in the table during data replication. In either case, applications should not be allowed to update the identity columns of a table to ensure that Oracle can identify rows and preserve the integrity of replicated data.

#### Conflict Resolution

When a receiving site in an advanced replication system is using asynchronous row-level replication and it detects a conflict in a transaction, the default behavior is to log the conflict and the entire transaction, and leave the local version of the data intact. In most cases, you should use Oracle's advanced replication facility to automate the resolution of replication conflicts. You can also check each server's DEFERROR data dictionary view for transactions that caused conflicts, and resolve them manually, if necessary.

##### Column Groups

Oracle uses column groups to detect and resolve conflicts during asynchronous, row-level symmetric replication. A column group is a logical grouping of one or more columns in a table. Every column in a replicated table is part of a single column group. When configuring replicated tables, you can create column groups and then assign columns and corresponding conflict resolution methods to each group.

Each column group in a replicated table can have a list of one or more conflict resolution methods. Indicating multiple conflict resolution methods for a group allows Oracle to resolve a conflict in different ways should others fail to resolve the conflict. When trying to resolve a conflict for a group, Oracle executes the group's resolution methods in the order that you list for the group.

By default, every replicated table has a shadow column group. A table's shadow column group contains all columns that are not within a specific column group. You cannot assign conflict resolution methods to a table's shadow group.

##### Conflict Resolution Methods

When designing column groups you can choose from among many built-in conflict resolution methods. For example, to resolve update conflicts, you might choose to have Oracle overwrite the column values at the destination site with the column values from the originating site. Oracle offers many other conflict resolution methods.

### Unique Advanced Replication Options

Some applications have special requirements of an advanced replication system. The following sections explain the Oracle unique advanced replication options, including

- Procedural Replication
- Synchronous (Real-Time) Data Propagation

#### Procedural Replication

Batch processing applications can change large amounts of data within a single transaction. In such cases, typical row-level replication could saturate a network with a huge quantity of data changes. To avoid such problems, a batch processing application that operates in an advanced replication environment can use Oracle's procedural replication to replicate simple stored procedure calls that will converge data replicas. Procedural replication replicates only the call to a stored procedure that an application uses to update a table. Procedural replication does not replicate data modifications.

To use procedural replication, at all sites you must replicate the packages that modify data in the system. After replicating a package, you must generate a wrapper for this package at each site. When an application calls a packaged procedure at the local site to modify data, the wrapper ensures that the call is ultimately made to the same packaged procedure at all other sites in the replicated environment. Procedural replication can occur asynchronously or synchronously.

##### Conflict Detection and Procedural Replication

When an advanced replication system replicates data using procedural replication, the procedures that replicate data are responsible for ensuring the integrity of the replicated data. That is, you must design such procedures either to avoid or to detect replication conflicts and resolve them appropriately. Consequently, procedural replication is most typically used when databases are available only for the processing of large batch operations. In such situations, replication conflicts are unlikely because numerous transactions are not contending for the same data.

Additional Information: See Oracle8 Replication.

#### Synchronous (Real-Time) Data Propagation

Asynchronous data is the normal configuration for advanced replication environments. However, Oracle also supports synchronous data propagation for applications with special requirements. Synchronous data propagation occurs when an application updates a local replica of a table, and within the same transaction also updates all other replicas of the same table. Consequently, synchronous data replication is also called real-time data replication. Use synchronous replication only when applications require that replicated sites remain continuously synchronized.

Note: A replication system that uses real-time propagation of replication data is highly dependent on system and network availability because it can function only when all sites in the system are concurrently available.

As Figure 31-12 shows, Oracle uses the same system of internal database triggers to generate RPCs that replicate data-level changes to other replication sites to support synchronous, row-level data replication. However, Oracle does not defer the execution of such RPCs. Instead, data replication RPCs execute within the boundary of the same transaction that modifies the local replica. Consequently, a data-level change must be possible at all sites that manage a replicated table or else a transaction rollback occurs.

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

#### Figure 31-12 Synchronous Data Replication Mechanisms

##### Replication Conflicts and Synchronous Data Replication

When a shared ownership system replicates all changes synchronously (real-time replication), replication conflicts are not possible. With real-time replication, applications use distributed transactions to update all replicas of a table at the same time. As is the case in nondistributed database environments, Oracle automatically locks rows on behalf of each distributed transaction to prevent all types of destructive interference among transactions. While a real-time replication system can prevent replication conflicts, this type of system is highly dependent on system and network availability because it can function only when all sites in the system are available.

Additional Information: See Oracle8 Replication for a full description of basic and advanced database replication.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index