The site priority group technique resolves conflicts by accepting the data that originated from the site with the highest priority.
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 |
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.
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 |
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 |
You must call DBMS_REPCAT.DEFINE_SITE_PRIORITY from the master definition site.
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.
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.
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 |
You must call DBMS_REPCAT.DROP_SITE_PRIORITY from the master definition site.
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;
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 |
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 |
You must call DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY from the master definition site.
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;
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.
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 |
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 |
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.
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.
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 |
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 |
You must call DROP_SITE_PRIORITY_SITE from the master definition site.
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.
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 |
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 |
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.
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.
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 |
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 |
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.
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
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
This HTML Help has been published using the chm2web software. |