You can have multiple instances on the same machine, each with their own data files, either sharing the ORACLE_HOME or each with different ORACLE_HOME. ORACLE_HOME and ORACLE_SID are the key environment variables used by Oracle to identify an instance. In addition $ORACLE_HOME/bin must be in your PATH environment variable.
To check the value of these environment variables:
$ env |grep ORACLE ORACLE_SID=pdb10i1 ORACLE_BASE=/u01/app ORACLE_HOME=/u01/app/oracle/product/10.2.0
The “oraenv” (or “coraenv“) shell scripts allows you to change the value of these environment variables and switch instances, which are usually located under /usr/local/bin
$ file /usr/local/bin/*oraenv /usr/local/bin/coraenv: Bourne shell script text executable /usr/local/bin/oraenv: Bourne shell script text executable
- To run multiple database instances under the same database version.
The instances all use the same software executables. The PATH for all the instances is the same and it contains $ORACLE_HOME/bin. Note that that the number of instances that can share executables (ie. can have the same $ORACLE_HOME) is limited only by system resources. In this case, to change instances; you will have to reset ORACLE_SID.
For example:
ORACLE_HOME=/u01/app/oracle/product/10.2.0
ORACLE_SID=pdb10i1
ORACLE_HOME=/u01/app/oracle/product/10.2.0
ORACLE_SID=pdb10i2
- To run multiple database instances under the different database version.
You are (or will be) running two (or more) different versions of Oracle software on the same machine at the same time. In such case, the executables are not shared. So, to change instances; you will have to reset ORACLE_SID, ORACLE_HOME and PATH.
For example:
ORACLE_HOME=/u01/app/oracle/product/10.2.0
ORACLE_SID=pdb10i1
ORACLE_HOME=/u01/app/oracle/product/11.1.0
ORACLE_SID=pdb11i1
When you have multiple versions of Oracle on the same machine, you may want to setup a different oracle (software owner) user for each version.
For Example:
oracle10 owns the 10g database with ORACLE_HOME=/u01/app/oracle/product/10.2.0
oracle11 owns the 11g database with ORACLE_HOME=/u01/app/oracle/product/11.1.0
To set this up:
- Create a new oracle user account to own and manage the new Oracle database (for example, oracle10). The home directory (ORACLE_HOME) must be different from that of the original oracle user account.
- Login to the oracle10 account and run the installer. All directories and files in $ORACLE_HOME will be owned by oracle10.
- When the installation is complete, run oraenv shell script installed by Oracle to verify that the environment variables are set correctly.
Hello;
Thanks for this. I’m sure it’s going to come in useful at some point soon as we have a number of servers set up this way. I’m rather new to Oracle and am wondering what the best way to run multiple databases on the same server in different time zones is (ie sysdate returns different time zones based on the database).
Thanks a lot.
–B
You can set the timezone at the time of database creation or after it. Refer to below documentation links for additional information:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5004.htm#i2082011
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#i1009187
Metalink document titled “Timestamps & time zones – Frequently Asked Questions”, Document ID 340512.1
Hope this helps!