Home » Server Options » Streams & AQ » Streams data conflict
Streams data conflict [message #347467] Thu, 11 September 2008 15:32 Go to next message
shrinika
Messages: 231
Registered: April 2008
Senior Member
Hello,

I am using three database DB1,DB2,DB3. I am using streams to replicate the
scott.dept table for all three database. I am using update conflict for dept
table. The database version is 9.2.0.8

Here is the code i ran for all three database.

DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'deptno';
cols(2) := 'dname';
cols(3) := 'loc';
dbms_apply_adm.set_update_conflict_handler(
object_name => 'scott.dept',
method_name => 'overwrite',
resolution_column => 'deptno',
column_list => cols);
end;
/



Here is the table content in all three database.

scott@DB1.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON



scott@DB2.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

scott@DB2.US.ORACLE.COM> 


scott@DB3.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

scott@DB3.US.ORACLE.COM> 



Now i inserted one record in DB1 and it is replicated in all the database.

scott@DB1.US.ORACLE.COM> insert into dept
  2  values(50,'IT','HOUSTON');

1 row created.

scott@DB1.US.ORACLE.COM> commit;

Commit complete.


After inserting the record, it is replicated to all three db

scott@DB1.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 IT             HOUSTON

scott@DB1.US.ORACLE.COM> 

scott@DB2.US.ORACLE.COM> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 IT             HOUSTON

scott@DB2.US.ORACLE.COM> 

scott@DB3.US.ORACLE.COM> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 IT             HOUSTON

scott@DB3.US.ORACLE.COM> 



Now i want to test the update conflict. I am updating the same record
in DB1,DB2 and DB3.


scott@DB1.US.ORACLE.COM> update dept
  2  set dname='db1',loc='db1'
  3  where deptno=50;

1 row updated.

scott@DB1.US.ORACLE.COM> commit;

Commit complete.

scott@DB1.US.ORACLE.COM> 


scott@DB2.US.ORACLE.COM> update dept set dname='db2',
  2  loc='db2' where deptno=50;

1 row updated.

scott@DB2.US.ORACLE.COM> commit;

Commit complete.

scott@DB2.US.ORACLE.COM> 

scott@DB3.US.ORACLE.COM> update
  2  dept set dname='db3',loc='db3'
  3  where deptno=50;

1 row updated.

scott@DB3.US.ORACLE.COM> commit;

Commit complete.

scott@DB3.US.ORACLE.COM> 


After the above change, the output is as follows.

scott@DB1.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 db2            db2

scott@DB1.US.ORACLE.COM> 

scott@DB2.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 db1            db1

scott@DB2.US.ORACLE.COM> 

scott@DB3.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 db2            db2

scott@DB3.US.ORACLE.COM> 


So the record deptno=50 is not consistant for all
three database. Can any please help me why the record(deptno=50)
is not consistent?

[Updated on: Fri, 12 September 2008 08:19]

Report message to a moderator

Re: Streams data conflict [message #382490 is a reply to message #347467] Thu, 22 January 2009 11:21 Go to previous message
upanwar82
Messages: 5
Registered: January 2009
Location: Cincinnati
Junior Member


Hi ,I am new for this site..

I have implemented bi-directional streams replication at two sites and working properly..

I have one issue...there are some tables which are not in sync between two databases.

How can i sync these tables.

I saw some packages dba_diffrences and recitifier they worked for one way repilcation...

Also, is there is any way that we dont need to capture and propagation process while sync the tables.

is there is any way to do it...

thanks,
New member
Previous Topic: Db link did not work between 2 dbs
Next Topic: Message posting via JMS queue in Oracle 11i ?
Goto Forum:
  


Current Time: Mon Oct 20 00:20:06 CDT 2014

Total time taken to generate the page: 0.11905 seconds