| Oracle7 Server Distributed Systems Manual, Vol. 2 |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Distributed Systems Manual, Vol. 2 | Library |
Product |
Contents |
Index |
This chapter covers the following topics:
Additional Information: For an overview of conflict resolution routines, see Writing a Conflict Resolution Routine
![[*]](jump.gif)
.
Attention: If one (or more) rows in a transaction causes a conflict that remains unresolved, the entire transaction is written to the error log. Subsequent transactions that depend on the original transaction can now conflict, and in turn, be written to the error log.
If one or more sites in your replicated environment propagate changes asynchronously, conflicts can occur if two or more sites update the same replicated data. Even if your environment is designed to avoid conflicts (for example, by partitioning data ownership), it is prudent to
If all of your sites propagate changes synchronously and you have no updatable snapshot sites, conflicting updates cannot occur, and you do not need to designate a conflict resolution method.
For each changed row, Oracle forwards
As shown in Figure 6 - 1, Oracle at the receiving site compares the old and current values of the row. Oracle detects a conflict if there are any differences between these values for any column in the row.
Note: Because a row can have different ROWIDs at different sites, Oracle uses the row's primary key to determine which rows to compare. If you do not want to use the primary key, designate one or more different columns by calling DBMS_REPCAT.SET_COLUMNS, as described .
Figure 6 - 1. Detecting Conflicts
If the procedure at the receiving site detects no conflict, the server at the receiving site writes the new value(s).
If a conflict is detected, Oracle applies the appropriate conflict resolution routine, if one is available. Any unresolved conflicts are logged in the DefError view at the receiving site.
When you replicate a table using row-level replication, you can designate one or more conflict resolution methods. Oracle applies these methods in the priority order you define until the conflict is resolved, or no more routines are available.
Note: For procedural replication, you must supply a conflict resolution method as part of your replicated procedure.
A uniqueness conflict is detected if a unique constraint is violated during an INSERT or UPDATE of the replicated row.
A delete conflict is detected if you change a row at a remote site after you delete that row from the local site. The delete conflict occurs because the old values of the deleted row at the local site do not match the current values of the same row at the remote site.
Warning: Because the primary key is used to determine which rows to compare, allowing modifications to the primary key is extremely risky to the integrity of your data.
The conflict detection mechanism detects update conflict column group by column group, so all columns must be a part of some column group.
You do not have to assign all of the columns in a table to a column group. However, you can only designate a conflict resolution method for the columns you assign to a column group.
Attention: If two or more columns in a table must remain consistent with respect to each other, place these columns within the same column group.
These system-defined routines do not support the following situations:
| Any Number of Master Sites (4 methods) | One or Two Master Sites (9 methods) | One Master Site and Multiple Snapshots Sites (12 methods) |
| latest timestamp | ||
| additive | ||
| minimum value (always decreasing) | ||
| maximum value (always increasing) | ||
| earliest timestamp | ||
| minimum value | ||
| maximum value | ||
| highest priority site | ||
| highest priority value | ||
| average | ||
| discard from snapshot sites | ||
| overwrite master site | ||
Note: The conflict resolution methods you assign need to ensure data convergence and provide results that are appropriate for how your business uses the data.
.If your replicated environment has more than two masters:
Note: The highest priority is given to the site with the highest priority value. Priority values can be any Oracle number and do not have to be consecutive integers.
Suggestion: To help determine which conflict resolution method to use, make a diagram or time-action table (such as Table 6 - 2) to help uncover any potential loopholes in your conflict resolution methodology.
Your preferred conflict resolution method might not always succeed. You can specify a backup method to have a greater chance of conflict resolution without manual intervention.
Some system-defined resolution methods, such as latest timestamp, occasionally require a backup method to successfully resolve conflicts. (Site priority is a possible backup method.) The latest timestamp method uses a special timestamp column to determine and apply the most recent change. In the unlikely event that the row at the originating site and the row at another site change at precisely the same second, you must provide a backup method.
Note: Oracle stores time to a granularity of one second.
You can also provide a user-defined method that records conflict information or notifies the DBA if the conflict cannot be resolved. You can arrange to receive notification for all conflicts, or for only those conflicts that cannot be resolved. You can mix any number of user-defined and system-defined conflict resolution routines.
If the new value of the designated column is less than the current value, the column group values from the originating site are applied at the destination site (assuming that all other errors were successfully resolved for the row). If the new value of the designated column is greater than the current value, the conflict is resolved by leaving the current values of the column group unchanged.
Note: If the two values for the designated column are the same (for example, if the designated column was not the column causing the conflict), the conflict is not resolved, and the values of the columns in the column group remain unchanged. Designate a backup conflict resolution method to be used in this case.
The maximum value method is the same as the minimum value method, except that the values from the originating site are only applied if the value of the designated column at the originating site is greater than the value of the designated column at the destination site.
There are no restrictions on the datatypes of the columns in the column group. Convergence for more than two master sites is only guaranteed if
Consider this sequence of events:
Oracle does not enforce time synchronization, which should be provided by another mechanism.
Note: A sample timestamp and site site maintenance trigger is shown in the Example section (see page 6 - 44).
The earliest timestamp method applies the changes from the site with the earliest timestamp, and the latest timestamp method applies the changes from the site with the latest timestamp.
Suggestion: Designate a backup method, such as site priority, to be called if two sites have the same timestamp. Standardize your timestamping mechanism; for example, you can convert the timestamp to a designated time zone, such as Greenwich Mean Time (GMT).
A clock counts seconds as an increasing value. Assuming that you have properly designed your timestamping mechanism and established a backup method in case two sites have the same timestamp, the latest timestamp method (like the maximum value method) guarantees convergence. The earliest timestamp method, however, cannot guarantee convergence for more than two masters.
The additive routine adds the difference between the old and new values at the originating site to the current value at the destination site.
current value = current value + (new value - old value)
The additive conflict resolution method provides convergence for any number of master sites.
The average conflict resolution method averages the new column value from the originating site with the current value at the destination site.
current value = (current value + new value)/2
The average method cannot guarantee convergence if your replicated environment has more than one master. This method is useful for an environment with a single master site and multiple updatable snapshots.
As shown in Figure 6 - 2, the RepPriorityView displays the priority level assigned to each value that the "priority" column can contain. You must specify a priority level for all possible values of the "priority" column.
Figure 6 - 2. Using Priority Groups
When you select the priority group method of conflict resolution, you must designate which column in your table is the "priority" column.
The RepPriority view displays the values of all priority groups defined at the current location. In the example shown in Figure 6 - 2, there are two different priority groups, site-priority and order-status. The customer table is using the site-priority priority group.
Site priority is a special kind of priority group. With site priority, the "priority" column that you designate is automatically updated with the global database name of the site where the update originated. The RepPriorityView displays the priority level assigned to each database site. Site priority can be useful if one site is considered to be more likely to have the most accurate information.
For example, in Figure 6 - 2, the New York site (priority value = 2) is corporate headquarters, while the Houston site (priority value = 1) is a sales office.
The headquarters office (New York = 2) is considered more likely than the sales office (Houston = 1) to have the most accurate information about the credit that can be extended to each customer.
Note: The priority-group column of the RepPriority view shows both the site-priority group and the order-status group.
When you are using site priority, convergence with more than two masters is not guaranteed. You can guarantee convergence with more than two masters when you are using priority groups, however, if the value of the "priority" column is always increasing. That is, the values in the priority column correspond to an ordered sequence of events; for example: ordered, shipped, billed.
For example, if you have a single master site that you expect to be used primarily for queries, with all updates being performed at the snapshot sites, you might select the overwrite method. The overwrite and discard methods are also useful if:
). Both append site name and append sequence can be used on character columns only.These methods can be useful when the availability of the data may be more important than the complete accuracy of the data. To allow data to be available as soon as it is replicated
Note: Similarly, the append sequence routine appends a generated sequence number to the column value. The column value is truncated as needed. If the generated portion of the column value exceeds the column length, the conflict routine does not resolve the error.
Unlike the append methods, the discard uniqueness method minimizes the propagation of data until data accuracy can be verified.
You can set up notification to occur when you want it:
. Modify and/or expand upon the example with your own conflict resolution routines. Note: If the conflict cannot ultimately be resolved, the entire transaction, including any updates to a notification table, will be rolled back. You can design your notification mechanism to use the Oracle DBMS_PIPES package or the interface to Oracle Office to ensure that notification occurs.
To declare a conflict resolution method, first complete the planning phase:
.
.
. . Modify and/or expand upon the example with your own conflict resolution routines.
Dynamic ownership is most useful in cases in which
The timestamp method is particularly useful because the data will converge regardless of the number of sites, but special care must be taken:
Note: The conflict resolution methods you assign need to ensure data convergence and provide results that are appropriate for how your business uses the data.
Instead, the application should mark a row as deleted (for example, by using a timestamp column that is filled in only upon delete). Periodically, the rows marked as deleted can be purged from the system using procedural replication, as described .
Suggestion: Make a table (or a diagram) similar to that shown to analyze the implications of the conflict resolution methods you select.
DBMS_REPCAT.MAKE_COLUMN_GROUP(
sname => 'acct',
oname => 'inv',
column_group => 'address',
list_of_column_names => 'addr1, addr2, city, state, zip');This example creates a column group called ADDRESS that consists of the ADDR1, ADDR2, CITY, STATE, and ZIP columns in the INV table.
To create a column group consisting of all of the columns in the table, you simply pass an asterisk (*) as the final argument to the call. You must call this procedure from the master definition site. Your changes take effect when you generate replication support for the table.
Additional Information: The parameters for the MAKE_COLUMN_GROUP procedure are described in Table 12 - 149, and the exceptions are listed in Table 12 - 150.
DBMS_REPCAT.ADD_GROUPED_COLUMN(
sname => 'acct',
oname => 'inv',
column_group => 'address',
list_of_column_names => 'phone, fax');This example adds the columns PHONE and FAX to the ADDRESS column group created in a previous example. To add all of the columns in the table to the column group, you could specify '*' as the LIST_OF_COLUMN_NAMES value.
You must call this procedure from the master definition site. Your changes take effect when you generate replication support for the table.
Additional Information: The parameters for the ADD_GROUPED_COLUMN procedure are described in Table 12 - 62, and the exceptions are listed in Table 12 - 63.
DBMS_REPCAT.DROP_GROUPED_COLUMN(
sname => 'acct',
oname => 'inv',
column_group => 'address',
list_of_column_names => 'phone, fax');This example removes the columns PHONE and FAX from the ADDRESS column group. To remove all of the columns in the table from the column group, you could specify '*' as the LIST_OF_COLUMN_NAMES value.
You must call this procedure from the master definition site. Your changes take effect when you generate replication support for the table.
Additional Information: The parameters for the DROP_GROUPED_COLUMN procedure are described in Table 12 - 119, and the exceptions are listed in Table 12 - 120.
DBMS_REPCAT.DROP_COLUMN_GROUP(
sname => 'acct',
oname => 'inv',
column_group => 'address');This example drops the ADDRESS column group associated with the INV table.
You must call this procedure from the master definition site. Your changes take effect when you generate replication support for the table.
Additional Information: The parameters for the DROP_COLUMN_GROUP procedure are described in Table 12 - 117, and the exceptions are listed in Table 12 - 118.
DBMS_REPCAT.DEFINE_COLUMN_GROUP(
sname => 'acct',
oname => 'inv',
gname => 'address');This example creates the ADDRESS column group associated with the INV table. This column group has no members. You must call the ADD_GROUPED_COLUMN procedure to add members to this group.
You must call this procedure from the master definition site. Your changes take effect when you generate replication support for the table.
Additional Information: The parameters for the DEFINE_COLUMN_GROUP procedure are described in Table 12 - 109, and the exceptions are listed in Table 12 - 110.
You must call these procedures from the master definition site. The conflict resolution method that you specify is not actually added until after the next time you generate replication support for the table.
You can designate multiple conflict resolution methods for a single column group, table, or constraint. If you provide multiple methods, they are applied in sequential order until the conflict is resolved or no more methods are available. You must provide a sequence order for each method that you add.
You can either designate one of the standard methods provided with the symmetric replication facility, or you can provide the name of a function that you have written yourself. Instructions for writing your own conflict resolution routine are provided . If you write your own conflict resolution routine, you must call CREATE_MASTER_REPOBJECT for this function to ensure that it exists at each master site.
For example, to indicate that you first want to use the TIMESTAMP method to resolve UPDATE conflicts with the ADDRESS column group, you would make a procedure call similar to the following:
DBMS_REPCAT.ADD_UPDATE_RESOLUTION(
sname => 'acctg',
oname => 'orders',
column_group => 'address',
sequence_no => 1,
method => 'TIMESTAMP',
parameter_column_name => 'update_time');Additional Information: The parameters for the ADD_UPDATE_RESOLUTION, ADD_DELETE_RESOLUTION, and ADD_UNIQUE_RESOLUTION procedures are described in Table 12 - 70, and the exceptions are listed in Table 12 - 71.
DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE( sname => 'acct_rec',
oname => 'inventory');
You must call this procedure from the master definition site for the given replicated object. Oracle must successfully create the necessary packages at the master definition site for this procedure to complete successfully. These objects are asynchronously created at the other master sites as described .
Additional Information: The parameters for the GENERATE_REPLICATION_PACKAGE procedure are described in Table 12 - 143, and the exceptions are listed in Table 12 - 144.
These procedures must be called from the master definition site. The routine you designate is not actually dropped from usage until after the next time you generate replication support for the table.
The following example drops the TIMESTAMP resolution method for the ADDRESS column group:
DBMS_REPCAT.DROP_UPDATE_RESOLUTION(
sname => 'acctg',
oname => 'orders',
column_group => 'address',
sequence_no => 1);Additional Information: The parameters for the DROP_UPDATE_RESOLUTION, DROP_DELETE_RESOLUTION, and DROP_UNIQUE_RESOLUTION procedures are described in Table 12 - 139, and the exceptions are listed in Table 12 - 140.
You must indicate which column in a table is associated with a particular priority group when you add the priority group conflict resolution routine for the table. The priority group must therefore contain all possible values for all columns associated with that priority group.
For example, suppose that you had a table, INVENTORY, with a column of type VARCHAR2, STATUS, that could have three possible values: ORDERED, SHIPPED, and BILLED. Now suppose that you want to resolve update conflicts based upon the value of this STATUS column.
DBMS_REPCAT.MAKE_COLUMN_GROUP( sname => 'acct', oname => 'inventory', column_group => 'all', list_of_column_names => '*');
DBMS_REPCAT.DEFINE_PRIORITY_GROUP( sname => 'acct', pgroup => 'status', datatype => 'varchar2');
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( sname => 'acct', pgroup => 'status', value => 'ordered', priority => 1);
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( sname => 'acct', pgroup => 'status', value => 'shipped', priority => 2);
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( sname => 'acct', pgroup => 'status', value => 'billed', priority => 3);
DBMS_REPCAT.ADD_UPDATE_RESOLUTION( sname => 'acct', oname => 'inventory', column_group => 'all', sequence_no => 1, method => 'PRIORITY_GROUP', parameter_column_name => 'status');
The next several sections describe how to manage priority groups.
DBMS_REPCAT.DEFINE_PRIORITY_GROUP( gname => 'acct', pgroup => 'status', datatype => 'varchar2');
This example creates a priority group called STATUS for the ACCT object group. The members of this priority group will have values of type VARCHAR2.
You must call this procedure from the master definition site. The member is not added to the priority group until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
Additional Information: The parameters for the DEFINE_PRIORITY_GROUP procedure are described in Table 12 - 111, and the exceptions are listed in Table 12 - 112.
You must call this procedure from the master definition site. The value is synchronously available at the master definition site, but is not available at any other master sites until you run GENERATE_REPLICATION_SUPPORT. If you are modifying a priority group that is already in use, call the procedures in the following order to ensure proper resolution of conflicts:
DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'acct', pgroup => 'status', value => 'shipped', priority => 2);
Additional Information: The parameters for the ADD_PRIORITY_type procedures are described in Table 12 - 66, and the exceptions are listed in Table 12 - 67.
You must call this procedure from the master definition site. The value is synchronously available at the master definition site, but is not available at any other master sites until you run GENERATE_REPLICATION_SUPPORT. If you are modifying a priority group that is already in use, call the procedures in the following order to ensure proper resolution of conflicts:
The following example changes the recognized value of items at priority level 2 from SHIPPED to IN_SHIPPING:
DBMS_REPCAT.ALTER_PRIORITY_VARCHAR2( gname => 'acct', pgroup => 'status', old_value => 'shipped', new_value => 'in_shipping');
Additional Information: The parameters for the ALTER_PRIORITY_type procedures are described in Table 12 - 78, and the exceptions are listed in Table 12 - 79.
You must call this procedure from the master definition site. The priority level of the member is not altered until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
The following example changes the priority of items marked as IN_SHIPPING from level 2 to level 4:
DBMS_REPCAT.ALTER_PRIORITY( gname => 'acct', pgroup => 'status', old_priority => 2, new_priority => 4);
Additional Information: The parameters for the ALTER_PRIORITY procedure are described in Table 12 - 76, and the exceptions are listed in Table 12 - 77.
You must call this procedure from the master definition site. The member is not actually removed from the priority group until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
In the following example, IN_SHIPPING is no longer a valid state for items in the STATUS priority group:
DBMS_REPCAT.DROP_PRIORITY_VARCHAR2( gname => 'acct', pgroup => 'status', value => 'in_shipping');
Additional Information: The parameters for the DROP_PRIORITY_type procedures are described in Table 12 - 129, and the exceptions are listed in Table 12 - 130.
You must call this procedure from the master definition site. The member is not actually removed from the priority group until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
In the following example, IN_SHIPPING (which was assigned to priority level 4) is no longer a valid state for items in the STATUS priority group:
DBMS_REPCAT.DROP_PRIORITY( gname => 'acct', pgroup => 'status', priority_num => 4);
Additional Information: The parameters for the DROP_PRIORITY procedure are described in Table 12 - 125, and the exceptions are listed in Table 12 - 126.
DBMS_REPCAT.DROP_PRIORITY_GROUP( gname => 'acct', pgroup => 'status');
In this example, STATUS is no longer a valid priority group.
Attention: Before calling this procedure, you must call the DROP_UPDATE_RESOLUTION procedure for any column groups in the replicated object group that are using the PRIORITY GROUP conflict resolution method with this priority group. You can determine which column groups are affected by querying the RepResolution view.
You must call this procedure from the master definition site. The priority group is not actually dropped until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
Additional Information: The parameters for the DROP_PRIORITY_GROUP procedure are described in Table 12 - 127, and the exceptions are listed in Table 12 - 128.
If you have chosen to use the site priority method to resolve update conflicts, you must first create a site priority group before you can add this conflict resolution method for a column group. Creation of a site priority group consists of two steps.
The next several sections describe how to manage site priority groups.
DBMS_REPCAT.DEFINE_SITE_PRIORITY( gname => 'acct', name => 'site');
This example creates a site priority group called SITE for the ACCT object group.
You must call this procedure from the master definition site. The necessary support for site priority is not generated until you call GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
Additional Information: The parameters for the DEFINE_SITE_PRIORITY procedure are described in Table 12 - 113, and the exceptions are listed in Table 12 - 114.
DBMS_REPCAT.ADD_SITE_PRIORITY_SITE( gname => 'acct', name => 'site', site => 'hq.widgetek.com', priority => 100);
This example adds the HQ site to the SITE group and sets its priority level to 100.
Note: The highest priority is given to the site with the highest priority value. Priority values do not have to be consecutive integers.
You must call this procedure from the master definition site. The site is not added to the group until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
Additional Information: The parameters for the ADD_SITE_PRIORITY_SITE procedure are described in Table 12 - 68, and the exceptions are listed in Table 12 - 69.
DBMS_REPCAT.ALTER_SITE_PRIORITY( gname => 'acct', name => 'site', old_priority => 100, new_priority => 200);
This example changes the priority level of a site in the SITE group from 100 to 200.
Note: The highest priority is given to the site with the highest priority value. Priority values do not have to be consecutive integers.
You must call this procedure from the master definition site. The priority level is not actually updated until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
Additional Information: The parameters for the ALTER_SITE_PRIORITY procedure are described in Table 12 - 80, and the exceptions are listed in Table 12 - 81.
DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE( gname => 'acct', name => 'site', old_site => 'hq.widgetek.com', new_site => 'hq.widgetworld.com);
This example changes the global database name of the HQ site to HQ.WIDGETWORLD.COM, while its priority level remains the same.
You must call this procedure from the master definition site. The site name is not actually updated until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
Additional Information: The parameters for the ALTER_SITE_PRIORITY_SITE procedure are described in Table 12 - 82, and the exceptions are listed in Table 12 - 83.
DBMS_REPCAT.DROP_SITE_PRIORITY_SITE( gname => 'acct', name => 'site', site => 'hq.widgetek.com');
This example drops the HQ site from the SITE group.
You must call this procedure from the master definition site. The site is not actually removed from the group until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
Additional Information: The parameters for the DROP_SITE_PRIORITY_SITE procedure are described in Table 12 - 133, and the exceptions are listed in Table 12 - 134.
to drop a site from a site priority group by priority level.
DBMS_REPCAT.DROP_SITE_PRIORITY( gname => 'acct', name => 'site');
In this example, SITE is no longer a valid site priority group.
Attention: Before calling this procedure, you must call the DROP_UPDATE_RESOLUTION procedure for any column groups in the replicated object group that are using the SITE PRIORITY conflict resolution method with this site priority group. You can determine which column groups are affected by querying the RepResolution view.
You must call this procedure from the master definition site. The site priority group is not actually dropped until you call the procedure GENERATE_REPLICATION_SUPPORT for any table in the object group (since there is no group-level equivalent for this command).
Additional Information: The parameters for the DROP_SITE_PRIORITY procedure are described in Table 12 - 131, and the exceptions are listed in Table 12 - 132.
Each view has three versions: USER_*, ALL_*, SYS.DBA_*. The views available include the following:
RepResolution_Method
Lists all of the available conflict resolution methods.
RepColumn_Group
Lists all of the column groups defined for the database.
RepGrouped_Column
Lists all of the columns in each column group in the database.
RepPriority_Group
Lists all of the priority groups and site priority groups defined for the database.
RepPriority
Lists the values and corresponding priority levels for each priority or site priority group.
RepConflict
Lists the types of conflicts (delete, update, or uniqueness) for which you have specified a resolution method, for the tables, column groups, and unique constraints in the database.
RepResolution
Shows more specific information about the conflict resolution method used to resolve conflicts on each object.
RepParameter_Column
Shows which columns are used by the conflict resolution routines to resolve a conflict.
Suppose that you allowed multiple sites to update your CUSTOMER table, and want to set up conflict resolution as follows:
CREATE TABLE customers ( custno NUMBER(4) PRIMARY KEY, last_name VARCHAR2(10), first_name VARCHAR2(10), addr1 VARCHAR2(30), addr2 VARCHAR2(30), amount NUMBER(7,2), timestamp DATE, site VARCHAR2(128), CONSTRAINT c_cust_name UNIQUE (last_name, first_name) )
CREATE TABLE conf_report ( line NUMBER(2), --- used to order message text txt VARCHAR2(80), --- conflict notification message timestamp DATE, --- time of conflict table_name VARCHAR2(30), --- table in which the conflict occurred table_owner VARCHAR2(30), --- owner of the table conflict_type VARCHAR2(6) --- INSERT, DELETE or UNIQUE )
Note: This example of conflict notification does not resolve any conflicts. You should either provide a method to resolve conflicts (for example, discard or overwrite), or provide a notification mechanism that will succeed (for example, using e-mail) even if the error is not resolved and the transaction is rolled back.
CREATE OR REPLACE PACKAGE notify AS
--- Report uniqueness constraint violations on customer table
FUNCTION customer_unique_violation(first_name IN OUT VARCHAR2,
last_name IN OUT VARCHAR2,
discard_new_values IN OUT BOOLEAN)
RETURN BOOLEAN;
END notify;
/
CREATE OR REPLACE PACKAGE BODY notify AS --- Define a PL/SQL table to hold the notification message TYPE message_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
PROCEDURE report_conflict(conflict_report IN MESSAGE_TABLE,
report_length IN NUMBER,
conflict_time IN DATE,
conflict_table IN VARCHAR2,
table_owner IN VARCHAR2,
conflict_type IN VARCHAR2) IS
BEGIN
FOR idx IN 1..report_length LOOP
BEGIN
INSERT INTO off_shore_accounts.conf_report
(line, txt, timestamp, table_name, table_owner, conflict_type)
VALUES (idx, SUBSTR(conflict_report(idx),1,80), conflict_time,
conflict_table, table_owner, conflict_type);
EXCEPTION WHEN others THEN NULL;
END;
END LOOP;
END report_conflict;
--- This is the conflict resolution routine that will be called first
--- when a uniqueness constraint violated is detected in the customer
--- table.
FUNCTION customer_unique_violation(first_name IN OUT VARCHAR2,
last_name IN OUT VARCHAR2,
discard_new_values IN OUT BOOLEAN)
RETURN BOOLEAN IS
local_node VARCHAR2(128);
conf_report MESSAGE_TABLE;
conf_time DATE := SYSDATE;
BEGIN
--- Get the global name of the local site
BEGIN
SELECT global_name INTO local_node FROM global_name;
EXCEPTION WHEN others THEN local_node := '?';
END;
--- Generate a message for the DBA
conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE CUSTOMER ON ' ||
TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
conf_report(2) := ' AT NODE ' || local_node;
conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING' ||
' APPEND SEQUENCE METHOD';
conf_report(4) := 'FIRST NAME: ' || first_name;
conf_report(5) := 'LAST NAME: ' || last_name;
conf_report(6) := NULL;
--- Report the conflict
report_conflict(conf_report, 5, conf_time, 'CUSTOMER',
'OFF_SHORE_ACCOUNTS', 'UNIQUE');
--- Do not discard the new column values. They are still needed by
--- other conflict resolution routines
discard_new_values := FALSE;
--- Indicate that the conflict was not resolved.
RETURN FALSE;
END customer_unique_violation;
END notify;
/
--- Create the replicated object group
dbms_repcat.create_master_repgroup(gname => 'ORDER_ENTRY',
group_comment => 'Foreign Orders',
master_comment => 'Main Office: NY');
--- Register customer as a replicated object.
dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY',
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CUSTOMERS',
type => 'TABLE');
--- Replication only the DDL for the notification table. Do NOT generate
--- replication support on this table
dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY',
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CONF_REPORT',
type => 'TABLE');
--- Register Notify package and body as a replicated object.
dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY',
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'NOTIFY',
type => 'PACKAGE');
dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY',
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'NOTIFY',
type => 'PACKAGE BODY');
The column group for the AMOUNT column is assigned the ADDITIVE method. The other column group is assigned the TIMESTAMP method with SITE PRIORITY as a back-up method. This example defines the priority of two sites.
--- Indicate the columns to resolve if a conflict is detected
--- Primary key is not in a user-defined column group
dbms_repcat.make_column_group(
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CUSTOMERS',
column_group => 'CG_CUSTOMERS',
list_of_column_names => 'LAST_NAME,FIRST_NAME,' ||
'ADDR1,ADDR2,' ||
'TIMESTAMP,SITE');
dbms_repcat.make_column_group(
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CUSTOMERS',
column_group => 'CG_CUST_AMT',
list_of_column_names => 'AMOUNT');
--- make priority group for site priority
dbms_repcat.define_site_priority(
gname => 'ORDER_ENTRY',
name => 'SITE PRIORITY',
comment => 'site priority for customers');
--- add values to site priority group
dbms_repcat.add_site_priority_site(
gname => 'ORDER_ENTRY',
name => 'SITE PRIORITY',
site => 'DBS1.REGRESS.RDBMS.DEV.US.ORACLE.COM',
priority => 200);
dbms_repcat.add_site_priority_site(
gname => 'ORDER_ENTRY',
name => 'SITE PRIORITY',
site => 'DBS2.REGRESS.RDBMS.DEV.US.ORACLE.COM',
priority => 100);
--- define update resolution for Amount column
dbms_repcat.add_update_resolution(
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CUSTOMERS',
column_group => 'CG_CUST_AMT',
sequence_no => 1,
method => 'ADDITIVE',
parameter_column_name => 'AMOUNT');
--- define timestamp with site-priority backup for other column group
dbms_repcat.add_update_resolution(
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CUSTOMERS',
column_group => 'CG_CUSTOMERS',
sequence_no => 1,
method => 'LATEST TIMESTAMP',
parameter_column_name => 'TIMESTAMP');
dbms_repcat.add_update_resolution(
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CUSTOMERS',
column_group => 'CG_CUSTOMERS',
sequence_no => 2,
method => 'SITE PRIORITY',
parameter_column_name => 'SITE',
priority_group => 'SITE PRIORITY');
--- register a user-defined resolution routine for notification dbms_repcat.add_unique_resolution( sname => 'OFF_SHORE_ACCOUNTS', oname => 'CUSTOMERS', constraint_name => 'C_CUST_NAME', sequence_no => 1, method => 'USER FUNCTION', comment => 'Notify DBA', parameter_column_name => 'FIRST_NAME,LAST_NAME', function_name =>'OFF_SHORE_ACCOUNTS.NOTIFY.CUSTOMER_UNIQUE_VIOLATION');
--- register a system-defined resolution routine for non-convergent
--- resolution of the uniqueness conflict.
dbms_repcat.add_unique_resolution(
sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CUSTOMERS',
constraint_name => 'C_CUST_NAME',
sequence_no => 2,
method => 'APPEND SEQUENCE',
comment => 'Resolve Conflict',
parameter_column_name => 'LAST_NAME');
Because the trigger uses one of the generated procedures to check whether or not the trigger should actually be fired, it is necessary to generate replication support for the CUSTOMERS table before creating the trigger. This will also allow transactions on the customer table to be propagated.
dbms_repcat.generate_replication_support(sname => 'OFF_SHORE_ACCOUNTS',
oname => 'CUSTOMERS',
type => 'TABLE');Now you can define the trigger:
create or replace trigger "OFF_SHORE_ACCOUNTS"."T_CUSTOMERS"
before insert or update on "OFF_SHORE_ACCOUNTS"."CUSTOMERS"
for each row
declare
TIMESTAMP$X DATE := SYSDATE;
SITE$X VARCHAR2(128) := dbms_reputil.global_name;
begin
-- Don't fire if a snapshot refreshing;
-- Don't fire if a master and replication is turned off
if ("OFF_SHORE_ACCOUNTS"."CUSTOMERS$TP".active) then
if not dbms_reputil.from_remote then
if inserting then
-- set site and timestamp columns.
:new."TIMESTAMP" := TIMESTAMP$X;
:new."SITE" := SITE$X;
elsif updating then
if (:old."ADDR1" = :new."ADDR1" or
(:old."ADDR1" is null and :new."ADDR1" is null)) and
(:old."ADDR2" = :new."ADDR2" or
(:old."ADDR2" is null and :new."ADDR2" is null)) and
(:old."FIRST_NAME" = :new."FIRST_NAME" or
(:old."FIRST_NAME" is null and :new."FIRST_NAME" is null)) and
(:old."LAST_NAME" = :new."LAST_NAME" or
(:old."LAST_NAME" is null and :new."LAST_NAME" is null)) and
(:old."SITE" = :new."SITE" or
(:old."SITE" is null and :new."SITE" is null)) and
(:old."TIMESTAMP" = :new."TIMESTAMP" or
(:old."TIMESTAMP" is null and :new."TIMESTAMP" is null)) then
-- column group was not changed; do nothing
NULL;
else
-- column group was changed; set site and timestamp columns.
:new."SITE" := SITE$X;
:new."TIMESTAMP" := TIMESTAMP$X;
-- consider time synchronization problems;
-- previous update to this row may have originated from a site
-- with a clock time ahead of the local clock time.
if :old."TIMESTAMP" is not null and
:old."TIMESTAMP" > :new."TIMESTAMP" then
:new."TIMESTAMP" := :old."TIMESTAMP" + 1 / 86400;
elsif :old."TIMESTAMP" is not null and
:old."TIMESTAMP" = :new."TIMESTAMP" and
(:old."SITE" is null OR :old."SITE" != :new."SITE") then
:new."TIMESTAMP" := :old."TIMESTAMP" + 1 / 86400;
end if;
end if;
end if;
end if;
end if;
end;You can propagate the trigger to other masters by registering it with the symmetric replication facility:
dbms_repcat.create_master_repobject(gname => 'ORDER_ENTRY', sname => 'OFF_SHORE_ACCOUNTS', oname => 'T_CUSTOMERS', type => 'TRIGGER');
After confirming that all replication objects are correctly generated and replicated, start up replication activity:
dbms_repcat.resume_master_activity(gname => 'ORDER_ENTRY');
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |