:

Listener, TNSNames and Connection Configuration

tnsnames.ora example

The Oracle listener is a cause of many issues when attempting to configure it for use. Because the listener is usually configured and then forgotten about it’s sometimes overlooked and only learnt about when there are errors. So, I thought it would be a good idea to cover some of the basics along with some error messages that you might come across which involve the listener and configuration of the listener.ora and tnsnames.ora files.

How the Listener Works

To put it simply, the Oracle listener is a server-side process which allows you to connect from a client machine to the database server. It “listens” for incoming requests on an IP address that you specify within the listener.ora file on the server, using a default port of 1521. The port is also configurable. Once you make a request to connect to the database from your client machine, the listener will authenticate you with the database and then hand off your connection to the database server itself so that you are now connected directly to the server. At that point it doesn’t matter if the listener is working or not because you no longer pass any information through the listener. In short, the listener just passes off your connection to the database if you give the correct address, username and password and have sufficient privileges to make a connection to the DB.

You can call the listener almost anything you like. The default name is “LISTENER”. If you want to have multiple listeners running on the same machine then it’s best to name them something other than the default which helps you to easily identify them. You can only have one IP address for each listener on each server and I would recommend using a virtual IP address (one that is different to the server IP) for ease of administration. For example, you might want to move the listener to another server and this way it’s easy to do so.

Configuration

The TNSNames.ora and Listener.ora files are the two most important configuration files when it comes to configuring the listener. We’ll go through configuring and using both of these files in the next two sections, starting with the TNSNames.ora file. Then we’ll look at another files called SQLNET.ora which is also used for configuration and connecting.

TNSNames

The TNSNames.ora file is used to list alias names, IP addresses or DNS names and protocols to use when connecting to the listener. In simple terms, it’s like an address book for how your client will connect to the database. It has all the information needed to contact the listener.

If you want to have a look at one, it’s usually found in the ORACLE_HOME under the NETWORK\ADMIN directory. So it will be something like D:\Oracle\Product\11.2\dbhome_1\NETWORK\ADMIN. That’s for a Windows system. Here is an example TNSNames.ora entry which I have on my machine:

TESTDB.DEV.INT.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = TESTDBlistener)(PORT = 1521))
    )
  (CONNECT_DATA =
    (SID = TESTDB)
    (SERVER = DEDICATED)
  )
)

From this entry you can find out quite a lot of information. It shows you that there is an alias called TESTDB.DEV.INT.COM which is just the name that you would use in your connection string to connect to the DB. The PROTOCOL being used is TCP and you can see that the HOST is using a name of TESTDBListener which can be looked up in the DNS to resolve to an IP address of the server on which the listener is running and the port is 1521. It also shows you that the name of the database instance is TESTDB (SID). This is all the information you need to establish a connection from the client to the database.

The entries within the TNSNames file can be added manually or through the Net Configuration Assistant. If you are not familiar with the configuration you should probably use the assistant but it will just add entries similar to the one above for you after you specify the information you want in the GUI. Over time you will manually add, delete and change them as it’s much easier. Always make sure that you make a copy of the file before you make any changes because it can be easy to miss out a bracket which then causes it to go wrong…

Note: The part in the connection string which lists the address as PROTOCOL=TCP is not the only option you have for making a connection. It’s also possible to use a different method to connect to the database under certain conditions which makes it faster. You can read more about that on my other site in an article that I wrote regarding TCP vs. IPC connections.

Now let’s take a look at the listener.ora file and how that is configured.

LISTENER.ora

As already stated, the listener.ora file is used to configure what the listener is listening for. It’s probably easiest to start by showing you an example of an entry within the listener.ora file and then explaining what it all means, so here is one example:

11GLIST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.60.41.140)(PORT = 1521))
    )
  )

SID_LIST_11GLIST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB.DEV.INT.COM)
      (ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_11203)
      (SID_NAME = TESTDB)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB1.DEV.INT.COM)
      (ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_11203)
      (SID_NAME = TESTDB1)
    )
  )

So, here we can see that there are two main sections. The first one starts with “11GLIST =” and this configures the name, protocol to be used and the IP address for the listener. In this case the listener will be called 11GLIST which uses the TCP protocol and listens on the IP address of 10.60.41.140.

The second section for the configuration of the listener which starts with “SID_LIST_11GLIST” lists the details about each database instance which is being listened for. So, database instances being listened for in the example above; the first one called TESTDB and the second one called TESTDB1. You can see that it lists the ORACLE_HOME and the SID for each of the databases, as well as the global database name. The global database name is just the database instance name and the domain in which the database resides.

There is also a third kind of file which you should use for configuration which is called the SQLNET.ora file which we will have a look at now.

SQLNET.ora

The SQLNET.ora file, also known as the Net Services profile, is used to control certain behaviour when a connection is made. For example, you can configure tracing, default domain names to use and the default naming method which should be used, most commonly the TNSNames.ora method.

A common setting to have in this file is the NAMES.DEFAULT_DOMAIN parameter, which controls what is appended to any connection string. It’s easiest to show this with an example so take a look at these connection strings which would usually be executed from a command prompt to connect to the database:

Sqlplus user/password@testdb
Sqlplus user/password@testdb.dev.int.com

The difference between the two is that the 1st one specifies a domain and the 2nd one doesn’t. If you had a SQLNET.ora file which had the entry NAMES.DEFAULT_DOMAIN = DEV.INT.COM in it then both of these connection strings would work. The idea is that by using this parameter you don’t have to specify the full database name and domain name each time you connect.

Now we’ve covered the configuration side let’s have a look at some errors which you may encounter.

SQLNET, Listener and Connection Errors

OK, so let’s start with some basic error messages that you might come across, the first one being TNS-12541: TNS:no listener. This is usually caused by the obvious…The listener not being up! Try to restart it using the command line. To open up the command line either set your ORACLE_HOME and type lsnrctl or put the full path to the executable which will be ORACLE_HOME\bin\lsnrctl. Once you are in if your listener is called LISTENER then you can use the commands “start”, “status” and “stop” to show you what’s going on and start and stop the listener. If you have a non-default name for your listener you will need to use the “set” command first. For example, if my listener was called LIST1 I would type SET current_listener LIST1 and then use the other commands.

If your listener won’t start you will need to look at the configuration and check that everything is OK. It can be easy for someone to modify the listener.ora file and remove/add a line or bracket by mistake which results in the listener not starting so go and check out that all the lines are there as they should be. You can see an example of a listener.ora entry above so make sure it looks the same. Alternatively, you can try using the Net Configuration Assistant to do it for you. This ensures that the formatting is OK.

NOTE: If you’ve copied and pasted entries into your listener.ora file you may find that there are additional line breaks which can’t be seen easily. Watch out for those!

TNSPING.exe

The TNSPING utility is a great tool for checking the responsiveness of the listener. It is the same as the PING utility but just for the Oracle listener. Use this to check that your TNSNames file is correct and that your connections resolves to a valid IP address and port number. For example, using the information from the examples above I would use:

TNSPING TESTDB to check that I was able to contact the listener and that it was expecting to receive connection requests for the TESTDB database instance. The TNSPING utility can be found in the same directory as the lsnrctl utility, which is ORACLE_HOME\bin.

NOTE: Just because a tnsping works it doesn’t mean that the database is up. It only means that the listener is up and it is listening for the connection that you are testing for. This working is no guarantee that you are able to connect to the database.

Below is an example of how the output should look from an unsuccessful TNSPING:

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
D:\Oracle\product\11.2.0\dbhome_11203\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

This shows that the configuration is incorrect and the SID/service name that you are attempting to connect to cannot be resolved. Check the TNSNames entry is there for your database you are attempting to connect to. If it is, check that the IP address/DNS entry are correct as previously mentioned above when regarding configuring the TNSNames.ora file. Then, check that you can ping the IP address from the server you are making the connection from. If you can’t, that is your problem. If you are using DNS you can try using NSLOOKUP to resolve the name. For example, NSLOOKUP TEST will attempt to look up and show you the IP address for the TEST name in your DNS. The problem will be in this area somewhere.

NOTE: Once you have made a successful connection to the database you no longer require the listener. Your client has a direct connection to the database itself, even stopping the listener won’t interfere with existing connections to the database.

ORA-03113: TNS:end-of-file on communication channel

This is most likely because something has gone wrong on the database server. Have a check through the alert log located on the DB server itself, and see if you can see any errors. If the DB looks OK then it might be some kind of network issue, so you will need to check TNSPING and PING from your client to the server to see where there is a problem.

ORA-12154: TNS:could not resolve the connect identifier specified

More often than not this is a configuration issue. You should run through the checks outlined above, ensuring that the TNSNames.ora file is correct, the IP/DNS name is reachable, etc. Once you have performed these checks it should become apparent where the problem lies. Also, make sure that you are using the correct path for the home that you expect to be using. I’ve seen many occasions where there are multiple ORACLE_HOMEs on the same server and the PATH environment variable is set to an incorrect one.

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

This means that the listener received a connection request but the name given to it was not recognised by the listener. It could be that the database has not dynamically registered itself with the listener yet or that the listener does not contain the appropriate configuration to allow connections from the client through the listener to the database. You should go through the information above with regard to the listener configuration and make sure that the LISTENER.ora file contains all of the relevant entries for the database that you are trying to connect to.

ORA-12170: TNS:Connect timeout occurred

This error happens when your client is unable to establish a connection and get authenticated in an appropriate amount of time. I have written an article about the ORA-3136 error message which you can read about and which is related to the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in the SQLNET.ora file.

You should check network connectivity, ping response times from the client to the database and/or listener server and that there are no denial-of-service (DOS) attacks against your database server, which will overload the system. You can check that out by looking in the listener log on the server and checking if you can see any error messages in there.

TNS-12500/ORA-12500: TNS: listener failed to start a dedicated server process

This error message indicates that the listener was not able to start a process on the Oracle database instance. There are a few reasons why this might have happened, including:

  • The maximum number of processes allowed for a single user was exceeded
  • The listener does not have execute permission on the Oracle program
  • The associated Microsoft Windows service is not started

You should check that the Windows service is started. If it is, check the maximum number of processes by issuing the following command:

SHOW PARAMETER processes

Then you can check out the maximum used number of processes, the high water mark, using this SQL:

SET LINES 120
SET PAGES 1000

SELECT *
FROM V$RESOURCE_LIMIT;

This will show you the current and maximum values for many resources, the “processes” one being the one of interest on this occasion.

I’ve also seen a case where the Windows database service was running as a non-local system account so that we could write out to a file share and after the change we were not able to make any connections to the database using the listener. It wasn’t until we changed the listener to also run as the non-local system account that it all started working again. I’ve written another article about using a non-local system account for the Windows services.

There are quite a few more error messages that you might come across but hopefully this covers the majority of the ones you are most likely to come across.

I’ve recently written a blog post about the ORA-12154: TNS: Could not resolve the connect identifier specified, so have a look over that if you are hitting that error message. I’ve also written a little more about the ORA-12154 error message on this site which will definitely help you.

I hope that this article has helped you in some way and that you’ve solved your problem. Let me know!

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *