:

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

A common error message that people receive and are often confused about is the ORA-12514 TNS Listener does not currently know of service requested in connect descriptor. I hope to help you find a solution to your ORA-12514 error message in this post…

So, let’s start by talking about why you might receive this ORA-12514 TNS listener… error message. The short answer is that it’s most likely a configuration issue. It could be a configuration problem at the client or server side, so let’s start by looking at the possible cause at the client side.

The TNS Listener Story

I’d like to use a little analogy to try and describe this problem here so bear with me…Let’s pretend that you want to contact someone by post (Imagine email doesn’t exist…). You only have three parts to their address; the house number and the street name and city. To write to them you need to write on your envelope their name, house number, street address and city. If you fail to put the house number or the street address and city, it’s not going to reach them. This is the same as the address for the listener; the street address is the IP address, the house number is the port and in this case the name of the person is the alias that you are using to connect.

TNSNames Explained

Let’s take a look at a TNSNames.ora entry so that we can see how this works.

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

So in the case above the alias (name) is TESTDB.DEV.INT.COM, the IP (street) address is a DNS entry TESTDBListener and the port (house number) is 1521. This allows the client to resolve the name you use (TESTDB.DEV.INT.COM) to attempt to make a connection to the database.

Make sure that you have put the correct alias. This includes the domain part of the name (.DEV.INT.COM) unless you already have that specified in the SQLNET.ora file using the NAMES.DEFAULT_DOMAIN parameter. If that’s correct, then we should make sure that the SID is correct. Sometimes this might say SERVICE_NAME. One is an old way of specifying what instance to connect to and the other more recent. You can use either, but they differ slightly. For example:

SID = TESTDB
SERVICE_NAME = TESTDB.DEV.INT.COM

The service name is made up of the SID + DOMAIN. Hopefully this is all correct for you and you can start to now look at possible mis-configuration issues on the server side.

Firewall Access

At this point you should also check that the connection is allowed through the firewall using the TELNET command. You test it like this from a command prompt on the client machine:

    cmd> telnet 10.60.41.140 1521

(You have to hold CTRL + ] to get this next message)

    Welcome to Microsoft Telnet Client
    Escape Character is 'CTRL+]'
    Microsoft Telnet> quit

That should show you that a connection is possible from your client machine to the IP address of your listener, 10.60.41.140 in the example above, and on the port which is specified in the TNSNames.ora file, 1521 in the example above which is the default port number. You will receive a timeout message if it doesn’t work, in which case you will need to contact your network admin team and ask them to allow the traffic through on that IP address.

NOTE: Once a connection is made through the listener the connection is passed off and the client is connected directly to the database. The connection no longer uses the same port, 1521 in this case, it will use another one so you need to make sure that SQLnet (TCP/IP) traffic is allowed through on the full port range for that IP address and not just 1521.

So, on the server side you have the listener which should be “listening” for incoming connections on an IP address and port number, and it has a list of services/databases that it is listening for. In our case we’re hoping that the listener is listening on the IP address that the TESTDBListener DNS entry resolves to and on port 1521, because this is what we have specified in the TNSNames.ora file on the client (as shown above).

You can find this out by looking in the LISTENER.ora file which is located in the ORACLE_HOME\network\admin directory or by starting up the listener control interface, which can be started by running the command lsnrctl in the ORACLE_HOME\bin directory. Once you’re in you can type “status” and it will show you what the listener is listening for. That’s if you have a default name of LISTENER for your listener! If it’s not the default name then you will need to run set current_listener <listener_name> first, adding the listener name in there then the status command will work.

You’re Making Good Progress, What’s Next?

OK, we’re making progress. We now know if/what the listener is listening for. You may have found that there were no entries in the listener for the database that you were trying to connect to. If that was the case, great, you can add the entry to the listener.ora file and reload the listener for it to be working. Let’s take a quick look at an entry in the listener.ora file:

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 this listener shows the IP which it is listening on (10.60.41.140), the port it is listening on (1521) and which SIDs (DBs) it is listening for (TESTDB and TESTDB1). The listener is called 11GLIST. This is just an example so you should check that all of your information matches up correctly for your environment. Let’s summarise what we should now be able to say:

  • The IP address in TNSNames.ora file on the client should be reachable via ping from the client machine
  • The same IP address should be present in the listener.ora file so as to be expecting incoming requests
  • If the TNSNames.ora file is using a name for the HOST check that you can look it up in the DNS using NSLOOKUP
  • Check that that DNS entry IP is correct as points 1 & 2 above
  • Ensure that the port is the same in the TNSNames.ora on the client as on the LISTENER.ora file on the server
  • Check that the port is open on that IP using the TELNET command
  • Verify that the listener is listening on the correct IP, port and for the databases that you expect it to

Hopefully that should cover in some detail the checks you can perform to ensure that the listener is listening for your connection and resolve your ORA-12514: TNS:listener does not currently know of service requested in connect descriptor error message. I’d like to mention just one more item before I leave this topic, though….

Dynamic Listener Registration

The database is able to dynamically register itself with the listener so that you don’t actually have to add the configuration statically to the listener.ora file. That’s something for another topic but something which you need to bear in mind when dealing with this scenario. Maybe your database hasn’t registered itself, maybe it isn’t configured to register, and maybe you are seeing the ORA 12514 TNS error message only intermittently as a result of this. All scenarios have to be considered.

I hope that helps to shed some light on a sometimes confusing subject!

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

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