Multiple oracle databases running on single machine

[Guest_imported]

Hi
Is it possible to have mutilple oracle databases running simultaneously on same machinee. What I know is that it is not possible. However someone told me that he has seen people acessing different databases runing on the same machine. User has to just set ORACLE_SID to the desired path while acessing database. Is it really possible to have mutliple database runing at a time on same machine and also can anyone tell where can I find such information
Regards
Brijesh

 

[joezapp]

I think you're talking about having multiple database schemas running on the same machine - at work here we have several - financial engineering, frontoffice, backoffice etc. These are accessed by reference to the SID i.e backoffice@database / fe@database etc.

For information look at Oracle Technet - it's free to join and I find it pretty useful. The documentation URL is;

http://technet.oracle.com/docs/products/oracle8i/doc_index.htm

Good luck.

Cheers,

Joe.

 

[carp]

Yes, you can have multiple databases running on the same machine; in fact, it's fairly common.

What you CAN'T do is have multiple schemas simultaneously mounting the same database (unless you are using Oracle Parallel Server).

 

[joezapp]

carp - I am being thick but I thought that given that Oracle databases have multiple users each of these can quite easily have their own schema can't they ?

I'd have said that these schemas are then collections of database objects stored within one or more databases. In that way we have one database with a variety of users (accounts, financial engineering etc) all with their own tables, indexes et al contained within and across the db's tablespaces. I thought a db's hierarchy went database => tablespaces => datafiles => segments / extents with schemas referring to the tablespace bit down.

Or have I grossly misread / understood either the terminology or question (Quite likely given that I'm primarily work on our web based systems) ?

Cheers,

Joe.

 

[carp]

Sigh!
Yes, you're absolutely right - I meant instances.
Sincerest apologies!

 

[joezapp]

No worries

I don't get involved too heavily in the DBA stuff on our databases but my boss would be happy to learn that what little knowledge I have has a slim grasp on reality. You gave me a minor panic 'cause I thought we used vanilla Oracle 8i and at the mention of 'parallel server' I got a bit flustered.

Anyway - hope all this is of some use to brijsw.

Cheers,

Joe.

 

[Guest_imported]

Yes, you can can have multiple instance (databases) on the same machine. I have worked in many environments where there are not only multiple instances but also multiple versions of Oracle on the same machine.

The key is to have the your enviromental variables set correctly for each instance. You can set the variables in the .profile file (unix) for each user or create a unix script that allows the user to select the database they want to connect to and automatically set the variables correctly.

The following is a portion of the .profile of one of my users.

>cat .profile

-------------------------------------
# SKY database SID
# SYNCHRO database SID - commented
ORACLE_SID=sky; export ORACLE_SID;

ORACLE_HOME=/ora/app/oracle/product/7.3.4
export ORACLE_HOME;

ORACLE_BASE=/ora/app/oracle
export ORACLE_BASE;

ORACLE_TERM=xsun5;
export ORACLE_TERM;

ORACLE_BIN=$ORACLE_HOME/bin
export ORACLE_BIN;

--------------------------------

When this user logs onto the unix server his enviromental variable are automatically set.

 

[DBAwhosaysNIE]

you could not 'accidentally' be running OPS; it requires a cluster and raw disk partions which are hard enough to set up even if you do know what you're doing.

FWIW, quick vocabulary clarification:

a DATABASE is a set of logically associated control, data & log files (or blocks on raw device). # of databases on a host is constrained only by available storage.

an INSTANCE is a set of background processes (smon, dbwr, etc.) and their shared memory segment (SGA) which use a database. # of instances on a server is limited mainly to memory, somewhat by CPU and occasionally by semaphores.

many people use these term interchangeably but they're really not. for instance (pardon the pun, I have an old E450 which has several databases on it but only one or two of which (dev vs test vs qa, etc.) have running instances at any given time.

one copies, mounts & backs up a database

one starts and shuts down an instance

not trying to annoy people (though I'm sure it will some), just educate.

later!