Contents:
Getting Started with DBMS_REPCAT
Column Groups with DBMS_REPCAT
Priority Groups with DBMS_REPCAT
Site Priority Groups with DBMS_REPCAT
Assigning Resolution Methods with DBMS_REPCAT
Monitoring Conflict Resolution with DBMS_REPCAT
Conflict resolution is perhaps the most difficult challenge for the administrator of a replicated environment that uses asynchronous replication. A conflict can arise when an insert, update, or delete to a replicated table occurs at two or more master sites. Oracle detects conflicts at the destination site when attempting to apply the changes. Three different types of conflicts can arise:
An inserted row has a primary key that already exists at the destination site.
The pre-update data in a row at the originating site does not match the current data at the destination site.
A deleted row does not exist at the destination site.
Through the DBMS_REPCAT package, Oracle's advanced replication option gives you tools for identifying and resolving conflicts automatically. The goal is to ensure that data at all master sites converges -- that is, all rows end up with identical data at all sites.
WARNING: The procedures described in this chapter are no substitute for careful application and schema design, and they can't resolve all conflicts.
Use the DBMS_REPCAT package to deal with conflict resolution. As we've seen in previous chapters, DBMS_REPCAT is an enormous package whose programs perform many different types of operations. Chapter 14, Snapshots, describes the snapshot-related programs; Chapter 15, Advanced Replication, describes the programs you call to create and maintain replicated environments. This chapter focuses on the programs you use in DBMS_REPCAT conflict resolution.
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, Introduction) 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 16-1 summarizes the DBMS_REPCAT procedures used in conflict resolution, and lists all of the programs in alphabetical order. In the sections that follow, we divide these programs by category (column groups, priority groups, site priorities, resolution method assignment, and conflict resolution monitoring). For example, the section "Column Groups" describes only the column group programs.
Name | Description | Use in SQL? |
---|---|---|
Adds table column(s) to an existing column group | No | |
Adds a member to an existing priority group | No | |
Adds a site to an existing site priority group | No | |
Adds custom conflict resolution handler for update, delete, or uniqueness conflicts | No | |
Changes priority level for a member of a priority group | No | |
Alters the value of a member of a priority group | No | |
Alters priority level of a site | No | |
Designates a site to a given priority level | No | |
Cancels collection of statistics about conflict resolution for a table | No | |
Creates or updates a comment on a column group, visible in DBA_REPCOLUMN_GROUP data dictionary view | No | |
Creates or updates comment on a priority group, visible in DBA_REPPRIORITY_GROUP | No | |
Creates or updates a comment on a site priority, visible in DBA_REPRIORITY_GROUP data dictionary view | No | |
RESOLUTION | Creates a comment on a conflict resolution method, visible in DBA_REPRESOLUTION data dictionary view | No |
Creates an empty column group for a replication group | No | |
Creates a priority group for a replication group | No | |
Creates a site priority group for a replication group | No | |
Drops a column group from a replication group | No | |
Drops a column from a column group | No | |
Drops a member of a priority group, selected by priority level | No | |
Drops a priority group from a replication group | No | |
Drops a member of a priority group, selected by value | No | |
Drops a site priority group from a replication group | No | |
Drops a site from a site priority group, selected by site name | No | |
Drops an update, delete, or uniqueness conflict resolution handling technique from a replication group | No | |
Creates a column group and adds one or more columns | No | |
Deletes entries from the DBA_REPRESOLUTION_STATISTICS data dictionary view | No | |
Starts collection of statistics for the resolution of update, delete, and uniqueness conflicts for a given table | No |
Table 16.2 lists the exceptions that may be raised by programs in the DBMS_REPCAT package that are specific to conflict resolution. Specific sections list the exceptions that may be raised by individual programs in DBMS_REPCAT.
Name | Number | Description |
---|---|---|
duplicatecolumn | -23333 | Attempt to add duplicate column to column group |
duplicategroup | -23330 | Attempt to add duplicate column group to a replicated table |
duplicateprioritygroup | -23335 | Attempt to create duplicate priority group |
duplicaterepgroup | -23374 | Attempt to create duplicate snapshot replication group |
duplicateresolution | -23339 | Attempt to create duplicate resolution method |
duplicateschema | -23307 | Attempt to create duplicate replication group |
duplicatevalue | -23338 | Attempt to create duplicate value in a priority group |
invalidmethod | -23340 | Attempt to use nonexistent conflict resolution method |
invalidparameter | -23342 | Invalid number of columns in call to ADD_UNIQUE_RESOLUTION |
missingcolumn | -23334 | Reference to nonexistent column |
missingconstraint | -23344 | Missing constraint (used internally) |
missingfunction | -23341 | User function does not exist |
missinggroup | -23331 | Column group does not exist |
missingobject | -23308 | Object does not exist as a table |
missingprioritygroup | -23336 | Priority group does not exist |
missingrepgroup | -23373 | Replication group does not exists |
missingresolution | -23343 | Reference conflict resolution method does not exist |
missingschema | -23306 | Schema does not exist |
missingvalue | -23337 | Missing value (used internally) |
nonmasterdef | -23312 | Site is not a master definition site |
nonsnapshot | -23314 | Site is not a snapshot site |
paramtype | -23325 | Invalid parameter type (used internally) |
referenced | -23332 | Attempt to drop column group used for conflict resolution |
statnotreg | -23345 | Conflict resolution statistics not registered (used internally) |
typefailure | -23319 | Attempt to replicate nonsupported datatype |
In addition to programs and exceptions, the DBMS_REPCAT package defines the following constant used for conflict resolution:
PL/SQL table of VARCHAR2(60) indexed by BINARY INTEGER.
Oracle provides a number of data dictionary views that are useful for analyzing the status and volume of conflicts, as listed in Table 16.3.
View Name | Description |
---|---|
Contains information about column groups. | |
Contains information about all conflict resolution methods that have been defined. | |
COLUMN | Contains information about all columns that are members of column groups. |
Contains information about columns that are designated to resolve conflicts. These columns have been passed in the list_of_column_names parameter of DBMS_REPCAT.ADD_<conflicttype>_RESOLUTION. | |
Contains information about every value and priority that has been defined for all priority groups and site priority groups. | |
Contains information about all priority groups and site priority groups. | |
Contains information about the conflict resolution technique that has been defined for all conflict types. | |
Contains information about statistics that have been gathered for conflict resolution. | |
Contains information about all available conflict resolution methods. | |
If resolution statistics are being collected, contains information about the execution of conflict resolution handlers. |
Tables 16-4 through 16-14 describe the contents of these views.
Column Name | Description |
---|---|
sname | Schema that owns table oname |
oname | Name of the replicated table |
group_name | Name of the column group |
group_comment | Comment for the column group |
Column Name | Description |
---|---|
sname | Schema that owns table oname. |
oname | Name of the replicated table. |
conflict_type | Type of conflict the resolution method resolves. |
reference_name | For DELETE conflicts, the table name. For UNIQUENESS conflicts, the unique constraint name. For UPDATE conflicts, the column group name. |
Column Name | Description |
---|---|
sname | Schema that owns table oname |
oname | Name of the replicated table |
group_name | Name of the column group |
group_comment | Comment for the column group |
Column Name | Description |
---|---|
sname | Schema that owns table oname. |
oname | Name of the replicated table. |
conflict_type | Type of conflict the method resolves. |
reference_name | For DELETE conflicts, the table name. For UNIQUENESS conflicts, the unique constraint name. For UPDATE conflicts, the column group name. |
sequence_no | Order in which the method is attempted. 1 is first. |
method_name | Name of the built-in resolution method, or `USER FUNCTION' for user defined methods. |
function_name | Name of the user defined function (if applicable). |
priority_group | Name of the priority group (if applicable). |
parameter_table_name | Name of the PL/SQL table containing columns that are passed to the resolution method. |
parameter_column_name | Name of the column passed to parameter_column_name in ADD_<confllicttype>_RESOLUTION call. |
parameter_sequence_no | Position of the column in the parameter_column_name parameter. |
Column Name | Description |
---|---|
sname | Name of the replicated schema. Obsolete with Version 7.3 onwards; replace by gname. |
priority_group | Name of the priority group. |
priority | Priority level (the higher the number, the higher the priority). |
data_type | Datatype of the priority group. |
fixed_data_length | Maximum length for CHAR datatypes. |
char_value | For CHAR priority groups, the value associated with the priority. |
varchar2_value | For VARCHAR2 priority groups, the value associated with the priority. |
number_value | For NUMBER priority groups, the value associated with the priority. |
date_value | For DATE priority groups, the value associated with the priority. |
raw_value | For RAW priority groups, the value associated with the priority. |
gname | Name of the replication group. |
nchar_value (Oracle8 only) | For NCHAR priority groups, the value associated with the priority. |
nvarchar2_value (Oracle8 only) | For NVARCHAR2 priority groups, the value associated with the priority. |
large_char_value (Oracle8 only) | For LARGE_CHAR priority groups, the value associated with the priority. |
Column Name | Description |
---|---|
sname | Name of the replicated schema. Obsolete with Version 7.3 onwards; replace by gname. |
priority_group | Name of the priority group or site priority group. |
data_type | Datatype of the priority group. |
fixed_data_length | Maximum length for CHAR datatypes. |
priority_comment | Comment for priority group. |
gname | Replication group to which priority group belongs. |
Column Name | Description |
---|---|
sname | Schema that owns table oname. |
oname | Name of the replicated table. |
conflict_type | Type of conflict the method resolves. |
reference_name | For DELETE conflicts, the table name. For UNIQUENESS conflicts, the unique constraint name. For UPDATE conflicts, the column group name. |
sequence_no | Order in which the method is attempted. 1 is first. |
method_name | Name of the built-in resolution method, or USER FUNCTION for user-defined methods. |
function_name | Name of the user-defined function (if applicable). |
priority_group | Name of the priority group (if applicable). |
resolution_comment | Comment on the resolution method. |
Column Name | Description |
---|---|
sname | Schema that owns table oname |
oname | Name of the replicated table |
created | Date statistics were first collected |
status | Current status of statistics collection (ACTIVE or CANCELLED) |
status_update_date | Date of last update to status |
purged_date | Date of last purge of statistics |
last_purge_start_date | Start Date passed to last call to PURGE_STATISTICS |
last_purge_end_date | End Date passed to last call to PURGE_STATISTICS |
Column Name | Description |
---|---|
conflict_type | Type of conflict the method resolves (UPDATE, UNIQUENESS, or DELETE) |
method_name | Name of the built in method, or name of user-supplied function |
Column Name | Description |
---|---|
sname | Schema that owns table oname. |
oname | Name of the replicated table. |
conflict_type | Type of conflict that Oracle resolved successfully (UPDATE, UNIQUENESS, or DELETE). |
reference_name | For DELETE conflicts, the table name. For UNIQUENESS conflicts, the unique constraint name. For UPDATE conflicts, the column group name. |
method_name | Name of the built-in resolution method, or `USER FUNCTION' for user defined methods. |
function_name | Name of the user defined function (if applicable). |
priority_group | Name of the priority group (if applicable). |
resolved_date | Date Oracle resolved the conflict. |
primary_key_value | Value of the primary key for the resolved row. |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.