#

| Chapter 16 |

# 16. Conflict Resolution

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:

Insert conflicts

  An inserted row has a primary key that already exists at the destination site.

Update conflicts

  The pre-update data in a row at the originating site does not match the current data at the destination site.

Delete conflicts

  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.

## 16.1 Getting Started with DBMS_REPCAT

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.

### 16.1.1 DBMS_REPCAT Programs

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.

Table 16.1: DBMS_REPCAT Program (Conflict Resolution Only)
Name | Description | Use in SQL?
--- | --- | ---
ADD_GROUPED_COLUMN | Adds table column(s) to an existing column group | No
ADD_PRIORITY_<datatype> | Adds a member to an existing priority group | No
ADD_SITE_PRIORITY_SITE | Adds a site to an existing site priority group | No
ADD_<conflicttype>_RESOLUTION | Adds custom conflict resolution handler for update, delete, or uniqueness conflicts | No
ALTER_PRIORITY | Changes priority level for a member of a priority group | No
ALTER_PRIORITY_<datatype> | Alters the value of a member of a priority group | No
ALTER_SITE_PRIORITY | Alters priority level of a site | No
ALTER_SITE_PRIORITY_SITE | Designates a site to a given priority level | No
CANCEL_STATISTICS | Cancels collection of statistics about conflict resolution for a table | No
COMMENT_ON_COLUMN_GROUP | Creates or updates a comment on a column group, visible in DBA_REPCOLUMN_GROUP data dictionary view | No
COMMENT_ON_PRIORITY_GROUP | Creates or updates comment on a priority group, visible in DBA_REPPRIORITY_GROUP | No
COMMENT_ON_SITE_PRIORITY | Creates or updates a comment on a site priority, visible in DBA_REPRIORITY_GROUP data dictionary view | No
COMMENT_ON_<conflicttype>_ RESOLUTION | Creates a comment on a conflict resolution method, visible in DBA_REPRESOLUTION data dictionary view | No
DEFINE_COLUMN_GROUP | Creates an empty column group for a replication group | No
DEFINE_PRIORITY_GROUP | Creates a priority group for a replication group | No
DEFINE_SITE_PRIORITY | Creates a site priority group for a replication group | No
DROP_COLUMN_GROUP | Drops a column group from a replication group | No
DROP_GROUPED_COLUMN | Drops a column from a column group | No
DROP_PRIORITY | Drops a member of a priority group, selected by priority level | No
DROP_PRIORITY_GROUP | Drops a priority group from a replication group | No
DROP_PRIORITY_<datatype> | Drops a member of a priority group, selected by value | No
DROP_SITE_PRIORITY | Drops a site priority group from a replication group | No
DROP_SITE_PRIORITY_SITE | Drops a site from a site priority group, selected by site name | No
DROP_<conflicttype>_RESOLUTION | Drops an update, delete, or uniqueness conflict resolution handling technique from a replication group | No
MAKE_COLUMN_GROUP | Creates a column group and adds one or more columns | No
PURGE_STATISTICS | Deletes entries from the DBA_REPRESOLUTION_STATISTICS data dictionary view | No
REGISTER_STATISTICS | Starts collection of statistics for the resolution of update, delete, and uniqueness conflicts for a given table | No

### 16.1.2 DBMS-REPCAT Exceptions

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.

Table 16.2: DBMS_REPCAT Exceptions (Conflict Resolution Only)
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

### 16.1.3 DBMS-REPCAT Nonprogram Elements

In addition to programs and exceptions, the DBMS_REPCAT package defines the following constant used for conflict resolution:

VARCHAR2S

  PL/SQL table of VARCHAR2(60) indexed by BINARY INTEGER.

### 16.1.4 Data Dictionary Views

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.

Table 16.3: Data Dictionary Views Associated with Conflict Resolution
View Name | Description
--- | ---
DBA_REPCOLUMN_GROUP | Contains information about column groups.
DBA_REPCONFLICT | Contains information about all conflict resolution methods that have been defined.
DBA_REPGROUPED_ COLUMN | Contains information about all columns that are members of column groups.
DBA_REPPARAMETER_COLUMN | 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.
DBA_REPPRIORITY | Contains information about every value and priority that has been defined for all priority groups and site priority groups.
DBA_REPPRIORITY_GROUP | Contains information about all priority groups and site priority groups.
DBA_REPRESOLUTION | Contains information about the conflict resolution technique that has been defined for all conflict types.
DBA_REPRESOL_STATS_CONTROL | Contains information about statistics that have been gathered for conflict resolution.
DBA_REPRESOLUTION_METHOD | Contains information about all available conflict resolution methods.
DBA_REPRESOLUTION_STATISTICS | 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.

Table 16.4: Columns in DBA_REPCOLUMN_GROUP View
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

Table 16.5: Columns in DBA_REPCONFLICT View
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.

Table 16.6: Columns in DBA_REPGROUPED_COLUMN View
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

Table 16.7: Columns in DBA_REPPARAMETER_COLUMN View
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.

Table 16.8: Columns in DBA_REPPRIORITY View
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.

Table 16.9: Columns in DBA_REPPRIORITY_GROUP View
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.

Table 16.10: Columns in DBA_REPRESOLUTION View
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.

Table 16.11: Columns in DBA_REPRESOL_STATS_CONTROL View
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

Table 16.12: Columns in DBA_REPRESOLUTION_METHOD View
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

Table 16.13: Columns in DBA_REPRESOLUTION_STATISTICS View
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.

15.6 DBMS_REPUTIL: Enabling and Disabling Replication |  | 16.2 Column Groups with DBMS_REPCAT

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