2

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

Improve this question
6
  • Why not just create a database link that allows you to access B from A? Commented Nov 8, 2009 at 21:06
  • Well I will use one, but the issue is to ensure the integrity of data from each side, if the update somes from A or from B, both of the database have to be updated. I'm reading about materialized views but I don't really get But precisly, how would you do that with DBlink? Commented Nov 8, 2009 at 21:15
  • 1
    The main issue is how to resolve conflicts if the same data is updated on Database A and Database B. Is there a business reason for keeping the two databases separate versus just having both sets of users update one single database? Commented Nov 8, 2009 at 23:19
  • Well, yes the databases structures will be slightly different. Besides web user don't have direct access to the production. The conflict resolution is indeed an issue i want to handle Thanks Commented Nov 9, 2009 at 9:40
  • 1
    What kind of integrity are you trying to enforce, or do you just want updates from A to be propagated to B? do you need changes to B propagated back to A? Commented Nov 9, 2009 at 11:42

3 Answers 3

Reset to default
1

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;
/
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.