Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 15.2 DBMS_REPCAT_ADMIN: Setting Up More Administrator AccountsChapter 15
Advanced Replication
Next: 15.4 DBMS_OFFLINE_OG: Performing Site Instantiation
 

15.3 DBMS_REPCAT: Replication Environment Administration

The DBMS_REPCAT package performs many advanced replication operations, including some described in other chapters. This section describes only the DBMS_REPCAT programs that you'll use to administer the advanced replication environment.

15.3.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.

15.3.1.1 DBMS_REPCAT programs

Table 15.3 lists in alphabetical order the DBMS_REPCAT procedures used to maintain an advanced replication environment. In the sections that follow, we divide these programs by category (replication groups, replication objects, replication support, master sites, the "repcatlog" queue, and quiescence); each section includes a table showing the programs available in that category. For example, the section Section 15.3.2, "Replication Groups with DBMS_REPCAT"" describes only the replication group programs.


Table 15.3: DBMS_REPCAT Programs (Replication Administration Only)

Name

Description

Use in SQL?

ADD_MASTER_DATABASE

Adds master database to replication group

No

ALTER_MASTER_PROPAGATION

Alters propagation method for a replication group at a given site (options are SYNCHRONOUS or ASYNCHRONOUS)

No

ALTER_MASTER_REPOBJECT

Performs DDL on a replicated object

No

COMMENT_ON_REPGROUP

Creates or updates a comment on a replication group; visible in DBA_REPGROUP data dictionary view

No

COMMENT_ON_REPSITES

Creates or updates a comment on a replication site; visible in DBA_REPSITES data dictionary view

No

COMMENT_ON_REPOBJECT

Creates or updates a comment on a replicated object; visible in DBA_REPOBJECT data dictionary view

No

CREATE_MASTER_REPGROUP

Creates a master replication group

No

CREATE_MASTER_REPOBJECT

Adds an object to a replication group

No

DO_DEFERRED_REPCAT_ADMIN

Performs outstanding administrative tasks at local master site

No

DROP_MASTER_REPGROUP

Drops a replication group

No

DROP_MASTER_REPOBJECT

Drops an object from a replication group

No

EXECUTE_DDL

Specifies DDL to execute at master sites

No

GENERATE_REPLICATION_PACKAGE

Generates packages required to replicate a given table

No

GENERATE_REPLICATION_SUPPORT

Generates triggers, packages, and procedures required to replicate a given table

No

GENERATE_REPLICATION_TRIGGER

Generates triggers and packages required to replicate a given table

No

PURGE_MASTER_LOG

Deletes entries from the local RepCatLog (DBA_REPCATLOG)

No

RELOCATE_MASTERDEF

Changes the master definition site for a replication group

No

REMOVE_MASTER_DATABASES

Drops one or more master databases from a replication group

No

REPCAT_IMPORT_CHECK

Confirms a replicated object's validity after an import

No

RESUME_MASTER_ACTIVITY

Enables propagation of a replication group that had been quiesced

No

SEND_AND_COMPARE_OLD_VALUES

Reduces propagation overhead by not sending unchanged columns to a master site.

No

SET_COLUMNS

Designates alternative column(s) to use instead of a primary to uniquely identify rows of a replicated table

No

SUSPEND_MASTER_ACTIVITY

Quiesces a replication group

No

WAIT_MASTER_LOG

Determines whether asynchronous DML has been applied at a master site

No

15.3.1.2 Exceptions

Table 15.4 describes exceptions raised by the DBMS_REPCAT programs described in this chapter.


Table 15.4: DBMS_REPCAT Exceptions

Name

Number

Description

commfailure

-23317

Unable to communicate with remote site

dbnotcompatible

-23375

Operation not available for current version of RDBMS

ddlfailure

-23318

DDL failed during object creation or maintenance activity

duplicateobject

-23309

Replicated object already exists

duplicateschema

-23307

Attempt to create duplicate replication group

fullqueue

-23353

Attempt to drop replication group or schema for which RPC entries are queued

invalidpropmode

-23380

Invalid propagation mode (used internally)

missingcolumn

-23334

Reference to nonexistent column

missinggroup

-23331

Replication group does not exist

missingobject

-23308

Object does not exist

missingrepgroup

-23373

Replication group does not exist

missingschema

-23306

Schema does not exist

missingvalue

-23337

Missing value (used internally)

nonmaster

-23313

Site is not a master site

nonmasterdef

-23312

Site is not a master definition site

nonsnapshot

-23314

Site is not a snapshot site

norepoption

-23364

Replication option not installed

notnormal

-23311

Replication group is not in normal propagation mode

notquiesced

-23310

Replication group is not quiesced

paramtype

-23325

Invalid parameter type (used internally)

reconfigerror

-23316

Attempt to drop master definition site with REMOVE_MASTER_DATABASES

repnotcompatible

-23376

Replication versions not compatible (used internally)

typefailure

-23319

Attempt to replicate nonsupported datatype

version

-23315

Replication versions not compatible (used internally)

15.3.2 Replication Groups with DBMS_REPCAT

Once you have created administrative accounts for your advanced replication environment and established the appropriate database links among your various sites, you are ready for the next step, which is to create a replication group. Here are the procedures you will use:

DBMS_REPCAT.CREATE_MASTER_REPGROUP
DBMS_REPCAT.DROP_MASTER_REPGROUP
DBMS_REPCAT.COMMENT_ON_REPGROUP

The following sections describe these programs in detail.

Figure 15.1 shows how replication groups work.

Figure 15.1: . Replication groups

Figure 15.1

15.3.2.1 The DBMS_REPCAT.CREATE_MASTER_REPGROUP procedure

The CREATE_MASTER_REPGROUP procedure creates a replication group at the master definition site. Here's the specification:

PROCEDURE DBMS_REPCAT.CREATE_MASTER_REPGROUP
   (gname IN VARCHAR2,
    group_comment  IN VARCHAR2 := '',
    master_comment IN VARCHAR2 := '',
    qualifier      IN VARCHAR2 := '');

Parameters are summarized in the following table.

Name

Description

gname

Name of the new replication group

group_comment

Comment for new replication group visible in DBA_REPGROUP data dictionary view

master_comment

Comment for the calling site, visible in DBA_REPSITES data dictionary view

qualifier

For internal use

15.3.2.1.1 Exceptions

The CREATE_MASTER_REPGROUP procedure may raise the following exceptions:

Name

Number

Description

ddlfailure

-23318

Unable to create REP$WHAT_AM_I package or package body

duplicaterepgroup

-23374

Replication group gname already exists

duplicateschema

-23307

Schema gname is already a replication group

missingrepgroup

-23373

The gname was not specified correctly

norepoption

-23364

Replication option not installed

dbnotcompatible

-23375

The gname is not a schema name, and RDBMS is a pre-7.3 release

15.3.2.1.2 Restrictions

You must be connected to the replication administrator account (typically REPADMIN) to call CREATE_MASTER_REPGROUP.

15.3.2.1.3 Example

The following call creates a replication group named SPROCKET:

BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPGROUP(	gname=> 'SPROCKET', -
	group_comment => 'Replication group SPROCKET created on
		'||sysdate|| ' by ' ||user, -
	master_comment => 'Master  Definition Site created on
	   '||sysdate|| ' by ' ||user);
END;

This call creates a replication group with no objects. The site from which you make the call is the master definition site for the group.

For an additional example, see the repgroup.sql file on the companion disk. That example queries the DBA_REPGROUP data dictionary view and lists all replication groups in the database.

15.3.2.2 The DBMS_REPCAT.DROP_MASTER_REPGROUP procedure

The DROP_MASTER_REPGROUP procedure drops one or more replication groups at the master definition site. Here's the specification:

PROCEDURE DBMS_REPCAT.DROP_MASTER_REPGROUP
   (gname IN VARCHAR2,
    drop_contents IN BOOLEAN := FALSE,
    all_sites IN BOOLEAN := FALSE);

Parameters are summarized in the following table.

Name

Description

all_sites

If TRUE and call is the master definition site, then drop the replication group from all sites in the environment

drop_contents

If TRUE, drop the objects in the replication group as well as the group itself

gname

Name of the new replication group

15.3.2.2.1 Exceptions

The DROP_MASTER_REPGROUP procedure raises the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with all masters, and all_sites is TRUE

fullqueue

-23353

Outstanding transactions queued for replication group gname

missingrepgroup

-23373

gname is not specified correctly

nonmaster

-23313

Calling site is not a master site

nonmasterdef

-23312

Calling site is not a master definition site, and all_sites is TRUE

15.3.2.2.2 Restrictions

Note the following restrictions on calling DROP_MASTER_REPGROUP:

  • You must be connected to the replication administrator account (typically REPADMIN) to call DROP_MASTER_REPGROUP.

  • DROP_MASTER_REPGROUP does not drop all snapshots if the gname parameter is the master of any snapshot groups. Dropping a master site does not necessarily remove it from the DBA_REPSITES at other masters.

TIP: Before calling DROP_MASTER_REPGROUP, call DBMS_REPCAT.REMOVE_MASTER_DATABASES from the master definition site to remove all masters for which you plan to drop the group and that do not contain any other replication groups. In addition, you can avoid the full queue error by quiescing the replication group before attempting to drop the replication group.

15.3.2.2.3 Example

This call, from the master definition site, drops a replication group from all sites where it exists:

BEGIN
    DBMS_REPCAT.DROP_MASTER_REPGROUP(
        gname => 'SPROCKET',
        all_sites => TRUE );
END;

The next call drops a replication group and all of its objects from the calling site, assumed to be a master site (not a master definition site):

BEGIN
    DBMS_REPCAT.DROP_MASTER_REPGROUP(
        gname => 'SPROCKET',
        drop_contents => TRUE );
END;

If you want to drop a replication group from all master sites, along with the replicated objects, you can do the following:

BEGIN
    DBMS_REPCAT.DROP_MASTER_REPGROUP(
        gname => 'SPROCKET',
        all_sites => TRUE
        drop_contents => TRUE );
 END;

15.3.2.3 The DBMS_REPCAT.COMMENT_ON_REPGROUP procedure

This procedure adds a new schema comment field to the DBA_REPCAT data dictionary view, or changes an existing one. The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DMBS_REPCAT.COMMENT_ON_REPGROUP
   (gname IN VARCHAR2 := '',
    comment IN VARCHAR2,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DMBS_REPCAT.COMMENT_ON_REPGROUP
   (gname IN VARCHAR2,
    comment IN VARCHAR2);

Parameters are summarized in the following table.

Name

Description

gname

Replication group to which comment is added

comment

Comment

sname

Not used

NOTE: As noted in the earlier sidebar entitled the sidebar "Replication Groups versus Replication Schema"," you can see that Oracle has dispensed with the sname parameter in Oracle8.

15.3.2.3.1 Exceptions

The COMMENT_ON_REPROUP procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with one or more master sites

missinggroup

-23331

Replication group gname does not exist

nonmasterdef

-23312

Calling site is not master definition site

15.3.2.3.2 Restrictions

The COMMENT_ON_REPGROUP procedure must be called from the master definition site.

15.3.2.3.3 Example

This call adds or replaces the comment in DBA_REPGROUP for the SPROCKET replication group:

BEGIN
    DBMS_REPCAT.COMMENT_ON_REPGROUP(
        gname 'SPROCKET',
        comment => 'Comment added on '||sysdate|| ' by '||user);
END;

COMMENT_ON_REPGROUP queues an RPC to update the field at all other master sites.

15.3.3 Replicated Objects with DBMS_REPCAT

After you have created your replication group(s) (with or without comments), you are ready to add, alter, and remove member objects. Here are the procedures you need:

DBMS_REPCAT.CREATE_MASTER_REPOBJECT
DBMS_REPCAT.SET_COLUMNS
DBMS_REPCAT.DROP_MASTER_REPOBJECT
DBMS_REPCAT.COMMENT_ON_REPOBJECT
DBMS_REPCAT.ALTER_MASTER_REPOBJECT
DBMS_REPCAT.EXECUTE_DDL

The following sections describe these programs in detail.

15.3.3.1 The DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure

The CREATE_MASTER_REPOBJECT procedure creates a replicated object. Its specification follows:

PROCEDURE DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
   sname IN VARCHAR2,
   oname IN VARCHAR2,
   type IN VARCHAR2,
   use_existing_object IN BOOLEAN := TRUE,
   ddl_text IN VARCHAR2 := NULL,
   comment IN VARCHAR2 := '',
   retry IN BOOLEAN := FALSE,
   copy_rows IN BOOLEAN := TRUE,
   gname IN VARCHAR2 := '');

Parameters are summarized in the following table.

Name

Description

sname

Name of the schema to which oname belongs.

oname

Name of the object to be added.

type

Object type. Valid types: TABLE, INDEX, SYNONYM, TRIGGER, VIEW, PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY.

use_existing_object

Set to TRUE to reuse existing objects with the same name and structure at master sites.

ddl_text

Text of DDL statement to create object oname (use this parameter if and only if object does not already exist).

comment

Comment on replicated object, visible in DBA_REPOBJECT data dictionary view.

retry

Flag indicating that this call is a reattempt of an earlier call. An attempt is made to create object only at master sites where it does not exist with a status of valid.

copy_rows

Populate tables and other master sites with data from master definition site.

gname

Name of the replication group to which oname should be added.

15.3.3.1.1 Exceptions

The CREATE_MASTER_REPOBJECT procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Not all master sites are reachable

ddlfailure

-23309

Object oname already exists in replication group gname, and retry is not set to TRUE

duplicateobject

-23374

Replication group gname already exists

missingobject

-23308

Object oname does not exist

nonmasterdef

-23373

Calling site is not the master definition site for replication group gname

notquiesced

-23310

Replication group gname is not quiesced

typefailure

-23319

The type is not supported

15.3.3.1.2 Restrictions

Note the following restrictions on calling CREATE_MASTER_REPOBJECT:

  • This procedure must be called from the master definition site.

  • The replication group must already exist and be quiesced.

15.3.3.1.3 Example

This section contains a series of examples showing how to create replication objects.

15.3.3.1.4 Adding an existing table to a replication group

This call adds table SPROCKET. PRODUCTS to the replication group SPROCKET:

BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT(sname   =>    'SPROCKET', 
                                        oname   =>    'PRODUCTS', 
                                        type    =>    'TABLE', 
                                        gname   =>    'SPROCKET');
END;

Since we have not specified ddl_text in this example, the table must already exist.

15.3.3.1.5 Creating an object at the master definition site

In this next example, we use CREATE_MASTER_REPOBJECT to create an object at the master definition site and add it to the replication group:

BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
		sname 		=> 	'SPROCKET', 
		oname 		=>	'STATES', 
		type		=>	'TABLE'
		ddl_text	=>	'CREATE TABLE sprocket.states(state_id VARCHAR2(2),
   						state_name VARCHAR2(20))',
		gname 		=>	'SPROCKET');
END;

Notice that the CREATE TABLE statement in this example specifies the owner of the table. Typically, the replication administrator account uses DBMS_REPCAT, not the owner of the replicated schema. When this is the case, you must be sure to specify the schema in which to create objects. One of the privileges granted through DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP is CREATE ANY TABLE.

In all likelihood, you will not create objects with the CREATE_MASTER_REPOBJECT procedure very often, because doing so is rather clumsy for all but the most simple objects. But it's there if you want it.

Setting the retry and use_existing_object parameters to TRUE in this third example creates the table PRODUCTS at all master sites where it does not already exist; setting copy_rows to TRUE copies the data from the master definition site to the master sites.

BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
			sname 			=> 	'SPROCKET', 
			oname 			=>	'PRODUCTS', 
			type			=>	'TABLE', 
			use_existing_object 	=>	TRUE,
			retry			=>	TRUE,
			copy_rows		=>	TRUE,
			gname 			=>	'SPROCKET');
END;

If tables exist at master sites, but do not have the same definition as at the master definition site, Oracle returns an error.

NOTE: If you are incorporating an existing database into a replication group, you should consider precreating all of the objects at the new site manually, especially if the objects have interdependencies. At my sites, we always run a "catalog" script to create all schema objects, including triggers, primary and foreign key definitions, check constraints, etc. We then let Oracle generate the replication support objects. This methodology gives us complete control over how the schema is created, and we can easily reproduce the objects in other environments.

15.3.3.1.6 Replicating a package

In this final example, we replicate a package. To replicate a package, you must make two calls to CREATE_MASTER_REPOBJECT, one for the package, and one for the package body.

	BEGIN
		DBMS_REPCAT.CREATE_MASTER_REPOBJECT
			sname						=> 'SPROCKET',
			oname						=> 'PRODUCTMAINT',
			type						=> 'PACKAGE',
			use_existing_object				=> TRUE,
			comment						=> 'Added on '||sysdate,
			retry						=> FALSE,
			gname						=> 'SPROCKET');
 
		DBMS_REPCAT.CREATE_MASTER_REPOBJECT
			sname						=> 'SPROCKET',
			oname						=> 'PRODUCTMAINT',
			type						=> 'PACKAGE BODY', 
			use_existing_object				=> TRUE,
			comment						=> 'Added on '||sysdate,
			retry						=> FALSE,
			gname						=> 'SPROCKET');
	END;

For an additional example, see the repobjs.sql file on the companion disk. The example queries the DBA_REPOBJECT data dictionary view and lists all replicated objects in the database.

15.3.3.2 The DBMS_REPCAT.SET_COLUMNS procedure

When you replicate a table, Oracle must be able to uniquely identify each record in the table so that it can propagate changes to the correct row or rows. By default, the advanced replication facility uses the primary key to identify rows. However, if your table does not have a primary key, or if you wish to use a different criteria to uniquely identify records, you can use SET_COLUMNS to designate a pseudo-primary key.

Here's the specification for the package:

PROCEDURE DBMS_REPCAT.SET_COLUMNS  
  (sname IN VARCHAR2,
   oname IN VARCHAR2,
   column_list IN VARCHAR2 | column_table IN
   dbms_utility.name_array);

Parameters are summarized in the following table.

Name

Description

sname

Name of the schema that owns the replicated table.

oname

Name of the table with the column_group.

column_list

A comma-delimited list of column names to use as the pseudo-primary key. Use either column_list or column_table.

column_table

A PL/SQL table of column names. Use either column_list or column_table.

15.3.3.2.1 Exceptions

DBMS_REPCAT.SET_COLUMNS may raise the following exceptions:

Name

Number

Description

nonmasterdef

-23312

Invoking site is not master definition site

missingobject

-23308

Table oname does not exist

missingcolumn

-23334

Column(s) specified do not exist in table oname

15.3.3.2.2 Restrictions

Note the following restrictions on calling DBMS_REPCAT.SET_COLUMNS.

  • DBMS_REPCAT.SET_COLUMNS must be run from the master definition site.

  • The changes do not take effect until the next call to DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT.

15.3.3.2.3 Example

The following call designates columns COLOR, MODEL, and YEAR as the pseudo-primary key columns in table SPROCKET.PRODUCTS:


BEGIN
	DBMS_REPCAT.SET_COLUMNS(sname		=> 'SPROCKET',
				oname		=> 'PRODUCTS', 
				 column_list	=> 'COLOR,MODEL,YEAR');
END;

15.3.3.3 The DBMS_REPCAT.DROP_MASTER_REPOBJECT procedure

The DROP_MASTER_REPOBJECT procedure drops a replicated object at the master site. The specification follows:

PROCEDURE DBMS_REPOBJECT.DROP_MASTER_REPOBJECT
  (sname IN VARCHAR2,
   oname IN VARCHAR2,
   type IN VARCHAR2,
   drop_objects IN BOOLEAN := FALSE);	

Parameters are summarized in the following table.

Name

Description

sname

Name of the schema to which oname belongs.

oname

Name of the object to be added.

type

Object type. Valid types: TABLE, INDEX, SYNONYM, TRIGGER, VIEW, PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY.

drop_objects

If TRUE, drop the object at all master sites; default is FALSE.

15.3.3.3.1 Exceptions

The DROP_MASTER_REPOBJECT procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Not all master sites are reachable

missingobject

-23308

Object oname does not exist

nonmasterdef

-23373

Calling site is not the master definition site for replication group gname

typefailure

-23319

The type is not supported

15.3.3.3.2 Restrictions

Note the following restrictions on calling DROP_MASTER_REPOBJECT:

  • This procedure must be called from the master definition site.

  • The replication group must already exist and be quiesced.

15.3.3.3.3 Examples

The following call removes table SPROCKET.PRODUCTS from the SPROCKET replication group, but preserves the table:

	BEGIN
		DBMS_REPCAT.DROP_MASTER_REPOBJECT(
					sname 		=> 'SPROCKET',
					oname		=> 'PRODUCTS',
					type		=> 'TABLE');
	END;

Dropping a table from a replication group automatically drops all replication triggers associated with the table and removes it from the replication data dictionary views.

The DROP_MASTER_REPOBJECT procedure can remove the object from the replication group, and also drop the object from the schema by setting the drop_objects parameter to TRUE, as shown in this example:

BEGIN
	DBMS_REPCAT.DROP_MASTER_REPOBJECT(
				sname 			=> 'SPROCKET',
				oname			=> 'PRODUCTS',
				type			=> 'TABLE',
				drop_objects		=>  TRUE);
END;

15.3.3.4 The DBMS_REPCAT.EXECUTE_DDL procedure

DBMS_REPCAT.CREATE_MASTER_REPOBJECT and DBMS_REPCAT.DROP_MASTER_REPOBJECT do not support every type of object. For example, you cannot use these procedures to drop and create constraints. Enter DBMS_REPCAT's EXECUTE_DDL procedure.

The EXECUTE_DDL procedure allows you to perform DDL at multiple sites. The specification follows:

PROCEDURE DBMS_REPCAT.EXECUTE_DDL
   (gname        IN VARCHAR2 := '',
    {master_list IN VARCHAR2 := NULL, | 
    master_table IN dbms_utility.dblink_array,}
    ddl_text     IN VARCHAR2,
    sname        IN VARCHAR2 := '');

Parameters are summarized in the following table.

Name

Description

gname

Name of the replicated object group.

master_list

Comma-separated string of master site global names at which DDL is to be performed. If NULL (the default), DDL is applied at all master sites in the replication group. Use either parameter master_list or master_table.

master_table

PL/SQL table of master site global names at which DDL is to be performed. Use either parameter master_list or master_table.

ddl_text

DDL statement to apply.

sname

Not used.

15.3.3.4.1 Exceptions

The EXECUTE_DDL procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with master site

ddlfailure

-23318

Unable to perform DDL

nonmaster

-23312

At least one site in master_list or master_table is not a master site

nonmasterdef

-23312

Calling site is not a master definition site

15.3.3.4.2 Restrictions

Note the following restrictions on calling EXECUTE_DDL:

  • This procedure must be called from the master definition site.

  • The replication group must already exist.

NOTE: The environment does not have to be quiesced.

15.3.3.4.3 Example

This example creates an index on the SPROCKET.STATES table at sites D7CA.BIGWHEEL.COM and D7NY.BIGWHEEL.COM. Note that as in the example of CREATE_MASTER_REPOBJECT, we must specify the schema in which to create the index.

DECLARE vMasters VARCHAR2(30);
BEGIN
	vMasters := 'D7CA.BIGWHEEL.COM,D7NY.BIGWHEEL.COM';
	DBMS_REPCAT.EXECUTE_DDL(
		gname			=> 'SPROCKET',
		master_list		=> vMasters,
		ddl_text		=>'CREATE INDEX sprocket.i_state_id ON
		   sprocket.tstates(state_id)',
		sname			=>'SPROCKET');
END;

15.3.3.5 The DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure

Just as you can propagate DDL to create objects with the EXECUTE_DDL procedure, you can also propagate DDL to alter objects with DBMS_REPCAT.ALTER_MASTER_REPOBJECT. Unlike EXECUTE_DDL, ALTER_MASTER_REPOBJECT does not allow you to specify a list of master sites; the call affects all masters. In other words, Oracle does not support site-specific customizations of replicated objects. The specification follows:

PROCEDURE DBMS_REPCAT.ALTER_MASTER_REPOBJECT
   (sname IN VARCHAR2,
    oname IN VARCHAR2,
    type IN VARCHAR2,
    ddl_text IN VARCHAR2,
    comment IN VARCHAR2 := '',
    retry IN BOOLEAN := FALSE);

Parameters are summarized in the following table.

Name

Description

sname

Name of the schema to which object oname belongs.

oname

Name of the object to alter.

type

The oname object type. Supported types: FUNCTION, INDEX, PACKAGE, PACKAGE BODY, SYNONYM, TABLE, TRIGGER, and VIEW.

ddl_text

Text of DDL statement to apply.

comment

Comment visible in DBA_REPOBJECT data dictionary view.

retry

If set to TRUE, procedure alters only objects whose status is not VALID at master sites.

15.3.3.5.1 Exceptions

The ALTER_MASTER_REPOBJECT procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with one or more master site(s)

ddlfailure

-23318

DDL at master definition site failed

missingobject

-23308

Object oname does not exist

nonmasterdef

-23312

Calling site is not the master definition site

notquiesced

-23310

Replication group gname is not quiesced

typefailure

-23319

DDL on objects of type type is not supported

15.3.3.5.2 Restrictions

Note the following restrictions on calling ALTER_MASTER_REPOBJECT:

  • This procedure must be run from the master definition site.

  • The replication group must be quiesced.

  • You must call DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for the altered object before resuming replication.

15.3.3.5.3 Example

If you set the retry parameter to TRUE, ALTER_MASTER_REPOBJECT applies the DDL only at sites where the object has a status of INVALID in the DBA_OBJECTS data dictionary view.

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT(
		sname		=> 'SPROCKET',
		oname		=> 'PRODUCTMAINT',
		type		=> 'PACKAGE BODY'
		ddl_text	=> 'ALTER PACKAGE SPROCKET.PRODUCTMAINT COMPILE BODY',
		comment		=> 'Recompiled on '||sysdate|| ' by '||user,
		retry		=> TRUE );
END;

Notice that we specify the schema for the object that we are altering. As with DBMS_REPCAT.EXECUTE_DDL, the ALTER_MASTER_REPOBJECT procedure operates on objects in the caller's schema by default, and the caller is generally the replication administrator account, not the schema account.

This example alters the width of the state_id column in table SPROCKET.STATES at all sites:

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT(
		sname		=> 'SPROCKET',
		oname		=> 'PRODUCTMAINT',
		type		=> 'PACKAGE BODY'
		ddl_text	=> 'ALTER TABLE SPROCKET.STATES MODIFY
			 (STATE_ID NUMBER(10))' ,
		comment		=> 'state_id widened on '||sysdate|| ' by '||user);
END;

15.3.3.6 The DBMS_REPCAT.COMMENT_ON_REPOBJECT procedure

As you have seen in the previous examples, you can associate comments with a replicated object when you create or alter it by passing a VARCHAR2 string to the comment parameter. You can see these comments in the object_comment field of DBA_REPOBJECTS.

You can also create comments without creating or altering the object with DBMS_REPCAT's COMMENT_ON_REPOBJECT procedure. The specification follows:

PROCEDURE DBMS_REPCAT.COMMENT_ON_REPOBJECT
   (sname IN VARCHAR2,
    oname IN VARCHAR2,
    type IN VARCHAR2,
    comment IN VARCHAR2);

Parameters are summarized in the following table.

Name

Description

sname

Name of schema to which object belongs

oname

Name of the object

type

Object type

comment

Comment

15.3.3.6.1 Exceptions

The COMMENT_ON_REPOBJECT procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with one or more master sites

missingobject

-23308

Object oname does not exist

nonmasterdef

-23312

Calling site is not master definition site

typefailure

-23319

Object type is not supported

15.3.3.6.2 Restrictions

The COMMENT_ON_REPOBJECT procedure must be called from the master definition site.

15.3.3.6.3 Example

The following call updates the comment for replicated table SPROCKET.PRICES:

BEGIN
    DBMS_REPCAT.COMMENT_ON_REPOBJECT(
			sname		=>	'SPROCKET', -
			oname 		=>	'PRICES', -
			type		=>	'TABLE', -
			comment		=>	'Headquarters updates this table once a month.');
END;

15.3.4 Replication Support with DBMS_REPCAT

The next step in the creation of a replicated environment is to generate replication support for your replicated tables, packages, and package bodies. In the case of replicated tables, this step creates a BEFORE ROW trigger, called tablename$RT, and three packages:

tablename$RP
tablename$RR
tablename$TP

This code propagates DML to remote sites, and applies DML on behalf of remote sites. We'll examine this code in the examples of these procedures:

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE
DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER

15.3.4.1 The DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure

The GENERATE_REPLICATION_SUPPORT procedure generates support for replicated tables, packages, and package bodies. The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
   (sname            IN VARCHAR2,
    oname            IN VARCHAR2,
    type             IN VARCHAR2,
    package_prefix   IN VARCHAR2 := NULL,
    procedure_prefix IN VARCHAR2 := NULL,
    distributed      IN BOOLEAN  := TRUE,
    gen_objs_owner   IN VARCHAR2 := NULL,
    gen_rep2_trigger IN BOOLEAN := FALSE);

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
   (sname             IN VARCHAR2,
    oname             IN VARCHAR2,
    type              IN VARCHAR2,
    package_prefix    IN VARCHAR2 := NULL,
    procedure_prefix  IN VARCHAR2 := NULL,
    distributed       IN BOOLEAN  := TRUE,
    gen_objs_owner    IN VARCHAR2 := NULL,
    min_communication IN BOOLEAN := TRUE);

Parameters are summarized in the following table.

Name

Description

sname

Name of the schema to which table oname belongs.

oname

Name of table for which package is being generated.

type

Object type. Supported types: TABLE, PROCEDURE, PACKAGE, and PACKAGE BODY.

package_prefix

Prefix used to name generated wrapper package for packages and package bodies.

procedure_prefix

Prefix used to name generated wrapper package for procedures.

distributed

If TRUE (the default), generate replication support for the object at each master; if FALSE, copy the reapplication support objects generated at the master definition site.

gen_objs_owner

Specifies schema in which to generate replication support objects; if NULL (the default), objects are generated under schema sname.

gen_rep2_trigger

(Oracle7 only)

Provided for backward compatibility; if any masters are pre-7.3 releases, this must be set to TRUE. The default is FALSE.

min_communication

(Oracle8 only)

If TRUE (the default), Oracle propagates changes with the minimum communication parameter, which avoids sending the old and new column values of unmodified fields.

15.3.4.1.1 Exceptions

The GENERATE_REPLICATION_SUPPORT procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with all masters

dbnotcompatible

-23375

One or more masters is a pre-7.3 release

missingobject

-23308

Table oname does not exist in schema sname

missingschema

-23306

Schema sname does not exist

nonmasterdef

-23312

Calling site is not a master definition site

notquiesced

-23310

Replication group to which object belongs is not quiesced

typefailure

-23319

Specified type is not a supported type

15.3.4.1.2 Restrictions

Note the following restrictions on calling GENERATE_REPLICATION_SUPPORT:

  • You must call this procedure from the master definition site for each object in the replication group.

  • The replication group must be quiesced.

  • If the object is not owned by the replication administrator account, the owner must have explicit EXECUTE privileges on the DBMS_DEFER package (described in Chapter 17).

  • If the INIT.ORA parameter COMPATIBLE is 7.3 or higher, the distributed parameter must be set to TRUE.

  • If the INIT.ORA parameter COMPATIBLE is less than 7.3 in any snapshot sites, the gen_rep2_trigger parameter must be set to TRUE, and the COMPATIBLE parameter at the master definition site must be set to 7.3.0.0 or greater.

15.3.4.1.3 Example

Suppose that we have a table SPROCKET.REON defined as follows:

Field Name

Nullable?

Datatype

region_id

NOT NULL

NUMBER(6)

region_name

NOT NULL

VARCHAR2(15)

Assuming that we have already added this table to the SPROCKET replication group, here is how we would generate replication support for it:

BEGIN
EXECUTE dbms_repcat.generate_replication_support( -
		sname => 'SPROCKET',	-
		oname => 'REGION',	-
		type => 'TABLE', 	-
		distributed => TRUE,	-
		gen_objs_owner=> 'SPROCKET',	-
		gen_rep2_trigger=> FALSE);
END;

This call creates a trigger and three packages, as described in the following table. Oracle immediately creates these objects at the master definition site, as well as the participating master sites.

Object Name

Object Type

Description

REGION$RT

BEFORE ROW Trigger

Invokes procedure REGION$TP.REPLICATE.

REGION$RR

Package + Body

Invokes conflict resolution handler. REGIONS$RP invokes this procedure only in the event of a conflict.

REGION$RP

Package + Body

Applies DML that originated at a remote site.

REGION$TP

Package + Body

Determines whether DML originates locally, and if so, queues as an RPC call the REGION$RP procedure corresponding to the type of DML (insert, update, or delete). Oracle propagates this RPC call to all master sites.

In the next sections, we examine what Oracle creates for us when we generate replication support for this table.

15.3.4.1.4 The replication support trigger

The following example shows the text of the REGION$RT trigger that the GENERATE_REPLICATION_SUPPORT call generates:

declare
  flag char;
begin
  if "REGION$TP".active then
    if inserting then
      flag := 'I';
    elsif updating then
      flag := 'U';
    elsif deleting then
      flag := 'D';
    end if;
    "REGION$TP".replicate(
      :old."REGION_ID",:new."REGION_ID",
      :old."REGION_NAME",:new."REGION_NAME",
      flag);
  end if;
end;

As you can see, this BEFORE ROW trigger simply sets a flag to indicate the type of DML being performed: "I" for inserts, "U" for updates, and "D" for deletes. It then passes this flag, along with the new and old values of each field, to the REGIONS$TP.REPLICATE procedure.

15.3.4.1.5 The replication support packages

The replication package REGION$TP invokes DBMS_SNAPSHOT.I_AM_A_REFRESH (see the description of this procedure in Chapter 14) to determine if the DML that fired the REGION$RT trigger originated locally, or if it is DML that another site has propagated. If the DML originated locally, then the REGION$TP builds a deferred call -- one of the REGION$RP procedures (REP_UPDATE, REP_INSERT, or REP_DELETE), as appropriate. Oracle queues this deferred call to all master sites. Here is the Oracle-generated code:

package body		"REGION$TP" as
  I_am_a_snapshot CHAR;
  is_snapshot BOOLEAN;
  function active return boolean
  is
  begin
    return (not((is_snapshot and dbms_snapshot.I_am_a_refresh) or
		not dbms_reputil.replication_is_on));
  end active;
  procedure replicate(
    "REGION_ID1_o" IN NUMBER,
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    "REGION_NAME2_n" IN VARCHAR2,
    flag IN CHAR)
  is
  begin
    if flag = 'U' then
      dbms_defer.call('SPROCKET','REGION$RP','REP_UPDATE',6,'SPROCKET');
      dbms_defer.number_arg("REGION_ID1_o");
      dbms_defer.number_arg("REGION_ID1_n");
      dbms_defer.varchar2_arg("REGION_NAME2_o");
      dbms_defer.varchar2_arg("REGION_NAME2_n");
    elsif flag = 'I' then
      dbms_defer.call('SPROCKET','REGION$RP','REP_INSERT',4,'SPROCKET');
      dbms_defer.number_arg("REGION_ID1_n");
      dbms_defer.varchar2_arg("REGION_NAME2_n");
    elsif flag = 'D' then
      dbms_defer.call('SPROCKET','REGION$RP','REP_DELETE',4,'SPROCKET');
      dbms_defer.number_arg("REGION_ID1_o");
      dbms_defer.varchar2_arg("REGION_NAME2_o");
    end if;
    dbms_defer.varchar2_arg(dbms_reputil.global_name);
    dbms_defer.char_arg(I_am_a_snapshot);
  end replicate;
begin
  select decode(master, 'N', 'Y', 'N')
    into I_am_a_snapshot
    from all_repcat where gname = 'SPROCKET';
  is_snapshot := (I_am_a_snapshot = 'Y');
end "REGION$TP";

Notice that Oracle passes the old and new values of each column in the table to the REGION$RP procedure. Oracle uses these values to confirm that the version of the row at the originating site is the same as the version of the row at the destination sites. If the old column values at the originating site do not match the current column values at the destination site, then Oracle detects a conflict and invokes the appropriate conflict resolution method.

You can see this logic in the package body of REGION$RP:

package body		"REGION$RP" as
  procedure rep_delete(
    "REGION_ID1_o" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) is
  begin
    if propagation_flag = 'N' then
      dbms_reputil.replication_off;
    end if;
    dbms_reputil.rep_begin;
    dbms_reputil.global_name := site_name;
    delete from "REGION"
    where ("REGION_ID1_o" = "REGION_ID"
    and   "REGION_NAME2_o" = "REGION_NAME");
    if sql%rowcount = 0 then
      raise no_data_found;
    elsif sql%rowcount > 1 then
      raise too_many_rows;
    end if;
    dbms_reputil.rep_end;
  exception
    when no_data_found then
      begin
	if not "REGION$RR".delete_conflict_handler(
	  "REGION_ID1_o",
	  "REGION_NAME2_o",
	  site_name,
	  propagation_flag) then
	  dbms_reputil.rep_end;
	  raise;
	end if;
	dbms_reputil.rep_end;
      exception
	when others then
	  dbms_reputil.rep_end;
	  raise;
      end;
    when others then
      dbms_reputil.rep_end;
      raise;
  end rep_delete;
  procedure rep_insert(
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) is
  begin
    if propagation_flag = 'N' then
      dbms_reputil.replication_off;
    end if;
    dbms_reputil.rep_begin;
    dbms_reputil.global_name := site_name;
    insert into "REGION" (
      "REGION_ID",
      "REGION_NAME")
    values (
      "REGION_ID1_n",
      "REGION_NAME2_n");
    dbms_reputil.rep_end;
  exception
    when dup_val_on_index then
      begin
	if not "REGION$RR".unique_conflict_insert_handler(
	  "REGION_ID1_n",
	  "REGION_NAME2_n",
	  site_name,
	  propagation_flag,
	  SQLERRM) then
	  dbms_reputil.rep_end;
	  raise;
	end if;
	dbms_reputil.rep_end;
      exception
	when others then
	  dbms_reputil.rep_end;
	  raise;
      end;
    when others then
      dbms_reputil.rep_end;
      raise;
  end rep_insert;
  procedure rep_update(
    "REGION_ID1_o" IN NUMBER,
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) is
  begin
    if propagation_flag = 'N' then
      dbms_reputil.replication_off;
    end if;
    dbms_reputil.rep_begin;
    dbms_reputil.global_name := site_name;
    update "REGION" set
      "REGION_ID" = "REGION_ID1_n",
      "REGION_NAME" =
	decode("REGION_NAME2_o",
	       "REGION_NAME2_n", "REGION_NAME",
	       "REGION_NAME2_n")
    where (((1 = 1 and
	    "REGION_NAME2_o" = "REGION_NAME2_n")) or
	   (1 = 1 and
	    "REGION_NAME2_o" = "REGION_NAME"))
      and "REGION_ID1_o" = "REGION_ID";
    if sql%rowcount = 0 then
      raise no_data_found;
    elsif sql%rowcount > 1 then
      raise too_many_rows;
    end if;
    dbms_reputil.rep_end;
  exception
    when no_data_found then
      begin
	if not "REGION$RR".update_conflict_handler(
	  "REGION_ID1_o",
	  "REGION_ID1_n",
	  "REGION_NAME2_o",
	  "REGION_NAME2_n",
	  site_name,
	  propagation_flag) then
	  dbms_reputil.rep_end;
	  raise;
	end if;
	dbms_reputil.rep_end;
      exception
	when others then
	  dbms_reputil.rep_end;
	  raise;
      end;
    when dup_val_on_index then
      begin
	if not "REGION$RR".unique_conflict_update_handler(
	  "REGION_ID1_o",
	  "REGION_ID1_n",
	  "REGION_NAME2_o",
	  "REGION_NAME2_n",
	  site_name,
	  propagation_flag,
	  SQLERRM) then
	  dbms_reputil.rep_end;
	  raise;
	end if;
	dbms_reputil.rep_end;
      exception
	when others then
	  dbms_reputil.rep_end;
	  raise;
      end;
    when others then
      dbms_reputil.rep_end;
      raise;
  end rep_update;
end "REGION$RP";

As you can see, Oracle invokes REGION$RR, the conflict resolution package:

package body		"REGION$RR" as
  function unique_conflict_insert_handler(
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR,
    errmsg IN VARCHAR2) return boolean is
  begin
    return FALSE;
  end unique_conflict_insert_handler;

  function delete_conflict_handler(
    "REGION_ID1_o" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) return boolean is
  begin
    return FALSE;
  end delete_conflict_handler;

  function update_conflict_handler(
    "REGION_ID1_o" IN NUMBER,
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) return boolean is
  begin
    return FALSE;
  end update_conflict_handler;

  function unique_conflict_update_handler(
    "REGION_ID1_o" IN NUMBER,
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR,
    errmsg IN VARCHAR2) return boolean is
  begin
    return FALSE;
  end unique_conflict_update_handler;
end "REGION$RR";

This is the default conflict handling package that GENERATE_REPLICATION_SUPPORT creates. Since no conflict resolution methods are defined for REGION, the unique_conflict_insert_handler, delete_conflict_handler, update_conflict_handler, and unique_conflict_update_handler programs all return FALSE, indicating that they cannot resolve the conflict. Chapter 17 contains details about how to define conflict resolution handlers.

15.3.4.1.6 Generating replication support for packages and procedures

As well as tables, you can also replicate procedures and packages. When you call a replicated procedure, Oracle builds a deferred RPC that it propagates to all master sites. This deferred RPC invokes the same procedure with the same arguments as the originating call. Oracle recommends procedural replication for situations that call for massive updates to tables (i.e., updates affecting tens of thousands of rows). Procedural replication duplicates the procedure call only, which is more efficient and network-friendly than row-level replication. (Row-level replication sends the old and new column values for every field of every row.)

Just as we made two calls to CREATE_MASTER_REPOBJECT to create a replicated package, we must also make two calls to GENERATE_REPLICATION_SUPPORT:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT( 
			sname		=> 'SPROCKET',
			oname		=> 'PRODUCTMAINT',
			type		=> 'PACKAGE',
			distributed	=> TRUE,
			gen_objs_owner	=> 'SPROCKET',
			gen_rep2_trigger=> FALSE);   
 
		DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
			sname		=> 'SPROCKET',
			oname		=> 'PRODUCTMAINT',
			type		=> 'PACKAGE BODY',
			distributed	=> TRUE,
			gen_objs_owner	=> 'SPROCKET',
			gen_rep2_trigger=> FALSE);
	END;

These two calls create a "wrapper" package and package body, named DEFER_PRODUCTMAINT. This package uses DBMS_DEFER.CALL (described in Chapter 17) to build RPCs to PRODUCTMAINT. To replicate a call to procedure ADDPRODUCT, we would call DEFER_PRODUCTMAINT.ADDPRODUCT.

package "DEFER_PRODUCTMAINT" as
  I_am_a_snapshot CHAR;
  procedure "ADDPRODUCT"(
    "PRODUCT_TYPE_IN" IN number,
    "CATALOG_ID_IN" IN varchar2,
    "DESCRIPTION_IN" IN varchar2,
    "REV_LEVEL_IN" IN varchar2,
    "PRODUCTION_DATE_IN" IN date,
    "PRODUCT_STATUS_IN" IN varchar2,
    call_local IN char := 'N',
    call_remote IN char := 'Y');
end "DEFER_PRODUCTMAINT";

package body "DEFER_PRODUCTMAINT" as
  procedure "ADDPRODUCT"(
    "PRODUCT_TYPE_IN" IN NUMBER,
    "CATALOG_ID_IN" IN VARCHAR2,
    "DESCRIPTION_IN" IN VARCHAR2,
    "REV_LEVEL_IN" IN VARCHAR2,
    "PRODUCTION_DATE_IN" IN DATE,
    "PRODUCT_STATUS_IN" IN VARCHAR2,
    call_local IN char := 'N',
    call_remote IN char := 'Y') is
  begin
    select decode(master, 'N', 'Y', 'N')
      into I_am_a_snapshot
      from all_repcat where gname = 'SPROCKET';
    if call_local = 'Y' then
      "SPROCKET"."PRODUCTMAINT"."ADDPRODUCT"(
	"PRODUCT_TYPE_IN",
	"CATALOG_ID_IN",
	"DESCRIPTION_IN",
	"REV_LEVEL_IN",
	"PRODUCTION_DATE_IN",
	"PRODUCT_STATUS_IN");
    end if;
    if call_remote = 'Y' then
	8, 'SPROCKET');
      dbms_defer.number_arg("PRODUCT_TYPE_IN");
      dbms_defer.varchar2_arg("CATALOG_ID_IN");
      dbms_defer.varchar2_arg("DESCRIPTION_IN");
      dbms_defer.varchar2_arg("REV_LEVEL_IN");
      dbms_defer.date_arg("PRODUCTION_DATE_IN");
      dbms_defer.varchar2_arg("PRODUCT_STATUS_IN");
      dbms_defer.char_arg('Y');
      dbms_defer.char_arg(I_am_a_snapshot);
    end if;
  end "ADDPRODUCT";
begin
  select decode(master, 'N', 'Y', 'N')
    into I_am_a_snapshot
    from all_repcat where gname = 'SPROCKET';
end "DEFER_PRODUCTMAINT";

15.3.4.2 The DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE procedure

In some situations, you may wish to generate only replication support triggers or replication support packages. For example, if you use DBMS_REPCAT's ALTER_MASTER_PROPAGATION procedure to change from synchronous to asynchronous replication, you will have to recreate replication triggers. The GENERATE_REPLICATION_PACKAGE and GENERATE_REPLICATION_TRIGGERS procedures provide this functionality.

The GENERATE_REPLICATION_PACKAGE procedure allows you to generate replication support packages. The specification follows:

PROCEDURE DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE
   (sname IN VARCHAR2,
    oname IN VARCHAR2);

Parameters are summarized in the following table.

Name

Description

sname

Name of the schema to which table oname belongs

oname

Name of table for which package is being generated

15.3.4.2.1 Exceptions

The GENERATE_REPLICATON_PACKAGE procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with all masters

dbnotcompatible

-23375

One or more masters is a pre-7.3 release

missingobject

-23308

Table oname does not exist in schema sname

nonmasterdef

-23312

Calling site is not a master definition site

notquiesced

-23310

Replication group to which object belongs is not quiesced

15.3.4.2.2 Restrictions

Note the following restrictions on calling GENERATE_REPLICATION_PACKAGE:

  • You must call this procedure from the master definition site.

  • The replication group must be quiesced.

  • The Oracle version must be 7.3 or later.

15.3.4.2.3 Example

The following call generates the replication support packages for table SPROCKET.PRODUCTS in all master sites:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE(
			sname	=> 'SPROCKET', 
			oname 	=> 'PRODUCTS');
	END;

15.3.4.3 The DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER procedure

The GENERATE_REPLICATION_TRIGGER procedure allows you to generate replication support triggers. The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER
   (sname IN VARCHAR2,
    oname IN VARCHAR2,
    gen_objs_owner IN VARCHAR2 := NULL,
    gen_rep2_trigger IN BOOLEAN := FALSE);

PROCEDURE DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER
   (gname IN VARCHAR2,
   {master_list IN VARCHAR2 := NULL 
   | master_table IN dbms_utility.dblink_array},
    gen_objs_owner IN VARCHAR2 := NULL);

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER
   (sname IN VARCHAR2,
    oname IN VARCHAR2,
    gen_objs_owner IN VARCHAR2 := NULL,
    min_communication IN BOOLEAN  := TRUE);

PROCEDURE DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER
   (gname IN VARCHAR2,
    gen_objs_owner IN VARCHAR2 := NULL,
    min_communication IN BOOLEAN := NULL);

Parameters are summarized in the following table.

Name

Description

sname

Name of the schema to which table oname belongs.

oname

Name of object for which support objects are being generated.

gen_rep2_trigger

(Oracle7 only)

Provided for backward compatibility; if any master sites are pre-7.3 releases, this parameter must be set to TRUE (default is FALSE).

gname

The replication group to which oname belongs.

master_list

Comma-delimited string of global names for masters in which support objects are to be generated.

master_table

PL/SQL table of global names for masters in which support objects are to be generated.

gen_objs_owner

Specifies schema in which to generate replication support objects; if NULL (the default), objects are generated under schema in which they currently reside.

min_communication

(Oracle8 only)

If TRUE (the default) the generated trigger sends the new value of a column only if the value has changed. Old field values are sent only if the field is part of the primary key, or part of a column group for which member columns have changed.

15.3.4.3.1 Exceptions

The GENERATE_REPLICATION_TRIGGER procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with all masters

dbnotcompatible

-23375

One or more masters is a pre-7.3 release and gen_rep2_trigger is not set to TRUE

missingobject

-23308

Table oname does not exist in schema sname

missingschema

-23306

Schema sname does not exist

nonmasterdef

-23312

Calling site is not a master definition site

notquiesced

-23310

Replication group to which object belongs is not quiesced

15.3.4.3.2 Restrictions

Note the following restrictions on calling GENERATE_REPLICATION_TRIGGER:

  • You must call this procedure from the master definition site.

  • The replication group must be quiesced.

  • The GENERATE_REPLICATION_SUPPORT or GENERATE_PACKAGE_SUPPORT must previously have been called for the object specified in the oname parameter.

15.3.4.3.3 Examples

The simplest invocation of the GENERATE_REPLICATION_TRIGGER procedure does the most work; this call generates replication triggers for all replicated tables at all master sites:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(gname=> 'SPROCKET' );
	END;

This next example generates replication triggers for the replicated table SPROCKET.PRODUCTS at all master sites:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(
		    gname    => 'SPROCKET',
		    oname    => 'PRODUCTS' );
	END;

The following call generates replication triggers for all replicated tables in the SPROCKET replication group at the master sites D7HI.BIGWHEEL.COM and D7WA.BIGWHEEL.COM:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(
		    gname    => 'SPROCKET',
		    master_list=> 'D7HI.BIGWHEEL.COM, D7WA.BIGWHEEL.COM' );
	END;

The following call regenerates the replication support for all objects in replication group SPROCKET at all master sites:

EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(gname=> 'SPROCKET' )

For an additional example, see the invalids.sql file on the companion disk. The example lists all objects in the database with a status of INVALID and generates the appropriate SQL statements to attempt to validate them.

15.3.5 Adding and Removing Master Sites with DBMS_REPCAT

Now you have generated replication support for those objects you intend to replicate, and you are ready to add master sites to your environment. In advanced replication parlance, a master site is a database instance where replicated objects and their replication support triggers and packages exist. Master sites are sometimes called peers, because every master site has the same objects and identical (or nearly identical) data. Any master site can perform DML on a replicated table, and Oracle propagates the DML to all other master sites. There is no single authoritative site, not even the master definition site. The distinction between the master definition site and the other masters is that the master definition site is the only site that can perform DDL on replicated objects, and the only one that can suspend or resume replication activity.

Here are the DBMS_REPCAT programs associated with creating and maintaining master sites in your replicated environment:

DBMS_REPCAT.ADD_MASTER_DATABASE
DBMS_REPCAT.REMOVE_MASTER_DATABASES
DBMS_REPCAT.COMMENT_ON_REPSITES
DBMS_REPCAT.RELOCATE_MASTERDEF

We describe these programs in the following sections.

15.3.5.1 The DBMS_REPCAT.ADD_MASTER_DATABASE procedure

The ADD_MASTER_DATABASE procedure adds a master site. The specifications differ for Oracle7 and Oracle8 as follows. (Note that the sname parameter no longer exists in Oracle8.)

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.ADD_MASTER_DATABASE
   (gname     IN VARCHAR2 := '',
    master    IN VARCHAR2,
    use_existing_objects IN BOOLEAN := TRUE,
    copy_rows IN BOOLEAN := TRUE,
    comment   IN VARCHAR2 := '',
    propagation_mode IN VARCHAR2 := 'ASYNCHRONOUS',
    sname N VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.ADD_MASTER_DATABASE
   (gname     IN VARCHAR2 := '',
    master    IN VARCHAR2,
    use_existing_objects IN BOOLEAN := TRUE,
    copy_rows IN BOOLEAN := TRUE,
    comment   IN VARCHAR2 := '',
    propagation_mode IN VARCHAR2 := 'ASYNCHRONOUS');

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group to which master site is being added

master

Global name of the new master site

use_existing_objects

Reuse existing objects at the new site

copy_rows

Copy rows from the invoking site to the new master site

comment

Comment on new master site, visible in DBA_REPSITES data dictionary view

propagation_mode

Propagation mode (SYNCHRONOUS or ASYNCHRONOUS)

sname (Oracle7 only)

Schema name (not used)

15.3.5.1.1 Exceptions

The ADD_MASTER_DATABASE procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Site master is not reachable

duplicateschema

-23307

Replication group gname already exists at site master

invalidpropmode

-23380

Propagation_mode is not SYNCHRONOUS or ASYNCHRONOUS

missingrepgroup

-23373

Replication group gname does not exist at the calling site

nonmasterdef

-23312

Calling site is not the master definition site

notquiesced

-23310

Replication group gname is not quiesced

repnotcompatible

-23376

Replication group gname does not exist at master, and master is a pre-7.3 release

15.3.5.1.2 Restrictions

Note the following restrictions on calling ADD_MASTER_DATABASE:

  • This procedure must be run from the master definition site.

  • The replication group must be quiesced.

15.3.5.1.3 Example

The ADD_MASTER_DATABASE procedure is relatively simple to use. For example, this call adds site D7NY.BIGWHEEL.COM to the SPROCKET replication group and instantiates all objects there:

	BEGIN
		DBMS_REPCAT.ADD_MASTER_DATABASE(
			gname		=> 'SPROCKET', 
			master		=> 'D7NY.BIGWHEEL.COM', 
			use_existing_objects=> 'FALSE', 
			copy_rows	=> 'TRUE', 
			propagation_mode=> 'ASYNCHRONOUS');
	END;

For additional examples, see the repsites.sql and links.sql files on the companion disk. The repsites.sql example queries all replication sites, sorted by replication group, and queries the DBA_REPSITES data dictionary view. The links.sql example lists all database links in the database (but not the passwords for those that were created using a CONNECT clause).

NOTE: It is generally easier to instantiate all objects at the new master site first. That way, the call to ADD_MASTER_DATABASE does not have to perform DDL to create the schema or send all of the data across a network link. If you instantiate the objects first, the call to ADD_MASTER_DATABASE has to generate replication support only for the objects at the new site and update other master sites with the new master's existence.

15.3.5.2 The DBMS_REPCAT.REMOVE_MASTER_DATABASES procedure

The REMOVE_MASTER_DATABASES procedure complements the ADD_MASTER_DATABASE procedure by removing master sites. The master sites being removed do not need to be accessible, but all other masters do. As with the ADD_MASTER_DATABASE procedure, the Oracle7 and Oracle8 specifications differ; Oracle8 does not have the sname parameter.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.REMOVE_MASTER_DATABASES
   (gname IN VARCHAR2 := '',
    master_list IN VARCHAR2,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.REMOVE_MASTER_DATABASES
   (gname IN VARCHAR2 := '',
    master_list IN VARCHAR2);,

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group from which master site(s) will be removed

master_list

A comma-delimited list of global_names of master sites to be removed; use either master_list or master_table

sname (Oracle7 only)

Schema name (not used)

15.3.5.2.1 Exceptions

The REMOVE_MASTER_DATABASES procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

One or more remaining master sites is not reachable

nonmaster

-23313

One or more of the specified masters is not a master database

nonmasterdef

-23312

Calling site is not the master definition site

reconfigerror

-23316

One of the specified masters in the master definition site

15.3.5.2.2 Restrictions

The REMOVE_MASTER_DATABASES procedure must be run from the master definition site.

15.3.5.2.3 Example

To remove site D7NY.BIGWHEEL.COM from the SPROCKET replication group and inform all other master sites, specify the following:

	BEGIN
		DBMS_REPCAT.REMOVE_MASTER_DATABASES(
			name		=> 'SPROCKET', 
			master_list 		=> 'D7NY.BIGWHEEL.COM');
	END;

NOTE: After removing master sites with REMOVE_MASTER_DATABASES, you should call DBMS_REPCAT.DROP_MASTER_REPGROUP at each of the master sites you removed. Although you do not need to quiesce the replication group to remove one or more master database(s), you are strongly encouraged to do so. Otherwise, you will have to manually clear the RPC queue and resolve any inconsistencies.

15.3.5.3 The DBMS_REPCAT.COMMENT_ON_REPSITES procedure

The COMMENT_ON_REPSITES procedure allows you to add or change a comment associated with a master site specified in the DBA_REPSITES data dictionary view. Here's the specification:

PROCEDURE DBMS_REPCAT.COMMENT_ON_REPSITES
   (gname IN VARCHAR2,
    master IN VARCHAR,
    comment IN VARCHAR2);

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group to which master belongs

master

Global name of master site

comment

Comment

15.3.5.3.1 Exceptions

The COMMENT_ON_REPSITES procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with one or more master sites

nonmaster

-23313

The master is not a master site

nonmasterdef

-23312

Calling site is not master definition site

15.3.5.3.2 Restrictions

You must call the COMMENT_ON_REPSITES procedure from the master definition site.

15.3.5.3.3 Example

The following call updates the comment for master site D7NY. BIGWHEEL.COM:

	BEGIN
		DBMS_REPCAT.COMMENT_ON_REPSITES(
			gname		=> 'SPROCKET', 
			master		=> 'D7NY.BIGWHEEL.COM', 
			comment		=> 'Comment added on '||sysdate|| ' by ' ||user)
	END;

15.3.5.4 The DBMS_REPCAT.RELOCATE_MASTERDEF procedure

If your master definition site becomes unusable, or if you simply want another site to serve that role, you can configure a different master site as the master definition site with the RELOCATE_MASTERDEF procedure described in this section. Follow these guidelines:

  • If your relocation is planned (i.e., all sites are up and reachable), set the notify_masters and include_old_masterdef parameters to TRUE.

  • If the current master definition site is not available, set the notify_masters parameter to TRUE and set include_old_masterdef to FALSE.

  • If the master definition site as well as some master sites are unavailable, invoke the RELOCATE_MASTERDEF procedure from each functioning master site with both the notify_masters and the include_old_masterdef parameters set to FALSE.

The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.RELOCATE_MASTERDEF
   (gname IN VARCHAR2 := '',
    old_masterdef IN VARCHAR2,
    new_masterdef IN VARCHAR2,
    notify_masters IN BOOLEAN := TRUE,
    include_old_masterdef IN BOOLEAN := TRUE,
    sname IN VARCHAR2 := '')

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.RELOCATE_MASTERDEF
   (gname IN VARCHAR2,
    old_masterdef IN VARCHAR2,
    new_masterdef IN VARCHAR2,
    notify_masters IN BOOLEAN := TRUE,
    include_old_masterdef IN BOOLEAN := TRUE);

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group.

old_masterdef

Global name of the current master definition site.

new_masterdef

Global name of the new master definition site.

notify_masters

If TRUE (the default), synchronously multicast information about the change to all masters; if FALSE, do not inform masters.

include_old_masterdef

If TRUE (the default), notify current master definition site of the change.

sname (Oracle7 only)

Not used.

15.3.5.4.1 Exceptions

The RELOCATE_MASTERDEF procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with master site(s) and notify_masters is TRUE

nonmaster

-23313

The new_masterdef is not a master site

nonmasterdef

-23312

The old_masterdef is not the master definition site

15.3.5.4.2 Restrictions

You must call RELOCATE_MASTERDEF from a master or master definition site.

15.3.5.4.3 Example

The following call relocates the master definition site for replication group SPROCKET from D7CA.BIGWHEEL.COM to D7NY.BIGWHEEL.COM, and informs all masters, as well as the master definition site, of the change:

BEGIN
    DBMS_REPCAT.RELOCATE_MASTERDEF(
		gname		=> 'SPROCKET', 
		old_master_def	=> 'D7CA.BIGWHEEL.COM', 
		new_master_def	=> 'D7NY.BIGWHEEL.COM', 
		notify_masters	=> TRUE, 
		include_old_masterdef=> TRUE);
END;

Suppose that the master definition site D7CA.BIGWHEEL.COM becomes permanently unavailable. We can convert another site, such as D7NY.BIGWHEEL.COM, into the master definition site without having to communicate with D7CA.BIGWHEEL.COM. We set the include_old_masterdef parameter to FALSE.

BEGIN
    DBMS_REPCAT.RELOCATE_MASTERDEF(
		gname		=> 'SPROCKET', 
		old_master_def	=> 'D7CA.BIGWHEEL.COM', 
		new_master_def	=> 'D7NY.BIGWHEEL.COM', 
		notify_masters	=> TRUE, 
		include_old_masterdef=> FALSE);
END;

15.3.6 Maintaining the Repcatlog Queue with DBMS_REPCAT

The programs in this category maintain the "repcatlog" queue. You'll use these procedures:

DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN
DBMS_REPCAT.WAIT_MASTER_LOG
DBMS_REPCAT.PURGE_MASTER_LOG

The following sections describe these programs.

15.3.6.1 The DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN procedure

Whenever you create or alter replicated objects -- for example, with the GENERATE_REPLICATION_SUPPORT or ALTER_MASTER_REPOBJECT procedure -- Oracle queues the changes in the "repcatlog" queue; the entries in this queue correspond to entries in the DBA_REPCATLOG data dictionary view. All DDL changes must originate at the master definition site, but the repcatlog queue exists at every master site.

The DO_DEFERRED_REPCAT_ADMIN procedure processes entries in the repcatlog queue at all master sites. You may have noticed this job in the job queue, scheduled to run once every ten minutes. Oracle creates this scheduled job the first time you execute one of the packages that performs DDL.

The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN
   (gname IN VARCHAR2 := '',
    all_sites IN BOOLEAN := FALSE,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN
   (gname IN VARCHAR2,
    all_sites IN BOOLEAN := FALSE);

As with all of the other DBMS_REPCAT procedures, the Oracle8 version does not have the sname parameter.

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group for which to push the repcatlog queue

all_sites

If TRUE, execute queued procedures at every master site

sname (Oracle7 only)

Not used

15.3.6.1.1 Exceptions

The DO_DEFERRED_REPCAT_ADMIN procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with master site

nonmaster

-23312

Master site associated with snapshot group is no longer a master site

15.3.6.1.2 Restrictions

The DO_DEFERRED_REPCAT_ADMIN procedure performs only the procedures that have been queued by the invoking user. Note that the job queue is used to perform the queued procedures automatically.

15.3.6.1.3 Example.

If you want to run DO_DEFERRED_REPCAT_ADMIN manually, either because you do not have DBMS_JOB background processes running, or because you want to push the repcatlog queue immediately, you can do so. Here is an example:

BEGIN
    DBMS_REPCAT.DO_DEFFERED_REPCAT_ADMIN
	( gname => 'SPROCKET',  all_sites => TRUE);
END;

For an additional example, see the catlog.sql file on the companion disk. The example lists entries in the repcatlog (DBA_REPCATLOG) with the time of submission in hours, minutes, and seconds.

15.3.6.2 The DBMS_REPCAT.WAIT_MASTER_LOG procedure

You can use the WAIT_MASTER_LOG procedure to ascertain whether the changes in the repcatlog queue have reached the master sites. This procedure has an OUT parameter, true_count, which the procedure populates with the number of outstanding tasks. The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.WAIT_MASTER_LOG
   (gname IN VARCHAR2 := '',
    record_count IN NATURAL,
    timeout IN NATURAL,
    true_count OUT NATURAL,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.WAIT_MASTER_LOG
   (gname IN VARCHAR2,
    record_count IN NATURAL,
    timeout IN NATURAL,
    true_count OUT NATURAL); 

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group

record_count

Number of records to allow to be entered in the DBA_REPCATLOG data dictionary view before returning

timeout

Number of seconds to wait before returning

true_count

Output variable containing the actual number of incomplete activities queued in the DBA_REPCATLOG data dictionary view

sname

Not used

There are no restrictions on calling WAIT_MASTER_LOG.

15.3.6.2.1 Exceptions

The WAIT_MASTER_LOG procedure may raise the following exception:

Name

Number

Description

nonmaster

-23312

Calling site is not a master site

15.3.6.2.2 Example

The following call returns after 60 seconds, or after five entries have been entered into the DBA_REPCATLOG data dictionary view at the current master for replication group SPROCKET. The number of records (corresponding to incomplete tasks) is stored in the variable vRecCount.

	VARIABLE vRecCount NATURAL
	BEGIN
		DBMS_REPCAT.WAIT_MASTER_LOG(gname=> 'SPROCKET', 
						record_count => 5, 
						timeout => 60, 
						true_count => vRecCount);
	END;

NOTE: You might find it more convenient to query the DBA_REPCATLOG data dictionary view directly.

15.3.6.3 The DBMS_REPCAT.PURGE_MASTER_LOG procedure

The DBA_REPCATLOG data dictionary view retains entries on DDL propagations that have failed; these entries are not removed when you resolve the problem that caused the failure. You may notice entries such as these:

  1  SELECT	source, status, request, to_char(timestamp, 'HH24:MI:SS') timestamp
  2  FROM dba_repcatlog
  3* ORDER BY id
system@d7ca SQL> /

Source	                Status	Request                         Time
--------------------	-------	------------------------	---------------
D7CA.BIGWHEEL.COM 	ERROR	CREATE_MASTER_REPOBJECT	        23:13:07
D7CA.BIGWHEEL.COM	ERROR	CREATE_MASTER_REPOBJECT	        23:13:07
D7CA.BIGWHEEL.COM	ERROR	CREATE_MASTER_REPOBJECT	        23:25:20
D7CA.BIGWHEEL.COM	ERROR	CREATE_MASTER_REPOBJECT	        23:25:20
D7CA.BIGWHEEL.COM	ERROR	CREATE_MASTER_REPOBJECT	        23:26:53
D7CA.BIGWHEEL.COM	ERROR	CREATE_MASTER_REPOBJECT	        23:26:53
D7CA.BIGWHEEL.COM	ERROR	DROP_MASTER_REPOBJECT	        14:03:27
D7CA.BIGWHEEL.COM	ERROR	DROP_MASTER_REPOBJECT	        14:03:27

8 rows selected.

You must use the PURGE_MASTER_LOG procedure to remove these entries from DBA_REPCATLOG. You can specify records to delete by id, originating master, replication group, and schema. If a parameter is NULL, it is treated as a wildcard. Specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.PURGE_MASTER_LOG
   (id IN NATURAL,
    source IN VARCHAR2,
    gname IN VARCHAR2 := '',
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.PURGE_MASTER_LOG
   (id IN NATURAL,
    source IN VARCHAR2,
    gname IN VARCHAR2);

Parameters are summarized in the following table.

Name

Description

id

Identification of the request (i.e., the ID field in DBA_REPCATLOG data dictionary view)

source

Global name of originating master

gname

Name of the replication group for which request was made

sname (Oracle7 only)

Not used

15.3.6.3.1 Exceptions

The PURGE_MASTER_LOG procedure may raise the following exception:

Name

Number

Description

nonmaster

-23312

The gname is NULL and calling site is not a master site

15.3.6.3.2 Restrictions

The calling site must be a master site.

15.3.6.3.3 Example

The following call removes all entries associated with replication group SPROCKET from the DBA_REPCATLOG data dictionary view:

	BEGIN
		DBMS_REPCAT.PURGE_MASTER_LOG(gname=> 'SPROCKET' );
	END;

For an additional example, see the caterr.sql file on the companion disk. The example lists entries in the repcatlog (DBA_REPCATLOG) containing errors, and displays the error message associated with each error.

NOTE: To clear all entries from the DBA_REPCATLOG data dictionary view, set all parameters to NULL.

15.3.7 Quiescence with DBMS_REPCAT

You may have noticed that many of the DBMS_REPCAT packages require you to quiesce the environment before using them. Quiescence, as it is called, accomplishes two things:

  1. It applies all outstanding DML for the replication group at all master sites.

  2. It prevents any additional DML on any of the replicated objects at all master sites.

In other words, quiescence ensures that all sites are up to date, and forces the replicated environment to stand still.

WARNING: Do not attempt to quiesce an environment that has unresolved errors or any other serious problems. If you cannot complete outstanding transactions, you will not be able to quiesce the environment.

You will use the following programs to quiesce your environment and start it up again:

DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY
DBMS_REPCAT.RESUME_MASTER_ACTIVITY

15.3.7.1 The DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY procedure

The SUSPEND_MASTER_ACTIVITY procedure quiesces an environment. The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBS_REPCAT.SUSPEND_MASTER_ACTIVITY
   (gname IN VARCHAR2 := '',
    execute_as_user IN BOOLEAN  := FALSE,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY
   (gname IN VARCHAR2,
    override IN BOOLEAN  := FALSE);

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group for which replication activity is to be suspended

execute_as_user

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

sname (Oracle7 only)

Not used

15.3.7.1.1 Exceptions

The SUSPEND_MASTER_ACTIVITY procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with one or more master site(s)

nonmasterdef

-23312

Calling site is not the master definition site

notnormal

-23311

Replication group gname is not in NORMAL state

15.3.7.1.2 Restrictions

Note the following restrictions on calling SUSPEND_MASTER_ACTIVITY:

  • You must run this procedure from the master definition site.

  • Prior to Oracle8, this procedure quiesces all replication groups at the master definition site, not just the group specified by the gname parameter.

15.3.7.1.3 Example

The following call suspends replication activity for the SPROCKET replication group:

BEGIN
    DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(  gname => 'SPROCKET' );
END;

NOTE: This call can take some time to complete if you have many master sites and/or many outstanding transactions. You can monitor the progress by querying the status field in the DBA_REPCATLOG data dictionary view.

15.3.7.2 The DBMS_REPCAT.RESUME_MASTER_ACTIVITY procedure

The RESUME_MASTER_ACTIVITY procedure starts up an environment that has been quiesced. The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.RESUME_MASTER_ACTIVITY
   (gname IN VARCHAR2 := '',
    override IN BOOLEAN := FALSE,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.RESUME_MASTER_ACTIVITY
   (gname IN VARCHAR2,
    override IN BOOLEAN := FALSE);

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group for which replication activity is to be resumed.

override

If FALSE (the default), activity is resumed only after all deferred REPCAT activity is completed; if set to TRUE, activity is resumed as soon as possible

sname

Not used

15.3.7.2.1 Exceptions

The RESUME_MASTER_ACTIVITY procedure may raise the following exceptions:

Name

Number

Description

commfailure

-23317

Unable to communicate with one or more master site(s)

nonmasterdef

-23312

Calling site is not the master definition site

notquiesced

-23310

Replication group gname is not quiesced

15.3.7.2.2 Restrictions

Note the following restrictions on calling RESUME_MASTER_ACTIVITY:

  • You must run this procedure from the master definition site.

  • The replication group must be quiesced or quiescing.

15.3.7.2.3 Example

The following example resumes replication activity for a replication group:

BEGIN
    DBMS_REPCAT.RESUME_MASTER_ACTIVITY(  gname => 'SPROCKET' );
END;

15.3.8 Miscellaneous DBMS_REPCAT Procedures

This section describes several DBMS_REPCAT programs that don't fall into any of the earlier categories.

NOTE: We describe the IMPORT_CHECK, ALTER_MASTER_PROPAGATION, and SEND_AND_COMPARE_OLD_VALUES procedures here because they are a part of the DBMS_REPCAT package. However, you will probably run these procedures after using the DBMS_OFFLINE_OG and/or DBMS_RECTIFIER_DIFF packages described in the next major section.

15.3.8.1 The DBMS_REPCAT.REPCAT_IMPORT_CHECK procedure

From time to time, you may need to rebuild a master site from an export dump file as either a recovery or maintenance procedure. Because object id numbers (as seen in SYS.OBJ$.OBJ# and DBA_OBJECTS.OBJECT_ID) change during these rebuilds, Oracle supplies a procedure (REPCAT_IMPORT_CHECK) that you must run immediately after an import of any master site to synchronize the new id numbers with the data stored in the table SYSTEM.REPCAT$_REPOBJECT.

You must run the REPCAT_IMPORT_CHECK procedure immediately after you import any master site. The specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.REPCAT_IMPORT_CHECK
   (gname IN VARCHAR2 := '',
    master IN BOOLEAN,
    sname IN VRCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.REPCAT_IMPORT_CHECK
   (gnamee in VARCHAR2 := '',
    master IN BOOLEAN);

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group being revalidated

master

Set to TRUE if site is a master, FALSE if it is a snapshot site

sname (Oracle7 only)

Not used

15.3.8.1.1 Exceptions

The REPCAT_IMPORT_CHECK procedure may raise the following exceptions:

Name

Number

Description

missingobject

-23308

Object with a status of VALID in REPCAT$_REPOBJECT does not exist

missingschema

-23306

Schema sname does not exist

nonmaster

-23312

Master is set to TRUE, but the calling site is not a master, or not the expected database

nonsnapshot

-23314

Master is set to FALSE but the calling site is not a snapshot site

15.3.8.1.2 Example

The following call revalidates all replicated objects and supporting objects for the SPROCKET replication group. Issue this call after an import:

BEGIN
    DBMS_REPCAT.REPCAT_IMPORT_CHECJ(	sname						=> 'SPROCKET',  master	 => TRUE );
END;

NOTE: Call REPCAT_IMPORT_CHECK with sname and master set to NULL (or with no parameters) to validate all replication groups at the site.

15.3.8.2 The DBMS_REPCAT.ALTER_MASTER_PROPAGATION procedure

The advanced replication option supports two modes of propagation: synchronous and asynchronous. The following table summarizes the pros and cons of each mode.

Mode

Advantages

Disadvantages

Synchronous

Data is always up to date at all sites. No possibility of conflicts.

Requires 100% availability of network connections. If a site is unreachable, transactions are blocked.

Asynchronous

Master sites can go offline temporarily with no adverse impact.

Must build conflict resolution into replicated applications and monitor unresolved conflicts.

As a practical matter, you should opt for synchronous replication only if your sites are tightly linked to each other in your network, both physically and logically. Examples of acceptably tight links include two machines sharing a hub, or two machines connected by a crossover cable.

Regardless of the propagation method you choose, Oracle lets you change a replication group's propagation mode later with the ALTER_MASTER_PROPAGATION procedure. Call this procedure to change the propagation mode of a replication group (from synchronous to asynchronous, or vice versa). Here's the specification:

PROCEDURE DBMS_REPCAT.ALTER_MASTER_PROPAGATION
   (gname IN VARCHAR2,
    master IN VARCHAR2,
   {dblink_table IN dbms_utility.dblink_array | dblink_list IN VARCHAR2},
    propagation_modee IN VARCHAR2 := 'ASYNCHRONOUS',
    comment IN VARCHAR2 := '');

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group whose propagation mode is being altered

master

Global name of the master site having its propagation mode altered

dblink_list

List of database links for which the master's propagation mode is being altered

propagation_mode

New propagation mode (SYNCHRONOUS or ASYNCHRONOUS)

comment

Comment visible in DBA_REPPROP data dictionary view

15.3.8.2.1 Exceptions

The ALTER_MASTER_PROPAGATION procedure may raise the following exceptions:

Name

Number

Description

nonmaster

-23312

One of the sites in dblink_list is not a master site

nonmasterdef

-23312

Calling site is not the master definition site

notquiesced

-23310

Replication group gname is not quiesced

typefailure

-23319

The propagation_mode is not SYNCHRONOUS or ASYNCHRONOUS

15.3.8.2.2 Restrictions

Note the following restrictions on calling ALTER_MASTER_PROPAGATION:

  • You must run this procedure from the master definition site.

  • The replication group must be quiesced.

15.3.8.2.3 Example

The following call changes the propagation mode between D7CA.BIGWHEEL.COM and D7NY.BIGWHEEL.COM to SYNCHRONOUS:

	BEGIN
		DBMS_REPCAT.ALTER_MASTER_PROPAGATION(
			gname		=> 'SPROCKET', 
			master		=> 'D7CA.BIGWHEEL.COM', 
			dblink_list	=> 'D7NY.BIGWHEEL.COM', 
			use_existing_objects=> 'FALSE', 
			propagation_mode=> 'SYNCHRONOUS');
	END;

NOTE: If you change the propagation mode, you must also regenerate the replication support triggers for all replicated tables; ALTER_MASTER_PROPAGATION does not do this automatically. After altering the propagation method, you must call DBMS_REPCAT.GENERATE_REPLICATION_TRIGGERS for all replicated tables in the replication group.

15.3.8.3 The DBMS_REPCAT.SEND_AND_COMPARE_OLD_VALUES procedure (Oracle8 only)

The default behavior of advanced replication is to send the old and new values of every column to participating master sites whenever you update a row in a replicated table. At the destination sites, Oracle uses this information to ensure that the version of the row that you updated matches the version of the row currently at the destination. However, if you know that certain columns in a table will never change, you can avoid sending the data in these columns when you propagate updates to participating master sites. Using the SEND_AND_COMPARE_OLD_VALUES procedure (available only in Oracle8) in this way, you'll reduce propagation overhead. Here is the specification:

PROCEDURE DBMS_REPCAT.SEND_AND_COMPARE_OLD_VALUES
   (sname IN VARCHAR2
    oname IN VARCHAR2,
    {column_list IN VARCHAR2 | column_table IN dbms_repcat.varchar2s},
    operation IN VARCHAR2 := 'UPDATE',
    send IN BOOLEAN := TRUE);

Parameters are summarized in the following table.

Name

Description

sname

Name of the replication group whose propagation mode is being altered

oname

Table being altered

column_list

Comma-separated list of columns whose propagation mode is being altered; "*" indicates all nonkey columns

column_table

PL/SQL table of containing columns whose propagation is being altered

operation

Operation for which this change applies; this may be UPDATE, DELETE, or "*" (indicating both updates and deletes)

send

If TRUE (the default), then the old values for the columns are sent; if FALSE, then old values are not sent

NOTE: The configuration changes you specify with this procedure do not take effect unless the min_communication parameter is TRUE for the table in question. That is, you must have executed DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for the table with min_communication = TRUE.

15.3.8.3.1 Exceptions

The SEND_AND_COMPARE_OLD_VALUES procedure may raise the following exceptions:

Name

Number

Description

missingobject

-23308

Object oname does not exist

missingcolumn

-23334

Column(s) specified do not exist in table oname

nonmasterdef

-23312

Calling site is not the master definition site

notquiesced

-23310

Replication group gname is not quiesced

typefailure

-23319

The oname is not a table

15.3.8.3.2 Restrictions

Note the following restrictions on calling SEND_AND_COMPARE_OLD_VALUES:

  • You must call this procedure from the master definition site.

  • The replication group sname must be quiesced.


Previous: 15.2 DBMS_REPCAT_ADMIN: Setting Up More Administrator AccountsOracle Built-in PackagesNext: 15.4 DBMS_OFFLINE_OG: Performing Site Instantiation
15.2 DBMS_REPCAT_ADMIN: Setting Up More Administrator AccountsBook Index15.4 DBMS_OFFLINE_OG: Performing Site Instantiation

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