Re: Bi-directional data synchronization with basic replication

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Thu, 03 Mar 2005 09:32:14 +0100
Message-ID: <d06hu2$hoi$1@news5.zwoll1.ov.home.nl>

Romi wrote:

> Frank van Bortel <fvanbortel_at_netscape.net> wrote in message news:<d04hs1$k5f$1_at_news4.zwoll1.ov.home.nl>...
> 

>>Romi wrote:
>>
>>>Hi,
>>>
>>>I would have one central database and two sites databases, all oracle
>>>10g standard edition.
>>>Site 1 and 2 will have their own set of data while central database
>>>will have data from both sites as well as its own data. Each set of
>>>data is identified by site_id.
>>>
>>>Data can be added/modified/deleted in site database as well as central
>>>database.
>>>
>>>Thus there is a need for bidirectional data synchronization with
>>>conflict resolution through timestamp.
>>>
>>>I am planning to create tables in central database and updatable
>>>materialized views in site databases. data synchronization will happen
>>>every 3 hours.
>>>
>>>Is this supported in Oracle 10g standard edition i.e. with basic
>>>replication ? Info available w.r.t. basic replication is very
>>>confusing :-(
>>>
>>>Thanks,
>>>Romi
>>
>>AFAIK, replication of updateable materialized views is Advanced
>>Replication, not basic. Conflict resolution is adv., too.
>>
>>However, that is not the 10G term anymore, Streams is.
>>You're talking Multiple Source Replication (Ch 14 Oracle
>>Streams Replication Admin Mnl, b10728.pdf)

> 
> 
> 
> As per metalink doc id Note:67145.1, 10g standard edition 
> "Provides bi-directional replication with automated conflict detection
> and  resolution supports configurations that include a single
> updatable master site with multiple updatable or read-only snapshot
> sites."
> 
> Another place I read that standard edition supports only read-only
> materialized views.
> 
> btw, as per my knowledge, streams is supported only with enterprise
> edition.
> 
> could someone please help with concrete information whether our
> requirement would be met by standard edition.
> Thanks.
> Romi

I stand corrected: the note suggests it can be done. And I just *know* there was a nag when I tried it.... Just can't recall. Wasn't 10g, though, but 8iR3. I have not (yet) done this, using Streams.

Some thoughts/warnings:
I would be *very* careful with timestamp conflict resolution:
- can you guarantee, your instances run synchronized

   to the microsecond/nanosecond?
- make sure your timestamp is more accurate, than the

   default (millisecs): computers are powerful enough    to process more than 1000 rows per second.

I fail to see the need for a site id in your design; replication will know the sites by priority, or id, but that is in the replication packages, not the data you shuffle around.
Primary Keys would suffice, as long as you create your updateable materialized views with the:
"with primary key including new values"
option.
This is the old-fashioned Adv. Repl.; not Streams...

-- 
Regards,
Frank van Bortel

Received on Thu Mar 03 2005 - 02:32:14 CST