Re: Questions for Oracle advanced replication again

Shi

When the connection to the second database is down for whatever reason (network is down, database is down etc.), transactions against replicated tables on the first database still succeed. The way replication works is that a trigger is built on the table that copies the change to a queue on the same database, so a transaction is completed when the replicated table is updated AND the trigger is successful. If the transaction is committed at this point, the data in the transaction is available for anyone on database 1 to see. When the connectivity is re-established to the second database, the transaction will be pushed out of the queue on the first database to the table on the second database, and then be available for everyone to see on the second database.

Because the data is committed in the replicated table and simply queued ready for the connection to be re-established, you never run into the resource consumption problem you mention.

HTH. Pete

Shi yousong wrote:

> Hi ,
>
> Thanks for your comment about my question. Now I try to mention my question
> more clearly.
>
> I plan to create a failover infrastructure by using 2 fully replicated
> Database DB1 and DB2. Now say DB1 has lost the connection to DB2 and one
> client session tries to update DB1 in a transaction A. Oracle system
> treats the transaction as deferred, put it into a queue. Since no network
> connection to DB2, transaction A can not apply on it.
>
> Suppose we use serial propagation, Oracle will work in two-step commit
> method. Is that right ? So transaction A should not be seen on DB1,that means
> the query for the same data should display the value before the transaction A
> starts. This will keep the DB1 and DB2 identical. However any update attempt
> will be deferred by putting the propagation job into a background queue, and
> resources held by the transaction will still be occupied. Say in a OLTP env.
> perhaps all the system resources will be run out if the situation lasts 3-4
> hours or a day. And than I get my conclusion:
> We can not use 2 fully replicated Oracle Server to form a Failover
> infrastructure no matter how to configure them, since if any one of the two
> is not available, the whole effect is the database becomes read-only.
>
> Hope I am totally wrong. Otherwise I just get a task from my manager that can
> not be implemented by using Oracle advanced replication facility.
>
> regards,
>
> Sam Shi
>
> Pete Sharman wrote:
>
> > One thing I would add to Tapan's comments. If you are just starting out
> > with replication, get yourself on the training for it, or get someone who
> > knows it to set it up for you. There are some issues that you can cause
> > yourself untold pain with if you configure it incorrectly. Once you know
> > what you're doing though, it's pretty straight forward.
> >
> > Pete
> >
> > Shi yousong wrote:
> >
> > > Hi,
> > >
> > > I am involved in a project to setup a replication infrastructure and
> > > plan to use the Row-Level async replication facility to implement a
> > > failover Oracle database infrastructure, so any change to the DB1 will
> > > be propagated to DB2 and vice versa. Here the questions are raised.
> > > What will happen if the connection between DB1 and DB2 is cut off?
> > > a. Say transaction A updates a table in DB1. Now can the transaction
> > > A be seen on DB1 when DB2 is not available? How about the resources
> > > occupied by transaction A ? released or still held on ?
> > > b. At the moment if DB1 and DB2 are identical to each other ?
> > > c. After 2-3 hours, the connection is restored for example, can Oracle
> > > automatically make the 2 DB consistent again?
> > >
> > > Thanks in advance.
> > >
> > > Database Developer
> > > Sam Shi

Received on Thu Sep 30 1999 - 12:11:35 CDT