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:
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.
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, describes the snapshot-related programs; Chapter 15, 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) 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.
Table 16-1. DBMS_REPCAT Program (Conflict Resolution Only)
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.
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 |
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.
Table 16-3. Data Dictionary Views Associated with Conflict Resolution
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.
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-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-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. |
3.133.111.85