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



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

Snapshot Site Replication


This chapter explains how to use create and maintain snapshot sites. The topics discussed include the following:

Note that most of the activities described in this chapter can be accomplished much more easily by using Oracle's Replication Manager, a GUI interface for replication. See the documentation for Oracle Replication Manager for more information.

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:

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:

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 page 5 - 12.

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.

Before Creating a Snapshot Site

Before creating a snapshot site, you must already have created at least one master site, as described in Chapter 4. If you have multiple master sites in your replicated environment, you should select which master site your snapshot site will be created from.

This master site will also be used to refresh any read-only or updatable snapshots at your new snapshot site. You can change a snapshot site's master site if required.

Creating Database Links for Snapshot Sites

A snapshot site for a replicated object group must have a database link to its associated master site that contains a username (for example, SCOTT), a password (for example, TIGER), and the fully qualified database name of the master. The appropriate database links must be created before you call CREATE_SNAPSHOT_REPGROUP or SWITCH_SNAPSHOT_MASTER.

Granting the Necessary Privileges

The user at the master site that was specified in the CONNECT TO clause of the database link from the snapshot site must either be granted replication administrator privileges or be the owner of the schemas in the replicated object group in order for deferred transactions to be propagated to the master site. Additionally, for the refresh of any updatable snapshots to succeed, this user must either own the snapshots or be SYS, or have the ALTER ANY SNAPSHOT privilege.

Creating a Snapshot Replication Site

This section outlines the procedure that you must perform to create a snapshot site containing updatable snapshots. Each of these steps is described in more detail later in this chapter.

Optionally, you may perform the following step at the master site:

Perform the following steps at each snapshot site:

Creating the Replicated Object Group

Create a new empty snapshot replicated object group in your local database by calling the CREATE_SNAPSHOT_REPGROUP procedure in the DBMS_REPCAT package, as shown in the following example:

DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP(
    gname            => 'accts', 
    master           => 'acct_hq.hq.com', 
    comment          => 'created on ...',
    propagation_mode => 'asynchronous');

Attention: Notice that the replicated object group name must match the master group name.

In this example, the ACCTS object group is created in the current database. When you add replicated objects to this object group by calling CREATE_SNAPSHOT_REPOBJECT, they will be refreshed using the ACCT_HQ database as their master. Changes from the snapshot site will be asynchronously propagated to its associated master site as described [*].

Because each snapshot site may contain a different subset of the objects at its associated master site, there is no snapshot equivalent to the DBMS_REPCAT.ADD_MASTER_DATABASE procedure. If you will be creating multiple snapshot sites with similar members, you may want to create them using a script, which can be modified and re-executed at each site.

Additional Information: The parameters for the CREATE_SNAPSHOT_REPGROUP procedure are described in Table 12 - 105, and the procedures are listed in Table 12 - 106.

Creating a Replicated Object

Add a replicated object to your snapshot site by calling the procedure CREATE_SNAPSHOT_REPOBJECT in the DBMS_REPCAT package, as shown in the following example:

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
    sname    => 'accts_rec', 
    oname    => 'emp', 
    type     => 'snapshot', 
    ddl_text => 'CREATE SNAPSHOT accts_rec.emp FOR UPDATE AS 
                SELECT * FROM emp@acct_hq.hq.com WHERE 
                deptno > 500', 
    comment  => 'created on ...',
    gname    => 'acct')
    gen_obj_owner ==> 'REPADMIN';

In this example, Oracle creates a snapshot at the snapshot site of the EMP table located at the master site. This snapshot contains a subset of the rows in the EMP table; that is, it only contains the rows for those employees whose department numbers are larger than 500. For example, this might be all sales staff in the western region.

Attention: Notice that the object name and updatable snapshot name must be identical. The master table must be a replicated object registered at the master site.

In this example, the SQL statement necessary to create the snapshot is passed as an argument to this procedure. Alternatively, you could have created the snapshot before calling this procedure and simply have omitted the DDL.

Warning: If you create an updatable snapshot using the FOR UPDATE clause of the CREATE SNAPSHOT command, but do not create the snapshot as a replicated object by calling CREATE_SNAPSHOT_REPOBJECT, any changes that you make to the updatable snapshot will be lost when you refresh the snapshot.

In addition to creating the snapshot as a replicated object at the snapshot site, Oracle also adds the object name and type to the RepObject view at the local site. This view is used to determine which objects need to push their changes to the master site.

Because the replicated object in this example is of type SNAPSHOT and its associated master table uses row-level replication, Oracle installs the appropriate replication support at the snapshot site. For snapshots of tables using procedural replication, be sure to replicate the associated procedure or package at the snapshot site.

Attention: Although not required, you will typically want all snapshots at a given snapshot site to be in the same snapshot refresh group. To ensure that snapshots in the same refresh group are refreshed consistently, their associated master tables must be located at the same master site.

Additional Information: The parameters for the CREATE_SNAPSHOT_REPOBJECT procedure are shown in Table 12 - 107, and the exceptions are listed in Table 12 - 108.

Creating Non-Snapshot Objects

For objects other than snapshots, you must not supply the DDL. Oracle copies the object from the master site that you designated when you created the snapshot site. If the object already exists at the snapshot site, Oracle compares the two objects and raises a duplicateobject exception if they do not match.

Creating Snapshots

For snapshots, you can either precreate the snapshot, or supply the DDL as part of the CREATE_SNAPSHOT_REPOBJECT call. For information on creating read-only snapshots, refer to Chapter 3.

Updatable snapshots are created and deleted in the same manner as read-only snapshots. See Chapter 3. To create an updatable snapshot, simply add the FOR UPDATE clause to the CREATE SNAPSHOT statement as shown in the following example:

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

Restrictions on Updatable Snapshots

Declarative constraints on snapshots and snapshot logs are not supported.

Snapshots of LONG columns are not supported.

Updatable snapshots must be simple snapshots; that is each row in the snapshot is based on a single row in a single remote table. A simple snapshot's defining query has no distinct or aggregate functions, GROUP BY or CONNECT BY clauses, subqueries, joins, or set operations.

Symmetric replication does not support replication of a subset of columns. All CREATE statements must be of the form

CREATE SNAPSHOT . . . FOR UPDATE
   AS SELECT * FROM . . .;

The following SQL statement is not supported:

CREATE SNAPSHOT . . . FOR UPDATE
   AS SELECT empno, ename FROM . . .;

Naming Updatable Snapshots

Naming conventions for updatable snapshots are the same as for read-only snapshots. See page page 3 - 4.

Privileges Required to Create Updatable Snapshots

To create an updatable snapshot, you must have the following sets of privileges:

Transaction Ownership

Under synchronous propagation, a transaction is owned by the owner of the trigger and will be propagated to the remote sit with that owner's privileges. You can change the ownership of the transaction, usually to the replication administrator who has full privileges at the remote site, by using GEN_OBJ_OWNER.

Dropping a Replicated Object

To drop a replicated object from a snapshot site, call the DROP_SNAPSHOT_REPOBJECT procedure in the DBMS_REPCAT package at that snapshot site, as shown in the following example:

DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT(
    sname        => 'acct_rec', 
    oname        => 'emp', 
    type         => 'snapshot', 
    drop_objects => TRUE);

In this example, the EMP snapshot will no longer be replicated, all supporting objects will be dropped, and the snapshot itself will be dropped. Had DROP_OBJECTS been set to FALSE, the snapshot would no longer be replicated, but the snapshot would remain in the schema until you removed it using the DROP SNAPSHOT command. The trigger and associated package used to add transactions to the deferred transaction queue, as well as any queued transactions, are not dropped until you drop the snapshot itself.

Additional Information: The parameters for the DROP_SNAPSHOT_REPOBJECT procedure are described in Table 12 - 137, and the exceptions are listed in Table 12 - 138.

Dropping a Snapshot Object Group

To drop a snapshot object group from your replicated environment, call the DROP_SNAPSHOT_REPGROUP procedure in the DBMS_REPCAT package, as shown in the following example:

DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP(
    gname         => 'accts', 
    drop_contents => TRUE);

In this example, the ACCTS replicated object group is dropped from your current snapshot site. All objects generated to support the replicated object group are dropped, and the replicated objects in the object group no longer propagate changes to their former master site.

To drop these objects completely, you must set DROP_CONTENTS to TRUE, as shown in the example. If you set DROP_CONTENTS to FALSE, the trigger generated to support replication of snapshot modifications remains.

Additional Information: The parameters for the DROP_SNAPSHOT_REPGROUP procedure are described in Table 12 - 135, and the exceptions are listed in Table 12 - 136.

Offline Instantiation

Offline instantiation of a snapshot site is primarily useful for those sites with a very large amount of snapshot data where the time required to transfer the data through network links to the new site would be prohibitive.

Creating a snapshot site using offline instantiation requires that you first create a snapshot for each table in a new snapshot replication group at the master site, then do an export of the base tables to a file or files that can then be transported (via tape or another medium) to the new site and used to instantiate the new snapshot site.

Perform the following steps at the specified sites:

CREATE SNAPSHOT FOOITEM AS SELECT * FROM ioug1.item@dbs1

CREATE SNAPSHOT FOOITEM AS SELECT * FROM ioug1.ITEM

Attention: Before creating your snapshots, ensure that you have the necessary storage space available at the master site.

For more information about using the Import/Export utilities, see page 7 - 12 and Oracle7 Server Utilities.

Propagating DML Changes

This section describes how updates made to a replicated snapshot are propagated to its associated master table, and how updates to the master table are, in turn, propagated to the snapshots. While master table changes are always propagated to the snapshot site asynchronously, in the form of a refresh, snapshot site changes can be propagated either synchronously or asynchronously. The PROPAGATION_MODE parameter of the CREATE_SNAPSHOT_REPGROUP and ALTER_SNAPSHOT_PROPAGATION commands determines how changes from the snapshot site are propagated to the master site.

How Changes are Propagated

The method that you choose to refresh and/or propagate your snapshot updates will be determined by the frequency of changes that you make to the data at the snapshot and master sites.

For example, you might want communication from the snapshot to the master to seem event-driven. By frequently propagating changes to the master site (such as every 10 seconds), you can ensure that shortly after each modification to an updatable snapshot, the change is forwarded to its associated master table. Yet, you might only refresh the snapshot once, at the start of each day, or you may never refresh the snapshot, if updates are performed only at the snapshot site.

Asynchronously Replicating Snapshot Updates to the Master Site

As shown in Figure 5 - 1, whenever you apply a change to a replicated updatable snapshot that is asynchronously propagating changes to its associated master site, the following events occur:

Figure 5 - 1. Applying Changes to an Updatable Snapshot

When Changes are Propagated

Updates are asynchronously propagated from the snapshot site to its master site whenever one of the following actions occurs:

Conflict Detection

When you asynchronously push changes from a snapshot to its master, Oracle compares the old values for the row at the snapshot site (that is, the values before any changes were applied) to the current values for the row at the master site. If the values are different, a conflict has occurred. If any conflicts are detected at the master site, Oracle invokes the appropriate conflict resolution routine, if any was specified. For example, you might create a routine to resolve a conflict between two rows by selecting the row with the most recent timestamp, or by combining the column values of the conflicting rows.

Note that if conflict resolution routines are employed, the values of some of the rows in your snapshot may change or even be removed after a refresh is performed. If you did not specify a conflict resolution routine at the master site, or if the routine specified is unable to resolve the conflict, the conflict is logged and must be resolved manually at the master site.

Additional Information: For additional information on conflict detection and resolution, refer to Chapter 6.

How Snapshot Changes are Applied to the Master Table

The DML changes propagated to the master table are applied in the same manner as changes are replicated from one master site to another master site. As shown in Figure 5 - 2, the deferred call is pushed from the snapshot site. The package at the master site applies the change to the master table. If this change results in a conflict, it must either be resolved by the appropriate conflict resolution routine, or logged in an error table.

Figure 5 - 2. Applying Changes to a Master Table 3

Two additional steps occur when changes are propagated from an updatable snapshot to the master table:

Because changes from the master site are applied at the snapshot site using the refresh mechanism, there is no need for conflict detection or resolution at the snapshot site. All conflict detection and resolution occurs at the master site.

Refreshing a Snapshot

As shown in Figure 5 - 3, when you refresh an updatable snapshot the following events occur:

Figure 5 - 3. Snapshot Refresh

Propagating Changes Between Snapshot Sites

As shown in Figure 5 - 4, snapshot sites never communicate with one another directly. Instead, they must communicate through their associated master sites. In order for a snapshot to see a change made to a snapshot at another snapshot site, the following series of events illustrates what must occur:

Figure 5 - 4. How Changes Propagate Between Snapshot Sites

Synchronously Replicating Snapshot Updates to the Master Site

If you choose to synchronously propagate your snapshot site changes to the associated master site, Oracle performs the following actions each time you modify an updatable snapshot:

The snapshot logs at both the updatable snapshot site and the master site, as well as the view and base table at the snapshot site, are updated in the same manner as if you had propagated the changes asynchronously.

Propagating DDL Changes

When you use the procedures in the DBMS_REPCAT package to make changes to your replicated environment, these changes are not visible to a snapshot site until you refresh the snapshot replicated object group. Refreshing a snapshot site updates the objects contained in the replicated object group, whereas refreshing a snapshot updates the values of the rows of a snapshot object in the replicated object group.

Refreshing a Snapshot Site

To refresh a snapshot site with the most recent information from its associated master site, call the REFRESH_SNAPSHOT_REPGROUP procedure in the DBMS_REPCAT package at that snapshot site, as shown in the following example:

DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP(
    gname                 => 'accts', 
    drop_missing_objects  => TRUE, 
    refresh_snapshots     => TRUE, 
    refresh_other_objects => TRUE
    execute_as_user       => FALSE);

In this example, all of the snapshots and other objects in the ACCTS object group of your current snapshot site will be refreshed, and any objects dropped at the associated master site will be dropped from the snapshot site. All snapshots in the replicated object group are consistently refreshed using the FORCE refresh option.

By default, only the RepGroup views for the given replicated object group are updated. Refreshing the RepGroup view implies that any changes made to the replicated environment, such as the addition of a new master site, or the removal of an object from the object group, are made visible to the snapshot site.

You can optionally choose to refresh the replicated snapshots and non-snapshot objects, such as views and procedures. Snapshots are refreshed as described [*]. Oracle refreshes the non-snapshot objects, if necessary, by dropping and re-creating them using the definition of the object at the associated master site.

Additional Information: The parameters for the REFRESH_SNAPSHOT_REPGROUP procedure are described in Table 12 - 155, and the exceptions are listed in Table 12 - 156.

Attention: During a snapshot refresh, Oracle locks the base table of the snapshot in exclusive mode. Because other users are prevented from updating a snapshot during a refresh (queries are still available), you should schedule your refreshes to occur when the expected activity on the snapshot is low.

Altering a Replicated Snapshot

If you must alter the shape of a snapshot as the result of a change to its master, you must drop and recreate the snapshot by calling the DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT procedure and then the DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT procedure.

Altering the Propagation Method of a Replicated Snapshot

To alter the method used to propagate changes from the snapshot site to its associated master site, use the DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION procedure, as shown in the following example:

DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION(
     gname             => 'accts',
     propagation_mode  => 'synchronous'
     execute_as_user   => 'REPADMIN');

In this example, all of the updatable snapshots in the ACCTS object group will now synchronously propagate their changes to their associated master tables.

When you call this procedure from the snapshot site, Oracle pushes the deferred transaction queue at the snapshot site, locks the snapshot base tables, and regenerates any triggers and their associated packages.

Additional Information: The parameters for the ALTER_SNAPSHOT_PROPAGATION procedure are described in Table 12 - 84, and the exceptions are listed in Table 12 - 85.

Forcing Ownership of a Transaction

You can change the ownership of a transaction, usually to the replication administrator who has full privileges at the remote site, by using EXECUTE_AS_USER.

Listing Snapshot Information

Query the DBA_SNAPSHOTS catalog view to obtain a listing of all of the snapshots in a database. For information on snapshot refresh groups, query the USER_REFRESH and USER_REFRESH_CHILDREN views. Query DBA_SNAPSHOT_LOGS at the master site to see master log information.

Sample Application

Suppose that you have the following tables in your INVENTORY database: CUSTOMER, ORDERS, ORDER_LINE, ITEM, and STOCK.

Now suppose that you decide to replicate these tables to multiple sites. Because you have chosen to asynchronously propagate your changes between sites, you decide to avoid possible update conflicts by partitioning the ownership of the data based on workflow.

To partition ownership, you add a STATUS column to the ORDERS table. The status of an order can be: S (shippable), B (billable), O (outstanding bill), or C (complete).

This example has two order entry sites, so you must take steps to ensure either that conflicts do not occur, or that they can be resolved. In this example, all orders have a unique order ID.

The sequence used to generate this ID is partitioned between the master definition site and this snapshot site. Even if the same customer places one order at the master definition site and another at the snapshot site, each order will have a unique ID and will be treated separately. In this example, only one of the sites is allowed to update the CUSTOMER table, therefore no conflict resolution is required for this table.

This example assumes that the appropriate database links have been created at each site, and that the appropriate privileges have been granted to the replication administrator at each site.

To create your replicated environment, perform the following steps:

 	DBMS_REPCAT.CREATE_MASTER_REPGROUP('inventory')

 -- replicate customer table
DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct','customer',
                                    'table','inventory',); 
-- insert appropriate calls to conflict resolution methods
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct',
                                         'customer','table');
 -- replicate orders table
DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct','orders',
                                    'table','inventory');
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct',
                                         'orders','table');
 -- replicate order_line table
DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct','order_line',
                                    'table','inventory');
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct',
                                         'order_line','table');
 -- replicate item table
DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct',
                                    'item','table','inventory');
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct','item',
                                         'table');
 -- replicate stock table
DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct','stock','table',
                                    'inventory');
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct','stock',
                                         'table');

 DBMS_REPCAT.ADD_MASTER_DATABASE('inventory', 'dbs2');

 DBMS_REPCAT.RESUME_MASTER_ACTIVITY('inventory');

 DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP('inventory', 'dbs2',
                                      'asynchronous')

 -- create read-only snapshot of customer table
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      'acct', 'customer', 'snapshot', 
      'CREATE SNAPSHOT customer' || 
      'AS SELECT * FROM customer@dbs2','','inventory');
 -- create updatable snapshot of orders table
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      'acct','orders','snapshot', 
      'CREATE SNAPSHOT orders FOR UPDATE AS' || 
      'SELECT * FROM orders@dbs2 WHERE status = ''B''',
      '','inventory');
 -- create updatable snapshot of order_line table
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      'acct', 'order_line', 'snapshot', 
      'CREATE SNAPSHOT order_line FOR UPDATE AS' || 
      'SELECT * FROM order_line@dbs2','','inventory');
 -- create updatable snapshot of item table
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      'acct', 'item', 'snapshot', 
      'CREATE SNAPSHOT item FOR UPDATE AS' || 
      'SELECT * FROM item@dbs2','','inventory')




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