[Chapter 14] 14.4 DBMS_REPCAT: Managing Snapshot Replication Groups

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:

Create a snapshot log for each master table if one does not already exist.
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

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$_".
Copy the export dump file(s) to the new snapshot site(s).
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;
```

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;

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

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.