I’m a developer and have been successfully using our test database “DPS” on our development Unix box “Scooby” since time immemorial. Up to yesterday morning that is. Now all of a sudden, I’m getting this error when I try to log in:
ERROR: ORA-12154: TNS:could not resolve service name
The DBA’s don’t have a clue and say that nothing in the configuration has changed.
The documentation says to check the tnsnames.ora file for errors and stray characters, but that file hasn’t been changed for a long time. (There are a few floating around on this system, but even the most recent one was last changed over a year ago)
Funny, we had this exact same problem this weekend at my company when we switched servers. Our solution was to rebbot both our servers and restart the listener. That should work. If it doesn’t, there are also some Unix environmental variables that may need to be set (number of Oracle users I think). That is more involved so let us know if you need more info after the server is restarted.
I forgot to add that I can use this database from any of my desktop applications. That would be in line with the listener on Scooby not being active, wouldn’t it?
If the listener is not active, you can still connect to the database while logged onto Scooby, it’s only when you are connecting from a different machine that the listener comes in to play.
Ask the DBA’s to connect to the database using something like:
sqlplus scott/tiger@DPS
DBA’s never believe others, you have to trick them into finding the problems themselves.
That error doesn’t indicate a problem with the listener on the database host but the tnsnames.ora file on your workstation. You’ve probably got a malformed entry or a typo in the file that prevents it from reading the entry you need. rename the original file and use the easy config utility to create a new file with only that particular entry and give it another try.
But it’s from my desktop that I can access the database. Everything works…Oracle for NT, Developer 6.0, MS-Access,
and even VisualCafeEE. Doesn’t this imply that my workstation TNSNAMES.ORA file should be fine? Of course,
I really am not as familiar as I should be with configuration issues, so I’m kind of in the dark here.
Of course, if it is a workstation problem as you suggest, remember that I didn’t ask them to look at it, so we’ll forgive 'em.
Javaman, I’m a little unclear here, are you saying you get the error but login to the database anyway? At any rate this is a client side error and it won’t be corrected on the server. Note: the client software can run on the server so that the scooby is both a client and a server but try not to get confused by that. Check to see if you have more than one version of oracle client on your PC and search for multiple versions of tnsnames.ora
It’s not a TNSNAMES.ORA problem after all. It’s something to do with my .profile. I haven’t made any intentional changes to this file since before the weekend, but nevertheless when I renamed my .profile so it wouldn’t run at startup, I was able to get to the database after logging in.
AHA! So it wasn’t a workstation issue after all. It was a client issue, but the fact that it was on the same host as the server clouded that. Glad you found it.
That’s intersting. So this problem is when you try to login from Scooby itself? What program are you using to access the database? My guess is that, for whatever reason, one of your env variables is screwed up. Oracle normally assumes that your tnsnames.ora file (and by that I mean the one you’re using on Scooby) will be at $ORACLE_HOME/network/admin. If your $ORACLE_HOME variable is being set to the wrong path, or isn’t being set at all, or possibly even if you don’t have permission to read the path it is being set to, then when you try to connect to the database you’ll have problems. Also, you can specify a different path for your tnsnames.ora file by setting the $TNS_ADMIN variable.
Of course, if you’re logging in as the user that runs the database and you’re attempting to connect through sqlplus or svrmgr then you shouldn’t need to have your tnsnames stuff (on Scooby) configured correctly. In which case it might be a problem with your $ORACLE_SID variable not matching what’s in the oratab file. But if you’re on a different user or you’re accessing the database through a different program, then I believe it will attempt to connect using tnsnames.
My first thought is that you have a bad entry in your tnsnames.ora file, javaman.
Where I work, there’s only one for a “user,” and it gets maintained automatically through periodic pushes from the tech support guys.
In our case, being the Oracle development team, we may have several different tnsnames files depending on which version of which software we’re using to connect to which database. We are forced by necessity to manage our own.
If it’s not the tnsnames, though, Amok is probably right in that you’re not specifying the correct $ORACLE_SID to connect to.
BTW, you didn’t say what application (if any) you were using to connect to your database. Is it SQL*Plus?