Imagine that you need to set up Oracle Application Express and Oracle HTTP server on an existing system (Oracle 10R2 SE on Windows 2003 server in my case).
The installation requires that you install Oracle HTTP server in a new oracle home.
After successfully installing Oracle HTTP Server (Apache) and Apex, you are so happy because everything installed seamlessly until you get a call eight hours later because some batch loader scripts stopped working.
Luckily those scripts are logging errors and what i find in the logs is the following error message:
ORA-12154: TNS:could not resolve service nameWhen you find this type of error, the best thing is to verify if you get the same error when manually connecting to the database using the same connection string from the same machine where the error was returned.
Don't try from a different client pc, because it wouldn't make sense in this case.
sqlplus user/pwd@connectorIf sqlplus returns the same error, it means that the currently used TNSNAMES.ORA does not contain a valid connector, but you go to the usual folder where the TNSNAMES.ORA file is located and everything looks fine.
So, which TNSNAMES.ORA file is being used then?
That can be easily verified using command TNSPING.
TNSPING will return the location of the TNSNAMES.ORA file being used to resolve the connector, which in turn depends on the priority of the Oracle homes and this takes us back to the beginning of this post: I've just created a new oracle home for the Oracle HTTP server and, as a consequence, the new oracle home was moved on the top of the list and therefore the TNSNAMES.ORA being used now is located in a different place. Note however that running programs may still be using the old one if they were launched before the new oracle home was created, which explains why they may still run ok.
This is why you should ALWAYS stop any processes connecting to the db when doing an installation of some component, because otherwise you can easily find in an inconsistent state: some things work, some don't and you don't understand why.
After realizing that the presence of the new oracle home caused the troubles, it's necessary to run the Oracle Installer and change the environment settings so that the "main" oracle home is restored on the top of the list.
See message translations for ORA-12154 and search additional resources.
2 comments:
Hi, I've got a situation. I'm using TOAD 8.6 to connect to a DB server. The connection worked fine until last week, when all of a sudden, it started popping out the 12154 error. I've not changed anything on my machine but added another service name to tnsnames.ora file. I tried a tnsping and this works fine too (Made sure the tnsnames.ora shown here is the same which is being used by TOAD as well). While connections to other servers work just fine, the problem is just for this one server. Checked with DB admin and he says everything on the server is fine, listener, processes, etc... Can you throw some light here as to what else could be missing?
What happens if you restore the old TNSNAMES.ORA or manually delete the service name you added?
If the error persists, then it cannot be caused by the new entry, if it goes away, then there must be a link between the two events.
Flavio
Post a Comment