Synchronizing oracle databases

[Vinu Neelakandhan]

Hi all :
This is a general question. Would appreciate your feedback on this.

Is it possible to synchronize two different instances of databases run by two different instances of the same application running in two different sites? Does Oracle database support this type of synchronization?

Or what are the different types of synchronizations which could be achieved by Oracle databases?

Any pointers or white papers on this would be highly appreciated.

Thanks in advance,
Vinu

[Printaporn_1]

Hi Vinu,

For Oracle 9i , you need to goto Real Application Cluster (RAC) and Oracle 8i it's Oracle parallel Server (OPS)

HTH

[Ian Dennison_1]

Vinu,

If you are thinking of DR (Standby database to the primary database) then oracle supports shipping the Production redo logs across and applying them to the Standby database. But to build this requires an offline Backup of Production restored to Standby.

All of this is covered on the Oracle website.

Share and Enjoy! Ian

[Vinu Neelakandhan]

Well, in this case, both the databases are Master database and both are active. These are independenty being accessed by two instances of the applications in two different cities.

These two database instances need to be synch'ed up. Moreover, it's not a shared database as well. Can this be done only with a shared database (Parallel server)?

Thanks much for the responses.
Vinu

[Christian Gebhardt]

Hello Vinu,

- With OPS/RAC you have different Servers/Instances which have one shared database(datafiles)

- to synchronize two separate databases(datafiles) with separate instances/servers you can use
-- standby database which work fine with oracle9i
-- oracle replication (not easy to configure and to maintain)
-- own mechanism via sqlnet,rcp, scp, ftp, ...

Chris

[Steven E. Protter]

Oracle parellel server/RAC depending on version will work. You need good bandwidth and lots of resource on both boxes because its resource intensive.

The above options will give you real time synchronization.

You need full oracle licenses per cpu on both boxes.

If you don't need 24/7 synchronization you can do an export/import process with cron and update overnight.

[Henrique Silva_3]

Vinu, this sounds to me like master to master replication.

The problems that I see right away are the conflicts that may arise here, for the DBs updating the same data on both sides, and then, when they try to synchonize it, conflicts will arise, which you will have to think ahead of time on how you will solve them.

In order to not have conflicts you would have to have a 2 phase commit, but that can be horrible when running over the WAN, and only very few applications really need it, such as banking, etc.

Without more info from what you are trying to do, it is hard to come up with solutions, but was it was stated before, there are many things you can do, from exp/imp, to stand by DBs to replication, to parallel servers.

Cheers,

Henrique Silva

[Shirish Joshi]

Hi,
Please refer
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96567/repmaster.htm#32386

(OTN free registration reqd).

--Shirish

[mghosalk]

Vinu,

Standby/RAC is not the solution.

You should check Replication solutions.
Either Oracle's Replication solution or
3rd Party (SharePlex)

-Mandar

[R. Allan Hicks]

We are planning to have a standby database for failover. Under 8i, there was a method of putting a second database in standby and shipping the archived redo logs to it. If the primary database bit the dust, the secondary database could be quickly started and work could resume.

This had the drawback of the possibility of losing the data in the unarchived redo logs.

Under 9i, there is a product called DataGuard that not only ships the archived redo logs across the net, but allows a copy of logwriter to write your unarchived data across the net so that the failover has less data loss. There are configuration options that you can set that will prevent any data loss.

I've used master to master replication and I basically like that product. It is good for load leveling and is a marginally good fit for redundant system operation.

The only drawback I had on it was that it could be a pain to initialize the second copy. You pretty much have to shutdown both sides and do a cold back up and restore. Start up the databases and configure the replication and then you can go back into production. Oracle has tools to synchronize the database, but I for one had a hard time using them in my environment. Mostly, because we are 24x7.

Take a look at some of the lit on http://otn.oracle.com/index.html. You'll find discussions of ORAC (Oracle Application Clusters) and Data Guard. Both are good solutions.