Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 15.6 DBMS_REPUTIL: Enabling and Disabling ReplicationChapter 16Next: 16.2 Column Groups with DBMS_REPCAT
 

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.


Previous: 15.6 DBMS_REPUTIL: Enabling and Disabling ReplicationOracle Built-in PackagesNext: 16.2 Column Groups with DBMS_REPCAT
15.6 DBMS_REPUTIL: Enabling and Disabling ReplicationBook Index16.2 Column Groups with DBMS_REPCAT

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference