Oracle7 Server Distributed Systems Volume II: Replicated Data

Replication Support at Snapshot Sites

A snapshot site can contain a subset of the information stored at its master site or all the objects at a master site. A snapshot site can contain the following objects:

read-only snapshots of master tables in the replicated object group

updatable snapshots of master tables in the replicated object group

replicated triggers used to queue deferred remote procedure calls from the snapshot site to its associated master site

objects that are replicated only in the sense that their SQL definitions are replicated (procedures, packages, functions, synonyms, and views)

Replication Support for Snapshots

Read-only snapshots require no special support from the symmetric replication facility. Read-only snapshots are created and refreshed in the same manner as described in Chapter 3.

Attention: This chapter describes only the differences between updatable and read-only snapshots. If you are not already familiar with snapshots, snapshot refresh groups, and the refresh mechanism, you must read Chapter 3 before proceeding.

Similar to read-only snapshots, an updatable snapshot is a full copy of a table or a subset of a table that reflects a recent state of the master table. Unlike read-only snapshots, updatable snapshots must be derived from a single master table; that is, they must be simple snapshots.

Also, like read-only snapshots, updatable snapshots must be periodically refreshed to apply the changes made to the master table. However, unlike read-only snapshots, when you refresh an updatable snapshot, the changes to the snapshot must also be taken into account.

When you create your updatable snapshots using the procedures provided with the symmetric replication facility, the changes made to the updatable snapshot are either synchronously or asynchronously applied at the master site, in much the same manner as changes are propagated between two master sites.

Changes from the master site, however, are asynchronously propagated to the snapshot site in the form of a refresh, in much the same manner as read-only snapshots are refreshed from their masters. The refresh mechanism for updatable snapshots is described in detail .

Updatable Snapshot Architecture

In addition to the objects created for read-only snapshots that are described

, when you create an updatable snapshot, two additional objects are created at the snapshot site:

Oracle creates a table, named USLOG$_snapshot_name, to store the ROWID and timestamp of rows updated in the snapshot. The timestamp column is not updated until the log is first used by a snapshot refresh.

Oracle creates an AFTER ROW trigger on the snapshot base table to insert the ROWIDs and timestamps of updated and deleted rows into the updatable snapshot log. The trigger is named USTRG$_snapshot_name.

When you create the snapshot as a replicated object, Oracle creates an additional trigger and associated package on the snapshot base table to call the generated procedures at the master site to apply the changes.

If you are propagating your changes synchronously, the trigger package directly executes the generated procedures at the master site, if you are using asynchronous propagation, the trigger package inserts the necessary deferred transactions into the deferred transaction queue at the snapshot site.

Of course, the primary difference between the architecture of read-only and updatable snapshots is that for read-only snapshots, Oracle creates a read-only view of the underlying base table, while for updatable snapshots, this view is writable.

If your CREATE SNAPSHOT statement includes a restriction in the where clause, this restriction is reflected in the values that are displayed when you create or refresh the snapshot. For example, if you issue the following statement:

CREATE SNAPSHOT emp FOR UPDATE
   AS SELECT * FROM scott.emp@sales.ny.com
     WHERE empno > 500;

your EMP snapshot will only display employees with employee numbers greater than 500. This behavior is identical to read-only snapshots. However, you are not restricted from updating this number, or inserting an employee with an employee number less than 500.

These values will remain in the table, and can be updated or deleted, until the next time that you refresh the snapshot. The refresh will remove any remaining rows with an employee number less than 500 for the snapshot. For more information on how these changes are propagated between the snapshot and its associated master table, see .

If you want to restrict the data in the updatable snapshot to always satisfy the requirements specified in the WHERE clause of the original CREATE statement, you should define your own view on the snapshot base table, using a CHECK constraint.

Replication Support for Non-snapshot Objects

Do not alter non-snapshot objects at the snapshot site. These objects must be altered only at the master definition site by using the DBMS_REPCAT package.

If your snapshot site contains any non-snapshot replicated objects that were altered using the DBMS_REPCAT package at its associated master site (these would typically result from DDL changes propagated from the master definition site), these changes can be applied at the snapshot site the next time that you refresh the replicated schema with REFRESH_OTHER_OBJECTS set to TRUE.