Checking Oracle with the tnsping Command By Sandra Henry-Stocker Two weeks ago, this column described the commands for setting up an Oracle user environment. By capturing such things as the Oracle home directory and Oracle instance into a file, you can easily adopt an environment that makes using sqlplus and issuing SQL commands easy. Just knowing a small number of SQL commands allows you to troubleshoot a database connection and determine whether a database server is running and managing data correctly. An important file that you might also want to get to know is the tnsnames.ora file. On a client, this file describes the various databases with which the client may interact; on a database server, it describes the databases or "instances" that the server supports. The tnsnames.ora file, which you should find in the directory defined by $ORACLE_HOME/network/admin, will have look something like this: DB01= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL = TCP) (Host = 123.45.67.89) (Port = 1521) ) ) (CONNECT_DATA = (SID = DB01) ) ) The parentheses should balance (i.e., an equal number of open and close parentheses) but the way the parameters are broken across lines is a matter of style. The parameters that you will be most interested in are: * Host ? The IP address or fully qualified domain name of the Database server; * Port ? The port where the Oracle server should be listening for database requests; * SID ? The name of the Oracle instance. The tnsping command can then be used to indicate whether a listener is active on behalf of a database. Once you've set up your Oracle environment (as described in the earlier column), you can issue a "ping " command. If there are no problems, then you should receive a response that says "OK" followed by the time required for the response. % ping DB01 Attempting to contact(ADDRESS=(COMMUNITY=DB01) (PROTOCOL=TCP) (Host=123.45.67.89) (Port=1521)) OK (30 msec) tnsping is especially helpful when you are unsure whether you are connecting properly to a database. The netstat command may only show you an ESTABLISHED connection: boson% netstat -anP tcp | grep 1521 10.4.3.111:35793 10.4.3.5:1521 8760 0 8760 0 ESTABLISHED The tnsping command, however, might indicate that the database is not listening or that it cannot resolve the tns service, indicating that it's time to check your tnsnames.ora file to ensure you are specifying the correct information or talk to your Oracle database administrator (DBA) to determine if there are any problems on the server. To learn more about Oracle administration, you might want to check out the slideshow available at: http://dclug.tux.org/bytes_1998_10_oracle/sld001.htm Though this slideshow addresses Oracle Database administration on Linux, it applies, more or less, to Oracle on any Unix system. If you work with systems that support or make use of Oracle, then you'll likely benefit from knowing enough about database administration to determine whether your databases are functioning properly.