#

| Chapter 15 Advanced Replication |

## 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

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

15.2 DBMS_REPCAT_ADMIN: Setting Up More Administrator Accounts |  | 15.4 DBMS_OFFLINE_OG: Performing Site Instantiation

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