Troubleshooting Client Connectivity Issues
Once in a while, the user may come to you telling he is not able to connect to the database. He could be trying to connect from sqlplus or SQL Developer or any other client software. You can try the following steps to troubleshoot the connectivity issues from a client machine. Database connectivity is a major thing.
– Make sure the user has a valid username/password with connect role.
– Check whether he is able to connect from either sqlplus or developer or any other client software.
– Verify whether he can ping the database server by Name or IP Address.
– Make sure he can successfully resolve the service name using tnsping.
tnsping < service_name >
You should have an entry for service_name in tnsnames.ora file under your ORACLE_HOME/network/admin directory. If you don’t have one, you can either copy the file from some other machine or create one using editor and copy the contents of file. You can also use Oracle Network Configuration tools to create the same.
– In the tnsnames.ora file, check whether you are using Hostname or IP address and can ping the same.
– If you have the tnsnames.ora file present, and still not able to resolve the service name by tnsping, verify whether your computer is configured to add a ‘domain name’ automatically while trying ping or tnsping. If that is the case, you can add the service name with domain name in tnsnames.ora file.
test, test.mydomain.com =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost-vip)(PORT = 20110))
(SERVER = DEDICATED)
(SERVICE_NAME = test.mydomain.com)
– You can also try tnsping for quick testing without having tnsnames.ora file. Do a tnsping on a working machine or server. Copy the working string from there:
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhostname-vip)(PORT = 20110)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test.mydomain.com)))
Now on the problematic machine, try tnsping using the working string inside double quotes like this
tnsping "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhostname-vip)(PORT = 20110)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test.mydomain.com)))"
If this works and it doesn’t work with tnsnames.ora file, then your entry in the file is not correct. Copy the contents from a working file and paste them exactly the same into your file. Even an additional space could make your entry non-workable.
– Make sure the port you are using in the connectivity string in not blocked by the firewall.
telnet < Host name or IP > < Port no. >
If it’s not open, you would see an error message like this
…Could not open connection to the host, on port 80: Connect failed
– Make sure the Database server is not restricting access to limited servers. The database server can restrict access by adding specific Hostnames in sqlnet.ora file. The sqlnet.ora file is also present under Oracle_Home/network/admin directory.
In case the issue in not client specific but happening for all clients, then you need to check the Database server. Make sure you can do a tnsping from server itself. The instance is up and listener is running.
You should need to check client side and server side firewall and antivirus sometimes.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.