Two instances with the same ORACLE_SID on one Unix server
It has been understood for a while that it is possible to create two instances with the same ORACLE_SID on one Unix server (https://groups.google.com/d/topic/comp.databases.oracle.server/Vs1n-LyptxA/discussion). However, it was supposed that two physically separate Oracle home directories were necessary for reasons that could not be explained. The above explanation and demonstration make it clear that the value of the ORACLE_HOME string variable is what matters and that physically separate Oracle home directories are not necessary.
You may have encountered the problem where you can connect to your Oracle database through the listener but cannot connect locally even though the value of ORACLE_SID in your environment is correct (http://www.freelists.org/post/oracle-l/local-connections-fail-but-listener-connections-succeed). In a Unix environment, this happens when your local connection is not using the value of ORACLE_HOME that was used to start the instance. Surprisingly, both the ORACLE_SID and ORACLE_HOME must be set correctly in order to connect to your instance.
Here’s the reason: When an Oracle instance is started in a Unix environment, a handle (“shmid”) for the shared memory segment (SGA) is constructed using the values of both ORACLE_SID and ORACLE_HOME as inputs to the “shmget” system call. To subsequently attach to the shared memory segment, each database connection needs to reconstruct the shmid of the shared memory segment and, to do this, they must use the same values of ORACLE_SID and ORACLE_HOME that were…
View original post 619 more words