Tnsping is a nice little utility that comes with Oracle. Lots of developers don’t know about this. If you have SQL*Plus available on your machine, chances are you also have tnsping. You can use this tool to troubleshoot Oracle connectivity issues, sort of like ping for TCP/IP. To use it, just type:
tnsping <Service Alias> [count]
Where Service Alias is defined in tnsnames.ora.
Typically, if tnsping returns an error, chances are the alias doesn’t exist in tnsnames.ora file or a typo. Just add/correct that in the file, you will be able to ping the Oracle instance.
On a Windows system, this is where an introduction to tnsnames.ora would end. But not on Unix.
Gotcha on Unix
When we had connectivity issues with Oracle 11g database this morning, we kept getting errors while trying to tnsping. I logged in with the Oracle id and checked the contents of tnsping.ora file. It looked OK. Also, with this id, I was able to login to Oracle without any problem. This was a puzzle.
Then I remembered the file level permission on *nix systems. I looked at the permissions attribute of the file, tnsnames.ora (If you do a ls -l
on Unix, it shows you that). Bingo! The file had didn’t have “read
” access for Other (public).
-rw-rw-r-- tnsping.ora
In this case, even though the file was there, it wasn’t “visible” to other ids, because of missing read permission. When I added that (r in bold), the other user was able to tnsping and connect to Oracle finally.
Note on Connectivity
If tnsping is successful, it merely tells us that the SQL*Net listener is running correctly on the server side. This doesn’t guarantee that the database itself is running. You need to login to find that out.
References
- http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/
- http://www.orafaq.com/wiki/Tnsnames.ora
- http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/TDT2/Default.aspx
- http://docs.oracle.com/cd/B19306_01/network.102/b14212/connect.htm#sthref1535
- http://www.dartmouth.edu/~rc/help/faq/permissions.html
Filed under: CodeProject, Databases, Oracle
Tagged: Connectivity, Oracle, SQL*Net, TNS, TNSPing
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.
I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.
The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!