Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot InstantiationChapter 14
Snapshots
Next: 15. Advanced Replication
 

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
  1. 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$_".

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

  3. 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;
  4. 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;
  5. Import the snapshot base table(s) from the export file(s) created in step 4.

  6. 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:

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

Figure 14.3


Previous: 14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot InstantiationOracle Built-in PackagesNext: 15. Advanced Replication
14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot InstantiationBook Index15. Advanced Replication

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.