After a conflict has been detected, resolve the conflict with the goal of data convergence ac
ross all sites. Oracle provides several prebuilt conflict resolution methods to resolve update conflicts and in many situations can g
uarantee data convergence across a variety of replication environments. Oracle also offers several conflict resolution methods to han
dle uniqueness conflicts, though these methods cannot guarantee data convergence.
Oracle does
not provide any prebuilt conflict resolution methods to handle delete or ordering conflicts. Oracle does, however, allow you to buil
d your own conflict resolution method to resolve data conflicts specific to your business rules. If you do build a conflict resolutio
n method that cannot guarantee data convergence, which is likely for uniqueness and delete conflicts, then you should also build a no
tification facility to notify the database administrator so that data convergence can be manually achieved.
Whether you use an Oracle prebuilt or user-defined conflict resolution method, it is applied as soon as the conflict i
s detected. If the defined conflict resolution method cannot resolve the conflict, then the conflict is logged in the error queue.
To avoid a single point of failure for conflict resolution, you can define additional conflict
resolution methods to backup the primary method. For example, in the unlikely event that the latest timestamp conflict resolution met
hod cannot resolve a conflict because the timestamps are identical, you may want to define a site priority conflict resolution method
, which breaks the timestamp tie and resolves the data conflict.
When you have a master table and an updatable materialized view based on that
master table, a refresh of the materialized view pushes its changes to the master site, where the master site handles any conflicts
resulting from the push with its configured conflict resolution methods. Then, the materialized view pulls changes at the master down
when the materialized view completes the refresh. The refresh is always initiated at the materialized view site.
Similarly, the master materialized view of an updatable materialized view behaves in the same way as a master ta
ble. However, to handle conflicts resulting from a push from a materialized view, the master materialized view uses conflict resoluti
on methods that it has pulled from its master. Here, the master can either be a master table at a master site or a master materialize
d view at another materialized view site. Conflict resolution methods cannot be configured directly at a materialized view site. Inst
ead, the conflict resolution methods are pulled down from the immediate master automatically when you create an updatable materialize
d view and when you generate replication support for a materialized view. A read-only materialized view does not pull-down conflict r
esolution methods from its master.
For example, suppose a level 3 materialized view pushes it
s changes to its level 2 master materialized view. This push may cause a conflict at the level 2 materialized view. To handle the con
flict, the level 2 materialized view uses the conflict resolution methods that it previously pulled from its level 1 master materiali
zed view. Similarly, the level 1 materialized view handles conflicts with the conflict resolution methods that it previously pulled f
rom its master site. Figure 5-1 illustrates this configuration.
Notice that each updatable materialized
view pulls-down conflict resolution methods from its master, even if the updatable materialized view does not have any materialized
views based on it. Notice also that a read-only materialized view does not pull-down conflict resolution methods from its master.
If you plan to change the conflict resolution methods for a master table in an environment with
multitier materialized views, then complete the following general procedure:
This regeneration of replication support is not performed automatically. In an environme
nt where different database administrators administer master sites and materialized view sites, the database administrator at the mas
ter sites must notify the database administrators at all of the affected materialized view sites of the changes in conflict resolutio
n methods. Then, it is the responsibility of all of the database administrators to coordinate the previous procedure.
Column subsetting enable
s you to exclude columns in master tables from materialized views by identifying specific columns in the SELECT statemen
t during materialized view creation. If only a subset of the columns in a column group are included in an updatable materialized view
, then do not create a conflict resolution method on this column group, unless the conflict resolution method is either discard or si
te priority. If the conflict resolution method is site priority, then column subsetting should only be used in single master replicat
ion environments where the master site has a higher priority number than the materialized view site.
For any type of conflict resolution method other than discard and the variant of site priority described previously, the upda
table materialized view sends information about changes for some of the columns in the column group but not others, causing Oracle to
return an error when it tries to apply the conflict resolution method. Because discard and this variant of site priority do not depe
nd on column information, you can use these methods along with column subsetting.
Keep this in mind if you are using multitier materialized views. Because the
conflict resolution methods are pulled down from the master site to a master materialized view, the same rules apply to master mater
ialized view sites and updatable materialized views based on them.
For each nested table column, Oracle creates a hidden column in the table called the N
ESTED_TABLE_ID column. Oracle also creates a separate table called a storage table to store the elements of the nested table.
The storage table stores a row for each element of the nested table for each parent table row. The storage table also contains a NESTED_TABLE_ID column, which corresponds to the parent table's NESTED_TABLE_ID column and is used to identify
the elements of the nested table for a particular parent row. Nested table columns require special consideration in Advanced Replicat
ion.The underlying storage tables require as much consideration for conflict resolution as the parent table, and there are additional
issues to consider.
Replication handles data manipulation language (DML) statements on neste
d tables as separate DML statements on the parent table and storage table. When DML statements are executed on nested table columns,
the actions performed by Oracle depend on the type of DML statement. The following table shows the actions performed by Oracle for ea
ch type of DML statement.
| Insert Statements |
<
th class="Informal" align="left" valign="bottom" scope="col"> D
elete Statements
Update Statements |
- Inserts new rows into storage tab
le (assuming nested table value is neither null nor empty)
- Inserts new row into
parent table, with the
NESTED_TABLE_ID value referring to previously inserted storage rows. |
- Deletes any storage table rows associated with the parent t
able being deleted.
- Deletes parent table row.
|
- Inserts new rows into storage table (assuming the nested table co
lumn is set to a value that is neither null nor an empty table).
- Updates the par
ent table row.
- Deletes old storage table rows.
|
Example of Nes
ted Table Conflicts
The following example illustrates how DML statemen
ts on nested table columns can lead to conflicts that are difficult to resolve. Following the example is information about ways to mi
nimize conflicts.
Suppose there is a university which stores information about its department
s in a department table containing a nested table column that stores information about each department's courses:
CREATE TYPE Course AS OBJECT (
course_no NUMBER(4),
title VARCHAR2(35),
credits NUMBER(1));
/
CREAT
E TYPE CourseList AS TABLE OF Course;
/
CREATE TABLE department (
name VARCHAR2(20) primary key,
director VARCHAR2(20),
office
VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab(
(PRIMARY KEY(nested_table_id,course_no)));
Th
e university has campuses across the United States and uses multimaster replication to support its different locations. Each location
can update the department table, which is replicated. On univ1.world, one of the master sites, information
is inserted about the Psychology department.
INSERT INTO department
VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133',
CourseList(Course(1000, 'General P
sychology', 5),
Course(2100, 'Experimental Psychology', 4),
Course(2200, 'Psychol
ogical Tests', 3),
Course(2250, 'Behavior Modification', 4),
Course(3540, 'Groups
and Organizations', 3),
Course(3552, 'Human Factors in Business', 4),
Course(421
0, 'Theories of Learning', 4)));
Advanced Replication propagates the
insert to all masters.
Then, change information arrives about the Psychology class offerings.
That is, a class is added. The information is updated on univ1.world.
UPDATE de
partment SET courses = CourseList(
Course(1000, 'General Psychology', 5),
Course(
2100, 'Experimental Psychology', 4),
Course(2200, 'Psychological Tests', 3),
Cour
se(2250, 'Behavior Modification', 4),
Course(3540, 'Groups and Organizations', 3),
Course(3552, 'Human Factors in Business', 4),
Course(4210, 'Theories of Learning', 4),
Course(4320, 'Cognitive Processes', 4))
WHERE name = 'Psychology';
After univ1.world has committed the update, but before the change is propagated to othe
r master sites, another master site, univ2.world, receives information that two more class have been added, both 4
320 and 4410.
UPDATE department SET courses = CourseList(
Course(1000, 'General Psychology', 5),
Course(2100, 'Experimental Psychology', 4),
Course(2200, 'Psychological Tests', 3),
Course(2250, 'Behavior Modification', 4),
Course(3540, 'Groups and Organizations', 3),
Course(3552, 'Human Factors in Business', 4),
Course(4210, 'Theories of Learning', 4),
Course(4320, 'Cognitive Processes', 4),
Course(4410, 'Abnormal Psychology', 4))
WHERE name = 'Psychology';
Both the update on univ1.world and the update on univ2.world are pushed.
There will be an update conflict on department table. Each user w
ho made an update expects that it is the first update since the insert statement. But actually, the local update has taken place firs
t, and therefore the NESTED_TABLE_ID has changed, because these are updates on the parent table. It is only updates on t
he nested table column (changing the storage table rows and NESTED_TABLE_ID) which are problematic. There is no problem
updating other columns in the parent table.
Suppose this conflict is resolved by keeping the
local table update. Delete conflict resolution would be required on the storage table to ignore the missing rows, which were already
deleted by the local update. The new rows inserted into the storage table, due to the update at the remote site, now have no referenc
e in the parent table. These new storage table rows must also be dealt with. Otherwise, they will be orphaned. The storage table woul
d grow with course rows which are not accessible from the department table.
Resolving conflic
ts by manipulating the storage table rows while updating the parent table is very difficult with two master sites in a multimaster re
plication environment and becomes nearly impossible as the number of master sites increases. If this type of update is necessary, the
n it may be best to not define any conflict resolution methods on the nested table and resolve conflicts manually. Incorrect conflict
resolution could lead to divergence. That is, tables on different masters may no longer match.
Recommendations for Avoiding Problematic Updates
The following recommendations enable you to avoid the problematic updat
es described in the preceding section:
- Use a foreign key constra
int, initially deferred, on the nested table. This constraint prevents dangling rows in the storage table. The following is an exampl
e of such a foreign key constraint:
ALTER TABLE courses_tab add CONSTRAINT courses_fk
FOREIGN KEY(NESTED_TABLE_ID) REFERENCES department(courses)
INITIALLY DEFERRED;
- Make sure all inserts on the parent table insert an empty nested ta
ble. Do not use a null nested table value. This practice helps to create a reusable
NESTED_TABLE_ID. The following is an
example of an insert that included an empty nested table:
INSERT INTO department (name, direct
or, office, courses)
VALUES('Psychology', 'Irene Friedman', 'Fulton Hall 133', CourseList());
- Make sure all inserts, deletes, and updates are performed directly
on the nested table rather than through DML on the parent table. This practice helps to reuse the present
NESTED_TABLE_ID value.
The following is an example of deleting rows directly from a nested table:
DELETE FROM TABLE
(SELECT courses FROM department WHERE name = 'Psycholo
gy');
Consider an example where the following rows are inserted dire
ctly into the nested table on univ1.world:
INSERT INTO TABLE
<
/a> (SELECT courses FROM department WHERE name = 'Psychology')
VALUES (Course(5000, 'Social Psychology', 5)
);
INSERT INTO TABLE
(SELECT courses FROM department WHERE name = 'P
sychology')
VALUES (Course(5100, 'Psychology of Personality', 4));
Then, the following rows are inserted directly into the nested table on univ2.world before the pr
eceding inserts on univ1.world are pushed:
INSERT INTO TABLE
(SELECT
courses FROM department WHERE name = 'Psychology')
VALUES (Course(5000, 'Social Psychology', 5));
INSERT INTO TABLE
(SELECT courses FROM department WHERE name = 'Psychology')
VALUES (Course(5100, 'Psychology of Personality', 4));
INSERT INTO TA
BLE
(SELECT courses FROM department WHERE name = 'Psychology')
VALUES (Course(5500, '
Cognitive Neuroscience', 5));
Here, primary key conflicts will occur
on the inserted rows in the storage table for courses 5000 and 5100), but a conflict resolution on the storage table which allows th
e inserts from one site to fail should provide the proper results. However, these inserts will not result in the more complicated pro
blem involving multiple tables described in "Example of Nested Table Conflicts", but the NESTED_TABLE_ID value is not lost, because this value has not changed.
- Consider using a trigger on the parent table that prevents inserts and updates that include manipulatio
n of the nested table column. The following is an example of such a trigger:
CREATE OR REPLACE
TRIGGER depart_trig
AFTER INSERT OR UPDATE ON department
FOR EACH ROW
DECLARE
new_ntid raw(100);
old_ntid raw(100);
BEGIN
-- obtain the nested table ids
SELECT sys_op_tosetid(:new.courses) INTO new_ntid from dual;
SELECT sys_op_tosetid(:old.courses) INTO old_ntid from dual;
IF INSERTING THEN
-
- raise error on insert of a null nested table column
IF :new.courses IS NULL THEN
raise_application_error(-20011, 'inserting null nested table ref');
END IF;
-- raise erro
r if new rows are inserted in the storage table
-- this is not strictly necessary, but it does enforce DML access
-- semantics of separate DMLS on parent table and storage table
IF :new.courses.count !=
0 THEN
raise_application_error(-20012,
'inserting rows into storage table while
inserting parent table row');
END IF;
ELSE
-- raise error if updat
e has caused the NESTED_TABLE_ID to change
IF new_ntid != old_ntid THEN
raise_appli
cation_error(-20013,
'updating storage table reference while updating parent table row');
END IF;
END IF;
END;
/
These recommendations continue to apply with multilevel nesting, where the storage table row beco
mes a parent to another storage table's rows. All of these recommendations are good strategies at each level of nesting.
Techniques for Avoiding C
onflicts
Although Oracle provides powerful methods for resolving data
conflicts, one of your highest priorities when designing a replicated database and front-end application should be to avoid data conf
licts. The next few sections briefly suggest several techniques that you can use to avoid some or all replication conflicts.
Use Column Groups
Column groups can help you avoid conflicts even if you do not apply any c
onflict resolution methods to the column groups. When your replicated table contains multiple column groups, each group is viewed ind
ependently when analyzing updates for conflicts.
For example, consider a replicated table wit
h column group a_cg and column group b_cg. Column group a_cg contains the following columns: a1, a2, and a3. Column group b_cg contains the following columns: b1, b2, and b3.
The following updates occur at replication sites sf.world and la.world:
- User
wsmith updates column
a1 in a row at sf.world.
- At exactly the same time, user mroth updates column
b2 in the same row at la.world.
In this case, no conflicts result because Oracle analyzes the updates separately in column groups a_cg and b_cg. If, however, column
groups a_cg and b_cg did not exist, then all of the columns in the table would be in the same column group
, and a conflict would have resulted. Also, with the column groups in place, if user mroth had updated column a3 instead of column b2, then a conflict would have resulted, because both a1 and a3 are in
the a_cg column group.
Use Primary Site and Dynamic Site Ownership Data Models
One way that you can avoid the possibility of replication conflicts is to limit the num
ber of sites in the system with simultaneous update access to the replicated data. Two replicated data ownership models support this
approach: primary site ownership and dynamic site ownership.
Primary Site Ownership
Primary ownership is the replicated data model that the read-only replication environments support. Primary ownership prevents all r
eplication conflicts, because only a single server permits update access to a set of replicated data.
Rather than control the ownership of data at the table level, applications can employ row and column subsetting to establish
more granular static ownership of data. For example, applications might have update access to specific columns or rows in a replicat
ed table on a site-by-site basis.
Dynamic Site Ownership
The dynamic ownership repl
icated data model is less restrictive than primary site ownership. With dynamic ownership, capability to update a data replica moves
from site to site, still ensuring that only one site provides update access to specific data at any given point in time. A workflow s
ystem clearly illustrates the concept of dynamic ownership. For example, related departmental applications can read the status code o
f a product order, for example, enterable, shippable, billable, to determine when they can and
cannot update the order.
Avoiding Specific Types of Conflicts
Whe
n both primary site ownership and dynamic ownership data models are too restrictive for your application requirements, you must use a
shared ownership data model. Even so, typically you can use some simple strategies to avoid specific types of conflicts.
Avoiding Uniqueness Conf
licts
It is quite easy to configure a replication environment to preve
nt the possibility of uniqueness conflicts. For example, you can create sequences at each site so that each sequence at each site gen
erates a mutually exclusive set of sequence numbers. This solution, however, can become problematic as the number of sites increase o
r the number of entries in the replicated table grows.
Alternatively, you can append a unique
site identifier as part of a composite primary key.
Finally, you can select a globally uniqu
e value using the SYS_GUID function. Using the selected value as the primary key (or unique) value will globally avoid u
niqueness conflicts.
Note:
Sequences are not valid replication object types and you must therefore create the sequence at each
site.
|
Avoiding Delete Conflicts
Always avoid delete conflicts r
eplicated data environments. In general, applications that operate within an asynchronous, shared ownership data model should not del
ete rows using DELETE statements. Instead, applications should mark rows for deletion and then configure the system to p
eriodically purge logically deleted rows using procedural replication.
Avoiding Update Conflicts
After trying to eliminate the possibility of uniqueness and delete conflicts in a replication system, you
should also try to limit the number of update conflicts that are possible. However, in a shared ownership data model, update conflict
s cannot be avoided in all cases. If you cannot avoid all update conflicts, then you must understand exactly what types of replicatio
n conflicts are possible and then configure the system to resolve conflicts when they occur.
Avoiding Ordering Conflicts
Conflict Resolution Architecture
Very few architectural mechanisms and processes are visible when implementing conflict resolution into your replication environ
ment. This section describes the few supporting mechanisms involved in conflict resolution and describes different aspects of Oracle'
s prebuilt conflict resolution methods.
Support Mechanisms
The most important mecha
nism involved in Oracle conflict resolution is the column group because it is the basis for all update conflict detection and resolut
ion. Additionally, the error queue can provide you with important information to monitor the conflict detection activity of your repl
ication environment.
Column Groups
Oracle uses column groups to detect and resolve
update conflicts. A column group is a logical grouping of one or more columns in a replicated table. Every column in a replicated tab
le is part of a single column group. When configuring replicated tables at the master definition site, you can create column groups a
nd then assign columns and corresponding conflict resolution methods to each group.
Column gr
oups have the following characteristics:
- A column can belong onl
y to one column group.
- A column group can consist of one or more columns of a ta
ble.
- Conflict resolution is applicable only to columns in a column group.
Ensuring Data Integrity with Multiple Column Groups
Having column groups enables you to designate different methods of resolving conflicts for differ
ent types of data. For example, numeric data is often suited for an arithmetical resolution method, and character data is often suite
d for a timestamp resolution method. However, when selecting columns for a column group, it is important to group columns wisely. If
two or more columns in a table must remain consistent with respect to each other, then place the columns within the same column group
to ensure data integrity.
For example, if the postal code column in a customer table uses on
e resolution method while the city column uses a different resolution method, then the sites could converge on a postal code that doe
s not match the city. Therefore, all components of an address should typically be within a single column group so that conflict resol
ution is applied to the address as a unit.
Shadow Column Groups
By default, every r
eplicated table has a shadow column group. The shadow column group of a table contains all columns that are not within a specific col
umn group. You cannot assign conflict resolution methods to a table's shadow group. Therefore, make sure to i
nclude a column in a column group when conflict resolution is necessary for the column. Oracle detects conflicts that involve columns
in the shadow column group but does not attempt to apply any conflict resolution methods to resolve these conflicts.
Column Objects and Column Gr
oups
An Oracle object based on a user-defined type that occupies a sin
gle column in a table is a column object. A column object cannot span column groups. That is, given a column group and a column objec
t, either the column object and all of its attributes must be within the column group, or the column object and all of its attributes
must be excluded from a column group.
Oracle's prebuilt conflict resolution methods cannot r
esolve conflicts based on undefined column object attribute values. If a column object is NULL, then its attributes are
undefined.
Ob
ject Tables and Column Groups
An object table is a special kind of tab
le in which each row represents an object based on a user-defined type. You can specify column groups that include a subset of the co
lumns in an object table.
Nested Tables and Column Groups
A nested table's storage
table is treated as an independent table in conflict resolution. Therefore, you can create a column group based on a subset of the co
lumns in a storage table.
Error Queue
If a conflict resolution method fails to reso
lve a data conflict, or if you have not defined any conflict resolution methods, then the error queue contains information about the
data conflict.
| See Also:
"Error Queue" for more informa
tion about the error queue |
Common Update Conflict Resolution Methods
Although Oracle provides eight prebuilt update conflict resolution methods, the latest timestamp and the overwrite conflict
resolution methods are the most commonly implemented resolution methods.
These methods are t
he most common because they are easy to use and, in the proper environments, can guarantee data convergence. The latest timestamp and
the overwrite conflict resolution methods are described in detail in the following two sections.
Table 5-2 Convergence Properties of Common Update Conf
lict Resolution Methods
Note:
All of Oracle's prebuil
t conflict resolution methods provide convergence in an environment with a single master site that has one or more materialized view
sites.
|
Latest Timestamp
The lates
t timestamp method resolves a conflict based on the most recent update, as identified by the timestamp of when the update oc
curred.
The following example demonstrates an appropriate application of the latest timestamp
update conflict resolution method:
- A customer i
n Phoenix calls the local salesperson and updates her address information.
- After hanging up the phone, the customer realizes that she gave the local salesperson the wrong postal code.
- The customer tries to call the local salesperson with the correct postal code, but the salesper
son cannot be reached.
- The customer calls the headquarters, which is loca
ted in New York. The New York site, rather than the Phoenix site, correctly updates the address information.
- The network connecting New York headquarters with the local Phoenix sales site goes down temporar
ily.
- When the New York/Phoenix network connection comes back up, Oracle s
ees two updates for the same address, and detects a conflict at each site.
- Using the latest timestamp method, Oracle selects the most recent update, and applies the address with the correct postal code.
Target Environments
The latest timestamp conflict resolution method works to converge replication environments with two or more mas
ter sites. Because time is always increasing, it is one of the few conflict resolution methods that can guarantee data convergence wi
th multiple master sites. This resolution also works well with any number of materialized views.
Support Mechanisms
To use the timesta
mp method, you must designate a column in the replicated table of type DATE. When an application updates any column in a
column group, the application must also update the value of the designated timestamp column with the local SYSDATE. For
a change applied from another site, the timestamp value should be set to the timestamp value from the originating site.
Note:
When you use a timestamp conflict resolution method, you should designate a backup method, such as site
priority, to be called if two sites have the same timestamp.
|
Implement Latest Timestamp
See the Replication Management tool's online help to learn how to define a latest timestamp conflict resolution method with th
e Replication Management tool.
Overwrite
The overwrite method replaces the current value at the destination site with the new value f
rom the originating site, and therefore can never guarantee convergence with more than one master site. This method is designed to be
used by a single master site and multiple materialized view sites. You can also use this form of conflict resolution with multiple m
aster sites, though it does not guarantee data convergence and should be used with some form of a user-defined notification facility.
For example, if you have a single master site that you expect to be used primarily for queri
es, with all updates being performed at the materialized view sites, then you might select the overwrite method. The overwrite method
is also useful if:
- Your primary concern is data convergence.
- You have a single master site.
- No particular business rule exists for selecting one update over the other.
- Yo
u have multiple master sites and you supply a notification facility to notify the person who ensures that data is correctly applied,
instead of logging the conflict in the
DEFERROR data dictionary view and leaving the resolution to your local database a
dministrator.
Target Environments
The overwrite conflict resolution method ensures data convergence for replication environments
that have a single master site with any number of materialized views. With this in mind, the overwrite conflict resolution method is
ideal for mass deployment environments.
If a conflict is detected, then the value originatin
g from the materialized view site is used, which means that priority is given to the most recently refreshed materialized views.
Support Mechanisms
No add
itional support mechanisms are required for the overwrite conflict resolution method.
Implement Overwrite
See the Replication Management tool's online help to learn how to define an overwrite conflict resolution method with the Replicati
on Management tool.
Additional Update Conflicts Resolution Methods
If the latest timestamp or the overwrite conflict resolution methods do not meet your needs to r
esolve data conflicts that are encountered in your replication environment, then Oracle offers six additional prebuilt update conflic
t resolution methods.
<
em>Table 5-3 Convergence Properties of Additional Update Conflict Resolution Methods
| Resolution Methods |
Convergence with Multiple Master Sites |
|
Additive |
YES |
|
Average |
NO |
|
Discard |
<
/a>
NO |
|
Earliest timestamp |
NO |
|
Maximum |
YES
(column values must always increase) |
|
Minimum |
YES
(column values
must always decrease) |
|
Priority group |
YES
(with ordered update values) |
|
Site priority |
NO |
Additive
The additive method works with column groups consisting of a single numeric column only. If a conflic
t arises, instead of choosing one value over another, then the difference of the two values is added to the current value.
The additive method adds the difference between the old and new values at the originating site to the c
urrent value at the destination site according to this formula:
current value = current value
+ (new value - old value)
The additive conflict resolution method pr
ovides convergence for any number of master sites and materialized view sites.
Target Environments
The additive conflict resolution me
thod is designed to conserve data rather than choose the most appropriate data. This method might be useful in a financial environmen
t where deposits and withdrawals happen so frequently that conflicts may arise; with a balance, it is important to conserve data rath
er than choose one value over another (though we might wish that deposits would always be chosen over withdrawals).
Support Mechanisms
No additional suppo
rt mechanisms are required for the additive conflict resolution method.
Implement Additive
See the Repli
cation Management tool's online help to learn how to define an additive conflict resolution method with the Replication Management to
ol.
Average
Like the additive
method, the average method works with column groups consisting of a single numeric column only.
Instead of adding the difference to the current value, the average method resolves the conflict by computing the average of the curr
ent and the new value.
The average conflict resolution method averages the new column value f
rom the originating site with the current value at the destination site.
current value = (cur
rent value + new value)/2
The average method cannot guarantee conver
gence if your replication environment has more than one master site.
Target Environments
Because the average method cannot guarantee d
ata convergence for replication environments with more than one master site, the average method is ideally implemented in mass deploy
ment environment with a single master site and any number of updatable materialized views.
Th
e average method might be useful for scientific applications that would rather average two values than choose one value over another
(for example, to compute the average temperature or weight).
Support Mechanisms
No additional support mechanisms are required for the average conflict res
olution method.
Implement Av
erage
See the Replication Management tool's online help to learn how to defi
ne an average conflict resolution method with the Replication Management tool.
Discard
The discard method ignores the values from the originating site and therefor
e can never guarantee convergence with more than one master site. The discard method ignores the new value from the originating site
and retains the value at the destination site. This method is designed to be used by a single master site and multiple materialized v
iew sites, or with some form of a user-defined notification facility.
For example, if you hav
e a single master site and multiple materialized view sites based on it, and you expect the materialized view sites to be used primar
ily for queries with all updates being performed at the master site, then you might select the discard method. The discard methods is
also useful if:
- Your primary concern is data convergence.
- You have a single master site.
- T
here is no particular business rule for selecting one update over the other.
- You
have multiple master sites and you supply a notification facility to notify the person who ensures that data is correctly applied, i
nstead of logging the conflict in the
DEFERROR view and leaving the resolution to your local database administrator.
Target Environments
The discard conflict resolution method is best suited for a mass deployment model having a single master site w
ith any number of materialized view sites. If a conflict is detected, then the value originating from the materialized view site is i
gnored, which means that priority is given to materialized views that refresh first.
Support Mechanisms
No additional support mechanisms are required for
the discard conflict resolution method.
Implement Discard
See the Replication Management tool's online h
elp to learn how to define a discard conflict resolution method with the Replication Management tool.
Earliest Timestamp
The earliest
timestamp methods resolves a conflict based on the earliest (oldest) update, as identified by the timestamp of when the upda
te occurred.
Target Environments
The earliest timestamp conflict resolution method works to converge replication environments with a s
ingle master site and any number of materialized views. Because time is always increasing, the earliest timestamp conflict resolution
cannot guarantee data convergence in replication environments with more than one master site. This resolution also works well with a
ny number of materialized views, if you have a backup conflict resolution method in the event that two transactions have the same tim
estamp.
Support Mechanisms
To use the timestamp method, you must designate a column in the replicated table of type DATE.
When an application updates any column in a column group, the application must also update the value of the designated timestamp col
umn with the local SYSDATE. For a change applied from another site, the timestamp value should be set to the timestamp v
alue from the originating site. Be sure to review the "Timestamp Configuration Issues" discussion.
Note:
When you use a timestamp conflict resolution method, you sh
ould designate a backup method, such as site priority, to be called if two sites have the same timestamp.
|
<
/div>
Implement Earliest Timesta
mp
See the Replication Management tool's online help to learn how to define
an earliest timestamp conflict resolution method with the Replication Management tool.
| See Also:
Oracle Database Advanced Replication Management API Re
ference to learn how to define this type of conflict resolution method with the replication management API |
Maximu
m
When Advanced Replication detects a conflict with a column group and
calls the maximum value conflict resolution method, it compares the new value from the origina
ting site with the current value from the destination site for a designated column in the column group. You must designate this colum
n when you select the maximum value conflict resolution method.
If the new value of the desig
nated column is greater than the current value, then the column group values from the originating site are ap
plied at the destination site, assuming that all other errors were successfully resolved for the row. If the new value of the designa
ted column is less than the current value, then the conflict is resolved by leaving the current values of the column group unchanged.
Note:<
/font>
If the two values for the designated column are the same (for example, if the designated c
olumn was not the column causing the conflict), then 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 for this case.
|
There are no restrictions on the datatypes of the columns in the column group. Convergence for more than on
e master site is only guaranteed if the column value is always increasing.
Note:
You should not e
nforce an always-increasing restriction by using a CHECK constraint because the constraint could interfere with conflict
resolution.
|
Target
Environments
If you have defined the maximum conflict resolution method and the targe
t column that is used to resolve the conflict is always increasing across all sites, then this method guarantees data convergence wit
h any number of master sites and materialized view sites.
Support Mechanisms
No additional support mechanisms are required for the maximum conflict resolu
tion method.
Implement Maxim
um
See the Replication Management tool's online help to learn how to define
a maximum conflict resolution method with the Replication Management tool.
<
a name="23444">
Minimum
When Advanced Replication detects a conflict with a column group and calls the <
strong class="GlossaryTerm">minimum value conflict resolution method, it compares the new value from the originating site wi
th the current value from the destination site for a designated column in the column group. You must designate this column when you s
elect the minimum value conflict resolution method.
If the new value of the designated column
is less than the current value, then the column group values from the originating site are applied at the de
stination site, assuming that all other errors were successfully resolved for the row. If the new value of the designated column is g
reater than the current value, then 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 no
t the column causing the conflict), then the conflict is not resolved, and the values of the columns in the column group remain uncha
nged. Designate a backup conflict resolution method to be used for this case.
|
There are no restrictions on the datatypes of the columns in the column group. Convergence for more than one master sit
e is only guaranteed if the column value is always decreasing.
Note:
You should not enforce an al
ways-decreasing restriction by using a CHECK constraint because the constraint could interfere with conflict resolution.
|
Target Environments
If you have defined the minimum conflict resolution method and the target column tha
t is used to resolve the conflict is always decreasing across all sites, then this method guarantees data convergence with any number
of master sites and materialized view sites.
Sup
port Mechanisms
No additional support mechanisms are required for the minimum conflict resolution method.
Implement Minimum<
/font>
See the Replication Management tool's online help to learn how to define a minimum co
nflict resolution method with the Replication Management tool. Or, see the information on the minimum and maximum methods in Oracle Database Advanced Replication M
anagement API Reference book to learn how to define this type of conflict resolution method with the replication management
API.
Priority
Groups
Priority groups allow you to assign a priority level to each p
ossible value of a particular column. If Oracle detects a conflict, then Oracle updates the table whose "priority" column has a lower
value using the data from the table with the higher priority value. Therefore, a higher value means a higher priority.
You can guarantee convergence with more than one master site when you are using priority groups if the val
ue of the priority column is always increasing. That is, the values in the priority column correspond to an ordered sequence of event
s; for example: ordered, shipped, billed.
As shown in Figure&nbs
p;5-2, the DBA_REPPRIORITY view displays the priority level assigned to each priority group member (value that the "
priority" column can contain). You must specify a priority for all possible values of the "priority" column.
Figure 5-2 Using Priority Groups
Text description of the i
llustration repln080.gif
The DBA_REPPRIORITY view displays the values of all
priority groups defined at the current location. In the example shown in Figure 5-2, there are
two different priority groups: site-priority and order-status. The customer table is using the site-priority priority g
roup. In the order-status priority group in this example, billed (priority 3) has a higher priority than shipped (priority 2), and shipped has a higher priority than ordered (priority 1).
Before you use the Replication Management tool to select the priority group method of update conflict resolution, you m
ust designate which column in your table is the priority column.
Target Environments
The priority group conflict resolution method is
useful for replication environments that have been designed for a work flow environment. For example, once an order has reached the <
code>shipping status, updates from the order entry department are always over-written.
Support Mechanisms
You need
to define the priority of the values contained in the target column. This priority definition is required so that Oracle knows how t
o resolve a conflict based on the priority of the column value that has been designated to resolve a conflict. The priority definitio
ns are stored in a priority group.
Implement Priority Groups
See the Replication Management tool's onlin
e help to learn how to define a priority group conflict resolution method with the Replication Management tool.
<
/a>
Site Priority
Site priority is a special kind of pr
iority group. With site priority, the priority column you designate is automatically updated with the global database name of the sit
e where the update originated. The DBA_REPPRIORITY view 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 5-2, the new_york.w
orld site (priority value = 2) is corporate headquarters, while the houston.world site (priority value =&nbs
p;1) is an updatable materialized view at a sales office. Therefore, the headquarters office is considered more likely than the sales
office to have the most accurate information about the credit that can be extended to each customer.
Note:
The priority-group column of the DBA_REPPRIORITY view shows both the site-priority group and the order-status
group.
|
When you are using site priority alone, convergence with
more than one master site is not guaranteed, but site priority can be a good backup method in a multimaster environment, especially
for breaking latest timestamp ties.
Similar to priority groups, you must complete several pre
paratory steps before using the Replication Management tool to select site priority conflict resolution for a column group.
Target Environments
As with priority groups, site priority conflict resolution is commonly implemented in a work-flow environment. Additionally
, when the site priority conflict resolution method is used in a mass deployment environment (which is a single master site and any n
umber of materialized views), data convergence can be guaranteed.
The site priority conflict
resolution method is also a good backup conflict resolution method should a primary conflict resolution method fail in a multimaster
environment.
Support Mechanisms
A column must be designated to store site information when a row is updated. Additionally, you need to
create a trigger that populates this site column with the global name of the updating site when a row is either updated or inserted.
A sample of this trigger is contained in the Replication Management tool's online help and in the Oracle Database Advanced Replication Management API Reference book.
You also need to define the priority of the sites that participate in your repli
cation environment. This priority definition is required so that Oracle knows how to resolve a conflict based on the priority of the
site that performed the update/insert. The site priority definitions are stored in a priority group.
Implement Site Priority
See the Replication Management tool's online help to learn how to define a site priority conflict resolution met
hod with the Replication Management tool.
Uniqueness Conflicts Resolution Methods
Oracle provides three prebuilt methods for resolving uniqueness conflicts:
Append the global site name of the originating site to the column valu
e from the originating site.
Append a generated sequence number to the column va
lue from the originating site.
Discard the row value from the originating site.<
/li>
The following sections explain each uniqueness conflict resolution method in detail.
|
<
hr>
Note:
Oracle's prebuilt uniqueness conflict resolution methods do not actually converge the data in
a replication environment; they simply provide techniques for resolving constraint violations. When you use one of Oracle's uniquene
ss conflict resolution methods, you should also use a notification mechanism to alert you to uniqueness conflicts when they happen an
d then manually converge replicated data, if necessary.
|
<
table class="Note" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note"
title="This is a layout table to format a note">
Note:
To add unique conflict resolution me
thod for a column, the name of the unique index on the column must match the name of the unique or primary key constraint.
|
Append Site Name
The append site na
me method works by appending the global database name of the site originating the transaction to the replicated column value
that is generating a dup_val_on_index exception. Although this method allows the column to be inserted or updated witho
ut violating a unique integrity constraint, it does not provide any form of convergence between multiple master sites. The resulting
discrepancies must be manually resolved; therefore, this method is meant to be used with some form of a notification facility.
Note:
Both append site name and append sequence can be used on character columns only.
|
This method can be useful when the availability of the data may be more importan
t than the complete accuracy of the data. To allow data to be available as soon as it is replicated
- Select append site name.
- Use a notification
scheme to alert the appropriate person to resolve the duplication, instead of logging a conflict.
When a uniqueness conflict occurs, the append site name method appends the global database name of the site originating th
e transaction to the replicated column value. The name is appended to the first period (.). For example, houston.world b
ecomes houston.
Append Sequence
The appen
d sequence methods works by appending a generated sequence number to the column value that is generating a dup_val_on_
index exception. Although this method allows the column to be inserted or updated without violating a unique integrity constra
int, it does not provide any form of convergence between multiple master sites. The resulting discrepancies must be manually resolved
; therefore, this method is meant to be used with some form of a notification facility.
Note:
Bot
h append site name and append sequence can be used on character columns only.
|
This method can be useful when the availability of the data may be more important than the complete accuracy of the dat
a. To allow data to be available as soon as it is replicated:
- Se
lect append sequence.
- Use a notification scheme to alert the appropriate person
to resolve the duplication, instead of logging a conflict.
The append sequence method a
ppends a generated sequence number to the column value. The column value is truncated as needed. If the generated portion of the colu
mn value exceeds the column length, then the conflict method does not resolve the error.
The discard uniqueness conflict resolution method resolves uniqueness conflicts b
y simply discarding the row from the originating site that caused the error. This method does not guarantees convergence with multipl
e master sites and should be used with a notification facility.
Unlike the append methods, th
e discard uniqueness method minimizes the propagation of data until data accuracy can be verified.
Delete Conflict Resolution Methods
Oracle does not provide any prebuilt methods for resolving delete conflicts. As di
scussed in the "Avoiding Delete Conflicts" section, you should desi
gn your database and front-end application to avoid delete conflicts. You can achieve this goal by marking rows for deletion and at r
egular intervals, using procedural replication to purge such marked rows.
To detect and resolve an update conflict for a row, the propagating site mu
st send a certain amount of data about the new and old versions of the row to the receiving site. Depending on your environment, the
amount of data that Oracle propagates to support update conflict detection and resolution can be different.
You can reduce data propagation in some cases by using the DBMS_REPCAT.SEND_OLD_VALUES procedure and the
DBMS_REPCAT.COMPARE_OLD_VALUES procedure to send old values only if they are needed to detect and resolve conflicts. For
example, the latest timestamp conflict detection and resolution method does not require old values for nonkey and non timestamp colu
mns.
Suggestion:
Further minimizing propagation of old values is particularly valuable if you are replicating LOB datatypes and
do not expect conflicts on these columns.
|
Note:
You must ensure that the appropriate old values a
re propagated to detect and resolve anticipated conflicts. User-supplied conflict resolution procedures must deal properly with NULL old column values that are transmitted. Using the SEND_OLD_VALUES and COMPARE_OLD_VALUES proce
dures to further reduce data propagation reduces protection against unexpected conflicts.
|
To further reduce data propagation, execute the following procedures:
DBMS_REPCAT.SEND_OLD_VALUES(
sname IN VARCHAR2,
oname IN VARCHAR2,
{ column_list IN VARCHAR2,
| column_table IN DB
MS_UTILITY.VARCHAR2s | DBMS_UTILITY.LNAME_ARRAY,}
operation IN VARCHAR2 := 'UPDATE',
send IN BOOLEAN := true );
DBMS_REPCAT.COMPARE_OLD_VALUES(
sname IN VARCHAR2,
oname IN VARCHAR2,
{ column_list IN
VARCHAR2,
| column_table IN DBMS_UTILITY.VARCHAR2s | DBMS_UTILITY.LNAME_ARRAY,}
ope
ration IN VARCHAR2 := 'UPDATE',
compare IN BOOLEAN := true );
After executing these procedures, you must use the DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure to generate replication support with min_communication set to true for this change to take ef
fect.
Note:
The operation parameter enables you to decide whether or not to transmit
old values for nonkey columns when rows are deleted or when nonkey columns are updated or both. If you do not send the old value, Or
acle sends a NULL in place of the old value and assumes the old value is equal to the current value of the column at the
target side when the update or delete is applied.
|
The specified
behavior for old column values is exposed in two columns in the DBA_REPCOLUMN data dictionary view: COMPARE_OLD_O
N_DELETE (Y or N) and COMPARE_OLD_ON_UPDATE (Y or N).
Send and Compare Exa
mple
The following example shows how you can further reduce data propa
gation by using these procedures. Consider a table called rsmith.reports with three columns. Column 1 is the primary key
and is in its own column group (column group 1). Column 2 and column 3 are in a second column group (column group 2).
Figure 5-3 Column Groups and Data Propagation
Te
xt description of the illustration repln050.gif
The conflict resolution strategy for the
second column group is site priority. Column 2 is a VARCHAR2 column containing the site name. Column 3 is a LOB col
umn. Whenever you update the LOB, you must also update column 2 with the global name of the site at which the update occurs. Because
there are no triggers for piecewise updates to LOBs, you must explicitly update column 2 whenever you do a piecewise update on the LO
B.
Suppose you use the DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure to gen
erate replication support for rsmith.reports with min_communication set to true and then use a
n UPDATE statement to modify column 2 (the site name) and column 3 (the LOB). The deferred remote procedure call (RPC) c
ontains the new value of the site name and the new value of the LOB because they were updated. The deferred RPC also contains the old
value of the primary key (column 1), the old value of the site name (column 2), and the old value of the LOB (column 3).
Note:
The conflict detection and resolution strategy does not require the old value of the LOB. Only column
C2 (the site name) is required for both conflict detection and resolution. Sending the old value for the LOB could add significantly
to propagation time.
|
To ensure that the old value of the LOB is
not propagated when either column C2 or column C3 is updated, make the following calls:
BEGIN
DBMS_REPCAT.SEND_OLD_VALUES(
sname => 'rsmith',
oname => 'reports',
column_list => 'c3',
operation =&
gt; 'UPDATE',
send => FALSE );
END;
/
BEGIN
DBMS_REPCAT.COMPARE_OLD_VALUES(
sname =>
; 'rsmith',
oname => 'reports',
column_list => 'c3',
operation => 'UPDATE',
compare => FALSE);
END;
<
a name="28740">/
You must use the DBMS_REPCAT.GENERATE_REPL
ICATION_SUPPORT procedure to generate replication support for rsmith.reports with min_communication
set to true for this change to take effect. Suppose you subsequently use an UPDATE statement to modify colu
mn 2 (the site name) and column 3 (the LOB). The deferred RPC contains the old value of the primary key (column 1), the old and new v
alues of the site name (column 2), and just the new value of the LOB (column 3). The deferred RPC contains nulls for the new value of
the primary key and the old value of the LOB.
Note:
Oracle conflict resolution does not support piecewise updates of LOBs.
|
Send and Compare When Using Column Objects
You can specif
y leaf attributes of a column object when you send and compare old values if the attributes are not replication key columns. For exam
ple, suppose you create the following cust_address_typ object type.
CREATE TYPE
cust_address_typ AS OBJECT
(street_address VARCHAR2(40),
postal_code V
ARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(10),
country_id CHAR(2));
/
You create the customers table using this type as a column object:
CREATE TABLE cust
omers
(customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
cust_address cust_address_typ,
phone_numbers phone_list_typ);
If you want to send and co
mpare old values for the street_address attribute of the cust_address_typ type in the customers table, then you run the following procedures to specify that you do want to send or compare the attribute value:
BEGIN
DBMS_REPCAT.SEND_OLD_VALUES(
sname => 'oe',
oname => 'customers',
column_list => 'cust_address.street_add
ress', -- object attribute
operation => 'UPDATE',
send =>
true );
END;
/
BEGIN
DBMS_RE
PCAT.COMPARE_OLD_VALUES(
sname => 'oe',
oname => 'custo
mers',
column_list => 'cust_address.street_address', -- object attribute
op
eration => 'UPDATE',
compare => true);
END;
/
Note:
If you have multiple levels of object attributes in one column object, then you can only specify
the final (or leaf) attribute for the column_list parameter. You cannot specify middle attributes.
|
You can also specify that you want to send and compare an entire column object. For
example, the following procedures specify the entire cust_address column object:
BEGIN
DBMS_REPCAT.SEND_OLD_VALUES(
sname => 'oe',
oname => 'customers',
column_list => 'cust_address', -- entire column object
operation => 'UPDATE',
send => true );
END
;
/
BEGIN
DBMS_REPCAT.COMPARE_OLD_VALUES(
sname => 'oe',
oname => 'customers',
column_list => 'cust_address', -- entire column object
operation => 'UPDATE',
compare => true);
END;
/
