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

# 2
Using Basic Replication

This chapter explains how to configure and manage a basic replication environment using read-only table snapshots. This chapter covers the following topics.

- Quick Start: Building a Basic Replication Environment.
- Preparing a Database for Snapshots.
- Creating Snapshot Logs.
- Creating Simple Snapshots.
- Creating Snapshots with Subqueries.
- Creating Refresh Groups.
- Managing a Basic Replication Environment.
- Tuning Performance for Snapshots.
- Other Basic Replication Options.
- Monitoring Basic Replication Environments.

Note: Examples in this chapter explain how to use both SQL commands and Oracle's Enterprise Manager to build and manage basic replication systems. For complete information about Enterprise Manager, see your Enterprise Manager documentation.

## Quick Start: Building a Basic Replication Environment

To create a basic replication environment that uses table snapshots to provide read-only access to a master site, complete the following steps:

1. Design the basic replication environment. Decide which master tables you want to replicate using read-only table snapshots, and which databases require such snapshots.
2. At each snapshot site, create the schemas and database links necessary to support snapshots.
3. At the master site, create the snapshot logs necessary to support fast refreshes of all snapshots.
4. Create the snapshots at each snapshot site.
5. At each snapshot site, create the refresh groups that the snapshots will use to refresh, and assign each snapshot to a refresh group.
6. Grant privileges necessary for application users to access snapshots.

For detailed information about each step, see the later sections of this chapter.

### A Simple Example

The following simple example demonstrates the steps necessary to build a basic replication environment.

#### Step 1: Design the Environment

The first step is to design the basic replication environment. This example demonstrates how to replicate the tables SCOTT.EMP and SCOTT.DEPT at the master site DBS1 using corresponding snapshots in DBS2.

Note: The primary key of SCOTT.EMP is the EMPNO column, and the primary key of SCOTT.DEPT is the DEPTNO column.

#### Step 2: Create Snapshot Site Schemas and Database Links

The master site, DBS1, already has the schema SCOTT with the tables EMP and DEPT, which have primary keys. The snapshot site must have a corresponding schema SCOTT to contain the proposed snapshots. Additionally, the snapshot schema SCOTT must have a private database link that establishes connections to the corresponding schema at the master site.

The following SQL command script completes the schema and database link setup at the snapshot site.

    CONNECT system/manager@dbs2;
    CREATE USER scott IDENTIFIED BY tiger QUOTA UNLIMITED ON data;
    GRANT CONNECT TO scott;
    CONNECT scott/tiger@dbs2;
    CREATE DATABASE LINK dbs1 CONNECT TO scott IDENTIFIED BY tiger;

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

Before creating snapshots, create the master site snapshot logs that will be necessary to support fast refreshes for the snapshots. The following SQL command script demonstrates how to create snapshot logs at the master site to support the snapshots.

    CONNECT system/manager@dbs1;
    CREATE SNAPSHOT LOG ON scott.emp;
    CREATE SNAPSHOT LOG ON scott.dept;

#### Step 4: Create Snapshots

Once the necessary snapshot logs are in place, you can create the snapshots. The following SQL command script demonstrates how to create the snapshots SCOTT.EMP and SCOTT.DEPT at the snapshot site.

    CONNECT system/manager@dbs2;
    CREATE SNAPSHOT scott.emp AS SELECT * FROM scott.emp@dbs1.acme.com;
    CREATE SNAPSHOT scott.dept AS SELECT * FROM scott.dept@dbs1.acme.com;

#### Step 5: Create Snapshot Site Refresh Groups

After creating the snapshots, make sure to assign all related snapshots to a refresh group that the snapshots will use to refresh. The following SQL command script demonstrates how to create and schedule the refresh group SCOTT.REFGRP1 at the snapshot site and assign to it the new EMP and DEPT snapshots.

    CONNECT system/manager@dbs2;
    DBMS_REFRESH.MAKE(
      name => 'scott.refgrp1',
      list => 'scott.dept,scott.emp',
      next_date => SYSDATE,
      interval => 'SYSDATE+1/24');
    COMMIT;

#### Step 6: Grant Access to Snapshots

After configuring snapshots, grant access to users.

    GRANT SELECT ON scott.emp TO ... ;

## Preparing a Database for Snapshots

Before building snapshots in a basic replication environment, you must prepare for the snapshots at each snapshot site. Specifically, each snapshot site must have the schemas and database links to support the proposed snapshots. To simplify the configuration of a basic replication environment, implement the schema and database link design that Figure 2-1 and the following sections describe.

#### Figure 2-1 Recommended Schema and Database Link Configuration For Basic Replication Environments

### Necessary Schemas

A schema containing a snapshot in a snapshot database should correspond to the schema that contains the master table in the master database. Therefore, identify the tables and encompassing schemas in the master database that you want to replicate using read-only table snapshots. Then in the snapshot database, create accounts with the same names as the schemas that contain master tables in the master database. For example, if all master tables are in the SALES schema of the DB1 database, create a corresponding SALES account in the snapshot database DB2.

### Necessary Database Links

The defining query of a snapshot uses one or more database links to reference remote table data. Before creating snapshots, the database links you plan to use with snapshots must be available. Furthermore, the account that a database link uses to access a remote database defines the security context under which Oracle creates and subsequently refreshes a snapshot.

To ensure proper behavior, a snapshot's defining query must use a database link that includes an embedded user name and password in its definition. This type of database link always establishes connections to the remote database using the specified account. Additionally, the remote account that the link uses must have the privileges necessary to access the data referenced in the snapshot's defining query.

To simplify the setup of a basic replication environment, create a private database link from each snapshot schema in the snapshot database to the corresponding schema in the master database. Be sure to embed the associated master database account information in each private database link at the snapshot database. For example, the SALES schema at a snapshot database DB2 should have a private database link DB1 that connects using the SALES username and password.

Note: For additional information about snapshot queries among distributed systems, see the book, Oracle8 Distributed Database Systems.

### Planning for Snapshot Refreshes

Before you create snapshots, you should have an idea of how you would like to refresh them using snapshot refresh groups. Some planning will help make the configuration of refresh groups much easier.

#### Granting Required Privileges

Privilege management can be challenging in a basic replicated environment. To create and refresh a snapshot, both the creator and snapshot owner must be able to issue the defining query of the snapshot. This capability depends directly on the database link that the snapshot's defining query uses to access the master table of the snapshot. To simplify privilege management for snapshot refreshes in a basic replication environment, use the recommended schema/database link setup described in the previous sections.

#### Designing Refresh Groups

Refresh groups not only provide a mechanism to refresh a number of snapshots efficiently as a group, but also allow you to preserve the referential integrity and transaction consistency among the table snapshots of several related master tables. After refreshing all snapshots in a refresh group, the data of all snapshots in the group corresponds to the same transaction consistent point in time.

Before building a basic replication environment, identify the snapshots that require referential and transaction integrity. Then group related snapshots accordingly into the same refresh group.

Note: Do not arbitrarily group snapshots to form large refresh groups. Snapshot refreshes of an unnecessarily large refresh group can generate a significant amount of rollback data requiring the use of a large rollback segment.

Additionally, it's important to decide how often applications will require a refresh of their snapshots. If the master tables receive predictable updates, automatically refresh the associated snapshots at the appropriate interval.

#### Starting SNP Background Processes

To simplify administration, most basic replication environments configure refresh groups to refresh all snapshots automatically. Each snapshot database in a basic replication environment must start one or more SNP background processes to support the automatic refresh of snapshot refresh groups. 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. When you configure refresh groups to update snapshots automatically, set this parameter to a value of one or higher. One snapshot refresh process will usually be sufficient unless you have many refresh groups that refresh simultaneously.
- 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.

## Creating Snapshot Logs

Every master table must have an associated snapshot log to support efficient, fast refreshes of corresponding snapshots. If a master table does not have an associated snapshot log, then complete refreshes of corresponding snapshots are the only option.

Oracle creates the snapshot log for a master table in the same database as the table. A master table's snapshot log is a table itself. When a transaction changes information in the master table, an internal trigger on the master table automatically inserts rows into the corresponding snapshot log. Rows in a snapshot log list changes made to the master table, as well as information about which snapshots have and have not been updated to reflect the changes at the master table.

A snapshot log is associated with a single master table. Likewise, a master table can have only one snapshot log. When a master table is the data source for several different snapshots, perhaps in different databases, all snapshots of the master table use the same snapshot log.

Note: For complete information about managing snapshot logs, see "Managing Snapshot Logs" on page 2-28.

To create a snapshot log for a master table, use Enterprise Manager's Schema Manager application or the SQL command CREATE SNAPSHOT LOG. The following example creates a snapshot log.

The equivalent CREATE SNAPSHOT LOG statement is:

    CREATE SNAPSHOT LOG ON scott.emp;

Before creating a snapshot log for a master table, you should consider several issues, as described in the sections that follow.

### Snapshot Log Names

When you create a snapshot log for a master table, Oracle automatically creates the log as a table MLOG$_master_table_name in the schema that contains the master table. If a master table name is longer than 20 bytes, Oracle truncates the master_table_name portion at 20 bytes and appends the name with a four-digit number to ensure uniqueness. This guarantees that the objects comply with the naming rules for schema objects.

### Required Privileges

The privileges required to create a snapshot log directly relate to the privileges necessary to create the underlying objects associated with a snapshot log.

- If you own the master table, you can create an associated snapshot log if you have the CREATE TABLE privilege.
- If you are creating a snapshot log for a table in another user's schema, you must have the CREATE ANY TABLE and COMMENT ANY TABLE privileges, as well as the SELECT privilege for the master table.

In either case, the owner of the snapshot log must have sufficient quota in the tablespace intended to hold the snapshot log.

### Timing of Snapshot Log Creation

If you plan to perform a fast refresh for snapshots, be sure to create a corresponding snapshot log for the snapshot's master table before creating the snapshot. If you create the snapshot log after the snapshot, Oracle will perform the first refresh of the snapshot as a complete refresh rather than as a fast refresh.

### Special Requirements for Primary Key Snapshots

By default, Oracle creates a snapshot log to support primary key snapshots. Therefore, the master table must contain a valid PRIMARY KEY constraint before you can create a snapshot log.

### Snapshot Log Storage Parameters

You can set the storage options for a snapshot log during creation using the Storage page of the Create Snapshot Log property sheet in Schema Manager.

In general, it is best to set a snapshot log's storage options as follows:

- Set PCTFREE to 0 and PCTUSED to 99.
- Set extent storage parameters according to the update activity (the number of INSERT, UPDATE, and DELETE statements) on the master table.

### Special Requirements for Subquery Snapshots

To support a simple snapshot that uses a subquery in its defining query, the following requirements must be met:

- For every master table that appears in a subquery, there must be a corresponding snapshot log.
- Each snapshot log must specify filter columns for each column referenced in the subquery of the snapshot, excluding primary key columns.

Note: Columns using a LOB datatype cannot be filter columns.

See "Advanced Subsetting with Subqueries" on page 2-16 for several examples of snapshot logs with filter columns that are necessary to support snapshots with subqueries.

### Internal Mechanisms of Snapshot Log Creation

When you create a snapshot log, Oracle performs several operations internally:

- Oracle creates the base table MLOG$_master_table_name to store the primary keys (and/or ROWIDs), filter columns, and timestamps of rows updated in the master table. Oracle does not update the timestamp column until the log is first used by a snapshot refresh.
- Oracle activates an internal trigger on the master table to insert the primary keys (and/or ROWIDs), filter columns, and timestamps of inserted, updated, and deleted rows into the master snapshot log.

The schema containing a master table also contains the base table for its snapshot log.

Caution: Do not alter or change data in the underlying table that supports a snapshot log.

## Creating Simple Snapshots

To create a read-only snapshot, you can use the Create Snapshot property sheet of Enterprise Manager's Schema Manager application or the SQL command CREATE SNAPSHOT.

Note: For complete information about managing snapshots, see "Managing Read-Only Snapshots" on page 2-34.

The following example creates a simple, read-only snapshot.

The equivalent CREATE SNAPSHOT statement is:

    CREATE SNAPSHOT scott.emp
     AS SELECT * FROM scott.emp@dbs1.acme.com;

Before creating a read-only table snapshot, consider the issues discussed in the sections that follow.

### Snapshot Names

A snapshot name must be unique within the encompassing schema. A snapshot name can be up to 30 bytes in length; however, keep snapshot names to 19 or fewer bytes when possible. When a snapshot name contains more than 19 bytes, Oracle automatically truncates the prefixed names of the underlying table, and appends them with a four-digit number to ensure new object names are unique and that they comply with the naming rules for schema objects.

### Required Privileges

Several privileges are necessary to create a fully functional snapshot.

- To create a snapshot in your own schema, you must have the CREATE SNAPSHOT, CREATE TABLE, and CREATE VIEW system privileges.
- To create a snapshot in another user's schema, you must have the CREATE ANY SNAPSHOT system privilege.
- The schema that contains the snapshot must have sufficient quota in the target tablespace to store the snapshot's base table and index.
- To create and refresh a snapshot, both the creator and snapshot owner must be able to issue the defining query of the snapshot. This capability depends directly on the database link that the snapshot's defining query uses.

### Special Requirements for Primary Key Snapshots

By default, Oracle creates all new snapshots as primary key snapshots. To create a snapshot:

- The master table for a snapshot must contain an enabled PRIMARY KEY constraint before you create the new snapshot.
- The defining query of the snapshot must reference all columns in the master table's primary key.

### Snapshot Refresh Settings

Although Schema Manager and the CREATE SNAPSHOT command allow you to specify refresh settings for individual snapshots, you should always refresh a snapshot as part of a refresh group. See "Creating Refresh Groups" on page 2-24 for more information about configuring snapshot refreshes with refresh groups. Additionally, see "Individual Snapshot Refreshes" on page 2-44 for more information about configuring an individual snapshot for refresh.

### A Snapshot's Defining Query

When creating a new snapshot, consider the following issues relating to the query that defines the snapshot's structure.

#### Referenced Master Table Column Datatypes

A snapshot's defining query can reference master table columns that use the following Oracle datatypes: NUMBER, DATE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, RAW, ROWID, BLOB, CLOB, and NCLOB. Snapshots cannot include columns that use the LONG, LONG RAW, or BFILE datatypes. Additionally, Oracle does not support user-defined object types within snapshots.

Oracle propagates BLOBs, CLOBs, and NCLOBs during fast refresh only when:

- A LOB has been updated (including piece-wise updates and appends).
- New rows exist that satisfy the selection criteria of the snapshot.

A snapshot's defining query cannot reference a LOB column in its WHERE clause.

#### Restrictions for Simple Snapshots

To create a simple snapshot, the snapshot's defining query cannot contain the following SQL attributes:

- Distinct or aggregate functions.
- GROUP BY or CONNECT BY clauses.
- Joins (other than the allowed types of subqueries).
- Set operations.

When a snapshot definition uses any of the above attributes, the snapshot is a complex snapshot. Oracle cannot use a snapshot log to perform fast refreshes for a complex snapshot.

#### Explicit Table References

Always design the defining query of a snapshot so it explicitly references its remote data. Otherwise, the snapshot might reference different remote data during snapshot creation and subsequent refreshes. For example, consider when SCOTT creates a snapshot using the following commands:

    CONNECT scott/tiger
    CREATE DATABASE LINK sales.hq.com USING 'hq.sales.com';
    CREATE SNAPSHOT emp AS SELECT * FROM emp@sales.hq.com;

In this example, the snapshot definition implicitly references the remote table SCOTT.EMP because the database link establishes a connection to the remote database as SCOTT/TIGER during snapshot creation. Instead, simply make the remote table reference explicit as the following example shows.

    CREATE SNAPSHOT emp AS SELECT * FROM scott.emp@sales.hq.com;

To clearly identify the remote data to which a snapshot corresponds, the snapshot's defining query should:

- Reference fully qualified table names rather than partial table names.
- Reference only remote tables, not remote master views or synonyms
- Not generate context-sensitive data. For example, do not create a simple snapshot with a query that uses the SQL functions SYSDATE, UID, or USER.

### Snapshot Storage Settings

You can set the storage options for a snapshot during creation using the Storage page of the Create Snapshot property sheet in Schema Manager.

In general, a simple snapshot's storage options should mimic the storage options for its master table because they share the same characteristics. However, if a simple snapshot does not duplicate all columns of its master table, modify the snapshot storage items accordingly.

### Clustered Snapshots

When a number of master tables are clustered in the master database, consider clustering the corresponding snapshots in the remote database. To create a snapshot as part of a data cluster, use the Cluster page of the Create Snapshot property sheet in Schema Manager.

When you create a snapshot in a data cluster, Oracle always uses the storage parameters of the cluster's data segment.

### Data Load Options

By default, when you create a snapshot, Oracle immediately executes the defining query of the snapshot to populate the snapshot's base table with the rows of the corresponding master table. In large replicated environments, you should consider using snapshot cloning to reduce the network overhead necessary to create snapshot databases. For more information about snapshot cloning, see "Snapshot Cloning and Offline Instantiation" on page 7-14.

## Creating Snapshots with Subqueries

In many cases, a snapshot must correspond to a subset of the rows in its master table. Often, you can use a simple WHERE clause in the defining query of a snapshot to identify the subset of master table rows. For example, consider a corporate salesforce automation system that defines the sales territory of each salesperson by the zip codes of customers. Each salesperson's personal computer database must maintain only the information about the customers in his or her sales territory, as well as the corresponding orders. For instance, a salesperson who is responsible for customers with the zip code 19555 can create a snapshot to provide read-only access to the customers in the corresponding sales territory.

    CREATE SNAPSHOT sales.customers AS
     SELECT * FROM sales.customers@dbs1.acme.com
      WHERE zip = 19555;

This simple example shows how to create basic subsetting for simple snapshots. More advanced subsetting often requires the use of a subquery in a snapshot's defining query. The following sections extend the example above to explain the benefits of subquery snapshots. Each related example explains how to create a snapshot with a subquery.

### Advanced Subsetting with Subqueries

A snapshot that uses a subquery can be a flexible solution for advanced subsetting requirements. The following examples demonstrate the creation and advantages of snapshots with subqueries in the fictional salesforce automation system described above. In the following examples, the name of the central company database is hq.acme.com. The following CREATE table commands describe some of the important master tables of the salesforce automation system in the SALES schema at the central company database:

CUSTOMERS Table

    CREATE TABLE sales.customers
     ( c_id  INTEGER PRIMARY KEY,
       zip   INTEGER
     -- other columns defined here
     );

ORDERS Table

    CREATE TABLE sales.orders
     ( o_id  INTEGER PRIMARY KEY,
       c_id  INTEGER
     -- other columns defined here
         );

ORDER_LINES Table

    CREATE TABLE sales.order_lines
     ( ol_id INTEGER,
       o_id INTEGER,
      PRIMARY KEY (ol_id, o_id)
     -- other columns defined here );

ASSIGNMENTS Table

    CREATE TABLE sales.assignments
     ( c_id INTEGER,
       s_id INTEGER,
      PRIMARY KEY (c_id, s_id)
    );

SALESPERSONS Table

    CREATE TABLE sales.salespersons
     ( s_id INTEGER PRIMARY KEY,
      s_name VARCHAR2(30) UNIQUE
     -- other columns defined here
    );

To create snapshots that can refresh with fast refreshes, snapshot logs must also exist for the corresponding master tables. For each master table that will be referenced by a snapshot that uses a subquery, the associated snapshot log must include the appropriate filter columns. The following SQL commands build the necessary snapshot logs at the central database, hq.acme.com.

    CREATE SNAPSHOT LOG ON sales.customers
     WITH PRIMARY KEY (zip);
    CREATE SNAPSHOT LOG ON sales.orders
     WITH PRIMARY KEY (c_id);
    CREATE SNAPSHOT LOG ON sales.order_lines
     WITH PRIMARY KEY;
    CREATE SNAPSHOT LOG ON sales.assignments
     WITH PRIMARY KEY;
    CREATE SNAPSHOT LOG ON sales.salespersons
     WITH PRIMARY KEY (s_name);

The following examples also assume that the corresponding SALES schema and private database link that are necessary to support the snapshots already exist in the spdb1.acme.com database.

#### Example 1

#### Figure 2-2 Advanced subsetting with a subquery.

The CUSTOMERS snapshot in the previous section provides read-only access to the salesperson's customer records. The salesperson also requires snapshots that provide read-only access to the order information that corresponds to the salesperson's customers. One technique would be to add a zip code column to the master ORDERS and ORDER_LINES tables and then define simple snapshots similar to the CUSTOMERS snapshot. However, such denormalization of the master tables is not typically desirable and can be difficult to maintain. A better solution is to define snapshots on the ORDERS and ORDER_LINES master tables that reference the zip code column in the CUSTOMERS table using a subquery.

The following SQL commands show how to create snapshots of the ORDERS and ORDER_LINES master tables that contain the orders and line items that correspond to the salesperson's sales territory:

ORDERS Snapshot

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

ORDER_LINES Snapshot

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

The subqueries in the example snapshots walk up the many-to-one references from the child to parent tables that may involve one or multiple levels. When these snapshots are created, Oracle fills the snapshot base tables with all orders or order line rows that belong to customers whose zip code column values match the snapshot selection criterion. Subsequent fast refreshes return only the rows that have changed since snapshot creation or since the last refresh.

If a zip code column is updated so that a customer no longer satisfies the selection criterion of the snapshot, the orders and order line rows for the customer will be removed from the snapshots during the next refresh. If a zip code column in another customer row is updated so that it satisfies the selection criterion of the snapshot, the customer's orders and order line rows will be added during the next refresh.

#### Example 2: A Better Approach

The snapshots that the previous section define derive their subsets of the CUSTOMERS, ORDERS, and ORDER_LINES tables by embedding a zip code into each snapshot's defining query. Consequently, the snapshots cannot accommodate a simple change in the salesperson's territory-for example, the snapshots would have to be rebuilt if the salesperson's sales territory zip code changed from 19555 to 19500. For greater flexibility, the snapshots in the following example show how subqueries can also traverse many-to-many references between tables in selected cases.

This example makes use of the SALESPERSONS and ASSIGMENTS tables. Each salesperson has a row in the SALESPERSONS table that maps their S_ID to their name. The ASSIGNMENTS table is an intersection table that maps customers to salespersons, for example, C_ID to S_ID. The ASSIGNMENTS table implements a many-to-many relationship between CUSTOMER and SALESPERSON: A salesperson can have multiple customers; a customer can have multiple salespersons.

The following SQL commands create new CUSTOMERS, ORDERS, and ORDER_LINES snapshots that can be fast refreshed.

CUSTOMERS Snapshot

    CREATE SNAPSHOT sales.customers AS
     SELECT * FROM sales.customers@hq.acme.com c
       -- conditions for customers
      WHERE EXISTS
      ( SELECT * FROM sales.assignments@hq.acme.com a
        WHERE a.c_id = c.c_id
        AND EXISTS
        ( SELECT * FROM sales.salespersons@hq.acme.com s
          WHERE s.s_id = a.s_id AND s_name = 'gsmith'));

ORDERS Snapshot

    CREATE SNAPSHOT sales.orders AS
     SELECT * FROM sales.orders@hq.acme.com o
        -- conditions for customers
      WHERE EXISTS
      ( SELECT c_id FROM sales.customers@hq.acme.com c
        WHERE o.c_id = c.c_id
          AND EXISTS
        ( SELECT * FROM sales.assignments@hq.acme.com a
         WHERE a.c_id = c.c_id
         AND EXISTS
         ( SELECT * FROM sales.salespersons@hq.acme.com s
           WHERE s.s_id = a.s_id AND s_name = 'gsmith')));

ORDER_LINES Snapshot

    CREATE SNAPSHOT sales.order_lines AS
     SELECT * FROM sales.order_lines@hq.acme.com ol
      WHERE EXISTS
      ( SELECT o_id FROM sales.orders@hq.acme.com o
        WHERE ol.o_id = o.o_id
        AND EXISTS
        ( SELECT c_id FROM sales.customers@hq.acme.com c
          WHERE o.c_id = c.c_id
          -- conditions for customers
          AND EXISTS
          ( SELECT * FROM sales.assignments@hq.acme.com a
            WHERE a.c_id = c.c_id
            AND EXISTS
            ( SELECT * FROM sales.salespersons@hq.acme.com s
              WHERE s.s_id = a.s_id AND s_name = 'gsmith'))));

These subqueries walk up the many-to-one references from ORDER_LINES to ORDERS to CUSTOMERS and traverse the many-to-many references between CUSTOMERS and SALESPERSONS using the ASSIGNMENTS intersection table. Again, when the CUSTOMERS, ORDERS, and ORDER_LINES snapshots are created, Oracle fills the snapshot base tables with all customer, order, and order line rows belonging to customers who have been assigned to the salesperson. Subsequent fast refreshes return only the rows that have changed since snapshot creation or since the last refresh. If an ASSIGNMENTS record is deleted or updated so a customer is no longer assigned to the salesperson, the customer, order, and order line rows for the salesperson will be removed from the snapshots during the next refresh. If an ASSIGNMENTS record is inserted or updated so a customer is now assigned to the salesperson, the appropriate customer, order, and order line rows will be added during the next refresh.

To avoid the join to the SALESPERSONS table, the previous snapshot definitions with subqueries could also be defined using only the ASSIGNMENTS intersection table. For example, you could create the CUSTOMERS snapshot using only a reference to the S_ID column the ASSIGNMENTS table as follows:

####

    CREATE SNAPSHOT sales.customers AS
     SELECT * FROM sales.customers@dbs1.acme.com c
     -- conditions for customers
     WHERE EXISTS
     ( SELECT * FROM sales.assignments@dbs1.acme.com a
       WHERE a.c_id = c.c_id AND a.s_id = 1001);

    Figure 2-3 Advanced subsetting with a subquery.

Using subqueries that traverse many-to-many relationships can provide greater flexibility in many cases. Note that in Example 2, salespeople can be assigned to different territories and hence different customers by performing the appropriate changes to the ASSIGNMENTS table. In the earlier examples, if a salesperson were assigned different zip codes, the snapshots would need to be dropped and re-created to include the new zip code territory assignment in the snapshot definition.

Similarly in Example 2, the company could change the way territories are defined and allocated. Note that the company could move from a geographic territory scheme based on zip codes to a more elaborate scheme involving vertical industry specializations or national accounts by just rederiving and performing the appropriate updates to the ASSIGNMENTS table. In the first example, additional columns would need to be added to the CUSTOMERS table to contain the vertical industry and/or national account information, and the snapshots would again need to be dropped and re-created to use these new columns.

### Restrictions for Snapshots for Subquery

All issues relevant to simple snapshots are also relevant to snapshots with subqueries. See the section "Creating Simple Snapshots" on page 2-10 for complete information. Additionally, the defining query of a snapshot with a subquery is subject to several other restrictions to preserve the simple snapshot's fast refresh capability.

Note: To determine whether a simple subquery snapshot satisfies the many restrictions below, create the snapshot with "fast refresh". Oracle will return errors if the snapshot violates any restrictions for simple subquery snapshots.

- Subqueries can "walk up" many-to-one references from child to parent tables when:
  - Prior to creating the snapshot, there is a PRIMARY KEY or UNIQUE constraint on the join column(s) referenced in each parent table.
  - The join expression uses exact match or equality comparisons, in other words, "equi-joins".
- Subqueries can also traverse many-to-many references provided that:
  - Prior to creating the snapshot, there is a PRIMARY KEY or UNIQUE constraint that includes both sets of join columns in the intersection table, and a PRIMARY KEY or UNIQUE constraint on the join column(s) and a separate UNIQUE constraint on the filter column(s) of the table the intersection table joins to. If there is only an intersection table, then there must be a PRIMARY KEY or UNIQUE constraint that includes both the join column(s) and filter column(s) of the intersection table.
  - The subquery specifies an exact match or equality comparison.

    Note: The combination of these two properties means that only one row is returned from the many-to-many reference. To illustrate this, see "Example 2: A Better Approach" on page 2-20. The PRIMARY KEY constraint of the ASSIGNMENT table contains both the C_ID and S_ID join columns. The S_ID join column of the SALESPERSON table has a PRIMARY KEY constraint and the S_NAME filter column has a UNIQUE constraint. The subquery specifies that the value in S_NAME equals a constant, in this case, 'gsmith'.

- Snapshots must be primary key snapshots.
- The master table's snapshot log must include all filter columns referenced in the subquery. Note: A filter column is a non-primary-key column referenced in the subquery predicate. For more information, see "Special Requirements for Subquery Snapshots" on page 2-9.
- The subquery must be a positive subquery. For example, you can use EXISTS, but not NOT EXISTS.
- Subqueries can include AND expressions, but each OR expression may only reference columns contained within one row. Multiple OR expressions within a subquery can be ANDed.
- Each table can be joined only once within the subquery, that is, each table can be in only a single EXISTS expression.
- Each table referenced in the subquery must be located in the same master database.
- The sum of the number of selected columns plus the number of primary key columns for each table used in the subquery must be less than the maximum number of columns allowed in a table (1,000 columns).

## Creating Refresh Groups

A table snapshot is a representation of its master data as that data existed at a specific moment in time. To keep a snapshot's data relatively current with the data of its master, Oracle must periodically refresh the snapshot. A snapshot refresh is an efficient batch operation that makes that snapshot reflect a more current state of its master.

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

Note: For complete information about managing refresh groups, see "Managing Snapshot Refreshes and Refresh Groups" on page 2-37.

To create a refresh group, you can use Schema Manager or an equivalent call to the DBMS_REFRESH.MAKE procedure.

- On the General page of the Create Refresh Group property sheet, specify the new group's name, schema, refresh settings, and associated rollback segment.
- On the Snapshots page of the Create Refresh Group property sheet, specify a list of member snapshots for the new group. The maximum number of snapshots in a refresh group is 100.

The equivalent call to the DBMS_REFRESH.MAKE procedure is:

    DBMS_REFRESH.MAKE(
     name             => 'sales.refgrp_1',
     list             => 'sales.customers,sales.orders,sales.order_lines',
     next_date        => SYSDATE,
     interval         => 'SYSDATE + 1/24',
     implicit_destroy => TRUE,
     rollback_seg     => 'trs_1'
    );

Before creating refresh groups, there are several issues to consider.

### Refresh Settings

When you create a refresh group, you can determine when to first refresh the group and then how often to refresh the group thereafter.

#### Next Date

A refresh group's initial refresh is determined by the setting of the Next Date field in the Create Refresh Group property sheet of Schema Manager (the NEXT_DATE parameter of the DBMS_REFRESH.MAKE procedure).

#### Interval

A refresh group's refresh interval determines the automatic refresh interval for all snapshots in the group. When setting a group's automatic refresh interval, understand the following behaviors:

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

#### Example Date Expressions

The following examples are typical date expressions you can use for a refresh group's refresh interval.

- SYSDATE + 7
  Relative date expression that evaluates to exactly seven days from the most recent refresh.
- SYSDATE + 1/48
  Relative date expression that evaluates to exactly a half hour from the most recent refresh.
- NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 3/24
  Explicit date expression that evaluates to every Monday at 3 AM.
- NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),'THURSDAY')
  Explicit date expression that evaluates to the first Thursday of each quarter.

#### Refresh Settings for Updatable Snapshots

When you create a refresh group, you can specify other settings that are designed specifically for refresh groups that include updatable snapshots in an advanced replication environment. For basic, read-only replication environments, simply use the default settings. See "Managing Refresh Groups" on page 4-22 for more information about configuring these special refresh group settings with advanced replication systems.

### Refresh Types

By default, Oracle attempts to refresh each snapshot in a group using a fast refresh. Oracle performs a complete refresh of an individual snapshot in a refresh group only when one of the following situations is true:

- A snapshot log is not available for a snapshot.
- A snapshot is complex-Oracle cannot fast refresh a complex snapshot.
- A snapshot was created with an individual refresh setting (not recommended).
- A snapshot's master table has been dropped and subsequently re-created.

### Rollback Segment Setting

When Oracle refreshes the snapshots in a refresh group, the server can generate a significant amount of rollback data. When you create a refresh group, be sure to target a sufficiently large rollback segment for the group's refreshes.

## Managing a Basic Replication Environment

The following sections explain how to manage the various components of a basic replication environment, including snapshot logs, snapshots, and snapshot refresh groups.

### Managing Snapshot Logs

The following sections explain how to manage snapshot logs. Topics include:

- Altering Snapshot Logs.
- Managing Snapshot Log Space.
- Reorganizing Master Tables that Have Snapshot Logs.
- Dropping Snapshot Logs.

#### Altering Snapshot Logs

After you create a snapshot log, you can alter its storage parameters and support for corresponding snapshots. The following sections explain more about altering snapshot logs.

##### Required Privileges

Only the owner of the master table or a user with the SELECT privilege for the master table can alter a snapshot log.

##### Altering Snapshot Log Storage Parameters

To alter a snapshot log's storage parameters, use the Storage and Options pages of the Snapshot Log property sheet or an equivalent ALTER SNAPSHOT LOG statement. For example:

    ALTER SNAPSHOT LOG ON sales.customers
     PCTFREE 25
     PCTUSED 40;

##### Altering a Snapshot Log to Add Filter Columns

To add new filter columns to a snapshot log, use the SQL command ALTER SNAPSHOT LOG. For example:

    ALTER SNAPSHOT LOG ON sales.customers
     ADD (zip);

#### Managing Snapshot Log Space

Oracle automatically tracks which rows in a snapshot log have been used during the refreshes of snapshots, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple snapshots can use the same snapshot log, rows already used to refresh one snapshot may still be needed to refresh another snapshot; Oracle does not delete rows from the log until all snapshots have used them.

For example, Oracle refreshes the CUSTOMERS snapshot at the SPDB1 database. However, the server that manages the master table and associated snapshot log does not purge the snapshot log rows used during the refresh of this snapshot until the CUSTOMERS snapshot at the SPDB2 database also refreshes using these rows.

As a result of how Oracle purges rows from a snapshot log, unwanted situations can occur that cause a snapshot log to grow indefinitely when multiple snapshots are based on the same master table. For example, such situations can occur when more than one snapshot is based on a master table and when:

- One snapshot is not configured for automatic refreshes and has not been manually refreshed for a long time.
- One snapshot has an infrequent refresh interval, such as every year (365 days).
- A network failure has prevented an automatic refresh of one or more of the snapshots based on the master table.
- A network or site failure has prevented a master from becoming aware that a snapshot has been dropped.

##### Purging Rows from a Snapshot Log

Always try to keep a snapshot log as small as possible to minimize the database space that it uses. To remove rows from a snapshot log and make space for newer log records, you can:

- Refresh the snapshots associated with the log so that Oracle can purge rows from the snapshot log.
- Manually purge records in the log by deleting rows required only by the nth least recently refreshed snapshots.

To manually purge rows from a snapshot log, execute the PURGE_LOG stored procedure of the DBMS_SNAPSHOT package at the database that contains the log. For example, to purge entries from the snapshot log of the CUSTOMERS table that are necessary only for the least recently refreshed snapshot, execute the following procedure:

    DBMS_SNAPSHOT.PURGE_LOG (
     master => 'sales.customers',
     num    => 1,
     flag   => 'DELETE');

Additional Information: The parameters for the DBMS_SNAPSHOT. PURGE_LOG procedure are described in Table 9-209.

##### Required Privileges

The owner of a snapshot log or a user with the EXECUTE privilege for the DBMS_SNAPSHOT package can purge rows from the snapshot log by executing the PURGE_LOG procedure.

##### Truncating a Snapshot Log

If a snapshot log grows and allocates many extents, purging the log of rows does not reduce the amount of space allocated for the log. To reduce the space allocated for a snapshot log:

1. Acquire an exclusive lock on the master table to prevent updates from occurring during the following process.

       LOCK TABLE sales.customers IN EXCLUSIVE MODE;

2. Using a second database session, copy the rows in the snapshot log (in other words, the MLOG$ base table) to a temporary table.

       CREATE TABLE sales.templog AS SELECT * FROM sales.mlog$_customers;

3. Using the second session, truncate the log using the SQL command TRUNCATE.

       TRUNCATE sales.mlog$_customers;

4. Using the second session, reinsert the old rows so that you do not have to perform a complete refresh of the dependent snapshots.

       INSERT INTO sales.mlog$_customers SELECT * FROM sales.templog;
       DROP TABLE sales.templog;

5. Using the first session, release the exclusive lock on the master table.

       ROLLBACK;

Note: Any changes made to the master table between the time you copy the rows to a new location and when you truncate the log do not appear until after you perform a complete refresh.

##### Required Privileges

The owner of a snapshot log or a user with the DELETE ANY TABLE system privilege can truncate a snapshot log.

#### Reorganizing Master Tables that Have Snapshot Logs

To improve performance and optimize disk use, you can periodically reorganize ("reorg") tables. This section discusses how to reorganize a master table and preserve the fast refresh capability of associated snapshots.

##### Reorganization Notification

When you reorganize a table, any ROWID information of the snapshot log must be invalidated. Oracle detects a table reorganization automatically only if the table is truncated as part of the reorg. See "Method 2 for Reorganizing Table t" on page 2-32.

If the table is not truncated, Oracle must be notified of the table reorganization. To support table reorganizations, two procedures, DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION and DBMS_SNAPSHOT.END_TABLE_REORGANIZATION notify Oracle that the specified table has been reorganized. The procedures perform clean-up operations, verify the integrity of the logs and triggers that the fast refresh mechanism needs, and invalidate the ROWID information in the table's snapshot log. The inputs are the owner and name of the master table to be reorganized. There is no output.

##### Truncating Master Tables

When a table is truncated, its snapshot log is also truncated. However, for primary key snapshots, you can preserve the snapshot log, allowing fast refreshes to continue. Although the information stored in a snapshot log is preserved, the snapshot log becomes invalid with respect to ROWIDs when the master table is truncated. The ROWID information in the snapshot log will seem to be newly created and cannot be used by ROWID snapshots for fast refresh.

If you specify the PRESERVE SNAPSHOT LOG option or no option, the information in the master table's snapshot log is preserved, but current ROWID snapshots can use the log for a fast refresh only after a complete refresh has been performed. This is the default.

Note: To ensure that any previously fast refreshable snapshot is still refreshable, follow the guidelines in "Methods of Reorganizing a Database Table" on page 2-31.

If the PURGE SNAPSHOT LOG option is specified, the snapshot log is purged along with the master table.

##### Examples

The following two statements preserve snapshot log information when the master table is truncated:

    TRUNCATE TABLE tablename PRESERVE SNAPSHOT LOG;
    TRUNCATE TABLE tablename;

The following statement truncates the snapshot log along with the master table:

    TRUNCATE TABLE tablename PURGE SNAPSHOT LOG

##### Methods of Reorganizing a Database Table

Oracle provides four table reorganization methods that preserve the capability for fast refresh; these appear under the following headings. Other reorg methods require an initial complete refresh to enable subsequent fast refreshes.

Note: Do not use direct loader during a reorg of a master table. (Direct Loader can cause reordering of the columns, which could invalidate the log information used in subquery and LOB snapshots.)

Method 1 for Reorganizing Table t

1. Call dbms_snapshot.begin_table_reorganization for table t.
2. Rename table t to t_old.
3. Create table t as select * from t_old.
4. Call dbms_snapshot.end_table_reorganization for new table t.

· Warning: When a table is renamed, its associated PL/SQL triggers are also adjusted to the new name of the table.

· Ensure that no transaction is issued against the reorganized table between calling dbms_snapshot.begin_table_reorganization and dbms_snapshot.end_table_reorganization.

Method 2 for Reorganizing Table t

1. Call dbms_snapshot.begin_table_reorganization for table t.
2. Export table t.
3. Truncate table t with PRESERVE SNAPSHOT LOG option.
4. Import table t using conventional path.
5. Call dbms_snapshot.end_table_reorganization for new table t.

· Warning: When you truncate master tables as part of a reorg, you must use the PRESERVE SNAPSHOT LOG clause of the truncate table DDL.

· Ensure that no transaction is issued against the reorganized table between calling dbms_snapshot.begin_table_reorganization and dbms_snapshot.end_table_reorganization.

Method 3 for Reorganizing Table t

1. Call dbms_snapshot.begin_table_reorganization for table t.
2. Export table t.
3. Rename table t to t_old.
4. Import table t using conventional path.
5. Call dbms_snapshot.end_table_reorganization for new table t.

· Warning: When a table is renamed, its associated PL/SQL triggers are also adjusted to the new name of the table.

· Ensure that no transaction is issued against the reorganized table between calling dbms_snapshot.begin_table_reorganization and dbms_snapshot.end_table_reorganization.

Method 4 for Reorganizing Table t

1. Call dbms_snapshot.begin_table_reorganization for table t.
2. Select contents of table t to a flat file.
3. Rename table t to t_old.
4. Create table t with the same shape as t_old.
5. Run SQL*Loader using conventional path.
6. Call dbms_snapshot.end_table_reorganization for new table t.

· Warning: When a table is renamed, its associated PL/SQL triggers are also adjusted to the new name of the table.

· Ensure that no transaction is issued against the reorganized table between calling dbms_snapshot.begin_table_reorganization and dbms_snapshot.end_table_reorganization.

#### Dropping Snapshot Logs

You can drop a snapshot log independently of its master table or any existing snapshots. For example, you might decide to drop a snapshot log if one of the following situations is true:

- All snapshots of a master table have been dropped.
- All snapshots of a master table are to be completely refreshed, not fast refreshed.

To drop a snapshot log, you can use Schema Manager to remove the log or an equivalent DROP SNAPSHOT LOG statement.

    DROP SNAPSHOT LOG ON sales.customers;

##### Required Privileges

Only the owner of the master table or a user with the DROP ANY TABLE system privilege can drop a snapshot log.

### Managing Read-Only Snapshots

The following sections describe how to create and manage read-only snapshots in a basic replication environment. Topics include:

- Using Read-Only Snapshots.
- Registering a Snapshot at its Master Site.
- Altering Read-Only Snapshot Storage Parameters.
- Dropping Read-Only Snapshots.
- Recovering Read-Only Snapshots from Media Failure.

#### Using Read-Only Snapshots

Applications can query snapshots just like a table or view. For example, the following query references a local snapshot named CUSTOMERS.

    SELECT * FROM sales.customers;

Applications cannot issue any INSERT, UPDATE, or DELETE statements when using a read-only snapshot; if they do, Oracle returns an error.

Caution: Although applications can issue INSERT, UPDATE, and DELETE statements against the base table of a snapshot, such operations will corrupt the snapshot. Applications should update a master table only, and allow Oracle to refresh the snapshot to make it a more current version of its master table. When applications must update a local snapshot rather than a remote master table, you must configure an advanced replication system using updatable snapshots. See Chapter 4, "Using Snapshot Site Replication" for more information.

Caution: Administrators should never modify the structure of a read-only snapshot in any way. For example, never add triggers or integrity constraints to the base table of a snapshot.

##### Required Privileges

To query a snapshot, a user must own the snapshot, have the SELECT privilege for the snapshot, or have the SELECT ANY TABLE system privilege.

Grant the SELECT privilege for a snapshot to all users who require access to the snapshot. Do not allow users to connect to the snapshot database using the account that owns any snapshots. Otherwise, a malicious user could use the private database link to access the remote master table in any way.

##### Creating Views and Synonyms Based on Snapshots

Optionally, you can create views and synonyms based on snapshots. For example, the following statement creates a view based on the EMP snapshot.

    CREATE VIEW sales_dept AS
     SELECT ename, empno
       FROM scott.emp
        WHERE deptno = 10;

#### Registering a Snapshot at its Master Site

At the master site, an Oracle database automatically registers information about the snapshots based on the master tables. The following sections explain more about Oracle's snapshot registration mechanism.

##### Viewing Information about Registered Snapshots

You can use the General page of the Snapshot Log property sheet in Schema Manager to list the snapshots associated with a snapshot log. For additional information, you can query the DBA_REGISTERED_SNAPSHOTS data dictionary view to list the following information about a remote snapshot:

- The owner, name, and database that contains the snapshot.
- The snapshot's defining query.
- Other snapshot characteristics, such as its refresh method (fast or complete).

You can also join the DBA_REGISTERED_SNAPSHOT view with the DBA_SNAPSHOT_LOGS view at the master site to obtain the last refresh times for each snapshot. Administrators can use this information to monitor snapshot activity from master sites and coordinate changes to snapshot sites if a master table needs to be dropped, altered, or relocated.

##### Internal Mechanisms

Oracle automatically registers a read-only snapshot at its master database when you create the snapshot, and unregisters the snapshot when you drop it.

Note: Oracle7 master sites cannot register snapshots.

Caution: Oracle cannot guarantee the registration or unregistration of a snapshot at its master site during the creation or drop of the snapshot, respectively. If Oracle cannot successfully register a snapshot during creation, Oracle completes snapshot registration during a subsequent refresh of the snapshot. If Oracle cannot successfully unregister a snapshot when you drop the snapshot, the registration information for the snapshot persists in the master database until it is manually unregistered.

##### Manual Snapshot Registration

If necessary, you can maintain registration manually. Use the REGISTER_SNAPSHOT and UNREGISTER_SNAPSHOT procedures of the DBMS_SNAPSHOT package at the master site to add, modify, or remove snapshot registration information.

Additional Information: The REGISTER_SNAPSHOT and UNREGISTER_SNAPSHOT procedures are described on Table 9-193 and Table 9-196, respectively.

#### Altering Read-Only Snapshot Storage Parameters

To alter the storage parameter of a snapshot that is not in a data cluster, you can use Schema Manager or the SQL command ALTER SNAPSHOT.

    ALTER SNAPSHOT sales.customers
      STORAGE (NEXT 500K);

##### Required Privileges

To alter a snapshot's storage parameters, the snapshot must be contained in your schema. Otherwise, you must have the ALTER ANY SNAPSHOT system privilege.

#### Dropping Read-Only Snapshots

You can drop a snapshot independently of its master tables or its snapshot log. To drop a local snapshot, use Schema Manager or the SQL command DROP SNAPSHOT.

    DROP SNAPSHOT sales.customers;

When you drop the only snapshot of a master table, you should also drop the snapshot log of the master table, if present.

##### Dropping a Snapshot's Master Table

When you drop a master table, Oracle automatically drops the associated snapshot log, if present. Alternatively, all associated snapshots remain and continue to be accessible. However, when Oracle attempts to refresh a snapshot based on a nonexistent master table, Oracle returns an error.

If you later re-create the master table, a dependent snapshot can again successfully complete refresh, as long as Oracle can successfully issue the defining query of the snapshot against the new master table. However, Oracle cannot perform a fast refresh of the snapshot until after you create a snapshot log for the new master table. If Oracle cannot successfully refresh a snapshot after dropping and re-creating its master table, drop and re-create the snapshot as well.

##### Privileges Required to Drop a Snapshot

Only the owner of a snapshot or a user with the DROP ANY SNAPSHOT system privilege can drop a snapshot.

#### Recovering Read-Only Snapshots from Media Failure

When a media failure occurs, it may be necessary to recover either a database that contains a master table of a snapshot or a database with a snapshot. If a master database is independently recovered to a past point in time (that is, coordinated time-based distributed database recovery is not performed), any dependent remote snapshot that refreshed in the interval of lost time will be inconsistent with its master table. In this case, the administrator of the master database should instruct the remote administrator to perform a complete refresh of any inconsistent snapshot.

Additional information: See the book Oracle8 Backup and Recovery Guide to learn about recovering from media failure.

### Managing Snapshot Refreshes and Refresh Groups

The following sections describe how to manage refresh groups for the snapshots in an Oracle database. Topics include:

- Altering Refresh Groups.
- Deleting a Refresh Group.
- Manually Refreshing Snapshot Refresh Groups.
- Troubleshooting Refresh Problems.

#### Altering Refresh Groups

After you create a refresh group, you can alter it in several ways. The following sections explain how to add new member snapshots to a refresh group, remove member snapshots from a refresh group, and alter the automatic refresh interval for a refresh group.

##### Adding Members to a Refresh Group

To add member snapshots to a refresh group, use the Snapshots page of the Refresh Group property sheet in Schema Manager or call the DBMS_REFRESH.ADD procedure.

    DBMS_REFRESH.ADD
      name   => 'sales_refgrp_1',
       list   => 'sales.order_lines'
    );

Note: The maximum number of snapshots in a refresh group is 100.

##### Removing Members from a Refresh Group

To remove member snapshots from a refresh group, use the Snapshots page of the Refresh Group property sheet in Schema Manager or call the DBMS_REFRESH.SUBTRACT procedure.

    DBMS_REFRESH.SUBTRACT
     name  => 'sales_refgrp_1',
     list   => 'sales.assignments'
    );

After removing a snapshot from a refresh group, Oracle will not refresh the snapshot unless you add it to another refresh group or refresh it manually.

##### Altering a Group's Refresh Settings

To change a refresh group's next refresh date or automatic refresh interval, use the General page of the Refresh Group property sheet in Schema Manager or call the DBMS_REFRESH.CHANGE procedure.

    DBMS_REFRESH.CHANGE
     name       => 'sales_refgrp_1',
     next_date  => SYSDATE,
     interval   => 'SYSDATE + 1'
    );

#### Deleting a Refresh Group

To drop a refresh group and implicitly remove all member snapshots from the group, use Schema Manager or call the DBMS_REFRESH.DESTROY procedure.

    DBMS_REFRESH.DESTROY( name => 'sales_refgrp_1');

When you drop a refresh group, Oracle will not refresh the former member snapshots unless you add them to another refresh group or refresh them manually.

#### Manually Refreshing Snapshot Refresh Groups

Refresh groups enable you to configure automatic refresh settings that ease the administration of snapshots in a replication environment. However, some circumstances justify the need to refresh a snapshot manually. For example, immediately following a bulk data load into a master table, dependent snapshots will not represent the master table's data. Rather than wait for the next scheduled automatic group refreshes, you might want to manually refresh dependent snapshot groups to propagate the new rows of the master table immediately to associated snapshots.

To refresh a group of snapshots manually, use the Refresh button of the General page in the Refresh Group property sheet of Schema Manager, or call the DBMS_REFRESH.REFRESH procedure.

    DBMS_REFRESH.REFRESH('sales_refgrp_1');

Forcing a manual refresh of a refresh group does not affect the next automatic refresh of the group.

#### Troubleshooting Refresh Problems

The following sections explain several common snapshot refresh problems.

##### Common Problems

Several common factors can prevent the automatic refresh of a group of snapshots:

- The lack of an SNP background process at the snapshot database.
- An intervening network or server failure.
- An intervening server shutdown.

When a snapshot refresh group is experiencing problems, ensure that none of the above situations is preventing Oracle from completing group refreshes.

##### Automatic Refresh Retries

When Oracle fails to refresh a group automatically, the group remains due for its refresh to complete. Oracle will retry an automatic refresh of a group with the following behavior:

- Oracle retries the group refresh first one minute later, then two minutes later, four minutes later, and so on, with the retry interval doubling with each failed attempt to refresh the group.
- Oracle does not allow the retry interval to exceed the refresh interval itself.
- Oracle retries the automatic refresh up to sixteen times.

If after sixteen attempts to refresh a refresh group Oracle continues to encounter errors, Oracle considers the group broken. The General page of the Refresh Group property sheet in Schema Manager indicates when a refresh group is broken. You can also query the BROKEN column of the USER_REFRESH and USER_REFRESH_CHILDREN data dictionary views to see the current status of a refresh group.

The errors causing Oracle to consider a snapshot refresh group broken are recorded in a trace file. After you correct the problems preventing a refresh group from refreshing successfully, you must refresh the group manually. Oracle then resets the broken flag so that automatic refreshes can happen again.

Additional Information: The name of the snapshot trace file is of the form SNPn, where n is platform specific. See your platform-specific Oracle documentation for the name on your system.

##### Snapshots Continually Refreshing

If you encounter a situation where Oracle continually refreshes a group of snapshots, check the group's refresh interval. Oracle evaluates a group's automatic refresh interval before starting the refresh. If a group's refresh interval is less than the amount of time it takes to refresh all snapshots in the group, Oracle continually starts a group refresh each time the SNP background process checks the queue of outstanding jobs.

##### Snapshot Logs Growing Without Bounds

If a snapshot log is growing without bounds, check to see whether a network or site failure has prevented a master from becoming aware that a snapshot has been dropped. You may need to purge part of the log as described in "Purging Rows from a Snapshot Log" on page 2-29 and unregister the snapshot.

## Tuning Performance for Snapshots

The following sections explain how you can tune the performance of snapshots.

### Indexing Snapshots

To maximize the query performance of applications that use a snapshot, you can create indexes for the snapshot. To index a column (or columns) of a snapshot, create an index on the underlying base table of the snapshot.

Caution: Do not declare non-deferrable UNIQUE constraints for a snapshot.

### Tuning Subquery Snapshots

You can improve the performance of fast refreshes for a snapshot with a subquery by defining referential integrity constraints (whenever possible) and non-unique indexes on the equi-join columns referenced in the snapshot's defining query. You must declare the necessary FOREIGN KEY constraints before you create the snapshot. Furthermore, you should not modify or drop these constraints as long as the snapshot exists.

If you use the cost-based optimizer, be sure to analyze the master table, snapshot base table, snapshot log (at the master), and the updatable snapshot log (at the snapshot site, if updatable).

Recommendation: In general, it is best to analyze the snapshot logs when they are empty or nearly empty.

A variable number of indexes are automatically added on the underlying snapshot tables used by simple snapshot with a subquery. These indexes prevent full-table scans during refresh when a change to a row in a master table affects that snapshot's partitioning criteria.

See "Example 2: A Better Approach" on page 2-20. Using the example ORDER_LINES snapshot, the SNAP$_ORDER_LINE table would have a nonunique index on each of the following columns: C_ID, O_ID, and OL_ID.

Note: Frequent modification of columns used by snapshots with restricted subqueries to perform equi-joins or filtering can cause performance degradation during refresh.

## Other Basic Replication Options

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

- Complex Snapshots.
- ROWID Snapshots.
- Individual Snapshot Refreshes.

### Complex Snapshots

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

Note: In most cases, you should avoid using complex snapshots, because Oracle cannot perform a fast refresh of a complex snapshot. Consequently, the use of complex snapshots can degrade network performance during complete snapshot refreshes.

#### A Comparison of Simple and Complex Snapshots

For certain applications, you might want to consider the use of a complex snapshot. Figure 3-1 and the following sections summarize some issues to consider.

#### Figure 2-4 Comparison of Simple and Complex Snapshots

##### Complex Snapshots

Method A shows a complex snapshot. The snapshot in Database II exhibits efficient query performance because the join operation has already been completed during the snapshot's refresh. However, complete refreshes must be performed, because it is a complex snapshot.

##### Simple Snapshots with a Joined View

Method B shows two simple snapshots in Database II, as well as a view that performs the join in the snapshots' database. Query performance against the view would not be as good as the query performance against the complex snapshot in Method A. However, the simple snapshots can be more efficiently refreshed using snapshot logs.

In summary, to decide which method to use:

- If you refresh rarely and want faster query performance, use Method A.
- If you refresh regularly and can sacrifice query performance, use Method B.

#### Storage Settings for Complex Snapshots

Because a complex snapshot is always completely refreshed, set its PCTFREE to 0 and PCTUSED to 100 for maximum efficiency.

### ROWID Snapshots

For backward compatibility, Oracle supports ROWID snapshots in addition to the default, primary key snapshots. Oracle bases a ROWID snapshot on the physical row identifiers (ROWIDs) of rows in the master table. ROWID snapshots should be used only for snapshots of master tables in an Oracle7 database, and should not be used when creating new snapshots of master tables in Oracle databases.

#### Creating a ROWID Snapshot

To explicitly create a ROWID snapshot for an Oracle7 Release 7.3 master table, you must use a CREATE SNAPSHOT statement with the WITH ROWID clause.

    CREATE SNAPSHOT ...
     WITH ROWID
      AS ...

#### Snapshot Logs and ROWID Snapshots

To support fast refreshes of ROWID snapshots, you must create a snapshot log of a master table with a CREATE SNAPSHOT LOG statement that includes the WITH ROWID clause.

#### Master Table Reorganization and ROWID Snapshots

All ROWID snapshots of a master table require a full refresh after you reorganize the table. Subsequently, Oracle can fast refresh all ROWID snapshots.

### Individual Snapshot Refreshes

In typical environments, all snapshots are refreshed as part of a refresh group. However, certain environments do not have concern for referential integrity among snapshots, and require or prefer that snapshots be refreshed individually. For example, you might want to refresh certain snapshots more frequently than others.

To configure a snapshot for individual refreshes, create a refresh group for only the single snapshot. Oracle automatically creates a refresh group for an individual snapshot when you:

- Create or alter a snapshot with Schema Manager and specify refresh settings.
- Create a snapshot with the SQL command CREATE SNAPSHOT and specify a REFRESH clause.
- Alter a snapshot with the SQL command ALTER SNAPSHOT and specify a REFRESH clause.

## Monitoring Basic Replication Environments

The Schema Manager component of Enterprise Manager allows you to view the properties of snapshot logs, snapshots, and refresh groups quickly in a basic replication environment. Alternatively, you can query a database's data dictionary to view information about snapshot logs, snapshots, and refresh groups. The following data dictionary views provide information about the components of a basic replication environment:

- DBA_SNAPSHOTS
- DBA_SNAPSHOT_LOGS
- DBA_REFRESH, DBA_RGROUP, DBA_REFRESH_CHILDREN
- DBA_REGISTERED_SNAPSHOTS

Note: Most data dictionary views have three versions, which have different prefixes: USER_*, ALL_*, and SYS.DBA_*.

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