Oracle8 Replication Release 8.0 A58245-01 |
Library | Product | Contents | Index

# 4
Using Snapshot Site Replication

This chapter explains how to configure and manage an advanced replication environment that uses snapshot sites. This chapter covers the following topics:

- Quick Start: Adding a Snapshot Site to an Advanced Replication System.
- Preparing for Snapshot Site Replication.
- Managing Snapshot Logs.
- Managing Snapshot Groups.
- Managing Snapshots.
- Managing Refresh Groups.
- Other Snapshot Site Administration Issues.

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.

## Quick Start: Adding a Snapshot Site to an Advanced Replication System

To add snapshot sites to an advanced replication environment, complete the following steps:

1. Design the snapshot sites for the advanced replication environment. Decide what tables and supporting objects to replicate at each snapshot site, and which master site in the system will support each snapshot site.
2. Use Replication Manager's setup wizard to configure a number of databases as snapshot sites. The Replication Manager setup wizard quickly configures all components necessary to support each snapshot site.
3. At each master site, create the snapshot logs to support the proposed snapshots.
4. At each snapshot site, create the refresh groups to support the proposed snapshots.
5. At each snapshot site, create one or more snapshot groups to replicate corresponding master group objects.
6. Grant privileges so application users can access data at each snapshot site.

Detailed information about each step and other optional configuration steps are described in later sections of this chapter.

### A Simple Example

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.

#### Step 1: Design the Snapshot Site

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.

#### Step 2: Use the Replication Manager Setup Wizard

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:

- Identify the master site DBS1.
- Identify the snapshot site DBS3.
- Create the default SNAPADMIN account at the snapshot site to serve as global replication administrator and propagator account.
- Create the schema SCOTT at the snapshot site to support the proposed snapshots.
- Configure the default scheduling of data propagation from the snapshot site to its master site and purging of the deferred transaction queue.

#### Step 3: Create Necessary Snapshot Logs at the Master Site

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).

#### Step 4: Create Necessary Refresh Groups at the Snapshot Site

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.

#### Step 5: Use the Replication Manager Snapshot Group Wizard

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:

- Choosing a master site for the new snapshot group.
- Picking a master group for the new snapshot group.
- Choosing the master group objects to replicate in the new snapshot group.
- Adjusting group settings and individual snapshot settings.

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.

#### Step 6: Grant Access to Replication Objects

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 ... ;

#### Other Steps

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.

## Preparing for Snapshot Site Replication

Before starting to build snapshot sites as part of an advanced replication environment, you must prepare each snapshot site database with the following:

- A replication administrator.
- A replication propagator.
- Database links to provide for snapshot site-to-master site database communication.

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:

- Decide how to propagate changes to the master site.
- Decide how to refresh snapshots.
- Start one or more SNP background processes at the snapshot site.

The following sections explain how to prepare a database for snapshot site replication.

### The Replication Setup Wizard

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:

- The wizard creates a database account to serve as a snapshot site replication administrator. By default, the wizard creates this account to serve also as the replication propagator.
- The wizard grants the necessary privileges to the replication administrator/propagator account.
- The wizard creates a scheduled link from the snapshot site to its corresponding master site.

To start the Replication Manager setup wizard:

1. Click File.
2. Click Setup Wizard.

The following sections explain how to use the Replication Manager setup wizard to prepare the snapshot sites in an advanced replication system.

#### Create Snapshot Sites

The initial page of the replication setup wizard prompts you to indicate what type of replication environment setup that you want to perform.

1. Select Setup Snapshot Sites.
2. Click Next.

#### Specify the Master Site

The next page of the wizard asks you to identify the master site for the new snapshot site(s) that you will be creating.

1. Type the name of the master site (for example, DBS1).
2. Type the password for the SYSTEM user at the master site.

#### Create Snapshot Sites

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:

1. Click New.
2. In the New Snapshot Site dialog that appears, enter the global database name of a snapshot site, as well as the password for the SYSTEM account at the site. (The setup wizard uses the SYSTEM account to perform subsequent configuration tasks.)
3. When you finish, click Add to add the site to the list of snapshot sites in the setup wizard.
4. Repeat Step 2 for each snapshot site. After you enter the final site's information, click OK to add the site and dismiss the New Snapshot Site dialog.
5. After reviewing the list of snapshot sites, click Next to continue.

#### Create Replication Administrator and Propagator Accounts

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:

- By default, the wizard creates a single account that is capable of performing both administrator and propagator functions. Simply enter an account name and password and then press Next to continue.
- Alternatively, to create a separate account for replication administration apart from the propagator account, click Different Propagator and enter different account information for the propagator user account. Then press Next to continue.

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.

#### Create Schemas to Organize Replication Objects

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:

1. Click Select Object Schemas.
2. From the Select from Master Schemas list that appears, select all master site schemas that contain replication objects that you will be replicating to the new snapshot sites. After reviewing the list of schemas, click Next to continue. Replication Manager then prompts for the password of each master site schema that you specified. Enter each password and click OK to continue.

#### Create Scheduled Links

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.

#### Specify Purge Scheduling

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.

#### Customize Each Snapshot Site

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:

- Replication administrators and propagators.
- Database link specifications.
- Scheduled propagation settings.

To customize a snapshot site's settings:

1. Select the target snapshot site to customize.
2. Click Customize.

Next, use the pages of the Customize Snapshot Site property sheet to customize the target snapshot site's:

- Replication administrator and/or propagator accounts.
- USING CLAUSE for administrator/propagator database links.
- Scheduled propagation and purge settings.
- Supporting replication schemas.

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.

#### Reviewing and Building the Configuration

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.

- If everything looks good, click OK to prepare the new snapshot sites for the replication environment.
- If you find a setting that is not correct, click Cancel to return to the Finish page. Then click Back and Next to navigate among the pages of the wizard and change any setting. Return to the final page of the wizard when you are ready to build the environment.

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.

#### What's Next?

After using the Replication Manager setup wizard, you should continue configuration by completing the following steps:

1. Create snapshot groups for each snapshot site. See "Managing Snapshot Groups" on page 4-12 for more information about creating and managing snapshot groups.
2. Configure conflict resolution for the master tables of all updatable snapshots in each replication group. See Chapter 5, "Conflict Resolution" for more information about configuring conflict resolution for replication objects.
3. Resume replication activity for each master group. See "Resuming Replication Activity for a Master Group" on page 3-19 for more information about managing the replication activity for master groups.
4. Monitor the replication environment to ensure that the system is operating properly. See "Monitoring an Advanced Replication System" on page 6-4 and "Other Snapshot Site Administration Issues" on page 4-24 for more information about monitoring the activity of master and snapshot groups, respectively.

### Planning Scheduled Links and Snapshot Refreshes

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.

- 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.

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.

### Starting SNP Background Processes

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.

- JOB_QUEUE_PROCESSES specifies the number of SNPn background processes per server, where n is 0 to 9 followed by A to Z. Set this parameter to a value of one or higher. Two or three background processes will usually be sufficient unless you have a large system.
- JOB_QUEUE_INTERVAL specifies the interval, in seconds, between wake-ups for the SNP background processes of a server. The default of 60 seconds is adequate for typical replication environments.

## Managing Snapshot Logs

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.

### Creating a Snapshot Log

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:

1. Click anywhere in the open database connection that connects to the master site.
2. Click the Create New toolbar button.
3. Click New Snapshot Log.

The Create Snapshot Log property sheet has three pages: General, Tablespace and Extents, and Filter Columns.

- Use the settings of the General page to identify the schema and name of the master table, and indicate whether the log supports primary key snapshots, ROWID snapshots, or both.
- Use the settings of the Tablespace and Extents page to specify storage specifications for the log.
- Use the settings of the Filter Columns page to identify filter columns for snapshots that contain subqueries.

### Altering a Snapshot Log

To edit the storage settings or filter columns of a snapshot log:

1. Click on the target snapshot log at the master site.
2. Click the Properties toolbar button.

The Edit Snapshot Log property sheet has three pages: General, Tablespace and Extents, and Filter Columns.

- Use the settings of the General page to supplement whether the log supports primary key snapshots, ROWID snapshots, or both.
- Use the settings of the Tablespace and Extents page to modify the storage specifications for the log.
- Use the settings of the Filter Columns page to modify the filter columns for snapshots that contain subqueries.

### Deleting a Snapshot Log

When a master table no longer supports snapshots that require fast refreshes, you can delete a corresponding snapshot log. To delete a snapshot log:

1. Click on the target snapshot log at the master site.
2. Click the Delete toolbar button.

## Managing Snapshot Groups

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.

### Creating a Snapshot Group

Before you create a new snapshot group, make sure that the following preliminary tasks have been completed.

- Create the scheduled link that the new snapshot group can use to propagate updates to the master site.
- If you plan to create a simple snapshot with fast refresh as part of creating a new snapshot group, make sure to create the necessary snapshot log before creating the group.

To create a new snapshot group:

1. Click anywhere in the open database connection that connects to the snapshot site.
2. Click the Create New toolbar button.
3. Click 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

#### Designing a Snapshot Group

The first three pages of the snapshot group wizard allow you to design the basic structure of a snapshot group.

- First, you must specify the master site for the new group.
- Next, you must choose the corresponding master group for the new snapshot group.
- Finally, you must select which objects in the master group to replicate as part of the new snapshot group; you can choose all objects in the master group, some, or none.

#### Setting Snapshot Group Settings and Defaults

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.

##### Group Propagation Mode

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.

##### Individual Snapshot Settings

After selecting a snapshot from the Snapshots in the group list, you can adjust many of the snapshot's properties.

- Click Updatable to create the selected snapshot as an updatable snapshot.
- Click Fast Refresh to use fast refreshes for the selected snapshot.
- Click Where clause and then Edit to specify a WHERE clause for the selected snapshot. See "Creating Updatable Snapshots with a WHERE Clause" on page 4-18 for more information.
- Click Tablespace & Extent Specs to specify storage settings for the selected snapshot.
- Click Minimum Communication to minimize the communication necessary to perform conflict detection for the selected updatable snapshot. See "Minimizing Data Propagation" on page 4-19 for more information about this setting.
- Select a Refresh Group to choose a refresh group for the selected snapshot. Click Edit to edit the properties of a selected refresh group; click New to create a new refresh group.

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.

##### Default Snapshot Settings

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.

### Registering a Snapshot Group at its Master Site

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

### Unregistering a Snapshot Group at its Master Site

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

### Adding Objects to a Snapshot Group

To add objects to a snapshot group:

1. Click the target snapshot group at the snapshot site.
2. Click the Add to group toolbar button.
3. Click Add Objects to a Snapshot Group.
4. Use the snapshot group wizard to add selected objects from the master group.
5. Click Next.
6. If you selected one or more master tables in Step 5, you can use the wizard to specify individual settings for corresponding snapshots.
7. Click Finish to have the wizard add the new objects to the snapshot group.

API Equivalent: DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT

### Altering Objects in a Snapshot Group

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.

### Deleting Objects from a Snapshot Group

To remove objects from a snapshot group:

1. Click the target snapshot group at the snapshot site.
2. Select the target objects to remove from the group.
3. Click the Delete toolbar button.

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

### Editing a Snapshot Group

To edit a snapshot group:

1. Click the target snapshot group at the snapshot site.
2. Click the Properties toolbar button.

The Edit Snapshot Group property sheet has the following pages: General and Objects.

- Use the General page to edit the target snapshot group's master site and propagation mode to the master site.
- Use the Objects page to add and remove objects in the snapshot group.

API Equivalent: DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER, DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION

### Deleting a Snapshot Group

To delete a snapshot group:

1. Click the target snapshot group at the snapshot site.
2. Click the Delete toolbar button.

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

## Managing Snapshots

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:

- A snapshot group can contain simple, read-only and updatable snapshots.
- A snapshot group does not support complex read-only snapshots. Oracle creates all complex read-only snapshots independent of any 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.

### Creating a Snapshot

Before you create a new simple snapshot, make sure that the following preliminary tasks have been completed.

- If you are creating a simple snapshot with fast refresh, create the necessary snapshot log for the master table before creating the snapshot.
- Create a refresh group that snapshots can use for automatic refreshes.

To create a read-only snapshot independent of a snapshot group:

1. Click anywhere in the open database connection that connects to the snapshot site.
2. Click the Create New toolbar button.
3. Click New Snapshot.

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

1. Click the target snapshot group at the snapshot site.
2. Click the Add to Group toolbar button.
3. Click Add Objects to 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

#### Datatype Considerations for Snapshots

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.

#### Creating Updatable Snapshots with a WHERE Clause

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.

#### Minimizing Data Propagation

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:

- Snapshot group wizard.
- Snapshot wizard.
- Edit Replication Object property sheet.

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.

### Creating Snapshots with Subqueries

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

1. Click the snapshot in the Snapshots in the group list of the Individual Snapshot Settings page of the snapshot group wizard.
2. Click the Where clause option, then click the corresponding Edit button.
3. Type the subquery for the new snapshot.

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.

### Regenerating Replication Support for an Updatable Snapshot

To generate support for an updatable snapshot:

1. Click the target snapshot.
2. Click the Properties toolbar button.
3. Click Generate Support on the General page of the Edit Replication Object property sheet.

API Equivalent: DBMS_REPCAT.GENERATE_SNAPSHOT_SUPPORT

### Altering a Snapshot

The following sections explain how to alter a snapshot in an advanced replication environment.

#### Editing a Snapshot's Storage Settings

To edit a snapshot's storage settings:

1. Click the target snapshot.
2. Click the Properties toolbar button.
3. Modify the settings of the Tablespace and Extents page of the Edit Snapshot property sheet (for snapshots independent of a group) or Edit Replication Object property sheet (for snapshots within a group).

#### Manipulating a Snapshot's Base Table

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.

#### Altering a Snapshot Definition

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.

### Deleting a Snapshot

To remove a snapshot from a snapshot group:

1. Click the target snapshot group at the snapshot site.
2. Select the target snapshots to remove from the group.
3. Click the Delete toolbar button.

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

## Managing Refresh Groups

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.

### Creating a Refresh Group

To create a new refresh group for a snapshot site:

1. Click anywhere in the database connection that connects to the snapshot site.
2. Click the Create New toolbar button.
3. Click New Refresh Group.

The Create Refresh Group property sheet has three pages: General, Snapshots, and Scheduling.

- Use the General page to specify the name and owner for the group.
- Use the Snapshots page to add snapshots to the refresh group.
- Use the Scheduling page to configure the refresh group's refresh settings.

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

### Adding Snapshots to a Refresh Group

To add one or more snapshots to a refresh group:

1. Click on a snapshot group or the Ungrouped Snapshots folder at the snapshot site.
2. Drag and drop selected snapshots into the target refresh group at the same snapshot site.

API Equivalent: DBMS_REFRESH.ADD

### Deleting Snapshots from a Refresh Group

To remove one or more snapshots from a refresh group:

1. Click the target refresh group at the snapshot site.
2. Select the target objects to remove from the group.
3. Click the Delete toolbar button.

API Equivalent: DBMS_REFRESH.SUBTRACT

### Changing Refresh Settings for a Snapshot Group

To edit a snapshot group's refresh settings:

1. Click the target refresh group at the snapshot site.
2. Click the Properties toolbar button.
3. Click the Scheduling page of the Edit Refresh Group property sheet.
4. Specify the new refresh settings for the refresh group.

API Equivalent: DBMS_REFRESH.CHANGE

### Manually Refreshing a Group of Snapshots

To force the immediate refresh of a refresh group:

1. Click the target refresh group at the snapshot site.
2. Click the Properties toolbar button.
3. Click the General page of the Edit Refresh Group property sheet.
4. Click Refresh Now.

API Equivalent: DBMS_REFRESH.REFRESH

### Deleting a Refresh Group

To delete a refresh group:

1. Click the target refresh group at the snapshot site.
2. Click the Delete toolbar button.

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

## Other Snapshot Site Administration Issues

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.

## Data Dictionary Views

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:

- DBA_SNAPSHOTS
- DBA_REFRESH
- DBA_REFRESH_CHILDREN

At the Master Site:

- DBA_REGISTERED_SNAPSHOT
- DBA_SNAPSHOT_LOGS

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