Oracle database synchronization

Question

Here is the context :

I have database A on one server, which is used from internal production. And database B on another server which provide some of the database A's information to a web site. A is updated internally, and B can is updated by the website's client.

What is the best solution to keep an intégrity between both database?

Thanks

Answer 1

Assuming that the table structures are the same for the tables you want to keep synchronized, look at Oracle's Advanced Replication to do this. Warning - set up can be complicated, even if you use a front-end like Grid/DB Control.

Answer 2

The database cannot make intelligent decisions to merge data. A distributed database will enforce the inherent concurrency control mechanisms you require. You will need to look at Oracle documentation on implementing distributed architecture, or rethink your current (or proposed) architecture.

If you simply want to link the databases, you will need to do this for each database:

create database link "LINK_NAME"
connect to USERNAME
identified by "PASS"
using 'OTHER_DB_NAME'

You can perform CRUD operations on tables in the remote database, just like you would locally, by using the @ symbol. For example:

INSERT INTO table_name@link_name VALUES('x');

will insert a row with one column into the remote database. You do not need to worry about concurrency in this instance.

You can use materialized views, but you will have out-of-date data. The basic syntax to create a materialized view:

CREATE MATERIALIZED VIEW HR.MY_MATERIALIZED_VIEW
NOCACHE
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
AS 
SELECT * FROM table_name;
/

Answer 3

I'm currently looking do something similar, keeping two different databases synchronised with each database having slightly different structures.

So I'd thought I'd mention Oracle Streams* as this is what I am looking to use. Like dpbradley's helpful answer, Streams can perform data replication but the tables in the different databases do not need to be same. See here for more information. A good diagram can be found by searching the above link for "Figure 1-12 Streams Configuration Sharing Information Between Databases"

*check the edition of Oracle database server software that both databases will be using as it appears that the full functionality of streams is only available in enterprise editions. Taken from here