#

| Chapter 14 Snapshots |

## 14.4 DBMS_REPCAT: Managing Snapshot Replication Groups

Although most of the procedures in the DBMS_REPCAT package are used to create and maintain the advanced replication environment, some of the procedures let you manipulate snapshot replication groups. This section describes only the snapshot-related programs. The bulk of the DBMS_REPCAT programs are described in Chapters 15 and 16.

The procedures in DBMS_REPCAT used for manipulating snapshot replication groups are in large measure analogous to the procedures DBMS_REFRESH provides for manipulating simple snapshot groups.

### 14.4.1 Getting Started with DBMS_REPCAT

The DBMS_REPCAT package is created when the Oracle database is installed. The dbmsrepc.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 script creates the public synonym DBMS_REPCAT. The package procedure DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT grants EXECUTE privileges on the package to the specified grantee. In addition, the package owner (SYS) and users with the EXECUTE ANY PROCEDURE system privilege may execute it.

#### 14.4.1.1 DBMS_REPCAT programs

Table 14.5 lists only the DBMS_REPCAT snapshot-related programs; see Tables 15-3 and 16-10 for the remaining DBMS_REPCAT programs.

Table 14.5: DBMS_REPCAT Programs (Snapshots Only)
Name | Description | Use in SQL
--- | --- | ---
ALTER_SNAPSHOT_PROPAGATION | Changes a snapshot replication group's propagation mode | No
CREATE_SNAPSHOT_REPGROUP | Creates a snapshot replication group | No
CREATE_SNAPSHOT_REPOBJECT | Adds an object to a snapshot replication group | No
DROP_SNAPSHOT_REPGROUP | Drops a snapshot replication group | No
DROP_SNAPSHOT_REPOBJECT | Drops an object from a snapshot replication group | No
REFRESH_SNAPSHOT_REPGROUP | Refreshes a snapshot replication group | No
SWITCH_SNAPSHOT_MASTER | Remasters a snapshot site to another master site | No

#### 14.4.1.2 DBMS_REPCAT exceptions

DBMS_REPCAT defines the following exceptions for the programs listed in Table 14.6.

Table 14.6: DBMS_REPCAT Exceptions
Name | Number | Description
--- | --- | ---
commfailure | -23317 | Unable to communicate with master
dbnotcompatible | -23375 | Attempt to use SYNCHRONOUS propagation in Pre-7.3 database
ddlfailure | -23318 | Unable to perform DDL
duplicateobject | -23309 | Object oname already exists
duplicaterepgroup | -23374 | Replication group gname already exists
missingobject | -23308 | Object oname does not exist in master's replication group gname
missingremoteobject | -23381 | Master site has not generated replication support for oname
missingrepgroup | -23373 | Replication group gname does not exist
missingschema | -23306 | Schema sname does not exist
misssnapobject | -23355 | Object oname does not exist at master
nonmaster | -23312 | Master site associated with snapshot group is no longer a master site
nonsnapshot | -23314 | Calling site is not a snapshot site
norepoption | -23364 | Replication option not installed
typefailure | -23319 | propagation_mode not specified correctly

### 14.4.2 Creating and Dropping Snapshot Replication Groups

The CREATE_SNAPSHOT_REPGROUP and DROP_SNAPSHOT_REPGROUP procedures allow you to create and destroy snapshot replication groups.

#### 14.4.2.1 The DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP procedure

This procedure creates a new, empty snapshot replication group. You must invoke it from the snapshot site. The program specification follows:

>     PROCEDURE DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP
>        (gname IN VARCHAR2,
>         master IN VARCHAR2,
>         comment IN VARCHAR2 := '',
>         propagation_mode IN VARCHAR2  := 'ASYNCHRONOUS');

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the new snapshot group
master | Global name of master site
comment | Comment for the snapshot group; visible in DBA_REPSITES data dictionary view
propagation_mode | Snapshot propagation mode (SYNCHRONOUS, or ASYNCHRONOUS)

#### 14.4.2.1.1 Exceptions

The CREATE_SNAPSHOT_REPGROUP procedure raises the following exceptions:

Name | Number | Description
--- | --- | ---
commfailure | -23317 | Unable to communicate with master
dbnotcompatible | -23375 | Attempt to use SYNCHRONOUS propagation in pre-7.3 database
duplicaterepgroup | -23374 | Replication group gname already exists
nonmaster | -23312 | The master parameter is not a master site
norepoption | -23364 | Replication option not installed
typefailure | -23319 | propagation_mode not specified correctly

#### 14.4.2.1.2 Restrictions

Note the following restrictions on calling CREATE_SNAPSHOT_REPGROUP:

- You must be connected to the replication administrator account (typically REPADMIN) to call the CREATE_SNAPSHOT_REPGROUP procedure.

- The snapshot group name must match the name of the master replication group.

#### 14.4.2.2 The offline snapshot instantiation procedure

The procedure for performing offline instantiation of snapshots in an advanced replication environment (using the CREATE SNAPSHOT command and the DBMS_OFFLINE_SNAPSHOT and DBMS_REPCAT packages) follows:

1. Create a snapshot log for each master table if one does not already exist.

2. Create a snapshot of each master table in the master database, and in the same schema as the master table. Of course, the name of the snapshot will have to be different from the name of the master table. The CREATE SNAPSHOT statement must also include a loopback database link qualifier.

>     CREATE SNAPSHOT snp_countries
>     AS SELECT * FROM countries@D7CA.BIGWHEEL.COM@TCPIP

3. Perform user exports of all schema that own master tables. You should be logged on to the schema owner account for these exports. The only tables that you need to export are the snapshot base tables -- that is, those whose names begin with "SNAP$_".

4. Copy the export dump file(s) to the new snapshot site(s).

5. Use CREATE_SNAPSHOT_REPGROUP at the snapshot sites to create a new snapshot replication object group. The name of this object group should be the same as the name of the replication group of which the master tables are members.

   >     BEGIN
   >     	DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP(
   >     		gname 		=> 'SPROCKET',
   >     		master 		=> 'D7CA.BIGWHEEL.COM',
   >     		comment		=> 'Group created on '||sysdate|| ' by '||user,
   >     		propagation_mode=> 'ASYNCHRONOUS');
   >     END;

6. Call DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD to begin loading the data from the export file(s). You must call the procedure for every snapshot you plan to import.

   >     BEGIN
   >     	DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD(
   >     		gname		=>	'SPROCKET',
   >     		sname		=>	'SPROCKET',
   >     		master_site	=>	'D7CA.BIGWHEEL.COM'
   >     		snapshot_oname	=>	'SNP_COUNTRIES'
   >     		storage_c	=>	'TABLESPACE sprocket_data STORAGE (INITIAL 64K)'
   >     		comment		=>	'Load of COUNTRIES snapshot begun at '||sysdate);
   >     END;

7. Import the snapshot base table(s) from the export file(s) created in step 4.

8. Call DBMS_OFFLINE_SNAPSHOT.END_LOAD for each snapshot when the load is complete.

   >     BEGIN
   >     	DBMS_OFFLINE_SNAPSHOT.END_LOAD(
   >     		gname		=> 'SPROCKET'
   >     		sname		=> 'SPROCKET'
   >     		snapshot_oname	=> 'SNP_COUNTRIES');
   >     END;

#### 14.4.2.3 The DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP procedure

The DBMS_REPCAT package's DROP_SNAPSHOT_REPGROUP procedure is the counterpart to the CREATE_SNAPSHOT_REPGROUP procedure. As you would suspect, this procedure drops an existing snapshot replication group, and optionally, all member snapshots. Here's the specification:

>     PROCEDURE DBMS_REPCAT>DROP_SNAPSHOT_REPGROUP
>        (gname IN VARCHAR2,
>         drop_contents IN BOOLEAN := FALSE);

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the snapshot group.
drop_contents | If TRUE, objects in gname are dropped. If FALSE (the default) they are simply no longer replicated.

#### 14.4.2.3.1 Exceptions

The DROP_SNAPSHOT_REPGROUP procedure raises the following exceptions:

Name | Number | Description
--- | --- | ---
missingrepgroup | -23373 | Replication group gname does not exist
nonmaster | -23313 | Calling site is not a snapshot site

#### 14.4.2.3.2 Restrictions

If drop_contents is set to FALSE, the triggers created to support snapshot modifications remain.

#### 14.4.2.3.3 Example

The following example illustrates the dropping of a snapshot replication group with the DROP_SNAPSHOT_REPGROUP procedure:

>     BEGIN
>     	DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP(
>     	gname		=> 'SPROCKET',
>     	drop_objects	=> TRUE);
>     END;

Here, we drop the snapshot replication group SPROCKET and drop the member snapshots as well.

### 14.4.3 Adding and Removing Snapshot Replication Group Objects

The CREATE_SNAPSHOT_REPOBJECT and DROP_SNAPSHOT_REPOBJECT procedures add and remove objects, respectively, from a snapshot replication group. These objects may be snapshots, packages, package bodies, procedures, synonyms, or views.

#### 14.4.3.1 The DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT procedure

The CREATE_SNAPSHOT_REPOBJECT procedure adds an object to a snapshot replication group. For new snapshot objects, this procedure generates row-level replication triggers for snapshots if the master table uses row-level replication. The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT. CREATE_SNAPSHOT_REPOBJECT
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         type IN VARCHAR2,
>         ddl_text IN VARCHAR2 := '',
>         comment IN VARCHAR2 := '',
>         gname IN VARCHAR2 := '',
>         gen_objs_owner IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         type IN VARCHAR2,
>         ddl_text IN VARCHAR2 := '',
>         comment IN VARCHAR2 := '',
>         gname IN VARCHAR2 := '',
>         gen_objs_owner IN VARCHAR2 := '',
>         min_communication IN BOOLEAN  := TRUE);

> NOTE: The only difference between the Orace7 and Oracle8 implementations is the addition of the min_communication parameter in Oracle8.

Parameters are summarized in the following table.

Name | Description
--- | ---
sname | Name of schema to which oname belongs.
oname | Name of object to be added.
type | Object type. Supported types are PACKAGE, PACKAGE BODY, PROCEDURE, SNAPSHOT, SYNONYM, and VIEW.
ddl_text | DDL used to create object (for type SNAPSHOT only).
comment | Comment on object, visible in DBA_REPOBJECT data dictionary view.
gname | Name of snapshot group to which object is being added. Defaults to sname if not specified.
gen_objs_owner | Name of the schema in which to create the generated trigger and trigger package or procedure wrapper for the object. Defaults to sname.
drop_objects | If set to TRUE, object is dropped too. If FALSE (the default), object is only removed from the snapshot group.
min_communication (Oracle8 only) | Must be FALSE if any master site is running Oracle7. TRUE, the default setting, uses the minimum communication algorithm.

#### 14.4.3.1.1 Exceptions.

CREATE_SNAPSHOT_REPGROUP raises the following exceptions:

Name | Number | Description
--- | --- | ---
commfailure | -23317 | Unable to communicate with master site
ddlfailure | -23318 | Unable to perform DDL
duplicateobject | -23309 | Object oname already exists
missingobject | -23308 | Object oname does not exist in master's replication group gname
missingremoteobject | -23381 | Master site has not generated replication support for oname
missingschema | -23306 | Schema sname does not exist
misssnapobject | -23355 | Object oname does not exist at master
nonmaster | -23312 | Master site associated with snapshot group is no longer a master site
nonsnapshot | -23314 | Calling site is not a snapshot site
typefailure | -23319 | Invalid value for type

> NOTE: CREATE_SNAPSHOT_REPOBJECT is called from the replication administrator (typically REPADMIN) account. If you are creating an snapshot with ddl_text, be sure to specify the schema in which it should be created (if other than the replication administrator account).

#### 14.4.3.1.2 Example

The following example illustrates how to add an object to an existing snapshot replication group:

>     BEGIN
>     	DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
>     		sname		=> 'SPROCKET',
>     		oname		=> 'PRICE_LIST',
>     		type 		=> 'SNAPSHOT' ,
>     		ddl_text=>
>     'CREATE SNAPSHOT  SPROCKET.PRICES AS SELECT * FROM
>         PRICES@D7CA.BIGWHEEL.COM' ,
>     		gnam		 => 'SPROCKET',
>     		gen_objs_owner => 'SPROCKET')
>     END;
>     BEGIN

#### 14.4.3.2 The DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT procedure

The DROP_SNAPSHOT_REPOBJECT procedure drops an object from a snapshot replication group.

>     PROCEDURE DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT
>        (sname IN VARCHAR2,
>         oname IN VARCHAR2,
>         type IN VARCHAR2,
>         drop_objects IN BOOLEAN := FALSE);.

For parameter descriptions, see the table in the Section 14.4.3.1, "The DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT procedure"" section.

#### 14.4.3.2.1 Exceptions

DROP_SNAPSHOT_REPOBJECT raises the following exceptions:

Name | Number | Description
--- | --- | ---
missingobject | -23308 | Object oname does not exist in master's replication group gname
nonsnapshot | -23314 | Calling site is not a snapshot site
typefailure | -23319 | Invalid value for type

#### 14.4.3.2.2 Restrictions

If the type parameter in DROP_REPOBJECT is SNAPSHOT and you do not set the drop_objects parameter to TRUE, replication triggers and associated packages remain in the schema, and deferred transactions (if any) remain in the DEFTRANS queue.

#### 14.4.3.2.3 Example

The following example drops an object from a snapshot replication group:

>     DBMS_REPCAT.FTOP_SNAPSHOT_REPOBJECT(
>     	sname	=> 'SPROCKET',
>     	oname	=> 'PRICE_LIST',
>     	type 	=> 'SNAPSHOT',
>     	drop_objects=> TRUE);
>     END;

### 14.4.4 Altering a Snapshot Replication Group's Propagation Mode

The ALTER_SNAPSHOT_PROPAGATION procedure changes a snapshot replication group's propagation mode. The propagation mode can be either SYNCHRONOUS or ASYNCHRONOUS. When you call this procedure, Oracle does the following:

- Pushes any transactions that exist in the deferred RPC queue for the group

- Locks the snapshot base tables (SNAP$_<tablename>)

- Regenerates replication triggers and packages for the objects in the group

#### 14.4.4.1 The DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION procedure

Call the ALTER_SNAPSHOT_PROPAGATION procedure to change the propagation mode of a particular snapshot. Specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION
>        (alter_snapshot_propagation(
>         gname IN VARCHAR2,
>         propagation_mode IN VARCHAR2,
>         comment IN VARCHAR2 := '',
>         execute_as_user  IN BOOLEAN  := FALSE);

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION
>        (gname IN VARCHAR2,
>         propagation_mode IN VARCHAR2,
>         comment IN VARCHAR2 := '' );

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the replication group to be altered
propagation_mode | The new propagation mode to use (SYNCHRONOUS or ASYNCHRONOUS)
comment | Comment visible in DBA_REPPROP data dictionary view
execute_as_user (Oracle7 only) | FALSE (default) indicates that remote system will authenticate calls using authentication context user who originally queued the RPC; TRUE indicates that remote system will use authentication context of the session user

#### 14.4.4.1.1 Exceptions

ALTER_SNAPSHOT_PROPAGATION raises the following exceptions:

Name | Number | Description
--- | --- | ---
dbnotcompatible | -23375 | Database version is not 7.3 or later
missingrepgroup | -23373 | Replication group gname does not exist
typefailure | -23319 | Invalid propagation_mode

#### 14.4.4.1.2 Restrictions

ALTER_SNAPSHOT_PROPAGATION must be called from a snapshot site.

#### 14.4.4.1.3 Example

The following example shows how to switch from asynchronous to synchronous propagation for a given snapshot replication group:

>     BEGIN
>     	DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION(
>     	gname               => 'SPROCKET',
>     	propagation_mode	=> 	'ASYNCHRONOUS',
>     	comment	=> 	'Mode set to asynchronous on ' ||sysdate||
>     			'by '||user);
>     END;

### 14.4.5 Manually Refreshing a Snapshot Replication Group

You can use the REFRESH_SNAPSHOT_REPGROUP to refresh manually a snapshot replication group. The procedure can optionally drop objects that are no longer in the group, and/or refresh the snapshots and other objects.

> NOTE: The REFRESH_SNAPSHOT_REPGROUP procedure replaces the REFRESH_SNAPSHOT_REPSCHEMA procedure. Although REFRESH_SNAPSHOT_REPSCHEMA still exists (as of Oracle 7.3.3), do not use it; it does not exist in Oracle 8.0.3.

#### 14.4.5.1 The DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP procedure

Call the DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP procedure to refresh a snapshot replication group manually. Specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP
>        (gname IN VARCHAR2,
>         drop_missing_contents IN BOOLEAN := FALSE,
>         refresh_snapshots IN BOOLEAN := FALSE,
>         refresh_other_objects IN BOOLEAN := FALSE,
>         execute_as_user IN BOOLEAN:= FALSE);

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP
>        (gname IN VARCHAR2,
>         drop_missing_contents IN BOOLEAN := FALSE,
>         refresh_other_objects IN BOOLEAN := FALSE )

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the replication group.
drop_missing_contents | If TRUE, drop schema objects that are no longer in the snapshot group. If FALSE (the default), objects are simply no longer replicated.
refresh_snapshots | If TRUE, force a refresh of snapshots in gname. Default is FALSE.
refresh_other_objects | If TRUE, refresh non-snapshot objects in gname, such as views and procedures. Non-snapshot objects are refreshed by dropping and recreating them. Default is FALSE.
execute_as_user (Oracle7 only) | FALSE (default) indicates that the remote system will authenticate calls using the authentication context user who originally queued the RPC; TRUE indicates that remote system will use authentication context of the session user.

#### 14.4.5.1.1 Exceptions

REFRESH_SNAPSHOT_REPGROUP raises the following exceptions:

Name | Number | Description
--- | --- | ---
commfailure | -23317 | Unable to communicate with the master site
nonmaster | -23313 | Master is no longer a master database
nonsnapshot | -23314 | Calling site is not a snapshot site

#### 14.4.5.1.2 Restrictions

REFRESH_SNAPSHOT_REPGROUP must be called from a snapshot site.

#### 14.4.5.1.3 Example

The following example illustrates how REFRESH_SNAPSHOT_REPGROUP is commonly used:

>     BEGIN
>     	DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP(
>     		gname => 'SPROCKET',
>     		drop_missing_contents				=> TRUE,
>     		refresh_snapshots				=> TRUE,
>     		refresh_other_objects				=> TRUE)
>     END;

This example refreshes all snapshots in group SPROCKET, drops schema objects that are no longer in the group, and recreates any views, procedures, or synonyms that have been created or altered at the master site.

### 14.4.6 Switching the Master of a Snapshot Replication Group

Should it ever become necessary to point a snapshot replication group to a different master site, you can do it with the SWITCH_SNAPSHOT_MASTER procedure.

#### 14.4.6.1 The DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER procedure

The SWITCH_SNAPSHOT_MASTER procedure lets you switch a snapshot replication group to a different master site. This procedure changes the master site for the specified snapshot group. The new master site must contain a replica of the replication group gname. The next time the snapshot group refreshes, Oracle performs a full refresh. The specifications for SWITCH_SNAPSHOT_MASTER differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

>     PROCEDUREDBMS_REPCAT.SWITCH_SNAPSHOT_MASTER
>        (gname IN VARCHAR2 := '',
>         master IN VARCHAR2,
>         execute_as_user IN BOOLEAN  = FALSE,
>         sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER
>        (gname IN VARCHAR2 := '',
>         master IN VARCHAR2)

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the snapshot group
master | Name of the new master site
execute_as_user (Oracle7 only) | FALSE (default) indicates that the remote system will authenticate calls using the authentication context user who originally queued the RPC; TRUE indicates that remote system will use authentication context of the session user
sname (Oracle7 only) | Not used

#### 14.4.6.1.1 Exceptions

The SWITCH_SNAPSHOT_MASTER procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
commfailure | -23317 | Unable to communicate with master site
nonmaster | -23312 | The master parameter is not a master site
nonsnapshot | -23314 | Calling site is not a snapshot site

#### 14.4.6.1.2 Restrictions

Note the following restrictions on calling SWITCH_SNAPSHOT_MASTER:

- The new master site must contain a replica of the replication group gname.

- Snapshots whose query is greater than 32K cannot be remastered.

#### 14.4.6.1.3 Example

The following call remasters snapshot group SPROCKET to D7NY.BIGWHEEL.COM:

>     BEGIN
>     	DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER(
>     		gname		=> 'SPROCKET',
>     		master		=> 'D7NY.BIGWHEEL.COM')
>     END

> NOTE: Put snapshot logs on the master tables at the new master site so that you can use fast refreshes.

Figure Figure 14.3 graphically illustrates this example.

#### Figure 14.3: Using DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER

14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation |  | 15. Advanced Replication

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.