Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Question on asynchronous data propagation from a local to the central oracle database

Re: Question on asynchronous data propagation from a local to the central oracle database

From: Pete Sharman <>
Date: 2 Jul 2002 10:45:04 -0700
Message-ID: <>

In article <>, says...
>I am in the process of evaluating various methods for propagating data
>from a local Oracle database to a central Oracle database. Oracle
>version is yet undecided but it would either be Oracle 8.1.7 (I know
>it gets desupported by December 2003) or Oracle 9i on Solaris m/c.
>The local database that I refer to, would be a database located in
>offices located at geographical locations that offer poor
>infrastructure and network bandwidths. The number of local databases
>could be up to 150-200.
>The central database shall be 1 and shall be used by users from
>offices that are in developed countries that offer good network
>bandwidths. Thus, both the central as well as the local database can
>be in use by different set of users at the same time.
>The users would be accessing both these databases over web based
>The requirement is that the data that gets added / modified / deleted
>at the local database site should be propagated to the central
>database but, in an asynchronous mode.
>I have read about data replication methods provided by Oracle. I've
>the following queries.
>1. Would it be possible to replicate data off a table from a local
>database to
> the central database while the table at the central database itself
>might be
> getting its own data from users? i.e. Table A is being inserted
>into in the
> local database at site A. Oracle replication shall try and
>propagate the
> insert to central site C. At that moment, a user B might be
>inserting data
> in the table A at central site as well. Would replication work when
>the table
> at the master site would also be active? I guess yes but, need
> from some one..

Yes. Otherwise multi-master replication or master-updatable snapshot replication would not work.

>2. The data from the local database needs to be available at the
>central site
> in near real-time but, not real-time. I gather that the jobs to
> data from the local to the central database shall get executed at a
> frequency. Can this frequency be set to 1 minute? Is anyone aware
>of any
> repercussions if the frequency is kept so low?

I tend to not go below 5 minutes because then the overhead of checking whether there are rows to move or not MAY cause a performance issue. Note the MAY is in uppercase because you can't confirm this till you do quite a bit of testing. However, near real-time may not be possible for you anyway if there are network problems like you stated earlier.

>3. I can also think of Oracle advanced queues as another Oracle
> offering that could prove useful to carry out the data propagation
>from the
> local to the central site. How reliable is Oracle AQ in terms of
> robustness and maintainability?

Replication uses the AQ technology anyway.

>4. The application shall be developed using Java and shall get
>deployed at both
> the central as well as the local sites either using a Web server or
> server. EJB 2.0 specifications do specify message driven beans. I
>feel that
> the data propagation can also be carried out using JMS and the
> driven beans. Any feedback on this thought?

Never tried this apporach so I can't comment.


HTH. Additions and corrections welcome.


SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Tue Jul 02 2002 - 12:45:04 CDT

Original text of this message