Re: MULTI SITE REPLICATION QUESTION

From: Lewis C <lewisc_at_excite.com>
Date: Fri, 15 Apr 2005 23:36:29 GMT
Message-ID: <6fj061pm9r5me5som772fqfjmtfrhp858g@4ax.com>

On 15 Apr 2005 12:20:54 -0700, "Brian" <brian_at_databaseknowledge.com> wrote:

>We want to set up 3 replicated databases at 3 sites within the US. Each
>database needs to be fully operational allowing
>select/insert/update/deletes. Each change needs to be replicated to the
>other sites. What are the different ways to accomplish this?
>
>We tried working with materialized views within the same schema and the
>db just can't keep up with the load.
>
>We are considering implementing db links and triggers. How practical is
>this?
>
>Is it possible to setup a 3 way RAC with each node located in another
>state? Essentially this is what we want to do.

RAC is not doable across states.

If each site needs to be active why aren't you using multi-master replication? Look in the advanced replication manual.

There are third-party replication tools available, quest, goldengate, etc. You'll have to pay for those.

If you are considering implementing db_links and triggers, read the below text from the manual I mentioned. I doubt you can code it to perform better than oracle does.

The following describes the process of asynchronous replication:

A user issues DML statement or executes a wrapper for a replicated procedure.

After a table has been set up for replication, any DML that a user commits on the table is captured for replication to all other master sites.

For each row that is inserted, updated, or deleted, an internal trigger creates a deferred remote procedure call (RPC) and places it in the deferred transaction queue. The deferred transaction queue contains all deferred RPCs.

If a procedure has been replicated and its wrapper is executed at a master site, then the procedure call is placed in the deferred transaction queue.

2. The deferred transaction queue stores deferred RPCs.

Each transaction in the deferred transaction queue has a list of destinations that define where the deferred transaction should be propagated; this list contains all master sites except for the originating site. There is one deferred transaction queue for each site, and this one queue can be used by multiple replication groups.

3. Propagation sends deferred transaction queue entry to destination.

At scheduled intervals or on-demand, the deferred transactions in the deferred transaction queue are propagated to the target destinations. Each destination may have a different interval.

4. The deferred transaction queue entry applied at a remote destination.

As a deferred transaction is being propagated to a target destination, each deferred RPC is applied at the destination site by calling an internal package. If the deferred transaction cannot be successfully applied at the destination site, then it is resent and placed into the error queue at the destination site, where the DBA can fix the error condition and re-apply the deferred transaction.

When a deferred transaction queue entry is applied at the remote destination, Oracle checks for data conflicts. If a conflict is detected, then it is logged at the remote location and, optionally, a conflict resolution method is invoked.

5. When a deferred transaction has been successfully pushed to all remote master sites, it is not purged from the deferred transaction queue at the originating site immediately. It may be purged later by a purge job, which runs at a user-defined interval.

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Fri Apr 15 2005 - 18:36:29 CDT