RE: Need your idea on setting up Distributed database

Very true.

My experience has been with 1 centralized (A) and 5 local databases (B). We used snapshots to achieve the distributed transaction tasks.

o For some Master type of tables (sort of static in nature such as Branch_Codes), the main table were kept on 'A' and their snapshots created on 'B'. This involved 1 dblink at 'B' to access 'A'. So, whenever there was a change in 'A', the next refresh cycle would propogate the change to 'B'. (I know your reqmt is just the other way around)

o On the other hand, for some reporting purposes, there were main tables kept on 'B' and their snapshot created on 'A'. This required horizontal partitioning of data, such that the single snapshot (there could be only 1 snapshot on 'A' for all 5 local databases 'B') would get data from all local 'B' Databases. We did this by using UNION such as:

On 'A' (This may not be the exact syntax)

create snapshot <snap_name>
select * from B1.TableXYZ_at_dblinkB1
UNION
select * from B2.TableXYZ_at_dblinkB2

etc.

This may not be good solution in your case considering the large number of local databases.

Anyway, you have some more info.

• Deepak
  • George.Brennan_at_warnermusic.com wrote:
    > This is not a trivial problem. With 245 locations
    > you need some serious thought about distributed
    > transactions, IPC (inter process communication) and
    > more.
    >
    > George.
    >
    > > -----Original Message-----
    > > From: "Balasubramanian M" <sbala_at_hclinsys.com>
    > > [mailto:sbala_at_hclinsys.com]
    > > Sent: Friday, May 26, 2000 1:56 PM
    > > To: smtp_at_inl001@servers["Multiple recipients of
    > list ORACLE-L"
    > > <ORACLE-L_at_fatcity.com>]
    > > Subject: Need your idea on setting up Distributed
    > database
    > >
    > >
    > > Dear all,
    > >
    > > I have to plan a strategy for setting up the
    > database. Let me
    > > explain the scenario of operation.
    > >
    > > Local databases are available in 245 locations and
    > there will
    > > be one centralized database.
    > > Whenever there is a transaction occuring at each
    > location it
    > > should be posted to both the local database and
    > the
    > > centralized database. We are using oracle8i
    > database.
    > >
    > > How i can post the database transaction to local
    > database
    > > after commit as well as centralized database ?
    > > This should happen in online.
    > >
    > > How many dblinks a centralized oracle8i database
    > server can
    > > support ? In this case from each location
    > > i need to have a database link to the centralized
    > database server.
    > >
    > > What database feature i can use to update the data
    > in both
    > > local and centralized database ?
    > >
    > > I request every one to share every ones thoughts
    > and tips and
    > > steps with me.
    > >
    > >
    > > Awaiting the response.
    > >
    > > Regards,
    > > Balasubramanian .M
    > > Bangalore.
    > >
    > >
    > > --
    > > Author: Balasubramanian M
    > > INET: sbala_at_hclinsys.com
    > >
    > > Fat City Network Services -- (858) 538-5051
    > FAX: (858) 538-5051
    > > San Diego, California -- Public Internet
    > access / Mailing Lists
    > >
    >

    ---

    
    > > To REMOVE yourself from this mailing list, send an
    > E-Mail message
    > > to: ListGuru_at_fatcity.com (note EXACT spelling of
    > 'ListGuru') and in
    > > the message BODY, include a line containing: UNSUB
    > ORACLE-L
    > > (or the name of mailing list you want to be
    > removed from). You may
    > > also send the HELP command for other information
    > (like subscribing).
    > >
    > >
    >
    >
    > --
    > Author:
    > INET: George.Brennan_at_warnermusic.com
    >
    > Fat City Network Services -- (858) 538-5051 FAX:
    > (858) 538-5051
    > San Diego, California -- Public Internet
    > access / Mailing Lists
    >

    ---

    
    > To REMOVE yourself from this mailing list, send an
    > E-Mail message
    > to: ListGuru_at_fatcity.com (note EXACT spelling of
    > 'ListGuru') and in
    > the message BODY, include a line containing: UNSUB
    > ORACLE-L
    > (or the name of mailing list you want to be removed
    > from). You may
    > also send the HELP command for other information
    > (like subscribing).