asked on

Design of Distributed Databases

Objective:

I want to design and implimentation using oracle the following:

*** Design of Distributed Databases ***

And after that ( which I am not discussing at the moment but later in some other question)

*** Query Decomposition ***
*** Data Localization and Optimization of Distributed Queries ***

Scenario:
I am oracle Developer and a little bit knowledge of DBA

So .. .. For "Design of Distributed Databases"

What type of hardware specifications are required.
From where I am going to start.
I am currently using Oracle 9i on windows xp and 2000professional.

In Short I just want to impliment this in lesser time, lesser cost, highest achievements.

Healthy web links/referenes/book links are helpful for me but if you explain yourself, It' s great effort for me

===> I know Orale well but very much new to DDBMS(Practical approach)

Thanks ... remercier ... bedanken

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Muhammad Ahmad Imran

ASKER

@ schwertner

Where sholud I start from...

I have two systems both p4 with winxp
One has install Oracle 8i the other one is oracle 9i

I am expecting answer from you.

So please help me in saying that I am working on DDBMS

Thanks

earth man2

look at DATABASE LINKS and REPLICATION in Oracle Concepts Docs.

schwertner

I read the course "Distributed DB" at the University.
Now let us speak about Oracle.

Facts:
1. You have 2 Oracle databases on separate/same machine. Yes!

2. You can access the other DB from the each machine. Yes! How? Using DB-links! Right!

3. You can construct Distrubuted Transaction on each machine! Yes!

This runs under "instance 1":
BEGIN TRANSACTION;
SELECT a FROM instance1.tableX;
UPDATE instance2.tableY; --via db-link - this makes the transaction distributed!!!
UPDATE instance1.TableZ;
END TRANSACTION;

The most important part is in 8. above:
8. Distributed Transaction Management

A transaction can update, insert or delete data from multiple databases.
The two-phase commit mechanism in Oracle ensures the integrity of distributed
nsactions. Row level locking ensures a high level of data concurrency.

4. THE MOST IMPORTANT FACT:
In the case of the above transaction Oracle automatically will apply 2 PHASE COMMIT PROTOCOL!!!!!

The main characteristic of 2PC protocol:
"two phase commit guarantees that everything (in the transaction!!!!!!) will either commit or rollback"
Only if you define TRANSACTION.

WARNING: Outside TRANSACTIONS 2PC protocol doesn't work!

Now I hope the distributed functionality in Oracle is clear!!

Muhammad Ahmad Imran

ASKER

it means that any two databases on two different system on any envoirnment, LAN WAN
a distributed database is constructed??

I am using LAN

Any further idea

I am student in DDBMS so your hints are helpful for me.

LeoAhmad

schwertner

Oracle is invariant what kind of connection are you using. Oracle NET protocol uses TCP/IP connection and LISTENERS. Normally they are listening on port 1521. So you can connect Oracle instances on different platforms. The only prerequiosite is to have TCP/IP connection between the instances.
Of course the quality of the connection (mainly the speed) has a great impact of the productivity.