<\/p>\n
We will use tnsping to complete this step. It’s a common misconception that tnsping tests connectivity to the instance. In actual fact, it only tests connectivity to the listener.<\/p>\n
Here, we will use it to prove that:<\/p>\n
a) the tnsnames.ora has the correct hostname and port<\/p>\n
b) there is a listener listening on the specified host and port<\/p>\n
<\/p>\n
Start by running tnsping:<\/p>\n
- \n
- tnsping <your_tns_entry_name><\/em><\/li>\n<\/ul>\n
If it is successful you will see something like this:<\/p>\n
![\"Resolving](\"http:\/\/content.n4stack.io\/wp-content\/uploads\/2018\/08\/Resolving-Oracle-networking-problems.png\")
<\/p>\n<\/p>\n
If not, here are some common errors, and some suggestions for fixing them:<\/p>\n
<\/h4>\n
TNS-03505: FAILED TO RESOLVE NAME<\/strong><\/h5>\n
The specified database name was not found in the tnsnames.ora, onames or ldap. This means that tnsping hasn’t even got as far as trying to make contact with a server – it simply can’t find any record of the database that you are trying to tnsping. Make sure that you’ve spelled the database name correctly, and that it has an entry in the tnsnames.ora.<\/p>\n
If you have a sqlnet.ora, look for the setting NAMES.DEFAULT_DOMAIN. If it is set, then all entries in your tnsnames.ora must have a matching domain suffix.<\/p>\n
<\/p>\n
TNS-12545: CONNECT FAILED BECAUSE TARGET HOST OR OBJECT DOES NOT EXIST<\/strong><\/h5>\n
The host specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with ‘ping <hostname>’. If ping returns ‘unknown host’, speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get ‘host unreachable’, again speak to your network person, the problem could be down to a routing or firewall issue.<\/p>\n
<\/p>\n
TNS-12541: TNS:NO LISTENER<\/strong><\/h5>\n
The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.<\/p>\n
<\/h4>\n
<\/p>\n
TNSPING HANGS FOR A LONG TIME<\/strong><\/h5>\n
I’ve seen this happen in situations where there is something listening on the host\/port – but it isn’t an oracle listener. Make sure you have specified the correct port, and that your listener is running. If all looks ok, try doing a ‘netstat -ap | grep 1521’ (or whatever port you are using) to find out what program is listening on that port.<\/p>\n
<\/p>\n
<\/p>\n
ATTEMPT A CONNECTION TO THE INSTANCE<\/strong><\/h4>\n
Once you have proven that the tnsnames is talking to the listener properly, the next step is to attempt a full connection to the instance. To do this we’ll use sqlplus:<\/p>\n
- \n
- sqlplus [username]\/[password]@<your_tns_entry_name><\/em><\/li>\n<\/ul>\n
If it works you will successfully log into the instance. If not, here are some common errors:<\/p>\n
<\/p>\n
ORA-01017: INVALID USERNAME\/PASSWORD; LOGON DENIED<\/strong><\/h5>\n
This is actually a good error in these circumstances! Even though you didn’t use the correct username or password, you must have successfully made contact with the instance.<\/p>\n
<\/p>\n
ORA-12505: TNS:LISTENER DOES NOT CURRENTLY KNOW IF SID GIVEN IN CONNECT<\/strong><\/h5>\n
Either the SID is misspelled in the tnsnames, or the listener isn’t listening for it. Check the tnsnames.ora first. If it looks ok, do a ‘lsnrctl status’ on your server, to see what databases the listener is listening for.<\/p>\n
<\/p>\n
ORA-12514: TNS:LISTENER COULD NOT RESOLVE SERVICE_NAME GIVEN IN CONNECT<\/strong><\/h5>\n
This is quite a common error and it means that, while the listener was contactable, the database (or rather the service) specified in the tnsnames wasn’t one of the things that it was listening out for.<\/p>\n
Begin by looking at your tnsnames.ora. In it, you will see a setting like SERVICE_NAME=<name>.<\/p>\n
If you are running a single instance database (ie. not RAC), and you are sure that you are not using services, it might be easier to change SERVICE_NAME= to SID= in your tnsnames. Using service names is the more modern way of doing things, and it does have benefits, but SID still works perfectly well (for now anyway).<\/p>\n
If you would prefer to continue using service names, you must first check that you have not misspelled the service name in your tnsnames. If it looks alright, next check that the listener is listening for the service. Do this by running ‘lsnrctl services’ on your server. If there isn’t an entry for your service, you need to make sure that the service_names parameter is set correctly on the database.<\/p>\n
<\/p>\n
And it’s as simple as that! Hopefully this helped you resolve the typical Oracle networking problems we’ve all faced at some point in our DBA careers!<\/p>\n
<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”3.12.2″ header_2_text_color=”#e05206″]<\/p>\n
Read more about Oracle:<\/h4>\n
[\/et_pb_text][et_pb_blog _builder_version=”3.12.2″ include_categories=”343″ posts_number=”3″ show_date=”off” show_categories=”off” show_pagination=”off” fullwidth=”off” \/][et_pb_divider _builder_version=”3.11″ background_color=”#e05206″ color=”#e05206″ height=”4px” max_width=”65%” module_alignment=”center” \/][et_pb_team_member _builder_version=”3.12.2″ name=”Grahame Dyce” position=”Senior Database Consultant” image_url=”http:\/\/content.n4stack.io\/wp-content\/uploads\/2018\/08\/Grahame-Dyce_GW.png” saved_tabs=”all” global_module=”53669″]<\/p>\n
Grahame is an SC cleared Oracle Certified Professional with nearly two decades of Oracle Database Administration and Application Server experience.<\/p>\n
He loves to cook and play basketball but can’t seem to figure out the Rubik’s Cube!<\/p>\n
- sqlplus [username]\/[password]@<your_tns_entry_name><\/em><\/li>\n<\/ul>\n