Re: Use One or Two Databases?

Stan Novinsky (stan_novinsky_at_jhuapl.edu) wrote: ...
> We want to set up a DB system (ORACLE) that may have to be
> used by two projects each with different needs. The
> computer configuration at this time is:
 

> 	-  VAX 4000/90 running OpenVMS 6.1.
> 	-  3 RZ28's (2 GB each)
> 	-  128 Meg Memory
> 	-  Ada, C, Motif, DECmessageQ
 


>    a.  Is it best to have one DB and separate the projects by 


>        tablespaces.


 


>    b.  Have two separate DB's one for each project.  If so,


>        how are the tablespace/datafiles placed on the drives.


 

> An issue that we must consider is the one project will be
> transferred to a target system at a later time.
 

> Thanks
 

> stan_novinsk_at_jhuapl.edu

Fundemental concepts ...

Instance: That which requires memory & CPU cycles in order

	   to manipulate data on behalf of the user
	   Some people know the Instance as a SID.

Database:  The schema (dictionary), data & associated disk 
	   space to be manipulated.  A database is contained
	   in an instance.  The schema for a database is owned
	   by a UserId

Tablespace: The disk file(s) allocated to Oracle for database(s).

A database is owned by a userid. Eg: the Oracle system database (which includes the schema and data) required to run Oracle is owned by user SYS. The sample database is usually owned by SCOTT.

A database is contained within (and manipulated by) one or more instances. An instance may manipulate one or more databases.

An Instance may be started & stopped independant of other instances. Once started, all databases being manipulated by the instance are available.

Answer to question ...

You may have several similar databases (eg: development, test, production), with identical schemas, but different data. If these are contained in the same instance (SID), then they must be owned by different userids (eg: User APP_DEV, APP_TEST, APP_PROD).

Situation 1: Several databases in 1 instance

Users from outside must have private synonyms or the app must qualify any SQL activities (eg: SELECT ... FROM APP_PROD.TABLE_1)

When users wish to switch from one database to the next, they must switch synonyms and/or qualifiers.

Benefit ... each instance grabs memory & uses cycles, therefore reducing # of instances to 1 maximizes these resources. Also, the DBA (you have one don't you???) only needs to maintain 1 SYS database.

Situation 2: Multiple instances, 1 database each.

Users must specify SID to get at correct version of app, however PUBLIC SYSNONYMS may be used or the app may have a defined qualifier (eg: SELECT ... FROM APP.TABLE1). The same userid may own the database contained in each instance because the database/instance combination is unique.

When users switch, the simply point at a different instance. The app & synonyms do not need to change.

Consideration ... Each instance requires cycles & memory. In order to have 3 instances up concurrently on the same machine, you may need to add memory (or each instance must be SGA sized appropriately). Considerable DBA work may be involved in adjusting sizes & monitoring same. However, unused instances may be shut down (making memory 'available') when not in use.

Personally I use a combination ... the production instance ONLY contains the production database. A second instance contains the test, dev, traininig, etc. Most of the time all users in the second instance share the database owner usereid & passwd, so I don't have to fight with sysnonyms.

Hope this helps (sorry about length & detail) /Hans Received on Thu Feb 09 1995 - 16:36:08 CET