#

| Chapter 16 Conflict Resolution |

## 16.4 Site Priority Groups with DBMS_REPCAT

The site priority group technique resolves conflicts by accepting the data that originated from the site with the highest priority.

### 16.4.1 About Site Priority Groups

The procedures for creating and maintaining site priority groups are almost completely analogous to those used for priority groups. The similarity arises because a site priority group is actually a special case of a priority group in which the range of data values is the range of global names in the replicated environment. In fact, Oracle stores the information about priority groups and site priority groups in the same data dictionary views (DBA_REPPRIORITY_GROUP and DBA_REPPRIORITY). However, unlike in the priority group technique, you should base site priority group rankings on your confidence in the data from each site, as opposed to the business rules associated with a workflow.

Use the following programs to maintain site priority groups:

DBMS_REPCAT.ADD_SITE_PRIORITY_SITE
DBMS_REPCAT.ALTER_SITE_PRIORITY
DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE
DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY
DBMS_REPCAT.DEFINE_SITE_PRIORITY
DBMS_REPCAT.DROP_SITE_PRIORITY
DBMS_REPCAT.DROP_SITE_PRIORITY_SITE

### 16.4.2 Creating, Maintaining, and Dropping Site Priorities

DBMS_REPCAT's DEFINE_SITE_PRIORITY and DROP_SITE_PRIORITY procedures allow you to create and drop site priorities. Use the COMMENT_ON_SITE_PRIORITY procedure to maintain the comment on the site priority.

#### 16.4.2.1 The DBMS_REPCAT.DEFINE_SITE_PRIORITY procedure

The DEFINE_SITE_PRIORITY procedure creates a site priority group. You can add sites to this group later. Specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.DEFINE_SITE_PRIORITY
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         comment IN VARCHAR2 := NULL,
>         sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.DEFINE_SITE_PRIORITY
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         comment IN VARCHAR2 := NULL)

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the replication group containing the site priority group
name | Name of the site priority group
comment | Comment, visible in DBA_REPPRIORITY_GROUP data dictionary view
sname (Oracle7 only) | Not used

#### 16.4.2.1.1 Exceptions

The DEFINE_SITE_PRIORITY procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
duplicateprioritygroup | -23335 | Site priority group name already exists
missingrepgroup | -23373 | Replication group gname does not exist
nonmasterdef | -23312 | Calling site is not the master definition site

#### 16.4.2.1.2 Restrictions

You must call DBMS_REPCAT.DEFINE_SITE_PRIORITY from the master definition site.

#### 16.4.2.1.3 Example

The following call creates a site priority group called SP_NORTH_AMERICA:

>     	BEGIN
>     		DBMS_REPCAT.DEFINE_SITE_PRIORITY(
>     			gname	=> 'SPROCKET',
>     			name	=> 'SP_NORTH_AMERICA',
>     			comment	=> 'Site Priority for North American Locations');
>     	END;

These changes take effect after the next call to DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for an object in the SPROCKET replication group.

#### 16.4.2.2 The DBMS_REPCAT.DROP_SITE_PRIORITY procedure

The DROP_SITE_PRIORITY procedure drops an existing site priority group that is no longer in use. Specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.DROP_SITE_PRIORITY
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.DROP_SITE_PRIORITY
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2)

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the replication group containing the site priority group
name | Name of the site priority group
sname (Oracle7 only) | Not used

> WARNING: As with the DROP_PRIORITY_GROUP procedure, do not attempt to drop a site priority group that is acting as an UPDATE conflict resolution handler for a column group. First, use DROP_UPDATE_RESOLUTION to drop the conflict handler for the column group.

#### 16.4.2.2.1 Exceptions

The DROP_SITE_PRIORITY procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
missingrepgroup | -23373 | Replication group gname does not exist
nonmasterdef | -23312 | Calling site is not the master definition site
referenced | -23332 | Site priority group is used by existing conflict resolution method

#### 16.4.2.2.2 Restrictions

You must call DBMS_REPCAT.DROP_SITE_PRIORITY from the master definition site.

#### 16.4.2.2.3 Example

The following example shows how to drop a site priority group that is no longer in use:

>     	BEGIN
>     		DBMS_REPCAT.DROP_SITE_PRIORITY(
>     			gname	=> 'SPROCKET',
>     			name	=> 'SP_NORTH_AMERICA');
>     	END;

#### 16.4.2.3 The DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY procedure

The COMMENT_ON_SITE_PRIORITY procedure creates or replaces the comment field in the DBA_REPPRIORITY_GROUP data dictionary view for the specified site priority group. Specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         comment IN VARCHAR2,
>         sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         comment IN VARCHAR2)

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the replication group containing the priority group
name | Name of the site priority group
comment | Comment
sname (Oracle7 only) | Not used

#### 16.4.2.3.1 Exceptions

The COMMENT_ON_SITE_PRIORITY procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
missingpriority | -1403 | Site priority group name does not exist
missingrepgroup | -23373 | Replication group gname does not exist
nonmasterdef | -23312 | Calling site is not master definition site

#### 16.4.2.3.2 Restrictions

You must call DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY from the master definition site.

#### 16.4.2.3.3 Example

The following example shows how to replace a comment on a site priority group:

>     	BEGIN
>     		DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY(
>     			gname	=> 'SPROCKET',
>     			name	=> 'SP_NORTH_AMERICA',
>     			comment	=> 'Comment added on '||sysdate|| ' by '|| user);
>     	END;

### 16.4.3 Maintaining Site Priorities

Once you have created a site priority group as described in the previous section, you can add and drop sites (with ADD_SITE_PRIORITY_SITE and DROP_SITE_PRIORITY_SITE) and modify their priority values (with ALTER_SITE_PRIORITY and ALTER_SITE_PRIORITY_SITE).

You can query the data dictionary table DBA_REPPRIORITY for information about site priority group members and their priorities.

#### 16.4.3.1 The DBMS_REPCAT.ADD_SITE_PRIORITY_SITE procedure

The ADD_SITE_PRIORITY_SITE procedure adds a new site to a site priority group. Specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.ADD_SITE_PRIORITY_SITE
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         site IN VARCHAR2,
>         priority IN NUMBER,
>         sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.ADD_SITE_PRIORITY_SITE
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         site IN VARCHAR2,
>         priority IN NUMBER);

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the replication group to which site priority group name belongs
name | Name of the site priority group
site | Global name of the new site
priority | Priority designated to site
sname (Oracle7 only) | Not used

#### 16.4.3.1.1 Exceptions

The ADD_SITE_PRIORITY_SITE procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
duplicatepriority | -23335 | Another site is already designated with priority specified by priority parameter
duplicatesite | -23338 | Site is already in the site priority group name
missingpriority | -1403 | Site does not exist
missingrepgroup | -23373 | Replication group gname does not exist
nonmasterdef | -23312 | Calling site is not the master definition site

#### 16.4.3.1.2 Restrictions

Note the following restrictions on calling ADD_SITE_PRIORITY_SITE:

- You must call the ADD_SITE_PRIORITY_SITE procedure from the master definition site.

- The new priority must be unique within the site priority group.

#### 16.4.3.1.3 Example

This example adds four sites to the site priority group SP_NORTH_AMERICA:

>     	BEGIN
>     		DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
>     			gname		=> 'SPROCKET',
>     			name		=> 'SP_NORTH_AMERICA',
>     			site		=> 'D7OH.BIGWHEEL.COM',
>     			priority	=> 10);
>     		DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
>     			gname		=> 'SPROCKET',
>     			name		=> 'SP_NORTH_AMERICA',
>     			site		=> 'D7NY.BIGWHEEL.COM',
>     			priority	=> 20);
>     		DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
>     			gname		=> 'SPROCKET',
>     			name		=> 'SP_NORTH_AMERICA',
>     			site		=> 'D7HI.BIGWHEEL.COM',
>     			priority	=> 30);
>     		DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
>     			gname		=> 'SPROCKET',
>     			name		=> 'SP_NORTH_AMERICA',
>     			site		=> 'D7CA.BIGWHEEL.COM',
>     			priority	=> 40);
>     	END;

> TIP: As with the ADD_PRIORITY_<datatype> procedure, it is a good idea to use multiples of 10 or more for the priority values so that you have some flexibility for future changes and additions.

After making these calls, we can query DBA_REPPRIORITY to confirm that Oracle added the sites.

>     SQL>  SELECT		gname,
>       2  			priority_group,
>       3  			varchar2_value		site_name,
>       4  			priority
>       5  FROM		dba_reppriority
>       6  WHERE		priority_group = 'SP_NORTH_AMERICA'
>       7  ORDER BY		priority;

>     GNAME	 PRIORITY_GROUP      SITE_NAME 	   	  PRIORITY
>     -------- -------------------- -------------------- --------
>     SPROCKET SP_NORTH_AMERICA     D7OH.BIGWHEEL.COM 	10
>     SPROCKET SP_NORTH_AMERICA     D7NY.BIGWHEEL.COM 	20
>     SPROCKET SP_NORTH_AMERICA     D7HI.BIGWHEEL.COM 	30

Although DBA_REPPRIORITY reflects the changes from the ADD_SITE_PRIORITY_SITE call, you must call DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for an object in the replication group for the changes to propagate to the other master sites. This is the case for all of the DBMS_REPCAT procedures pertaining to site priority groups.

#### 16.4.3.2 The DBMS_REPCAT.DROP_SITE_PRIORITY_SITE procedure

The DROP_SITE_PRIORITY_SITE procedure removes a site from a site priority group. Specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.DROP_SITE_PRIORITY_SITE
>       (gname IN VARCHAR2 := '',
>        name IN VARCHAR2,
>        site IN VARCHAR2,
>        sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.DROP_SITE_PRIORITY_SITE
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         site IN VARCHAR2);

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the replication group to which site priority group name belongs
name | Name of the site priority group
site | Global name of the new site
sname (Oracle7 only) | Not used

#### 16.4.3.2.1 Exceptions

The DROP_SITE_PRIORITY_SITE procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
missingpriority | -1403 | Site priority does not exist
missingrepgroup | -23373 | Replication group gname does not exist
nonmasterdef | -23312 | Calling site is not the master definition site

#### 16.4.3.2.2 Restrictions

You must call DROP_SITE_PRIORITY_SITE from the master definition site.

#### 16.4.3.2.3 Example

Here we drop D7TX.BIGWHEEL.COM from the SP_NORTH_AMERICA site priority:

>     	BEGIN
>     		DBMS_REPCAT.DROP_SITE_PRIORITY_SITE(
>     			gname	=> 'SPROCKET',
>     			name	=> 'SP_NORTH_AMERICA',
>     			site	=> 'D7TX.BIGWHEEL.COM');
>     	END;

As with the other site priority procedures, you must call GENERATE_REPLICATION_SUPPORT for an object in the replication group to propagate this change to other master sites.

#### 16.4.3.3 The DBMS_REPCAT.ALTER_SITE_PRIORITY procedure

Just as you can change the priority of a value in a priority group, you can change the priority of a site in a site priority group. Use the ALTER_SITE_PRIORITY procedure to do this. The specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.ALTER_SITE_PRIORITY
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         old_priority IN NUMBER,
>         new_priority IN NUMBER,
>         sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.ALTER_SITE_PRIORITY
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         old_priority IN NUMBER,
>         new_priority IN NUMBER);
>         site IN VARCHAR2);

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | Name of the replication group to which the site priority group name belongs
name | Name of the site priority group
old_priority | Site's current priority
new_priority | Site's new priority
sname (Oracle7 only) | Not used

#### 16.4.3.3.1 Exceptions

The ALTER_SITE_PRIORITY procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
duplicatepriority | -1 | Priority new_priority already exists for the site priority group name
missingpriority | -1403 | Priority old_priority is not associated with any sites
missingrepgroup | -23373 | Replication group gname does not exist
missingvalue | -23337 | Value old_value does not already exist
nonmasterdef | -23312 | Calling site is not the master definition site
paramtype | -23325 | Parameter new_value is incorrect datatype

#### 16.4.3.3.2 Restrictions

Note the following restrictions on calling ALTER_SITE_PRIORITY:

- You must run this procedure from the master definition site.

- The new priority must be unique within the site priority group.

#### 16.4.3.3.3 Example

In this example, we move D7NY.BIGWHEEL.COM (from a previous example) to a higher priority, between D7HI.BIGWHEEL.COM and D7CA.BIGWHEEL.COM:

>     	BEGIN
>     		DBMS_REPCAT.ALTER_SITE_PRIORITY(
>     			gname		=> 'SPROCKET',
>     			name		=> 'SP_NORTH_AMERICA',
>     			old_priority	=> 20,
>     			new_priority	=> 35);
>     	END;

And, querying the DBA_REPPRIORITY data dictionary view again, we see the changed data (shown here in boldface):

>     SQL>  SELECT		gname,
>       2  			priority_group,
>       3  			varchar2_value		site_name,
>       4  			priority
>       5  FROM		dba_reppriority
>       6  WHERE		priority_group = 'SP_NORTH_AMERICA'
>       7  ORDER BY		priority;

>     GNAME	 PRIORITY_GROUP      SITE_NAME 	   	  PRIORITY
>     -------- -------------------- -------------------- --------
>     SPROCKET SP_NORTH_AMERICA     D7OH.BIGWHEEL.COM 	10
>     SPROCKET SP_NORTH_AMERICA     D7HI.BIGWHEEL.COM 	30
>     SPROCKET SP_NORTH_AMERICA     D7NY.BIGWHEEL.COM 	35
>     SPROCKET SP_NORTH_AMERICA     D7CA.BIGWHEEL.COM 	40

This shows D7NY.BIGWHEEL.COM with its new and higher priority.

#### 16.4.3.4 The DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE

The ALTER_SITE_PRIORITY_SITE procedure is analogous to the DBMS_REPCAT.ADD_PRIORITY_<datatype> procedure; use it to change the site name for an existing named site in a site priority group. The specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

>     PROCEDURE DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         old_site IN VARCHAR2,
>         new_site IN VARCHAR2,
>         sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

>     PROCEDURE DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE
>        (gname IN VARCHAR2 := '',
>         name IN VARCHAR2,
>         old_site IN VARCHAR2,
>         new_site IN VARCHAR2);

Parameters are summarized in the following table:

Name | Description
--- | ---
gname | Name of the replication group to which the site priority group name belongs
name | Name of the site priority group
old_site | Global name of the site currently associated with the priority level
new_site | Global name of the site that is to replace old_site at old_site's priority level
sname (Oracle7 only) | Not used

#### 16.4.3.4.1 Exceptions

The ALTER_SITE_PRIORITY_SITE procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
duplicatesite | -1 | new_site is already in the site priority group
missingpriority | -1403 | Site priority group name does not exist
missingrepgroup | -23373 | Replication group gname does not exist
missingvalue | -23337 | old_site is not in the site priority group
nonmasterdef | -23312 | Calling site is not the master definition site

#### 16.4.3.4.2 Restrictions

Note the following restrictions on calling ALTER_SITE_PRIORITY_SITE:

- You must call this procedure from the master definition site.

- The new site must be unique in the site priority group.

#### 16.4.3.4.3 Example

In this example, we replace the site associated with priority level 10 with D7TX.BIGWHEEL.COM:

>     	BEGIN
>     		DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE(
>     			gname		=> 'SPROCKET',
>     			name		=> 'SP_NORTH_AMERICA',
>     			old_site	=> 'D7OH.BIGWHEEL.COM',
>     			new_site	=> 'D7TX.BIGWHEEL.COM');
>     	END;

And again, we can see the change in DBA_REPPRIORITY:

>     SQL>  SELECT		gname,
>       2  			priority_group,
>       3  			varchar2_value		site_name,
>       4  			priority
>       5  FROM		dba_reppriority
>       6  WHERE		priority_group = 'SP_NORTH_AMERICA'
>       7  ORDER BY		priority;

>     GNAME	 PRIORITY_GROUP      SITE_NAME 	   	  PRIORITY
>     -------- -------------------- -------------------- --------
>     SPROCKET SP_NORTH_AMERICA     D7TX.BIGWHEEL.COM 	10
>     SPROCKET SP_NORTH_AMERICA     D7HI.BIGWHEEL.COM         30
>     SPROCKET SP_NORTH_AMERICA     D7NY.BIGWHEEL.COM         35
>     SPROCKET SP_NORTH_AMERICA     D7CA.BIGWHEEL.COM         40

16.3 Priority Groups with DBMS_REPCAT |  | 16.5 Assigning Resolution Methods with DBMS_REPCAT

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