Re: multiple Oracle databases

In article <1992Feb12.230042.23506_at_news.gvl.unisys.com> robin_at_prc.unisys.com (Robin McEntire) writes:
>I'm creating several databases here in support of a particular application and
>I'm distributing my tables among these databases in order to group them in some
>meaningful manner.
>
>My question has to with the fact that all of these tables will be used by the
>application. My plan is, quite naturally, to create DATABASE LINKs from one of
>the databases into each of the other databases. In this manner I can connect
>to this primary database and have access to all tables.

Personally I think this is not the right solution. Of course you can create multiple databases ( 'instances' as they are called in Oracle terminology) and create database links, synonyms etc. to access tables in other databases. Multiple instances are usually created for environments which are not really dependent, like a 'test' and 'production' database.

In situations like you mentioned, people often use one 'instance' of Oracle. When you have various subsystems or projects or whatever you call them, store all data for such a subsystem in the Oracle 'account' of that subsystem. For instance, if you have to subsystems, which use eachothers data, called 'finance' and 'personnel', create two Oracle users, called finance and personnel, with their own password, probably only known to a few people. Then grant access to the users who have to access this data (select, insert, update, delete), and create (public) synonyms for all tables, views, sequences, etc which are needed.

This way you seperate data for various sub-projects, and it still remains transparent to the application. One reason to seperate the data might be for backup reasons, or to protect against loss of data. E.g. when a disk crashes, and you are restoring from tape, you would like to continue with the finance application, because the data on the crashed disk was of another application. You do this, by assigning a default tablespace to each of the aforementioned users 'personnel' and 'finance', and by placing the datafiles for these tablespaces on seperate disks. Now when one of the disks crashes, the corresponding tablespace will be put off-line by Oracle, and you can even continue to work with the rest of data. Similarly when you have installed a new disk, the tablespace remains off-line, until you have finished restoring the data from tape. This is what Oracle calls 'On-line partial backup and recovery'.

Ofcourse to do a backup you will not have to put any tablespace off-line.

Sandor Nieuwenhuijs
Oracle Netherlands
Product Manager UNIX

                ALL OPINIONS ARE STRICTLY MINE