ora12154 by rocker12

ORA-12154

Networking Tips

What does ORA-12154 mean? ORA-12154 is perhaps one of the more infamous networking error messages you can get. The official description of it runs like this: Cause: Oracle Net could not locate the net service name specified in the tnsnames.ora configuration file. Action: Perform these steps: 1. Verify that a tnsnames.ora file exists. 2. Verify that there are not multiple copies of the tnsnames.ora file. 3. In the tnsnames.ora file, verify that the net service name specified in your connect string is mapped to a connect descriptor. 4. Verify that there are no duplicate copies of the sqlnet.ora file. 5. If you are using domain names, verify that your sqlnet.ora file contains a NAMES.DEFAULT_DOMAIN parameter. If this parameter does not exist, you must specify the domain name in your connect string. 6. If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out. 7. If you are connecting from a login dialog box, verify that you are not placing an "@" symbol before your connect net service name. 8. Activate client tracing and repeat the operation. Cause: Oracle Net could not locate the database service name or net service name specified in the directory server. Action: Perform these steps: 1. Verify that the database service or net service name entry exists in the directory that this computer was configured to use. Verify that the sqlnet.ora file includes the following entry: NAMES.DIRECTORY_PATH=(ldap, other_naming_methods) Which is all nice and lovely –but doesn’t really explain what is going on, as far as I’m concerned! So let’s take it a step at a time, and then maybe the official ‘action’ steps might make a bit more sense. Suppose that my tnsnames.ora file looks like this: DB9 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) ) )

17/03/2002

Page 1 of 8

ORA-12154

Networking Tips

That tells me that by requesting a connection to “db9”, I’ll be directed to the Listener running on the server called “MOZART” (that name needs to be resolved into an IP address by something like lmhosts or Active Directory), listening on port 1521. When I get there, I’ll ask the Listener to connect to the Instance properly known as “db9.aldeburgh.local” Now, if there was a problem with any part of the ADDRESS aspect of this tnsnames entry, I wouldn’t be getting ORA-12154 errors. I would instead get ORA-12514 errors (TNS: No Listener). The fact that this error number is a simple transposition of the one for the could not resolve service name one is a perpetual source of confusion and frustration, but you just have to live with it. Keep a keen eye on the actual error messages received! So the problem isn’t there. It’s in the CONNECT_DATA part, or in the very first line. The error message tells us that we can’t work out what it is that you want to connect to. For example, and remembering what the tnsnames.ora looks like, look what happens when I try and connect like this: SQL> connect system/manager@SALES ERROR: ORA-12154: TNS:could not resolve service name

The request was to connect to something aliased as “SALES” –yet my tnsnames.ora has no entry for such an Instance, only for something called “db9”. Oracle has searched the tnsnames.ora looking for an explanation of how to connect to “SALES” and simply can’t find one. That’s a very simple reason for the 12154 error message. But things can get trickier. What about this one: SQL> connect system/manager@db9 ERROR: ORA-12154: TNS:could not resolve service name

Here, without altering my tnsnames.ora file in any way, I’ve correctly supplied the “db9” alias, and yet it’s still not working. Many first-timers rush to the conclusion that it’s a case sensitivity issue. My tnsnames.ora has an entry for “DB9” and I’ve tried to connect to “db9”… so perhaps that’s it? No. Case is not sensitive within the tnsnames.ora. In fact, there’s no obvious reason for my inability to connect this time… you just have to know that the existence of a sqlnet.ora file can throw things out. The job of sqlnet.ora is to inform Oracle what names resolution method we should be using (hostname, local names, names servers etc). It’s also a means of setting some default attributes for connections, such as whether they should be encrypted –or what domain name should be assumed for all connections. It’s that last one that happens to be stuffing things up for us. Copyright © Howard Rogers 2002

17/03/2002

Page 2 of 8

ORA-12154

Networking Tips

When I open my sqlnet.ora, I find this line: names.default_domain = world

What this means is that each and every connection string I type, unless it explicitly includes a domain name, will have a “.world” silently appended to it by Oracle. So when I type connect system/manager@db9 what is actually being submitted is a connect system/manager@db9.world –which would be fine if that is what my tnsnames.ora contained… but as we know it doesn’t. There are two solutions to this problem. Either get rid of sqlnet.ora so that this silent addition of domain names doesn’t happen. Or, alter tnsnames.ora so that the alias ‘db9’ reads the full ‘db9.world’. Either method works fine: First, getting rid of sqlnet.ora: C:\Documents and Settings\howardjr>type f:\oracle\ora90\network\admin\sqlnet.ora The system cannot find the file specified. C:\Documents and Settings\howardjr>sqlplus system/manager@db9 SQL*Plus: Release 9.0.1.0.1 - Production on Sun Mar 17 15:54:41 2002 (c) Copyright 2001 Oracle Corporation.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production

Now, changing tnsnames.ora having put back the original sqlnet.ora: C:\Documents and Settings\howardjr>type f:\oracle\ora90\network\admin\tnsnames.ora DB9.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) ) ) C:\Documents and Settings\howardjr>sqlplus system/manager@db9 SQL*Plus: Release 9.0.1.0.1 - Production on Sun Mar 17 15:50:52 2002 (c) Copyright 2001 Oracle Corporation.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 – Production

17/03/2002

Page 3 of 8

ORA-12154

Networking Tips

Of course, it’s a bit daft to be continually making connections to a domain that doesn’t actually exist. So the real cure is not to get rid of sqlnet.ora, or to make meaningless alterations to tnsnames.ora, but to make sure that the right domain name is supplied as a default, and also to edit the tnsnames.ora so that if a User supplies just the short-form alias, the silent addition of the domain name still finds a match in the tnsnames.ora: C:\Documents and Settings\howardjr>type f:\oracle\ora90\network\admin\tnsnames.ora DB9.aldeburgh.local = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) ) ) C:\Documents and Settings\howardjr>type f:\oracle\ora90\network\admin\sqlnet.ora names.default_domain = aldeburgh.local C:\Documents and Settings\howardjr>sqlplus system/manager@db9 SQL*Plus: Release 9.0.1.0.1 - Production on Sun Mar 17 15:59:12 2002 (c) Copyright 2001 Oracle Corporation.

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production

So, I supply a connection request missing a domain entirely. Sqlnet.ora causes ‘aldeburgh.local’ to be appended to that. An entry in tnsnames.ora exists for the shortform+domain name version. Hence the short-form can be resolved. Knowing this, can you now see why the official Oracle recommendations that I included at the beginning of this paper ask you to perform the various checks that it does? Take them one by one: 1. Verify that a tnsnames.ora file exists. Obvious really. Without a tnsnames.ora at all, you’re not going to be connecting to anything, unless you’ve configured another naming method (see point 9 below). What you connect to has got to be resolved somehow. 2. Verify that there are not multiple copies of the tnsnames.ora file It’s no good making beautiful edits to your tnsnames.ora if there is a copy of the file in another directory somewhere –and Oracle happens to be using that copy and not the one you’ve been editing! (Don’t laugh –I once spent a good hour wondering why my extensive tnsnames.ora edits weren’t making the slightest bit of difference to connection behaviour!) By default, Oracle will use the copy in the ORACLE_HOME\network\admin directory. But someone may have previously set the Copyright © Howard Rogers 2002

17/03/2002

Page 4 of 8

ORA-12154

Networking Tips

environment variable TNS_ADMIN, which allows you to house your .ora files wherever that points to. Unfortunately, Oracle always searches the default location first, and ignores the TNS_ADMIN setting if it finds the right files there. So it’s quite easy to expend a lot of effort editing the TNS_ADMIN versions, not realising that they won’t actually be used. 3. In the tnsnames.ora file, verify that the net service name specified in your connect string is mapped to a connect descriptor Isn’t the English language a wonderful thing?! This gem of a sentence actually means “make sure that what you type after the “@” symbol in the ‘connect x/y@abc’ string matches what is listed as the alias in the tnsnames.ora file”. That’s the problem we first encountered, back on page 2. I was connecting to “SALES”, the tnsnames.ora only had an entry for “db9”. There wasn’t a match, so the SALES name couldn’t be resolved into anything meaningful. 4. Verify that there are no duplicate copies of the sqlnet.ora file. On the same principle that it is pointless editing a tnsnames file in TNS_ADMIN if the operational file can be found in ORACLE_HOME\network\admin, it also makes no sense trying to edit the sqlnet.ora file in the TNS_ADMIN directory if the real one is still stored in the default location. We’ve seen that shortname+the default domain as supplied by sqlnet.ora must match the alias listed in tnsnames.ora… so editing sqlnet.ora may be needed to get a successful connection. But editing the nonworking copy of the file won’t get you very far. 5. If you are using domain names, verify that your sqlnet.ora file contains a NAMES.DEFAULT_DOMAIN parameter. If this parameter does not exist, you must specify the domain name in your connect string. This is the exact problem I was describing on page 3. Sqlnet.ora’s spooky tendency to want to append domain names to supplied connect aliases means that we have to have an appropriate default set there to start with, and a matching alias+domain in the tnsnames.ora. This was the second option described on page 3. 6. If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out. The converse is also true, as I demonstrated as the first possible solution on page 3. When there’s no sqlnet.ora, no domain name is appended to the connect alias supplied by the User, so the tnsnames.ora can also just have the short form alias (in my case, just “db9”). 7. If you are connecting from a login dialog box, verify that you are not placing an "@" symbol before your connect net service name. This is a slightly bizarre possibility. They’re talking about using, for example, the GUI version of SQL*Plus, which prompts you for a logon like this:

17/03/2002

Page 5 of 8

ORA-12154

Networking Tips

Users who have gotten into the habit of typing ‘connect system/manager@db9’ in the command line version of SQL*Plus sometimes make the mistake of thinking that the “@” symbol is a requirement even in the GUI, as I’ve shown in this example. It’s not: entered like this, I’ll actually be looking for an alias in my tnsnames file of “@db9” –for the GUI, anything entered in the ‘host string’ window is taken absolutely literally. 8. Activate client tracing and repeat the operation A counsel of despair, and not really a piece of meaningful advice for this particular error message. There are tracing utilities (such as TRCROUTE and TNSPING) which can help you see what happens when a connection is established, but if the problem is that the tnsnames.ora file can’t find a match to what you’ve requested a connection to, those utilities aren’t actually going to be of much use. 9. Verify that the database service or net service name entry exists in the directory that this computer was configured to use. Verify that the sqlnet.ora file includes the following entry: NAMES.DIRECTORY_PATH=(ldap, other_naming_methods) This piece of advice is actually quite sensible. Concentrate on the second sentence: the NAMES.DIRECTORY_PATH parameter in sqlnet.ora governs which names resolution method Oracle will use to work out what a User is trying to connect to. You need to make sure that the right naming method is selected, and that any and all files needed to make that method work actually exist. For example, I can generate an ORA-12154 by editing my sqlnet.ora so that it claims we are using the Host Naming names resolution method: C:\ >type f:\oracle\ora90\network\admin\sqlnet.ora NAMES.DIRECTORY_PATH=(hostname) names.default_domain = aldeburgh.local C:\ >sqlplus system/manager@db9 SQL*Plus: Release 9.0.1.0.1 - Production on Sun Mar 17 16:43:53 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. ERROR: ORA-12154: TNS:could not resolve service name

17/03/2002

Page 6 of 8

ORA-12154

Networking Tips

This error arises because actually, I’ve set things up to use the Local Naming method (in other words, use a local copy of tnsnames.ora to resolve service names). But here I am telling Oracle that it should use host naming… as you might expect, it’s just not going to work. Similarly, if I tell Oracle that I have a Names Server for name resolution, I really do need to actually have one configured and running! If you’re configured for one method, make sure that sqlnet.ora is aware of it, and make sure that the infrastructure that naming method requires actually exists, and works. In my case, editing the sqlnet.ora fixes the problem: C:\>type f:\oracle\ora90\network\admin\sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES) NAMES.DEFAULT_DOMAIN = aldeburgh.local C:\Documents and Settings\howardjr>sqlplus system/manager@db9 SQL*Plus: Release 9.0.1.0.1 - Production on Sun Mar 17 16:55:16 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option JServer Release 9.0.1.1.1 - Production

The line in bold there shows that I’ve now told Oracle to use a tnsnames.ora, and we already know that I have that in place, with all the right entries. Hence the successful connection. I’ll just finally mention one other cause of ORA-12154: bad formatting in the tnsnames.ora. In particular, it can be very sensitive to the indentation and layout being played around with. Take this as an example: C:\>type f:\oracle\ora90\network\admin\tnsnames.ora DB9.aldeburgh.local = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db9.aldeburgh.local) ) ) C:\>sqlplus system/manager@db9 SQL*Plus: Release 9.0.1.0.1 - Production on Sun Mar 17 17:19:38 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. ERROR: ORA-12154: TNS:could not resolve service name

Someone’s been just a little too eager to get rid of all the indentations that were in the original file (though, if you compare its actual contents to the one on page 1, you’ll see Copyright © Howard Rogers 2002

17/03/2002

Page 7 of 8

ORA-12154

Networking Tips

that there’s otherwise practically no difference). When the indentations go, somehow Oracle loses its ability to distinguish the separate components, and thus to parse the file into meaningful bits and pieces. Everything else can be right (and in this particular test, everything else was correct), but this simple formatting error can cause the infamous error. So that’s ORA-12154. It’s not too tricky to resolve: just make sure your tnsnames.ora is laid out correctly; that what the User types after the “@” symbol matches up with what you’ve got listed as possible aliases in the tnsnames file; that you’ve taken into account sqlnet.ora’s habit of silently appending bits and pieces to what is typed after the “@” symbol; that if you’re using a names resolution method, the infrastructure that method relies on is actually in place. Do all that, and the errors should go away.

17/03/2002

Page 8 of 8