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 gnam e .
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