Re: Mirroring a table to remote instances

DA Morgan wrote:
> stephen O'D wrote:
> > Say I have a lookup table in a database that looks like
> >
> > ID (PK)
> > datafield1
> > datafield2
> > ...
> > datafieldx
> >
> > It will only ever have inserts and updates applied to it, never
> > deletes.
> >
> > There will probably be close to 30Million records in this table.
> >
> > I need to mirror changes to this table to mutliple sites through the
> > day (probably every 15 minutes or so) without copying the whole table
> > each time. What is the best way of doing this?
> >
> > One option I considered was to add a flag (replicated N or NULL) to the
> > table for each 'replication site' and when a row is inserted or updated
> > change the flag from null to 'N'. Then use the merge statement to
> > merge only those records that been added/changed. What I don't like
> > about this solution is
> >
> > 1. You need a column per replication site
> > 2. I don't think there is anyway for me to merge and update the flag to
> > null while having a consistent view of the data - ie merge where flag =
> > N would see different data than update set Flag = NULL where flag = N
> > meaning I would have to do something like:-
> >
> > begin
> > for rec in (select * from table where flag = N)
> > update/insert remote
> > update local
> > end loop;
> > end;
> >
> > I know I could set the transaction to serializable, but I fear the
> > volume of updates on this table could see a lot of failed transactions
> > with the 'cannot serialize access for this transaction error'.
> >
> > Another idea is to use AQ to broadcast the updates/inserts to each site
> > via a multi subscriber queue.
> >
> > I am pretty sure this is a common task - has anyone got any better
> > ideas/advice? Allowing the remote instance to query the master table
> > each time they need it in a query is not really an option due to the
> > number of selects they will be issuing against it!
> >
> > Thanks,
> >
> > Stephen.
>
> And your Oracle version is?
>
> Streams (AQ) would be my first choice.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

I really thought I included my version ... but clearly not! It is 9.2.0.6.

AQ certainly seems like the most simple solution, as it avoids any complexities surrounding consistent views of the data.

Any other options/suggest would be greatly appreciated! Received on Thu Dec 15 2005 - 09:06:47 CST