6
Conflict Resolution

This chapter illustrates how to define conflict resolution methods for your replicated environment. The following topics will be discussed:

Prepare for Conflict Resolution

Though you may take great care in designing your database and front-end application to avoid conflicts that may arise between multiple sites in a replicated environment, you may not be able to completely eliminate the possibility of conflicts. One of the most important aspects of replication is to ensure data convergence at all sites participating in the replicated environment.

When data conflicts do occur, you need a mechanism to ensure that the conflict will be resolved in accordance with your business rules and that the data converges correctly at all sites.

Oracle replication offers a variety of conflict resolution methods that will allow you to define a conflict resolution system for your database that will resolve conflicts in accordance with your business rules. If you have a unique situation that Oracle's pre-built conflict resolution methods cannot resolve, you have the option of building and using your own conflict routines.

Plan

Before you begin implementing conflict resolution routines for your replicated tables, you should take the time to analyze the data in your system to determine where the most conflicts may occur. For example, static data such as an employee number may change very infrequently and is not subject to a high occurrence of conflicts. An employee's customer assignments, however, may change often and would therefore be prone to data conflicts.

Once you have determined where the conflicts are most likely to occur, you need to determine how to resolve the conflict. Do you want the latest change to have precedence, or should one site over another have precedence?

As you read each of the sections describing the different conflict resolution routines, you will learn what each method is best suited for. Take the time to read each section and then think about how your business would want to resolve any potential conflicts.

After you have identified the potential "problem" areas and have determined what business rules would resolve the problem, use Oracle's conflict resolution routines (or one of your own) to implement a conflict resolution system.

Create Update Conflict Resolution Methods

The most common data conflict that you will encounter is 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 resolve 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 the "ADD_conflicttype_RESOLUTION procedure" section and the "Overwrite and Discard" section in the Oracle8i Replication for more information.

Note:
This section will use objects not found in the other scripts within this book (since the configuration orc1.world, orc2.world, orc3.world, and snap1.world contains 3 master sites and 1 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 routines, you need to quiesce
--the master group that contains the table that you want to apply the conflict
--resolution routine to.

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

--All Oracle conflict resolution routines are based on logical column groupings
--termed "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 routine for a specified table. This example will create a
--"Overwrite" conflict resolution routine. 

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 routine, you need to 
--regenerate replication support for the table that received the conflict
--resolution routine.

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

--After replication support has been regenerated, you need to 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 will 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 routines, you need to quiesce
--the master group that contains the table that you want to apply the conflict
--resolution routine to.

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

--All Oracle conflict resolution routines are based on logical column groupings
--termed "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 routine for a specified table. This example will create a
--"MINIMUM" conflict resolution routine. 

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 routine, you need to 
--regenerate replication support for the table that received the conflict
--resolution routine.

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

--After replication support has been regenerated, you need to 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.

There are several elements 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 routines, you need to quiesce
--the master group that contains the table that you want to apply the conflict
--resolution routine to.

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

--If the target table does not already contain a timestamp field,
--then you need to 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,
--you need to 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, you need to create a
--trigger that records the timestamp of when a row is either inserted
--or updated. This recorded value will be 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 routines are based on logical column groupings
--termed "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 routine for a specified table. This example will specify the
--"LATEST TIMESTAMP" conflict resolution routine 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 routine, you need to 
--regenerate replication support for the table that received the conflict
--resolution routine.

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

--After replication support has been regenerated, you need to 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 routines, you need to quiesce
--the master group that contains the table that you want to apply the conflict
--resolution routine to.

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

--All Oracle conflict resolution routines are based on logical column groupings
--termed "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 routine for a specified table. This example will specify the
--"ADDITIVE" conflict resolution routine 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 routine, you need to 
--regenerate replication support for the table that received the conflict
--resolution routine.

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

--After replication support has been regenerated, you need to 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;
/

--You need to 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 will "hold" the values that you defined.

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

--The DBMS_REPCAT.ALTER_PRIORITY_datatype procedure is avaiable in several
--different versions; there is a version for each available datatype 
--(NUMBER, VARCHAR2, etc.)  See "ALTER_PRIORITY_datatype procedure" 
-- for more information.  Execute this API as often as 
--necessary until you have dfined 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, you need to add the
--PRIORITY GROUP resolution method to your replicated table. The following API
--examples 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 routine, you need to 
--regenerate replication support for the table that received the conflict
--resolution routine.

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

--After replication support has been regenerated, you need to 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. Thus, many of the procedures associated with site priority behave similarly to the procedures associated with priority groups. Instead of resolving conflicts based on the priority of a field's value, the conflict will be 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 will be used.

CONNECT repadmin/repadmin@orc1.world

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

--You will need to 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,
--you need to 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, you need to 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 will "hold"
--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, you need to
--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 routine, you need to 
--regenerate replication support for the table that received the conflict
--resolution routine.

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

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

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

Create Uniqueness Conflict Resolution Methods

In a replicated environment, you may encounter situations where you will receive a conflict on a unique constraint, often resulting from an insert. If your business rules allow you to delete the duplicate row, you can define such resolution with Oracle's pre-built conflict resolution routines.

More often, however, you will want to modify the conflicting value so that it no longer violates the unique constraint; modifying the conflicting value will ensure that you don't lose important data. Oracle's pre-built uniqueness conflict resolution routine can make the conflicting value unique by appending a site name or a sequence number to the value.

An additional component that accompanies uniqueness conflict routines is a notification facility. The conflicting information will be modified by Oracle so that it can be inserted into the table, but you should be notified so that you can analyze the conflict to determine if the record should be deleted, or the data merged into another record, or a completely new value be defined for the conflicting data.

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

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      GNAME => 'scott_mg');
END;
/

--As you might expect, a uniqueness conflict resolution routine detects and
--resolves conflicts encounted on a column(s) with a UNIQUE constraint. Use
--the ALTER_MASTER_REPOBJECT procedure (described) to add
--a UNIQUE constraint to the EMP table.

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

--After you have add the UNIQUE constraint to your replicated table,
--you need to make sure that you re-generate replication support for
--the affected table. 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;
/

--The following table (conf_report) will store the messages received from
--your notification facility.

BEGIN
   DBMS_REPCAT.EXECUTE_DDL(
      GNAME => 'scott_mg',
      DDL_TEXT => 'CREATE TABLE scott.conf_report (
                   line NUMBER(2),
                   txt VARCHAR2(80),
                   timestamp DATE,
                   table_name VARCHAR2(30),
                   table_owner VARCHAR2(30),
                   conflict_type VARCHAR2(7))');
END;
/

CONNECT scott/tiger@orc1.world

--The following package (notify) will send a notification to the CONF_REPORT
--table when a conflict is detected.

--The conflict resolution notification package that is created in this script is
--described in detail in the Oracle8i Replication book (see the "User-Defined
--Conflict Notification Methods" section to learn more about the following
--package and procedures).

CREATE OR REPLACE PACKAGE notify AS
   FUNCTION emp_unique_violation(ename IN OUT VARCHAR2,
      discard_new_values IN OUT BOOLEAN)
   RETURN BOOLEAN;
END notify;
/

CREATE OR REPLACE PACKAGE BODY notify AS
   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 scott.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;
   FUNCTION emp_unique_violation(ename 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
      BEGIN
         SELECT global_name INTO local_node FROM global_name;
      EXCEPTION WHEN others THEN local_node := '?';
      END;
      conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE EMP 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 SITE NAME METHOD';
      conf_report(4) := 'ENAME: ' || ename;
      conf_report(5) := NULL;
      report_conflict(conf_report, 5, conf_time, 'EMP', 'SCOTT', 'UNIQUE');
      discard_new_values := FALSE;
      RETURN FALSE;
   END emp_unique_violation;
END notify;
/

CONNECT repadmin/repadmin@orc1.world

--The following package will be replicated to all of the master sites in your
--replication environment; this will ensure that the notification facility is
--available at all master sites.

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type =>  'PACKAGE',
      oname => 'notify',
      sname => 'SCOTT');
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type =>  'PACKAGE BODY',
      oname => 'notify',
      sname => 'SCOTT');
END;
/

--After you have completed building your notification facility you need to 
--add the notification facility as one of your conflict resolution methods 
--(even though it only notifies of a conflict). The following API example will
--demonstrate adding the notification facility as a USER FUNCTION.

BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      SNAME => 'scott', 
      ONAME => 'emp', 
      CONSTRAINT_NAME => 'emp_ename_unique',
      SEQUENCE_NO => 1, 
      METHOD => 'USER FUNCTION',
      COMMENT => 'Notify DBA',
      PARAMETER_COLUMN_NAME => 'ename',
      FUNCTION_NAME => 'scott.notify.emp_unique_violation'); 
END;
/

--After you have added the notification facility, you are ready to add the
--actual conflict resolution method to your table. The following API example
--will demonstrate adding the APPEND SITE NAME uniqueness conflict resolution
--routine to your replicated table.

BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      SNAME => 'scott',
      ONAME => 'emp',
      CONSTRAINT_NAME => 'emp_ename_unique',
      SEQUENCE_NO => 2,
      METHOD => 'APPEND SITE NAME',
      PARAMETER_COLUMN_NAME => 'ename');
END;
/

--After you have defined your conflict resolution routine(s), you need to 
--regenerate replication support for the table that received the conflict
--resolution routine(s).

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

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

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

Create Delete Conflict Avoidance Methods

Unlike update conflicts where there are two values to compare, simply deleting a row makes the update conflict resolution methods described in the previous section ineffective since only one value would exist.

The best way to deal with deleting rows in a replication environment is to "avoid" the conflict by marking a row for deletion and periodically purging the table of all "marked" records. Since you are not physically removing this row, your data will be able to converge at all master sites if a conflict arises because you still have two values to compare (assuming that no other errors have occurred). After you are sure that your data has converged, you can purge "marked" rows using a replicated purge procedure.

When you are developing your front-end application for your database, you will probably want to "filter out" the columns that have been marked for deletion; this will make it appear to your users as though the row was physically deleted. Simply exclude the rows that have been marked for deletion in the SELECT statement for your data set; for example, a select statement for a current employee listing might look like:

SELECT * FROM emp WHERE remove_date IS NULL;

This section will describe how to prepare your replicated table to avoid delete conflicts. You will also see how to use procedural replication to purge those records that have been "marked" for deletion.


CONNECT repadmin/repadmin@orc1.world

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

--You need to add a column to your replicated table that will store the
--mark for deleted records. It is advisable to use a timestamp to mark your
--records for deletion (timestamp will reflect when the record was marked for
--deletion. Since you will use a timestamp, your new column will need to be
--a DATE datatype.  Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to add
--the REMOVE_DATE column to your existing replicated table.

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

--After you have inserted a new column into your replicated object,
--you need to 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;
/

--The following package will be replicated to all of the master sites in your
--replication environment. This package will purge all "marked" records from
--the specified table.

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'PACKAGE',
      oname => 'PURGE',
      sname => 'SCOTT',
      ddl_text => 'CREATE OR REPLACE PACKAGE scott.purge AS
                      PROCEDURE remove_emp(purge_date DATE);
                   END;');
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'PACKAGE BODY',
      oname => 'PURGE',
      sname => 'SCOTT',
      ddl_text => 'CREATE OR REPLACE PACKAGE BODY scott.purge AS
                      PROCEDURE remove_emp(purge_date IN DATE) IS
                      BEGIN
                         DBMS_REPUTIL.REPLICATION_OFF;
                         LOCK TABLE scott.emp IN EXCLUSIVE MODE;
                         DELETE scott.emp WHERE remove_date IS NOT NULL AND
                            remove_date < purge_date;
                         DBMS_REPUTIL.REPLICATION_ON;
                      EXCEPTION WHEN others THEN
                         DBMS_REPUTIL.REPLICATION_ON;
                      END;
                   END;');
END;
/

--After you have created your package (package and package body), you need to
--generate replication support for each component. After you generate
--replication support, a synonym will be created for you and added to your
--master group as a replicated object. This synonym will be labeled as
--DEFER_PURGE.REMOVE_EMP.

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

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'PURGE', 
      type => 'PACKAGE BODY',
      min_communication => TRUE); 
END;
/

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

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

6Conflict Resolution