Release 8.0
A58245-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Replication Release 8.0 A58245-01 |
|
This chapter explains how to configure and manage an advanced replication environment that uses snapshot sites. This chapter covers the following topics:
Note: This chapter explains how to manage an advanced replication system that uses the default replication architecture-row-level replication using asynchronous propagation. For information about configuring procedural replication and synchronous propagation, see Chapter 7, "Advanced Techniques". Also, examples appear throughout this chapter of how to use the Oracle Replication Manager tool to build and manage an advanced replication system with updatable snapshot sites. Corresponding replication management API calls are listed for your reference.
To add snapshot sites to an advanced replication environment, complete the following steps:
Detailed information about each step and other optional configuration steps are described in later sections of this chapter.
The following simple example demonstrates the steps necessary to add a snapshot site in an advanced replication environment. After configuring all master sites in an advanced replication environment, perform the following steps.
The first step is to design each snapshot site. 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 master group. In either case, they must be simple snapshots that have a one-to-one correspondence to tables at the master site. A snapshot group can also contain other replication objects such as procedures, packages, functions, synonyms, and views. This example demonstrates how to create updatable snapshots of the master tables SCOTT.EMP and SCOTT.DEPT at the master site DBS1.
Note: The primary key of SCOTT.EMP is the EMPNO column, and the primary key of SCOTT.DEPT is the DEPTNO column.
The Replication Manager setup wizard helps you configure the supporting accounts, links, schemas, and scheduling at all snapshot sites in an advanced replication system. For this example, use the setup wizard to:
Before creating snapshot groups and snapshots for a snapshot site, make sure to create the necessary snapshot logs at the master site. A snapshot log is necessary for every master table that supports at least one simple snapshot with fast refreshes. Using Replication Manager's Create Snapshot Log property sheet, create a snapshot log for the master tables SCOTT.EMP and SCOTT.DEPT.
Note: To create a snapshot log using Replication Manager, you must establish a database connection to the master site as a database administrator with the necessary privileges (for example, as SYSTEM).
Before creating snapshot groups and snapshots for a snapshot site, make sure to create the refresh groups that you will use to refresh snapshots at the snapshot site. Oracle organizes and refreshes individual snapshots as part of a refresh group. Using Replication Manager's Create Refresh Group property sheet, create the refresh group SCOTT.REFGRP_1. Simply use the default scheduling settings for the new refresh group.
When you use snapshot sites in an advanced replication environment, Oracle replicates tables and related replication objects as part of a snapshot group. Using the database connection to the snapshot site DBS3, open Replication Manager's snapshot group wizard, which then guides you through the creation of a new snapshot group, including the following tasks:
Use the wizard to create a new snapshot group that corresponds with the EMPLOYEE master group of the master site DBS1. When using the wizard, create full snapshots of the master tables SCOTT.EMP and SCOTT.DEPT. For both snapshots, use the default propagation and storage settings, and the refresh group SCOTT.REFGRP_1.
After configuring a new snapshot group, grant access to the snapshots so that users that connect to the snapshot site can use them.
GRANT SELECT ON scott.emp TO ... ;
This simple example does not mention other steps that might be necessary to configure some advanced replication systems. For example, when an advanced replication system operates using a shared ownership data model, you'll want to configure conflict resolution for all master tables before allowing applications to work with updatable snapshots. Refer to Chapter 5, "Conflict Resolution" for more detailed information about configuring conflict resolution.
Before starting to build snapshot sites as part of an advanced replication environment, you must prepare each snapshot site database with the following:
The Replication Manager setup wizard can build a snapshot site quickly with the above components. After building a snapshot site with the Replication Manager setup wizard, you must complete some additional preparation steps:
The following sections explain how to prepare a database for snapshot site replication.
Preparing a snapshot site for a default configuration is a simple process using Replication Manager's replication setup wizard. At each snapshot site that you create, this wizard performs the following steps:
To start the Replication Manager setup wizard:
The following sections explain how to use the Replication Manager setup wizard to prepare the snapshot sites in an advanced replication system.
The initial page of the replication setup wizard prompts you to indicate what type of replication environment setup that you want to perform.
The next page of the wizard asks you to identify the master site for the new snapshot site(s) that you will be creating.
The next page of the wizard lets you create a list of the snapshot sites in the replication system. At this point, it is likely that you will not have any Replication Manager database connections available to use for the setup wizard. When this is the case, perform the following steps:
The next page of the wizard lets you specify information for the database accounts that will function as each snapshot site's replication administrator and propagator. The wizard creates accounts with the same name and password at all snapshot sites in the system.
The setup wizard allows two different types of account configurations at each snapshot site:
Typically, the default wizard setup option that creates one account for both functions is acceptable. However, some systems with unique security concerns require distinct administrator and propagator accounts. For more information about specialized security configurations for snapshot sites in an advanced replication environment, see "Alternative Security Setup for an Advanced Replication Snapshot Site" on page 7-24.
The same page of the setup wizard lets you indicate what schemas to create as schemas that will contain replication objects at the new snapshot sites. The wizard creates schemas to match those that contain replication objects at the master site, as well as private database links from snapshot site schemas to corresponding master site schemas.
To add new schemas to the list:
The next page of the setup wizard lets you indicate default propagation characteristics for all snapshot sites. The setup wizard uses this information to create corresponding scheduled links from each snapshot site to the master site. For explanations of each setting in this page of the wizard, see "Managing Scheduled Links" on page 3-9.
After reviewing the default scheduling settings, click Next to continue.
The next page of the setup wizard lets you configure the default purge schedule for the deferred transaction queue at each snapshot site in the system. For explanations of each setting in this page of the wizard, see "Purging a Site's Deferred Transaction Queue" on page 3-12.
After reviewing the default purge settings, click Next to continue.
The next page of the setup wizard lets you customize settings for individual snapshot sites in the system. If you choose not to customize snapshot sites in the system, each site will have matching:
To customize a snapshot site's settings:
Next, use the pages of the Customize Snapshot Site property sheet to customize the target snapshot site's:
After reviewing the customized settings for a snapshot site, click OK. To customize another snapshot site's settings, repeat the process above. When you are finished customizing all snapshot sites, click Next to continue.
The next page of the setup wizard asks if you are ready to complete the configuration of the snapshot sites for the advanced replication system. Click Finish to continue. Replication Manager then presents an informational dialog that lets you quickly review your settings.
After you click Finish, Replication Manager builds the snapshot sites.
Note: If you want to record a script of the API procedures that are executed during the setup process, click Record a script before building the system. Additionally, Replication Manager records another script Repsetup.log in the current working directory.
Additional Information: See "Alternative Security Setup for an Advanced Replication Snapshot Site" on page 7-24.
After using the Replication Manager setup wizard, you should continue configuration by completing the following steps:
An updatable snapshot in an advanced replication environment both "pushes" and "pulls" data to and from its master table, respectively. Advanced replication systems use Oracle's snapshot refresh mechanism to pull changes asynchronously from a master table to associated updatable snapshots. In contrast, updates to an updatable snapshot are asynchronously (or synchronously) pushed to its master table using Oracle's row-level data propagation mechanisms. Because an updatable snapshot's push and pull tasks are independent operations, you configure them associatively or separately.
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.
Before you create a snapshot site, decide how you would like to refresh snapshots using snapshot refresh groups, and also how you would like to configure each snapshot group's scheduled link to propagate changes to its master site. Some planning will help make subsequent configuration steps much easier.
Additional Information: See "Managing Scheduled Links" on page 3-9 for more information about creating and managing scheduled links.
Additional Information: See "Managing Refresh Groups" on page 4-22 for more information about configuring and managing refresh groups.
To simplify administration, most advanced replication environments configure data propagation and snapshot refreshes to occur automatically. Accordingly, each snapshot site in an advanced replication environment must start one or more SNP background processes. The following initialization parameters control the SNP background process setting for each server.
A master table's snapshot log keeps track of fast refresh data for all corresponding simple snapshots. 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 very efficiently. Replication Manager has many features that help you to create and manage snapshot logs.
The following sections explain more about managing snapshot logs.
Before creating snapshot groups and snapshots for a snapshot site, make sure to create the necessary snapshot logs at the master site. A snapshot log is necessary for every master table that supports at least one simple snapshot with fast refreshes.
To create a snapshot log at the master site of a snapshot site:
The Create Snapshot Log property sheet has three pages: General, Tablespace and Extents, and Filter Columns.
To edit the storage settings or filter columns of a snapshot log:
The Edit Snapshot Log property sheet has three pages: General, Tablespace and Extents, and Filter Columns.
When a master table no longer supports snapshots that require fast refreshes, you can delete a corresponding snapshot log. To delete a snapshot log:
A snapshot group in an advanced replication system maintains a partial or complete copy of all or some of the objects in a corresponding master group. Replication Manager has many features that help you create and manage snapshot groups. The following sections explain more about managing snapshot groups.
Before you create a new snapshot group, make sure that the following preliminary tasks have been completed.
To create a new snapshot group:
Replication Manager's snapshot group wizard then guides you through the creation of a new snapshot group. The following sections explain more about the settings that each page of the snapshot group wizard lets you indicate.
API Equivalents: DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP, DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT, DBMS_REFRESH.MAKE
The first three pages of the snapshot group wizard allow you to design the basic structure of a snapshot group.
The snapshot group wizard lets you specify group and default settings for all snapshots in a new snapshot group. The following sections explain each setting.
When a snapshot group contains updatable snapshots, you must indicate how you want the DML changes made to the snapshots at the snapshot site to be propagated to the snapshot site's master site. The default setting is for asynchronous propagation. For more information about synchronous propagation in an advanced replication environment, see "Using Synchronous Data Propagation" on page 7-6.
After selecting a snapshot from the Snapshots in the group list, you can adjust many of the snapshot's properties.
Note: By default, the snapshot group wizard will create a refresh group, with the same name as the snapshot group, for all snapshots in the snapshot group.
After selecting Default Snapshot Settings from the Snapshots in the group list, you can adjust the default settings for all snapshots in the group that do not have specific settings. The wizard lets you set default storage settings, a default minimum communication setting, and a default refresh group for all snapshots in a new snapshot group.
When you create a snapshot group, Oracle automatically attempts to register the group at the master site. Should registration fail for any reason, you can manually register a snapshot group at its master site using the replication management API.
API Equivalent: DBMS_REPCAT.REGISTER_SNAPSHOT_REPGROUP
When you delete a snapshot group or switch a snapshot group's master site, Oracle automatically attempts to unregister the group at its former master site. Should this process fail for any reason, you can manually unregister a snapshot group at a master site using the replication management API.
API Equivalent: DBMS_REPCAT. UNREGISTER_SNAPSHOT_REPGROUP
To add objects to a snapshot group:
API Equivalent: DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT
To alter the definition of a replication object in a snapshot group, you should always use Replication Manager (or an equivalent API call). Use of Enterprise Manager or a SQL DDL command (for example, ALTER TABLE) to alter an object in a replicated environment can create inconsistencies.
Note: Local customization of individual replicas at a snapshot site is outside the scope of Oracle's advanced replication facility. As a replication administrator, you must ensure that local customizations do not interfere with any global customizations done with Replication Manager.
You cannot alter the definition of nonsnapshot objects in a snapshot group. For more information about altering individual snapshots in a snapshot group, see "Altering a Snapshot" on page 4-20.
To remove objects from a snapshot group:
Note: When you drop an object from a snapshot group, Replication Manager automatically removes all corresponding system-generated objects that were necessary to support the object.
API Equivalent: DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT
To edit a snapshot group:
The Edit Snapshot Group property sheet has the following pages: General and Objects.
API Equivalent: DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER, DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION
To delete a snapshot group:
Note: When you drop a snapshot group, you can also decide whether to drop the group's objects from the database. If you choose not to drop a group's objects when you drop the group, the objects remain in the database at the snapshot site. Snapshots that remain after dropping the corresponding snapshot group persist as ungrouped snapshots and will be refreshed as long as they remain in a refresh group. However, Oracle does not forward to the master site changes made to an ungrouped updatable snapshot.
API Equivalent: DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP
A table snapshot is a local copy of table data that originates from one or more remote master tables. Applications can query the data in a read-only table snapshot, but cannot insert, update, or delete rows in the snapshot. However, applications can query, insert, update, or delete the rows in an updatable snapshot.
Oracle's data replication facility supports independent table snapshots as well as simple snapshots that are part of a snapshot group. Consider the following issues when deciding whether to create a new snapshot as part of a snapshot group:
Replication Manager has many features that help you create and manage snapshots in an advanced replication environment. The following sections explain more about managing snapshots.
Note: Replication Manager is not designed to manage read-only snapshots in a basic replication environment. See Chapter 2, "Using Basic Replication" for more information about creating and managing a basic replication environment that uses read-only snapshots.
Before you create a new simple snapshot, make sure that the following preliminary tasks have been completed.
To create a read-only snapshot independent of a snapshot group:
Replication Manager's snapshot wizard then guides you through the creation of a new snapshot.
Note: When you create an updatable snapshot using Replication Manager, the snapshot wizard always creates the new snapshot as part of the appropriate snapshot group.
To create a read-only snapshot or updatable snapshot as part of a snapshot group
Replication Manager's snapshot group wizard then guides you through the process of creating new snapshot group objects, including snapshots. See "Creating a Snapshot Group" on page 4-12 for information on settings for the snapshot group wizard.
API Equivalent: DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT
Oracle supports snapshots of master table columns that use the following datatypes: NUMBER, DATE, VARCHAR2, CHAR, NVARCHAR2, NCHAR, RAW, ROWID.
Oracle also supports snapshots of master table columns that use the following large object types: binary LOBs (BLOBs), character LOBs (CLOBs), and national character LOBs (NCLOBs). However, you cannot reference LOB columns in a WHERE clause of a snapshot's defining query. The deferred and synchronous remote procedure call mechanism used for replication propagates only the piece-wise changes to the supported LOB datatypes when piece-wise updates and appends are applied these LOB columns.
Note: Oracle8 does not support replication of LOB datatypes in replication environments where some sites are running Oracle7 release 7.3.
Oracle does not support the replication of columns that use the LONG datatype. Oracle simply omits the data in LONG columns from snapshots.
Oracle also does not support user-defined object types and external or file-based LOBs (BFILEs). Attempts to configure snapshots containing columns of these datatypes returns an error message.
You can create an updatable snapshot with a WHERE clause to make the snapshot reflect a subset of the rows in its master table. For example, consider the following workflow environment where the updatable snapshots of the ORDERS master table reflect different sets of orders depending on the STATUS value of orders at the master site.
At the ORDER Database:
CREATE SNAPSHOT sales.orders FOR UPDATE AS SELECT * FROM sales.orders@dbs1.acme.com WHERE status = 'SHIPPABLE';
At SHIP Database:
CREATE SNAPSHOT sales.orders FOR UPDATE AS SELECT * FROM sales.orders@dbs1.acme.com WHERE status = 'BILLABLE';
At ACCT Database:
CREATE SNAPSHOT sales.orders FOR UPDATE AS SELECT * FROM sales.orders@dbs1.acme.com WHERE status = 'COMPLETE';
Oracle does not restrict applications from updating the columns that define the selection criteria for the result set of an updatable snapshot. For example, an application connected to the ORDER database could change the status of an order to "BILLABLE". Because of this behavior, it is possible for an updatable snapshot to contain rows that no longer match the selection criteria of the snapshot, at least until the snapshot is refreshed. For many applications, this behavior is acceptable.
Alternatively, when you want the rows of an updatable snapshot always to match the selection criteria for the snapshot, define a view on the snapshot or snapshot's base table with a CHECK constraint.
The Minimize Communication setting found in some Replication Manager wizard pages and property sheets lets you determine how much data snapshot sites must transfer to perform conflict detection for an updatable snapshot (primary key snapshots only). When you use the default setting, to minimize communication, Oracle propagates only the new values for updated columns plus the old values of the primary key and the columns in each updated column group. The following Replication Manager components provide access to the Minimize Communication setting:
Note: The default setting, to minimize communication, is valid only for Oracle8 databases. When you base an updatable snapshot on a master table in an Oracle7 release 7.3 database, you must disable the Minimize Communication setting. When disabled, Oracle propagates the old and new values of all columns in a row when any column in the row is updated. This is the behavior expected by Oracle7 release 7.3.
Additional Information: See "Minimizing Data Propagation for Update Conflict Resolution" on page 5-40.
You can create a new snapshot as a subquery snapshot when using either the snapshot group wizard or the snapshot wizard.
To create a subquery snapshot when using the snapshot group wizard
To create a subquery snapshot when using the snapshot wizard, type the subquery for the new snapshot in the Where Clause page of the snapshot wizard.
Additional Information: See "Creating Snapshots with Subqueries" on page 2-16 for more information about subquery snapshots.
Additional Information: See "Creating Updatable Snapshots with a WHERE Clause" on page 4-18 for more information about specifying a WHERE clause for a snapshot.
To generate support for an updatable snapshot:
API Equivalent: DBMS_REPCAT.GENERATE_SNAPSHOT_SUPPORT
The following sections explain how to alter a snapshot in an advanced replication environment.
To edit a snapshot's storage settings:
Do not manipulate, modify, add to, or subtract from, the data in the base table of a snapshot. You can declare integrity constraints, such as referential or uniqueness constraints, for the base table of a snapshot. However, such constraints must be configured for deferred constraint checking.
You can also define PL/SQL triggers on the base table of a snapshot. However, such triggers must be coded so that they do not fire during snapshot refresh. Triggers that fire during snapshot refresh may generate unexpected results.
Additional Information: See "Triggers and Replication" on page 7-34.
Never use Enterprise Manager or a SQL DDL command (for example, ALTER TABLE) to alter a snapshot definition. To alter the definition of a snapshot, drop the snapshot and then re-create it.
Note: Local customization of individual replicas at snapshot sites is outside the scope of Oracle's advanced replication facility. As a replication administrator, you must ensure that local customizations do not interfere with any global customizations done with Replication Manager.
To remove a snapshot from a snapshot group:
Note: When you drop a snapshot from a snapshot group, Replication Manager automatically removes all corresponding system generated objects that were necessary to support the snapshot.
API Equivalent: DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT
To preserve referential integrity and transaction consistency among the table snapshots of several related master tables, Oracle organizes and refreshes individual snapshots as part of a refresh group. 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. Replication Manager has many features that help you create and manage refresh groups in an advanced replication environment. The following sections explain more about managing refresh groups.
Additional Information: See "Creating Refresh Groups" on page 2-24 for more information about creating and managing refresh groups.
To create a new refresh group for a snapshot site:
The Create Refresh Group property sheet has three pages: General, Snapshots, and Scheduling.
Note: To refresh a snapshot, the user account of the database link used by the snapshot must have SELECT privileges on the master base table and, for fast refreshes, on the corresponding snapshot log.
API Equivalent: DBMS_REFRESH.MAKE
To add one or more snapshots to a refresh group:
API Equivalent: DBMS_REFRESH.ADD
To remove one or more snapshots from a refresh group:
API Equivalent: DBMS_REFRESH.SUBTRACT
To edit a snapshot group's refresh settings:
API Equivalent: DBMS_REFRESH.CHANGE
To force the immediate refresh of a refresh group:
API Equivalent: DBMS_REFRESH.REFRESH
To delete a refresh group:
Note: After you drop a refresh group, Oracle no longer refreshes the group's orphaned snapshots automatically. To refresh the snapshots, you must add them to another snapshot refresh group or refresh them manually.
API Equivalent: DBMS_REFRESH.DESTROY
The preceding sections of this chapter explained the most commonly performed administrative procedures that involve snapshot sites. For additional information on less commonly performed administrative procedures for snapshot sites, see "Advanced Management of Master and Snapshot Groups" on page 6-2.
In addition to using Replication Manager to view information about an snapshot site in an advanced replication environment, you can also query the following data dictionary views:
At the Snapshot Site:
At the Master Site:
