I am going to assume you are using the tnsnames.ora file to specify your available database services. If so connection errors usually come down to two things.
The application cannot find the TNS entry you specified in the connection string.
The TNS entry was found, but the IP or host is not correct in the tnsnames.ora file.
To expand on number 1 (which I think is your problem). When you tell Oracle to connect using something like:
sqlplus user/pass@service
The service is defined in the tnsnames.ora file. If I attempt to connect with a service that is not defined in my tnsnames.ora, I get the error you get:
[sodonnel@home ~]$ sqlplus sodonnel/sodonnel@nowhere
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 31 21:42:15 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
So you need to check a few things:
Is there a tnsnames.ora file – I think yes because your console can connect
Is there an entry in the file for the service – I think also yes as the console connects
Can the application find the tnsnames.ora?
Your problem may well be number 3 – does the application run as a different user than when you run the console?
Oracle looks for the tnsnames.ora file in the directory defined in the TNS_ADMIN environment variable – If you are running as different users, then maybe the TNS_ADMIN environment variable is not set, and therefore it cannot find the file?
Had a similar issue, only my web app was fine and it was SQLPlus that was giving me issues connecting, and the ORA-12154 could not resolve the connect identifier specified error. I had 11g and 12 Oracle clients installed. My environment variables were all set to point at my 12 instance:
ORACLE_HOME = C:oracleproduct12
PATH = C:oracleproduct12bin;….
TNS_ADMIN = C:oracleproduct12networkadmin
There is also a registry entry required at HKLMSoftwareOracleKEY_OraClient12Home1, a string entry of TNS_ADMIN with the same path as the environment variable.
I have a tnsnames.ora at both C:oracleproduct11networkadmin and C:oracleproduct12networkadmin. As far as I know, both my web app and the 12 SQLPlus client I was using should have been using all 12 version variables.
My troubleshooting steps:
Change all environmental variables above from 12 to 11.
Connect with 11g’s SQLPlus (worked!)
Change all environmental variables above back from 11 to 12.
Connect with 12’s SQLPlus again (worked!)
So I don’t really know what caused 12’s SQLPlus to stop connecting, but this kind of reset may work for someone, so thought I’d document it here.