Oracle7 Server Distributed Systems Volume II: Replicated Data

Refreshing Snapshots

You can refresh a snapshot to make the snapshot reflect a more recent state of its master tables. There are two types of refreshes: a fast refresh and a complete refresh. A fast refresh uses the snapshot log of a master table to refresh a simple snapshot by transmitting only the changes needed to bring the snapshot up to date. Only simple snapshots (that is, those consisting of a subset of rows and columns of a single table) can execute a fast refresh. A complete refresh entirely replaces the existing data in a simple or complex snapshot. Also, snapshots can be refreshed automatically or manually, either individually or in groups.

Consider the following issues when deciding how to refresh a snapshot:

Generally a simple snapshot should use fast refreshes because they are more efficient than complete refreshes.

If the master tables receive predictable updates, automatically refresh the associated snapshots at the appropriate interval.

After bulk loads to the master tables, manually refresh all snapshots based on the master tables. This propagates the new rows of the master tables to associated snapshots.

If you need to refresh a collection of snapshots to a single point in time, such as when there is a parent/child relationship between a pair of snapshots, use snapshot refresh groups.

Privileges Required to Refresh a Snapshot

To refresh a snapshot, you must meet the following criteria:

You must own the snapshot or have the ALTER ANY SNAPSHOT system privilege.

The snapshot owner (or the user that you have connected as, if you are using a database link) must have SELECT privileges on the master table and, for fast refreshes, on the snapshot log.

Automatically Refreshing Snapshots

If you want to have your snapshots automatically refreshed at a periodic interval, you must complete the following steps:

You must specify a snapshot refresh interval and type of refresh.

You must have one or more SNP background processes that wake up periodically and refresh any snapshots that are due to be refreshed.

Specifying a Snapshot Refresh Interval

If you want to refresh an individual snapshot automatically, specify a refresh interval using the START WITH and NEXT parameters in the REFRESH clause of a CREATE SNAPSHOT or ALTER SNAPSHOT statement. This automatically creates a snapshot refresh group that consists of exactly one snapshot. This refresh group has the same name as the snapshot itself.

If you want to refresh a collection of snapshots to a single point of time automatically, you must create a snapshot refresh group using the DBMS_REFRESH.MAKE procedure. To refresh a snapshot refresh group automatically, supply NEXT_DATE and INTERVAL values when you create the group.

The dates or date expressions in the START WITH and NEXT parameters of an individual snapshot, or INTERVAL and NEXT_DATE parameters in the procedure calls for a refresh group, must evaluate to a future point in time. The INTERVAL value is evaluated immediately before the refresh begins.

Thus, you should select an interval that is greater than the length of time required to perform a refresh. A date literal must be enclosed in single quotes, while date expressions do not require quotes.

If a snapshot should be refreshed periodically at a set interval, use the NEXT or INTERVAL parameter with a date expression similar to "SYSDATE + 7". For example, if you set the automatic refresh interval to "SYSDATE + 7" on Monday, but for some reason, such as network failure, the snapshot is not refreshed until Thursday, "SYSDATE + 7" now evaluates to every Thursday, not Monday. If you always want to refresh a snapshot group at a specific time automatically, regardless of the last refresh (for example, every Monday), the INTERVAL or NEXT parameters should specify a date expression similar to "NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')".

Table 3 - 1 lists some common date expressions used for snapshot refresh intervals.

Date Expression Evaluation
SYSDATE + 7 Exactly seven days from the last refresh
SYSDATE + 1/48 Every half hour
NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')+3/24 Every Monday at 3 PM
NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3), 'THURSDAY') The first Thursday of each quarter

Table 3 - 1. Examples of Common Refresh Intervals

Examples

The following example shows valid combinations for specifying a refresh interval:

CREATE SNAPSHOT snap
   . . .
   REFRESH COMPLETE
           START WITH '01-JUN-94'
           NEXT sysdate + 7
   AS . . . ;

This statement creates the SNAP snapshot, specifying complete automatic refreshes, the first of which occurs on June 1, 1994, with an automatic refresh interval of seven days from the most recent refresh.

The following command creates the ACCT refresh group, which is composed of three snapshots that will be refreshed every Monday:

dbms_refresh.make(
      name             => 'acct', 
      list             => 'scott.acct, scott.finance, scott.inventory', 
      next_date        => SYSDATE, 
      interval         => 'next_day(SYSDATE + 1, ''MONDAY'')', 
      implicit_destroy => TRUE, 
      lax              => TRUE);

Attention: Note that because the interval function evaluates to a literal, it must be enclosed in single quotes. Because you must use two single quotes to represent one single quote within a literal, in this example, the literal MONDAY is enclosed in two sets of single quotes.

Troubleshooting Automatic Refresh Problems

Several factors can prevent the automatic refresh of snapshots: not having an SNP background process, an intervening network or instance failure, or an instance shutdown. You may also encounter an error if you attempt to define a master detail relationship between two snapshots. You should define master detail relationships only on the master tables by using declarative referential integrity constraints; the related snapshots should then be placed in the same refresh group to preserve this relationship. Although not prevented by Oracle, if you attempt to define these constraints at the snapshot level, when refreshed, the snapshots may temporarily enter a state where they violate the integrity constraints that you have defined, producing a runtime error.

When any of the factors described above prevents the automatic refresh of a snapshot group, the group remains due to be refreshed. (Remember, when you specify a refresh interval for an individual snapshot, Oracle automatically creates a refresh group for that snapshot.) See for additional troubleshooting information.

Snapshots Failing to Refresh

If Oracle encounters a failure, such as a network failure, when attempting to refresh a snapshot refresh group it attempts the refresh again. The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling between each attempt. When the retry interval exceeds the refresh interval, Oracle continues to retry the refresh at the normal refresh interval.

Thus, snapshot refresh groups due to be refreshed will generally be refreshed automatically shortly after you start an SNP background process or resolve any network failures.

However, if Oracle continues to encounter errors when attempting to refresh a snapshot, it considers the group broken after its sixteenth unsuccessful attempt.

Oracle indicates that a snapshot is broken by setting the BROKEN column of the USER_REFRESH and USER_REFRESH_CHILDREN views to Y.

The errors causing Oracle to consider a snapshot refresh group broken are recorded in a trace file. After you have corrected these errors, you must manually refresh the group by calling the procedure DBMS_REFRESH.REFRESH described on 3 - 19. This resets the broken flag to N, and automatic refreshes will proceed from this point.

Snapshots Continually Refreshing

If you encounter a situation where your snapshots are being continually refreshed, you should check the refresh interval that you specified. This interval is evaluated before the snapshot is refreshed. If the interval that you specify is less than the amount of time it takes to refresh the snapshot, the snapshot will be refreshed each time the SNP background process checks the queue of outstanding jobs.

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

Snapshot Logs Growing Without Bounds

If a snapshot log is growing without bounds check for snapshots that were dropped but remain registered, You may need to purge part of the log by calling DBMS_SNAPSHOT.PURGE_LOG, as described

Manually Refreshing Snapshots

If you want to manually force a refresh to occur, you have two options.

You can manually refresh an existing snapshot refresh group.

You can manually refresh one or more snapshots, that may, or may not, be part of one or more refresh groups.

Manually Refreshing a Snapshot Refresh Group

To refresh a refresh group manually, call the REFRESH procedure in the DBMS_REFRESH package, as shown in the following example:

DBMS_REFRESH.REFRESH('acctg');

This example causes the refresh of the ACCTG group to occur immediately instead of waiting for the next automatic refresh interval. Manually refreshing a refresh group does not affect the next automatic refresh of the group.

Additional Information: The parameter for the REFRESH procedure is described in Table 12 - 60.

Manually Refreshing One or More Snapshots

To consistently refresh one or more snapshots that are not members of the same refresh group, use the REFRESH procedure in the package DBMS_SNAPSHOT.

This command allows you to provide a comma-separated list of snapshots that you want to refresh to a transaction-consistent point in time. These snapshots can belong to other refresh groups. Refreshing them using this procedure will not affect their regularly scheduled refresh interval if they are part of an automatic snapshot refresh group. However, if they are members of other groups, you should be aware that those groups will no longer be transactionally consistent.

The following example performs a complete refresh of SCOTT.EMP, a fast refresh of SCOTT.DEPT, and a default refresh of SCOTT.SALARY. By default, DBMS_SNAPSHOT.REFRESH refreshes the snapshot using the mode specified when the snapshot was created. If no mode was specified, the default mode is FORCE.

DBMS_SNAPSHOT.REFRESH( list   => 'scott.emp, scott.dept, scott.salary',
                       method => 'CF');

Additional Information: The parameters for the REFRESH procedure are described in Table 12 - 188.

Snapshot Refresh and Remote Databases

You must fully qualify remote database names when issuing a CREATE SNAPSHOT statement or snapshot refreshes may fail due to naming and privilege conflicts. For example, if you created a snapshot using the following commands:

CONNECT scott/tiger

CREATE DATABASE LINK sales.hq.com USING 'hq.sales.com';

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

then refreshes of the snapshot will fail if the user is not SCOTT.

Instead, you should replace the above CREATE SNAPSHOT statement with the following:

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

Your manual refreshes should then succeed. Automatic refreshes, however, will only work if the username and password strings are embedded in the database link, as shown in the following statement:

CREATE DATABASE LINK sales.hq.com CONNECT TO scott 
     IDENTIFIED BY tiger USING 'sales.hq.com';

The username and password that you specify allow the SNP background process to connect to the database. For the refresh to succeed, this user must have SELECT privileges on the master table and, for fast refreshes, the snapshot log.

Date Expression	Evaluation
`SYSDATE + 7`	Exactly seven days from the last refresh
`SYSDATE + 1/48`	Every half hour
`NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')+3/24`	Every Monday at 3 PM
`NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3), 'THURSDAY')`	The first Thursday of each quarter