How do I synchronize two Oracle Databases?

Question

I want to create a project in which it is required to synchronize local database with remote database. Changes made in local database must be reflected to remote database. This must be done synchronously. I have application which does CRUD operations on local database written in java/jpa. Synchronization should be triggered from java code. I thought of triggers on each table of database which will maintain changes in local database. But I doubt its secure or not. After googling I found out that oracle lite database is best for synchronizing two databases, but I need to get started quickly. Operating system I am using is Windows Xp. Database size is around 2gb.

I did created thread for achieving same thing in java but from suggetion I restarted same thread in the database context.

Answer 1

Hi Xinus,

First of all I'd suggest you reconsider your design. The simplest way to repicate your data is through views as suggested in this SO. You could create a DATABASE LINK between your two DBs and create views at the remote site that would query the local database. This would be the simplest way to have Real-Time synchronization (less code, less maintenance).

If you really want to replicate your data synchronously, you should read the Replication Guide. You could go with materialized views. You will need to define materialized view logs on your tables at your master site. At the remote site you will create ON COMMIT REFRESH materialized views.

Answer 2

What is the goal of synchronizing the database? Should remote database be read-only? If so DataGuard is an option.

Answer 3

I've been mulling this problem for a few days as we need to do development in parallel with QA running their tests. We do have two separate Oracle instances, but keeping them in sync manually would have been a huge pain. I'm not a DBA, so just looking at the replication stuff made me run away in fear.

Instead, I fired up Navicat for Oracle and (ab)used the Data Transfer feature - makes keeping two schemas in physically separate databases synchronized pretty painless. A pretty damn useful tool! (I don't work for them, just like their tool.)

Answer 4

What you are referring to is called replication. Synchronization is something different.

If you have multiple devices with a data subset requirement, for example a region sales person only requires data that reflects the area he/she serves, then Oracle Database Lite Mobile Server is what you are looking for. Basically, you have a central Oracle database and your want some data to be pushed to the client. That client will make updates/inserts/deletes to the device and then they will sync. New information on the server will be pushed to their device while their changes will be uploaded to the server. You will require conflict resolution (server/client wins... Oracle Mobile Server also allows for custom built conflict resolution). Oracle Mobile Server is also use to provision your Application Software and Manage Devices remotely. The tool is designed so that you only require a central DBA. The remote machines do not require any DBA type of activities.