Oracle7 Server Utilities

Import and Snapshots

There are four interrelated objects in a snapshot system: the master table, optional snapshot log, the master table trigger that updates the snapshot log, and the snapshot itself. All of these objects are exported normally. The master table trigger, if it exists, is exported as part of the master table. The tables (master table, snapshot log table definition, and snapshot tables) can be exported independently of one another. This section discusses how fast refreshes are affected when these objects are imported.

Master Table

The imported data is recorded in the snapshot log if the master table already exists for the database being imported to and it has a snapshot log.

Master Table Trigger

The snapshot log update trigger, if it exists, is exported as part of the master table. Similarly, it is automatically imported from the export file. Because all triggers are imported after all tables, the update trigger is imported after the master table and snapshot log.

Snapshot Log

When a snapshot log is exported, only the table definition is placed in the export file, because the master table ROWIDs stored in the snapshot log would have no meaning upon import. Therefore, an imported snapshot log will be empty. As a result, each snapshot's first attempt to do a fast refresh will fail, generating an error indicating that a complete refresh is required.

To avoid the refresh error, do a complete refresh after importing a snapshot log. After doing a complete refresh, subsequent fast refreshes will work properly.

Snapshots

A snapshot that has been restored from a backup file has "gone back in time" to a previous state. On import, the time of the last refresh is imported as part of the snapshot table definition. The function that calculates the next refresh time is also imported.

Each refresh leaves a signature. A fast refresh uses the log entries that date from the time of that signature to bring the snapshot up to date. When the fast refresh is complete, the signature is deleted and a new signature is created. Any log entries that are not needed to refresh other snapshots are also deleted (all log entries with times before the earliest remaining signature).

Importing a Backup

When restoring a dropped snapshot from a backup, there are two potential problems:

If a snapshot is refreshed at time A, exported at time B, and refreshed again at time C, then the re-imported version has the last refresh time recorded as time A. Log entries needed for a fast refresh may no longer exist. If the log entries do exist (because they are needed for another snapshot that has yet to be refreshed), then they are used, and the fast refresh completes successfully. Otherwise, the fast refresh fails, generating an error that says a complete refresh is required.

Whether the fast refresh succeeds or fails, the signature left at time C remains. All log entries after time C will remain in the log, waiting for a refresh that never occurs. This situation causes the log to grow continuously -- it does not shrink, even when all snapshots have been refreshed. Subsequent fast refreshes fail, generating an error that says a complete refresh is required. This problem can be solved by purging the snapshot log.

Suggestion: Both of these problems can be avoided by exporting backup copies of snapshots immediately after doing a fast refresh. If out of date backups must be imported, do a complete refresh at the earliest opportunity.

Importing into an Existing Snapshot

If snapshot rows have been lost, and the snapshot is restored from a backup without dropping it; other problems may result. If the snapshot is exported at time A and a refresh occurs at time B, the snapshot's signature reflects time B, but data is imported as of time A.

Subsequent fast refreshes update from time B, not from time A. So changes logged in the master table between time A and B are not reflected in the snapshot.

This situation can only occur if IGNORE=Y is specified for the import. (Otherwise, the snapshot rows are not imported.) As a result, the situation is undetectable -- the IGNORE setting prevents an object creating error from being issued.

Suggestion: To avoid such problems, export backup copies of snapshots immediately after doing a fast refresh. If out of date backups must be imported, do a complete refresh at the earliest opportunity.

Second Copy of a Snapshot

If a snapshot is imported into two databases or into two different schemas, then there are two copies of the snapshot, but only one signature.

Warning: The first snapshot to do a fast refresh recognizes the signature and gets the expected results. When a fast refresh is performed for the second snapshot the signature will be missing, causing an error because a complete refresh is required. After the complete refresh is performed, the second snapshot will have its own signature and subsequent fast refreshes will work properly.