RE: local connections fail, but listener connections succeed
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Thu, 3 Jan 2013 22:22:31 -0800
Message-ID: <BLU165-W50FD6376A3A4A5214F5325EB200_at_phx.gbl>
Here is what happens in a Unix environment. I'm sure there is a close parallel in a Windows environment. When the Oracle instance is started, an "shmid" for the shared memory segment (SGA) is constructed using the values of the environment variables ORACLE_SID and ORACLE_HOME as inputs. To attach to the shared memory segment subsequently, any slave process needs to reconstruct the shmid of the shared memory segment and to do this it must use the same values of ORACLE_SID and ORACLE_HOME as were used to start the Oracle instance. It is not enough to use the same value of ORACLE_SID. It must also use the same value of ORACLE_HOME. Assume that the Oracle instance and listener were started with one set of values of ORACLE_SID and ORACLE_HOME. If you use the same value of ORACLE_SID but use a different (but valid) value of ORACLE_HOME you will be unable to connect locally using sqlplus or rman or any other Oracle executable. However, you will still be able to connect through the listener. You will be unable to connect locally even if the new value of ORACLE_HOME is equivalent to the previous value; that is, it is a soft link as in the following example. Assume that the Oracle binaries are installed /home/oracle/app/oracle/product/11.2.0/dbhome_1 Create a soft link to the location of the Oracle binaries using the command ln -s /home/oracle/app/oracle/product/11.2.0/dbhome /home/oracle/app/oracle/product/11.2.0/dbhome_x Start the instance and listener using ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_x Create another soft link to the location of the Oracle binaries using the command ln -s /home/oracle/app/oracle/product/11.2.0/dbhome /home/oracle/app/oracle/product/11.2.0/dbhome_y Note that both soft links are valid and point to the location where the Oracle binaries are installed Try to connect using ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_y The connection attempt fails even though the new value of ORACLE_HOME correctly points to the location where the Oracle binaries are installed I therefore suspect that your environment variables are the culprits. Kindest regards,
Iggy
P.S. There is a comparison of inter process communication in Unix and Windows in http://www.cs.brown.edu/~scl/files/IPCWinNTUNIX.pdf.
> From: SUzzell_at_MICROS.COM
> This is backwards enough that I don't quite know where to start with it. This morning we noticed that for one environment, we cannot make direct local connections... but we can connect to our hearts' content via the listener. I'm used to seeing the listener act up and being able to connect directly but not via the listener - not this.
Date: Thu, 3 Jan 2013 22:22:31 -0800
Message-ID: <BLU165-W50FD6376A3A4A5214F5325EB200_at_phx.gbl>
Here is what happens in a Unix environment. I'm sure there is a close parallel in a Windows environment. When the Oracle instance is started, an "shmid" for the shared memory segment (SGA) is constructed using the values of the environment variables ORACLE_SID and ORACLE_HOME as inputs. To attach to the shared memory segment subsequently, any slave process needs to reconstruct the shmid of the shared memory segment and to do this it must use the same values of ORACLE_SID and ORACLE_HOME as were used to start the Oracle instance. It is not enough to use the same value of ORACLE_SID. It must also use the same value of ORACLE_HOME. Assume that the Oracle instance and listener were started with one set of values of ORACLE_SID and ORACLE_HOME. If you use the same value of ORACLE_SID but use a different (but valid) value of ORACLE_HOME you will be unable to connect locally using sqlplus or rman or any other Oracle executable. However, you will still be able to connect through the listener. You will be unable to connect locally even if the new value of ORACLE_HOME is equivalent to the previous value; that is, it is a soft link as in the following example. Assume that the Oracle binaries are installed /home/oracle/app/oracle/product/11.2.0/dbhome_1 Create a soft link to the location of the Oracle binaries using the command ln -s /home/oracle/app/oracle/product/11.2.0/dbhome /home/oracle/app/oracle/product/11.2.0/dbhome_x Start the instance and listener using ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_x Create another soft link to the location of the Oracle binaries using the command ln -s /home/oracle/app/oracle/product/11.2.0/dbhome /home/oracle/app/oracle/product/11.2.0/dbhome_y Note that both soft links are valid and point to the location where the Oracle binaries are installed Try to connect using ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_y The connection attempt fails even though the new value of ORACLE_HOME correctly points to the location where the Oracle binaries are installed I therefore suspect that your environment variables are the culprits. Kindest regards,
Iggy
P.S. There is a comparison of inter process communication in Unix and Windows in http://www.cs.brown.edu/~scl/files/IPCWinNTUNIX.pdf.
> From: SUzzell_at_MICROS.COM
> This is backwards enough that I don't quite know where to start with it. This morning we noticed that for one environment, we cannot make direct local connections... but we can connect to our hearts' content via the listener. I'm used to seeing the listener act up and being able to connect directly but not via the listener - not this.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 04 2013 - 07:22:31 CET