Re: Replication and update conflict resolution

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Thu, 30 Dec 1999 10:06:34 -0800
Message-ID: <386B9F2A.CE9E29EC@us.oracle.com>

Have a look at the API Reference Manual for Replication. It has the following code.

HTH. Pete

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_at_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;
/

Violin wrote:

> Hello,
>
> I have 2 databases(8.0.5),and SIDs are DB1,DB2
> Now I need to replicate data from DB1 to DB2 (replicate SCOTT's schema),
> I configure multi-master replication with Replication Manager.
>
> My question is,I want to use site priority for the update conflict resolution.
> For example.....If DB1 and DB2 update DEPT at the same time,
> DB1 > update dept set dname = 'MIS DB1' where deptno = 10;
> DB1 > commit;
>
> DB2 > update dept set dname = 'MIS DB2' where deptno = 10;
> DB2 > commit;
>
> When DB1 and DB2 replicating transaction to each other,
> I hope DB1 site is high priority and DB2 is low.
> So after propagating the transactions above,
> The dname of  DEPT in DB1 and DB2 should be 'MIS DB1' where deptno = 10;
>
> But I don't know how to configure the resolution.
> Can anybody give me some sample code?
>
> Best Regards.
>
> Violin.
> violin.hsiao_at_mail.pouchen.com.tw

Received on Thu Dec 30 1999 - 12:06:34 CST