Oracle Tips and Tricks — David Fitzjarrell

June 30, 2008

What’s In A Name?

Filed under: General — dfitzjarrell @ 12:40

I read this post: http://arjudba.blogspot.com/2008/06/ora-12541-tnsno-listener.html (that has since been removed) describing the ORA-12541 error and what, exactly, to do to correct the problem. The one GLARING omission to his text involves running named listeners (those NOT named ‘LISTENER’). Let’s look at how someone following his advice without looking further into the listener configuration can, somewhat inexplicably, not fix the problem.

The listener.ora file contains configuration information for, you guessed it, the listener process essential for networked access to a database from a remote client. [A common ‘complaint’, which I’ll list here for want of a better place, is that the Net Configuration Assistant doesn’t ‘create’ the listener. Really, there’s nothing to create on a UNIX or Linux system with the listener as one can have a fully functioning listener without a listener.ora file. [This is also true of Windows but a service is required before the listener can be started.] The Net Configuration Assistant records the configuration of your network in the listener.ora file (server name/IP Address, SID, port number), and on Windows systems creates the required service. The Net Configuration Assistant will start the listener after the listener.ora file is created. The only time you need to run the Net Configuration assistant is on Windows systems, where a service needs to be created. On UNIX and Linux systems you can simply use lsnrctl to start a listener with the default name and port even if you do not have a listener.ora file configured.] One of the configuration parameters available is a listener name, which defaults to LISTENER in many installations, but may also be set to just about any name one could desire in place of the default. A plain-vanilla, standard listener.ora might look like this (example is for a shared server mode listener):

 LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))    -- configures listener for
  )                                            -- multithreaded control agent

and use the default name of LISTENER for the listener process. Then, again, it might look like this:


 APP_LISTNR =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

Notice it does NOT use the default name of LISTENER, instead using the name APP_LISTNR as possibly a more descriptive name for the process. This is where the problems in his post here begin. Let’s follow his advice and see where we’re led.

The author of the problematic blog entry assumes a listener configuration using the default listener name, and in the first case his listed advice of running ‘lsnrctl start’ would work quite well:

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:53:50

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

and you’ll also see the current status of the listener and a summary of the available services. But, for the second case (with the non-default-named listener) simply running ‘lsnrctl start’ WILL start a listener named LISTENER, not the configured listener named APP_LISTNR:

$ lsnrctl start

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:53:50

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

And, in that status report one sees the following tale-telling line:

The listener supports no services

indicating that, even though you’ve successfully started a listener named LISTENER, it doesn’t support anything. [With 10g and later releases the listener.ora file may not have any hard-coded entries opting instead to take advantage of database auto-registration. In such cases when the listener is started the above message will appear. Wait for a minute or two and execute a ‘lsnrctl status’ request and you’ll likely see auto-registered databases displayed and the message regarding the listener not supporting services will have disappeared. Notice also that the entries for auto-registered databases have a status of READY and the status of hard-coded listener entries is UNKNOWN. Why UNKNOWN? Oracle is being told to listen for these databases regardless and, as such, no ‘polling’ is taking place; there is no problem with such services. If, after five minutes of waiting, you still see that the listener supports no services you’re likely running into the named listener issue described here.] So you may have ‘fixed’ the ORA-12541 error (as you DO have a listener running at the specified port and address), but you still cannot connect because you’ve traded the ORA-12541 error for an ORA-12514:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

and the advice from the same soul for an ORA-12514 (which he lists as mutually exclusive from an ORA-12541) won’t work as your listener, named LISTENER, doesn’t support any services because someone decided to name the listener for this particular system as APP_LISTNR. And, gee whiz, that particular listener hasn’t been started yet.

Starting the named listener properly fixes the entire situation:

 $ lsnrctl start app_listnr

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 06:53:50

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /oracle/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

Reading the status report for this listener after startup has completed reveals that now the requested services are available:

Services Summary...
  MY_DB           has 2 service handler(s)
  APPDB           has 1 service handler(s)
The command completed successfully

So the listener, named APP_LISTNR, has been started and is available to field connections to the databases listed. And you’ve cured the ORA-12541 and ORA-12514 errors (which, in this case, are not mutually exclusive).

It’s funny (in a not-so-humorous way) how non-standard listener configurations can make generic advice, well, less than useful. One should always check the listener.ora file (if one exists) before blindly marching into oblivion, as a non-default name for a listener may be set thus making the ubiquitous ‘lsnrctl start’ advice meaningless.

What’s in a name? Plenty if you’ve decided to use a non-default naming convention for your listener.

So if your listener IS named LISTENER what else could cause an ORA-12514? Several things, actually, starting with a misconfigured or missing sqlnet.ora file. Let’s say you have tnsnames.ora entries that look like this:

APU.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(Host = SNGSBBD.SMORT.NET)(Port = 1529))
    )
    (CONNECT_DATA = (SID = APU))
  )

Noting that all of the aliases have ‘.world’ appended to them does make for consistency, however if you’ve been used to a sqlnet.ora file configured like this:

NAMES.DEFAULT_DOMAIN = world
NAME.DEFAULT_ZONE = world  # currently unsupported parameter, ignored
                           #
                           # legacy setting from Oracle version 8 SQL*Net
                           # configuration

and it now has none of those entries listed above attempting to tnsping or access APU will generate an ORA-12514 since APU isn’t in the tnsnames.ora file, APU.world is. A properly configured sqlnet.ora file will “automagically” append the NAMES.DEFAULT_DOMAIN value to the supplied alias and create APU.world from APU. Of course if NAMES.DEFAULT_DOMAIN is listed as .net then you have other issues as none of the tnsnames.ora aliases are defined as .net and you’ll not rid yourself of the ORA-12514 errors until that file is corrected.

Another cause of an ORA-12514 is trying to use an alias which is not defined in the tnsnames.ora file. Trying to connect to, say, ABU instead of APU would throw an ORA-12514 error, and rightly so, because there is no ABU.world entry defined (presuming a configuration like our prior example above). So it’s imperative to inform the user community of the proper database TNS aliases to use.

Yet another cause is defining your tnsnames.ora alias with the wrong port number. If your listener happens to be configured for port 1532, and you let the Net Configuration Assistant have its defaults for your latest entry the ORA-12514 errors appear; you’re confused by this error because, gee, the assistant finished successfully so what could POSSIBLY be wrong? As you can see, plenty, because you must configure your tnsnames.ora entries correctly for the listener you’re trying to access, in this case by using port number 1532 instead of the default, which is 1521.

[Another error, ORA-12154, can rear its ugly head in networked environments when the remote location of the tnsnames.ora file is unavailable. It can also appear if the TNS_ADMIN environment variable is set to the wrong location (which, again, makes the tnsnames.ora file unavailable). It’s easy to misread these error numbers (since they all have a 1, 4 and a 5) so care is necessary when doing so such that a proper resolution can be effected, because the solution to an ORA-12154 error (point the environment to the proper location of the tnsnames.ora file or have the network folks fix the errant nfs mount) is different than the solution to an ORA-12514 or an ORA-12541 error.]

Yes, there are some ‘standard’ items to look for when ORA-12541 and ORA-12514 errors occur, but there are also some non-standard items as well, and it’s the responsible blogger who informs you of both, as half an answer is sometimes worse than no answer at all.

A rose by any other name would certainly smell as sweet, but that new name may keep you from getting to your Oracle database if the listener is involved.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: