Tnsnames.ora - Strange Connectivity problem

Hi,

I'm having a few connection problems. Basically, I have two 9i instance running on Redhat Linux. Both instances share the same ORACLE_HOME and are running fine i.e. both also accept external connections so the listener is ok.

Yesterday I applied an Oracle Patch (bug 4192148 ) and since then I cannot connect to a remote instance - either using a DB link or SqlPus_at_remote_instance. When doing so I get error "ORA-12154: TNS:could not resolve service name".

The tnsnames.ora is in the default location and is complete (no changes since it last worked). I even tried specifiying the default tns location - ORACLE_HOME/network/admin -using the TNS_ADMIN environmental variable but this also generated the same error.

I've also checked tnsnames.ora for any errors but there were none. The tns entries also use service names.

When I use tnsping against the remote instances it works fine so it is being picked up here. All the environmental variables are correct and if ORACLE_SID = INSTANCE1 then it is not possible to connect to INSTANCE2 using sqlplus_at_instance2 and vice versa.

I'm a bit stumped as to where I start looking next. I'm not a greatly experienced DBA and therefore any help would be appreciated.

Thanks in advance
John Received on Wed May 03 2006 - 09:42:21 CDT