:

ORA-12154: TNS: could not resolve service name

ORA-12154 TNS could not resolve service name

5 Checks to Resolve The 12154 ORA Error

I assume that you are using the TNSNaming method to connect to your databases, using a TNSNames.ora file for this article.

Check 1 – Connectivty, IP

The first thing to check out is that the server is up…So, open up a command prompt and use the PING utility to check that:

PING <IP ADDRESS>

This should return values saying that there is a response. If it doesn’t, it means that the server is unavailable or there is no permitted route to it from where you connecting from.

If it pings the next step is to make a connection to the IP address and port which you have configured. To check this, you can use the TELNET command. Here is an example:

TELNET 10.60.40.60 1521

You can run this command from a command window. It should come up with a blank looking screen if it connects. In which case hold down CTRL and the right parentheses key at the same time, then type “quit” to return back to the command prompt window.

If you can’t connect, and it times out, then you have to speak to the network team and ask them to open up SQL net connectivity on that IP and port.

The IP address you need to use can be found in your TNSNames file which can be found, on a Windows server, somewhere similar to:

D:\Oracle\Product\11.2.0\db_1\Network\Admin

There is an example of a TNSNames entry further below where you can see that theHOST = TESTListener. This means that there is a DNS entry which resolves to an IP address. In order to work out what the IP address is you can use the NSLOOKUP command like this from a command prompt:

NSLOOKUP TESTListener

Check 2 – Connectivity, TNSNames

For this check you will need to use the TNSPing utility. This tools confirms whether or not you can access the listener.

TNSPing <DatabaseName>

Note: It DOES NOT indicate whether or not the DB is up, just that the listener is listening for requests for the service that you have specified.

On Windows, the TNSPing executable can be found in the ORACLE_HOME\BIN directory which is usually something like D:\Oracle\Product\11.2.0\db_1\BIN

You should check your TNSNames file (D:\Oracle\Product\11.2.0\db_1\Network\Admin) first and see what entries are in there to make sure you are attemping a TNSPing using the correct name. Here is an example:

TEST.DEV.INT.COMPANY.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTlistener)(PORT = 1521))
)
(CONNECT_DATA =
(SID = TEST)
(SERVER = DEDICATED)
)
)

As I mentioned above, you can see that the HOST = TESTListener which is a DNS entry and therefore you should use the NSLOOKUP command to determine what IP the name resolves to. In order to make a valid connection, you need use the alias“TEST.DEV.INT.COMPANY.COM” in your connection string. For example:

SQLPLUS user1/user1@TEST.DEV.INT.COMPANY.COM

This will connect to user1 with a password of user1 to the TEST database. In order to prevent yourself having to type out the full SID and domain name each time (TEST.DEV.INT.COMPANY.COM), you can instruct the default domain to be appended to any TNSNames alias that you connect to. This is done by using the SQLNet.ora file which you will find in the same place as your TNSNames file.

To configure this for the example above you can add this to the SQLNET.ora file:

NAMES.DEFAULT_DOMAIN = DEV.INT.COMPANY.COM

In addition to this, I also add the following two lines.

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES)

The first one is to ensure that no unnecessary authentication is attempted, as this slows down connections vastly. If you notice that when you attempt to connect to your Oracle database the connection appears to hang before it finally makes the connection to the DB, then this is a good first port of call to reduce the connection time.

The second is to ensure that you use TNSNames for your connection method. Obviously, if you are not using TNSNames as the connection method and some other way is configured then don’t change it…

So, back to the connection issue. You could first attempt to use the TNSPing utility to verify that the listener is accepting connections to the name that you specify in your TNSNames file. To do this, you can run:

TNSPing TEST.DEV.INT.COMPANY.COM

It will output whether or not it was successful. If it wasn’t, read on…

Check 3 – Configuration, configuration, configuration

So, you still can’t connect and are receiving the error ORA-12154: TNS: could not resolve service name error message. The next step is to ensure that everything is configured properly, here is a checklist:

  1. You have performed the connectivity tests above and know that traffic is permitted to the IP you are attempting to connect to
  2. Confirm that you have typed the connection string properly
  3. Ensure that the domain matches what’s specified as the alias in the TNSNames file
  4. Ensure that the SID or SERVICE_NAME part of the TNSNames entry matches the SID or SERVICE_NAME of the database on the server that you are attempting to connect to
  5. Use the query SELECT * FROM global_name to verify that the db_name and db_domain are what you expected and that these values are used in your TNSNames file alias
  6. Make sure that the HOST is specified correctly, and connects to the allowed port – 1521 is the default

If you are 100% sure that this is all configured properly, then it should work. However, you may now receive the error message:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

And this leads on to the next section…

Check 4 – SID vs SERVICE_NAME

If you have everything configured correctly on the client side, in your TNSNames file and SQLNET.ora file, you will more than likely be facing this error, if you are still having problems:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

This message says that the SID or SERVICE_NAME that you are passing to the listener is not recognised by the listener and therefore the listener is unable to connect you through to the database.

You need to make sure that the “SID =” part of the TNSNames configuration is correct and the the same name as the database that is running on the server. If it is, you may wish to try out using the “SERVICE_NAME =” instead of “SID = “, as this can sometimes be the problem, or vice versa. For example:

TNSNames Configuration:

TEST.DEV.INT.COMPANY.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTlistener)(PORT = 1521))
)
(CONNECT_DATA =
(SID = TEST)
(SERVER = DEDICATED)
)
)

Where it says SID = TEST, you can change it to SERVICE_NAME = TEST.DEV.INT.COMPANY.COM. This may help to resolve the issue. If not, read on…

Check 5 – Are you Listening?

So, you have completed all of the above checks and it’s still not working…Don’t worry, this could be the final check that you need to make it work!

In order to connect to the DB, you will have a listener which runs, most likely, on the same server as your database. This has to be configured so that the listener is ready to receive requests over an agreed IP address and port number on that IP. In addition, you have to specify what the listener is listening for…By that I mean you need to tell it which services/sids/databases it should be expecting to service and get connection requests for.

The listener.ora file is the configuration file for the listener, or listeners, that are running on the server. It can be found in the same place as the SQLNET.ora and TNSNames.ora files, usually somewhere like D:\Oracle\Product\11.2.0\db_1\network\admin on a Windows machine.

Within the listener.ora file, there will be one or more entries like this:

LISTENER =
  (DESCRIPTION_LIST =

(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.61.40.100)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TEST.DEV.INT.COMPANY.COM)
(ORACLE_HOME = D:\oracle\Product\11.2.0\db_1)
(SID_NAME = TEST)
)
)

Note: The LISTENER part in here is the name of the listener. It might not be the default name of LISTENER, it may be something else.

The HOST part says which IP addres the listener is listening on and the PORT shows the port.

IMPORTANT: You have may more than one IP address on a server to ensure that the IP address that you are attempting to connect to in the TNSNames file on the client side matches the IP address that the listener you want to make the connection through is listening on. It should also be the same PORT number.

The part underneath that which starts SID_LIST_LISTENER lists down which services/SIDs/databases the listener will be listening for. This should match up with the actual service_name/DB name on the server and should be the same as in the TNSNames file on the client side.

I hope that this helps to resolve your connection issues….Perhaps you are now experiencing TNS-12542: TNS:address already in use which I have also written an article on.

Like it, share it...

Category: SQL Net


Related Posts

Leave a Reply

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