In this blog, I will cover some typical TNS errors that we run across as an Oracle DBA and how to go about troubleshooting the errors.
First of all, when you encounter a TNS error you need to understand that it relates to the network communication between a client connection and the actual Oracle database. Generally, TNS issues aren’t caused by the Oracle database.
ORA-12154: TNS:could not resolve the connect identifier specified : Common cause is the connect identifier you’re using does NOT exist in the tnsnames.ora file on the host you’re attempting to connect from.
You generally will not see anything in the database listerner.log or alert.log because the connection never makes it to the database host. Add the connect identifier to tnsnames.ora, with the appropriate host and sid/service id information and try the connection again.
Go to the $ORACLE_HOME/network/admin directory and open the tnsnames.ora file and look for the connect identifier, verify that it’s correct. You could have a typo or be missing the connection details in the tnsnames.ora file (on windows %ORACLE_HOME%\network\admin).
PROD = <---------------------------- this is your connection identifier (minus the equal sign)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = dbserver01)
(PORT = 1521)
)
)
(CONNECT_DATA = (SID = prod))
)
ORA-12545: Connect failed because target host or object does not exist : If the connect identifier you’re using does exist in the tnsnames.ora but the host defined cannot resolve to the database server this error occurs. Again, on the database server, you will not see an error in the database listener.log or the alert.log because the connection never made it to the database host. At this point, you need to verify that the host in the tnsnames.ora actually resolves to the database server. If the host is defined as an IP address, is it the correct IP address?
If the host is a hostname, you could have an issue with your client’s local “hosts” file or the network dns server. Check both of these. Test if you can ping the hostname from this server, to see where it resolves to and which ip address it associates with the hostname. Have the network team check the DNS server for the hostname, ensure the hostname is associated with only one IP address, and that it’s the correct IP address. If it doesn’t, then your issue is most likely with the dns server.
PROD =
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = dbserver01) <---------------------------- This example shows you are using a hostname and not a hardcoded IP address?
(PORT = 1521)
)
)
(CONNECT_DATA = (SID = prod))
)
If the client is a Linux host, issue a ping command on the hostname and the ip address as specified in the tnsnames.ora and local host file. Do you see an error?
On a windows server use the cmd command to get to a command prompt and issue the ping command.
ping ‘hostname’
ping ‘IP address’
ORA-12541: TNS:no listener : if the connection identifier is correctly defined in the tnsnames.ora on the client, and the host/IP address is correct and resolves correctly to the database server but the database listener is not up and running, then this error would appear. Your course of action for this error should be to start with verifying if the database listener is up and running.
Log into the database server as the oracle os user and issue the following two commands to verify the listener is up and running and what the status is:
On a linux/unix server:
ps -ef |grep lsnr
lsnrctl status
On a windows server go to “services” and look for the Oracle listener and ensure it’s running.
ORA-12560: TNS:protocol adapter error : If the connection identifier is correctly defined in the tnsnames.ora on the client and the host/IP address is correct and resolves correctly to the database server and the database listener is up and running but the port is incorrect, then this error would appear.
Go to the $ORACLE_HOME/network/admin directory and open the tnsnames.ora file and look for the connect identifier, verify it’s correct. You could have a typo or be missing the connection details in the tnsnames.ora file.
(on windows $ORACLE_HOME$/network/admin)
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = dbserver01)
(PORT = 1521) <---------------------------- is this the correct port the database server is listening on?
)
)
(CONNECT_DATA = (SID = prod))
)
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor : If the connection identifier is correctly defined in the tnsnames.ora on the client and the host/IP address is correct and resolves correctly to the database server and the database listener is up and running and the port is correct, but the service_name is incorrect. Check the listener services running (lsnrctl status or lsnrctl services commands). Ensure there is no typo in your tnsnames.ora.
If the client is a Linux host, issue a tnsping command to test the service_name defined in the tnsnames.ora
tnsping ‘service_name’
Log into the database server as the oracle os user and issue the following command to see what actual services are registered with the running listener
lsnrctl status
lsnrctl services
ORA-12519: TNS:no appropriate service handler found error : A common cause of this issue is that the database has maxed out on processes connected. This can be easily checked by reviewing the alert log for a ORA-00020 maximum number of processes (your db limit) exceeded. If you’re maxing out processes, you may have an application that has pushed out too many connections (a connection leak), or your user access is growing and you need to increase the database parameter “processes.”
Find your alert log and view it, adrci is helpful to ‘show alert’ if at the command line
Select * from v$resource_limit where resource_name = ‘processes’;
To increase the process parameter you must shut down the database. On a production system, this will need approval and an off-hours window to perform the action. You must also look at your hardware resources and other database parameters to ensure this new setting can be increased without causing additional resource limitations.
sqlplus / as sysdba
alter system set processes=<new value> scope=spfile;
shut immediate;
startup
This blog went over a few common TNS errors and quick ways to troubleshoot them. This is not the all-encompassing list of causes for these errors and TNS errors, in general, but this should head you in the right direction in your troubleshooting.
If you’re experiencing more involved TNS issues and need additional help resolving your issue, please get in touch with an XTIVIA Oracle DBA today!