An example of Conflict Resolution Method

Here I am going to configure Oracle to use latest timestamp method to settle conflict ON SCOTT.EMP table’s EMPNO column.

We need to

1. Define a column group for use by the resolution method.
2. Define the resolution method.
3. Add additional support as required.

1. Define the Column Group

repA> BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'scott_repg');
END;

repa>execute dbms_repcat.make_column_group('SCOTT','EMP','EMP_COLGP','*');

In this example, we are defining a column group called EMP_COLGP on the EMP table in the SCOTT schema.
This column group will include every column in the table, since the ‘*’ is being used.
It is not necessary to include all columns in the column group. Since the EMPNO column can act as a primary key, we could have only defined EMPNO in our column group, which would have caused Oracle to resolve only conflicts on that column. By defining all columns in the column group, any conflict within the column group will use the conflict resolution method.

2. Define the Conflict Resolution Method

execute dbms_repcat.add_update_resolution(sname => 'SCOTT',
oname => 'EMP',
column_group => 'EMP_COLGP',
sequence => 1,
method => 'LATEST TIMESTAMP',
parameter_column_name => 'EMPNO');

Returned error
ERROR at line 1:
ORA-23325: parameter type is not TIME, TIME WITH TIME ZONE, TIMESTAMP,
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, DATE
ORA-06512: at “SYS.DBMS_REPCAT_CONF”, line 814
ORA-06512: at “SYS.DBMS_REPCAT_CONF”, line 1639
ORA-06512: at “SYS.DBMS_REPCAT”, line 307
ORA-06512: at line 2

Reason : EMPNO is not a DATETIME/TIMESTAMP field which Oracle could use in case of a Conflict. I will use SITE PRIORITY resolution method.

If we wanted to define a second method, we could use the same command, but replace the method parameters and change the sequence to equal two. Oracle will apply the two conflict resolution methods in the sequence order.

SQL> execute dbms_repcat.define_site_priority('scott_repg','SITE_PRI');
PL/SQL procedure successfully completed.

SQL> execute dbms_repcat.add_site_priority_site(‘scott_repg’,’SITE_PRI’,’repB’,10);
PL/SQL procedure successfully completed.

SQL> execute dbms_repcat.add_site_priority_site(‘scott_repg’,’SITE_PRI’,’repA’,100);
PL/SQL procedure successfully completed.

I set here repA as a primary site with strength of 100 while repB is only 10. In case of conflict Oracle should use values at SiteA.

repA>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'scott',
oname => 'dept',
type => 'TABLE',
min_communication => TRUE);
END;

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

repA> BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'scott_repg');
END;

repA> update scott.emp set STATUS='SITE_A';

repA> select status from scott.emp;

STATUS
———-
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A

repB>update scott.emp set STATUS='SITE_B';

.repB>select status from scott.emp;

STATUS
———-
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B

Execute manually job dbms_defer_sys.push or wait for INTERVAL to see an update.

repB> select status from scott.emp;

STATUS
———-
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A

Please see updated COLUMN value from SITE_B to SITE_A at Site_B as a result of site priority.