I have two linux systems, both running redhat as. one system called buzz has the oracle client installed (220.127.116.11) and the other system woody has the server installed (18.104.22.168).
Now the problem is with the client machine (and the server as well, this is related to the same issue i think). now i have 4 databases setup on the woody machine which i can set the oracle sid and sqlplus to each of the databases through interactive login as well as logging in through enterprise manager.
But on the client machine everything is setup correctly according to the documentation with the database entries in the tnsnames.ora file setup correctly, i can tnsping to each database i can ping to each database but i cannot login in interactively to the database with sqlplus. but i can login to the database using the command line options (eg sqlplus user/password@database) and all is fine.
I know this is a bit of a hairy issue. but if anyone knows anything about this or may know of a website that may have the information i should know please reply.
If anything is unclear please write me and i will try to explain it a bit further.
well i have tried with the client and the server at the same version (22.214.171.124.0) but i also upgraded the client to (126.96.36.199.1) to see if it was a bug with the system. it seems that on the server i can set the SID and connect interactively quite easily ( i have tried this because i have more than 1 oracle server) but if i set the sid to a database that resides on another machine i can only connect through the command line options.
The same situation occurs on the client machine which is currently at 188.8.131.52.1 if i try to connect to any database on another machine, i cannot login in interactively. I know that my tnsnames.ora file is configured correctly as i have pulled it from a known working install.
After some more information I discovered that it was my understanding that is the problem and not with the system. I thought that if you set the oracle sid environment variable "export ORACLE_SID=database" and run sqlplus that it would lookup the SID definition in the tnsnames.ora file.
Which from what i have since found out is that the ORACLE_SID is only for local databases and not databases that reside on other machines.