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.
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.
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.
Name | Description | Use in SQL? |
---|---|---|
Adds master database to replication group | No | |
Alters propagation method for a replication group at a given site (options are SYNCHRONOUS or ASYNCHRONOUS) | No | |
Performs DDL on a replicated object | No | |
Creates or updates a comment on a replication group; visible in DBA_REPGROUP data dictionary view | No | |
Creates or updates a comment on a replication site; visible in DBA_REPSITES data dictionary view | No | |
Creates or updates a comment on a replicated object; visible in DBA_REPOBJECT data dictionary view | No | |
Creates a master replication group | No | |
Adds an object to a replication group | No | |
Performs outstanding administrative tasks at local master site | No | |
Drops a replication group | No | |
Drops an object from a replication group | No | |
Specifies DDL to execute at master sites | No | |
Generates packages required to replicate a given table | No | |
Generates triggers, packages, and procedures required to replicate a given table | No | |
Generates triggers and packages required to replicate a given table | No | |
Deletes entries from the local RepCatLog (DBA_REPCATLOG) | No | |
Changes the master definition site for a replication group | No | |
Drops one or more master databases from a replication group | No | |
Confirms a replicated object's validity after an import | No | |
Enables propagation of a replication group that had been quiesced | No | |
Reduces propagation overhead by not sending unchanged columns to a master site. | No | |
Designates alternative column(s) to use instead of a primary to uniquely identify rows of a replicated table | No | |
Quiesces a replication group | No | |
Determines whether asynchronous DML has been applied at a master site | No |
Table 15.4 describes exceptions raised by the DBMS_REPCAT programs described in this chapter.
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) |
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.
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 |
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 |
You must be connected to the replication administrator account (typically REPADMIN) to call CREATE_MASTER_REPGROUP.
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.
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 |
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 |
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.
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;
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.
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 |
The COMMENT_ON_REPGROUP procedure must be called from the master definition site.
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.
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.
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. |
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 |
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.
This section contains a series of examples showing how to create replication objects.
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.
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.
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.
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. |
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 |
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.
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;
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. |
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 |
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.
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;
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. |
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 |
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.
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;
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. |
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 |
Note the following restrictions on calling ALTER_MASTER_REPOBJECT:
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;
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 |
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 |
The COMMENT_ON_REPOBJECT procedure must be called from the master definition site.
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;
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 |
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. |
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 |
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.
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.
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.
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.
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";
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 |
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 |
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.
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;
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. |
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 |
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.
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.
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.
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) |
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 |
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.
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.
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) |
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 |
The REMOVE_MASTER_DATABASES procedure must be run from the master definition site.
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.
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 |
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 |
You must call the COMMENT_ON_REPSITES procedure from the master definition site.
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;
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. |
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 |
You must call RELOCATE_MASTERDEF from a master or master definition site.
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;
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.
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 |
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 |
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.
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.
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.
The WAIT_MASTER_LOG procedure may raise the following exception:
Name | Number | Description |
---|---|---|
nonmaster | -23312 | Calling site is not a master site |
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.
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 |
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 |
The calling site must be a master site.
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.
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:
It applies all outstanding DML for the replication group at all master sites.
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 |
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 |
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 |
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.
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.
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 |
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 |
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.
The following example resumes replication activity for a replication group:
BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY( gname => 'SPROCKET' ); END;
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.
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 |
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 |
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.
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 |
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 |
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.
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.
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.
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 |
Note the following restrictions on calling SEND_AND_COMPARE_OLD_VALUES:
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.
This HTML Help has been published using the chm2web software. |