[Chapter 14] 14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation

14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation

The DBMS_OFFLINE_SNAPSHOT package allows you to instantiate snapshots without having to run the CREATE SNAPSHOT command or the DBMS_REPEAT.SNAPSHOT_REPOBJECT procedure over the network (those methods are described under DBMS_REPCAT, later in this chapter). Doing offline instantiation in this way is particularly useful in cases where you wish to instantiate a snapshot site with a large amount of data in an advanced replication environment. Offline instantiation refers to the population of snapshots with the import and export utilities, as opposed to using the DBMS_SNAPSHOT.REFRESH procedure. This technique is less time-consuming and less taxing on your network, and it minimizes the time your environment must be quiesced.

You will typically use DBMS_OFFLINE_SNAPSHOT's BEGIN_LOAD and END_LOAD procedures in conjunction with the DBMS_REPCAT package's CREATE_SNAPSHOT_REPGROUP procedure; this procedure creates a new replicated snapshot group. The following sections summarize the syntax of the calls to BEGIN_LOAD and END_LOAD. See the later section Section 14.4, "DBMS_REPCAT: Managing Snapshot Replication Groups" " for a discussion of the DBMS_REPCAT procedure and how these procedures work together to instantiate snapshots in an advanced replication environment.

14.3.1 Getting Started with DBMS_OFFLINE_SNAPSHOT

The DBMS_OFFLINE_SNAPSHOT package is created when the Oracle database is installed. The dbmsofln.sql script (found in the built-in packages source directory, as described in Chapter 1 ) contains the source code for this package's specification. This script is called by catrep.sql , which must be run to install the advanced replication packages. The wrapped sql script prvtofln.plb creates the public synonym DBMS_OFFLINE_SNAPSHOT. No EXECUTE privileges are granted on DBMS_OFFLINE_SNAPSHOT; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.

14.3.1.1 DBMS_OFFLINE_SNAPSHOT programs

Table 14.4 summarizes the programs available through DBMS_OFFLINE_SNAPSHOT.

Table 14.4: DBMS_OFFLINE_SNAPSHOT Programs
Name	Description	Use in SQL?
BEGIN_LOAD	Call before beginning to load data from an export file	No
END_LOAD	Call after the load is complete	No

14.3.1.2 DBMS_OFFLINE_SNAPSHOT exceptions

The DBMS_OFFLINE_SNAPSHOT package raises the following exceptions:

Name	Number	Description
badargument	-23430	The gname, sname, master_site, or snapshot_oname parameter is NULL or".
missingremotesnap	-23361	The snapshot_oname parameter does not exist at the remote master site (master_site parameter).
snaptabmismatch	-23363	The base table name of the snapshot at master site and snapshot site do not match.

14.3.2 DBMS_OFFLINE_SNAPSHOT Interface

This section describes the programs available through the DBMS_OFFLINE_SNAPSHOT package.

14.3.2.1 The DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD procedure

Call the BEGIN_LOAD procedure before beginning to load data from an export file. The specifications for the Oracle7 and Oracle8 versions differ as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD
   (gname IN VARCHAR2,
    sname IN VARCHAR2,
    master_site IN VARCHAR2,
    snapshot_oname IN VARCHAR2,
    storage_c IN VARCHAR2 := '',
    comment IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD
   (gname IN VARCHAR2,
    sname IN VARCHAR2,
    master_site IN VARCHAR2,
    snapshot_oname IN VARCHAR2,
    storage_c IN VARCHAR2 := '',
    comment IN VARCHAR2 := '',
    min_communicatio IN BOOLEAN := TRUE );

Parameters are summarized in the following table.

Name	Description
gname	The replication group to which the new snapshot belongs.
sname	The schema that owns the new snapshot.
master_site	The global name of the snapshot master site.
snapshot_oname	The name of the temporary snapshot created at the master site.
storage_c	Optional storage clause for the new snapshot.
comment	Optional comment for the snapshot; stored with entry in DBA_SNAPSHOTS if supplied.
min_communication (Oracle8 only)	The min_communication parameter controls how the update trigger on updateable snapshots queues changes back to the master site. If this parameter is set to TRUE (the default), then old column values are sent only if the update changes their value. New column values are sent only if the column is part of primary key, or if the column is in a column group that has been modified.

The BEGIN_LOAD procedure does not raise any exceptions.

14.3.2.2 The DBMS_OFFLINE_SNAPSHOT. END_LOAD procedure

Call the END_LOAD procedure after the data import (initiated by the BEGIN_LOAD procedure) is complete. The specification is the same for Oracle7 and Oracle8:

PROCEDURE DBMS_OFFLINE_SNAPSHOT.END_LOAD
   (gname IN VARCHAR2,
    sname IN VARCHAR2,
    snapshot_oname IN VARCHAR2);

Parameters have the same meanings as for the BEGIN_LOAD procedure (see the previous section). The END_LOAD procedure does not raise any exceptions.