Conflict Resolution, 3 of 6

Create Conflict Resolution Methods for Update Conflicts

The most common data conflict occurs when the same row at two or more different sites were updated at the same time, or before the deferred transaction from one site was successfully propagated to the other sites.

One method to avoid update conflicts is to implement a synchronous replicated environment, though this solution requires large network resource.

The other solution is to use the Oracle conflict resolution methods to deal with update conflicts that may occur when the same row has received two or more updates.

Overwrite and Discard

The overwrite and discard methods ignore the values from either the originating or destination site and therefore can never guarantee convergence with more than one master site. These methods are designed to be used by a single master site and multiple snapshot sites, or with some form of a user-defined notification facility.

The overwrite method replaces the current value at the destination site with the new value from the originating site. Conversely, the discard method ignores the new value from the originating site.

See Also:
"ADD_conflicttype_RESOLUTION procedure" and "Overwrite and Discard" in Oracle8i Replication for more information.

Note:
This section uses objects not found in the other scripts within this book, because the configuration ORC1.WORLD, ORC2.WORLD, ORC3.WORLD, and SNAP1.WORLD contains three master sites and one snapshot site and is not appropriate for OVERWRITE and DISCARD.

--The following procedures need to be executed by the replication administrator.

CONNECT repadmin/repadmin@saturn.universe

--Before you can define any conflict resolution methods, quiesce the 
--master group that contains the table to which you want to apply the 
--conflict resolution method.

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'TITAN_MG');
END;
/

--All Oracle conflict resolution methods are based on logical column groupings
--called "column groups."  Create a column group for your target table by using
--the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure.

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'TITAN',
      oname => 'PLANET',
      column_group => 'PLANET_CG1',
      list_of_column_names => 'ORDER,CIRCUMFERENCE,MOONS');
END;
/

--Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION API to define the conflict
--resolution method for a specified table. This example creates an
--"Overwrite" conflict resolution method. 

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'TITAN',
      oname => 'PLANET',
      column_group => 'PLANET_CG1',
      sequence_no => 1,
      method => 'OVERWRITE',
      parameter_column_name => 'ORDER,circumference,moons');
END;
/

--After you have defined your conflict resolution method, regenerate 
--replication support for the table that received the conflict
--resolution method.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'TITAN',
      oname => 'PLANET', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--After replication support has been regenerated, resume replication
--activity by using the RESUME_MASTER_ACTIVITY procedure API.

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'titan_mg');
END;
/

Minimum and Maximum

When the advanced replication facility detects a conflict with a column group and calls either the minimum or maximum value conflict resolution methods, it compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate this column when you define your conflict resolution method.

If the new value of the designated column is less than or greater than (depending on the method used) 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. Otherwise the rows remain unchanged.

--The following procedures need to be executed by the replication administrator.

CONNECT repadmin/repadmin@orc1.world

--Before you can define any conflict resolution methods, quiesce the 
--master group that contains the table to which you want to apply the 
--conflict resolution method.

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

--All Oracle conflict resolution methods are based on logical column groupings
--called "column groups."  Create a column group for your target table by using
--the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure.

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'SCOTT',
      oname => 'SALGRADE',
      column_group => 'SALGRADE_CG1',
      list_of_column_names => 'LOSAL');
END;
/

--Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION API to define the conflict
--resolution method for a specified table. This example creates a
--"MINIMUM" conflict resolution method. 

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'SCOTT',
      oname => 'SALGRADE',
      column_group => 'SALGRADE_CG1',
      sequence_no => 1,
      method => 'MINIMUM',
      parameter_column_name => 'LOSAL');
END;
/

--After you have defined your conflict resolution method, regenerate 
--replication support for the table that received the conflict
--resolution method.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'SALGRADE', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--After replication support has been regenerated, resume replication
--activity by using the RESUME_MASTER_ACTIVITY procedure API.

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

Timestamp

The earliest timestamp and latest timestamp methods are variations on the minimum and maximum value methods. 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 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.

Several elements are needed to make timestamp conflict resolution work well:

Synchronized Time Settings Between Computers

Timestamp field and trigger to automatically record timestamp

--The following procedures need to be executed by the replication administrator.

CONNECT repadmin/repadmin@orc1.world

--Before you can define any conflict resolution methods, quiesce the 
--master group that contains the table to which you want to apply the 
--conflict resolution method.

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

--If the target table does not already contain a timestamp field,
--then add an additional column to your table to record the 
--timestamp value when a row is inserted or updated. Additionally,
--you must use the ALTER_MASTER_REPOBJECT API to apply the DDL to
--the target table. Simply issuing the DDL may cause the replicated
--object to become invalid.

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'SCOTT',
      oname => 'EMP',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE scott.emp ADD (timestamp DATE)');
END;
/

--After you have inserted a new column into your replicated object,
--make sure that you re-generate replication support for the 
--affected object. This step should be performed immmediately
--after you alter the replicated object.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--Once the timestamp field has been created, create a trigger 
--that records the timestamp of when a row is either inserted
--or updated. This recorded value is used in the resolution of
--conflicts based on the Timestamp method. Instead of directly executing the
--DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to
--create the trigger and add it to your master group.

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'TRIGGER',
      oname => 'INSERT_TIME',
      sname => 'SCOTT',
      ddl_text => 'CREATE TRIGGER scott.insert_time
                      BEFORE
                         INSERT OR UPDATE ON scott.emp FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIMESTAMP := SYSDATE;
                         END IF;
                      END;');
END;
/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'INSERT_TIME', 
      type => 'TRIGGER',
      min_communication => TRUE); 
END;
/

--All Oracle conflict resolution methods are based on logical column groupings
--called "column groups."  Create a column group for your target table by using
--the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. 

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'SCOTT',
      oname => 'EMP',
      column_group => 'EMP_CG1',
      list_of_column_names => 'MGR, HIREDATE, SAL, TIMESTAMP');
END;
/

--Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION API to define the conflict
--resolution method for a specified table. This example specifies the
--"LATEST TIMESTAMP" conflict resolution method using the TIMESTAMP column
--that you created earlier.

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'SCOTT',
      oname => 'EMP',
      column_group => 'EMP_CG1',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIMESTAMP');
END;
/

--After you have defined your conflict resolution method, regenerate 
--replication support for the table that received the conflict
--resolution method.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--After replication support has been regenerated, resume replication
--activity by using the RESUME_MASTER_ACTIVITY procedure API.

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

Additive and Average

The additive and average methods work with column groups consisting of a single numeric column only. Instead of "accepting" one value over another, this conflict resolution method either adds the two compared values together or takes an average of the two compared values.

--The following procedures need to be executed by the replication administrator.

CONNECT repadmin/repadmin@orc1.world

--Before you can define any conflict resolution methods, quiesce the 
--master group that contains the table to which you want to apply the 
--conflict resolution method.

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

--All Oracle conflict resolution methods are based on logical column groupings
--called "column groups."  Create a column group for your target table by using
--the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. 

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'SCOTT',
      oname => 'BONUS',
      column_group => 'BONUS_CG1',
      list_of_column_names => 'SAL');
END;
/

--Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION API to define the conflict
--resolution method for a specified table. This example specifies the
--"ADDITIVE" conflict resolution method using the SAL column.

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'SCOTT',
      oname => 'BONUS',
      column_group => 'BONUS_CG1',
      sequence_no => 1,
      method => 'ADDITIVE',
      parameter_column_name => 'SAL');
END;
/

--After you have defined your conflict resolution method, regenerate 
--replication support for the table that received the conflict
--resolution method.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'BONUS', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--After replication support has been regenerated, resume replication
--activity by using the RESUME_MASTER_ACTIVITY procedure API.

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

Priority Groups

Priority groups allow you to assign a priority level to each possible value of a particular column. If Oracle detects a conflict, Oracle updates the table whose "priority" column has a lower value using the data from the table with the higher priority value.

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

--Make sure that the JOB field is part of the column group that your
--site priority conflict resolution mechanism is used for. Use the 
--ADD_GROUPED_COLUMN procedure to add this field to an existing column group.
--If you do not already have a column group, you can create a new column group
--using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure.

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'SCOTT',
      oname => 'EMP',
      column_group => 'EMP_CG1',
      list_of_column_names => 'MGR, HIREDATE, SAL, job');
END;
/

--Before you begin assigning a priority value to the values in your table, you
--must create a priority group that "holds" the values that you defined.

BEGIN
   DBMS_REPCAT.DEFINE_PRIORITY_GROUP (
      gname => 'SCOTT_MG',
      pgroup => 'JOB_PG',
      datatype => 'VARCHAR2');
END;
/

--The DBMS_REPCAT.ADD_PRIORITY_datatype procedure is avaiable in several
--different versions. There is a version for each available datatype 
--(NUMBER, VARCHAR2, and so on).  See "ADD_PRIORITY_datatype procedure" 
-- for more information.  Execute this API as often as 
--necessary until you have defined a priority value for all possible 
--table values.

BEGIN
   DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
      gname => 'SCOTT_MG',
      pgroup => 'JOB_PG',
      value => 'PRESIDENT',
      priority => 100);
END;
/

BEGIN
   DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
      gname => 'SCOTT_MG',
      pgroup => 'JOB_PG',
      value => 'MANAGER',
      priority => 80);
END;
/

BEGIN
   DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
      gname => 'SCOTT_MG',
      pgroup => 'JOB_PG',
      value => 'SALESMAN',
      priority => 60);
END;
/

BEGIN
   DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
      gname => 'SCOTT_MG',
      pgroup => 'JOB_PG',
      value => 'ANALYST',
      priority => 40);
END;
/

BEGIN
   DBMS_REPCAT.ADD_PRIORITY_VARCHAR2(
      gname => 'SCOTT_MG',
      pgroup => 'JOB_PG',
      value => 'CLERK',
      priority => 20);
END;
/

--After you have completed assigning your priority values, add the
--PRIORITY GROUP resolution method to your replicated table. The following API
--example shows that it is the second conflict resolution method for the
--specified column group (SEQUENCE_NO).

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'SCOTT',
      oname => 'EMP',
      column_group => 'EMP_CG1',
      sequence_no => 2,
      method => 'PRIORITY GROUP',
      parameter_column_name => 'JOB',
      priority_group => 'JOB_PG');
END;
/

--After you have defined your conflict resolution method, regenerate 
--replication support for the table that received the conflict
--resolution method.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--After replication support has been regenerated, resume replication
--activity by using the RESUME_MASTER_ACTIVITY procedure API.

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

Site Priority

Site priority is a specialized form of priority groups. Therefore, many of the procedures associated with site priority behave similarly to the procedures associated with priority groups. Instead of resolving a conflict based on the priority of a field's value, the conflict is resolved based on the priority of the sites involved.

For example, if you assign ORC2.WORLD a higher priority value than ORC1.WORLD and a conflict arises between these two sites, the value from ORC2.WORLD is used.

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

--You must add a SITE column to your table to store the site value in
--your replicated table. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure 
--to apply the DDL to the target table. Simply issuing the DDL may cause 
--the replicated object to become invalid.

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'SCOTT',
      oname => 'EMP',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE scott.emp ADD (site VARCHAR2(20))');
END;
/

--After you have inserted a new column into your replicated object,
--make sure that you re-generate replication support for the 
--affected object. This step should be performed immmediately
--after you alter the replicated object.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--After you have added the SITE column to your table, make sure 
--that this field is part of the column group that your site
--priority conflict resolution mechanism is used for. Use the 
--ADD_GROUPED_COLUMN procedure to add this field to an existing
--column group. If you do not already have a column group, you can create a
--new column group using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure.

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'SCOTT',
      oname => 'EMP',
      column_group => 'EMP_CG1',
      list_of_column_names => 'MGR, HIREDATE, SAL, site');
END;
/

--Before you begin assigning a site priority value to the sites in your 
--replicated environment, you must create a site priority group that "holds"
--the values that you defined.

BEGIN
   DBMS_REPCAT.DEFINE_SITE_PRIORITY (
      gname => 'SCOTT_MG',
      name => 'SITE_PG');
END;
/

--Define the priority value for each of the sites in your replication
--environment using the DBMS_REPCAT.ADD_SITE_PRIORITY_SITE procedure. 
--Execute this API as often as necessary until you have defined a site 
--priority value for each of the sites in our replication environment.

BEGIN
   DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
      gname => 'SCOTT_MG',
      name => 'SITE_PG',
      site => 'ORC1.WORLD',
      priority => 100);
END;
/

BEGIN
   DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
      gname => 'SCOTT_MG',
      name => 'SITE_PG',
      site => 'ORC2.WORLD',
      priority => 50);
END;
/

BEGIN
   DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
      gname => 'SCOTT_MG',
      name => 'SITE_PG',
      site => 'ORC3.WORLD',
      priority => 25);
END;
/

--After you have completed assigning your site priority values, add the 
--SITE PRIORITY resolution method to your replicated table. The following 
--API examples shows that it is the third conflict resolution method
--for the specified column group (SEQUENCE_NO).

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'SCOTT',
      oname => 'EMP',
      column_group => 'EMP_CG1',
      sequence_no => 3,
      method => 'SITE PRIORITY',
      parameter_column_name => 'SITE',
      priority_group => 'SITE_PG');
END;
/

--After you have defined your conflict resolution method, regenerate 
--replication support for the table that received the conflict
--resolution method.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--After replication support has been regenerated, resume replication
--activity by using the RESUME_MASTER_ACTIVITY procedure API.

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/