When using tnsping, can I specify a port number along with the SID?
I use Bitvise Tunnelier to connect to a series of Oracle databases using SQL Developer; the connection is made to localhost and a specific port number, and this works fine. I'd like to connect through other means (e.g., .NET), and I'm troubleshooting with tnsping.
Is there any way to run tnsping with a port number as well as the SID? I've tried:
tnsping DatabaseSIDName:9001
tnsping DatabaseSIDName;9001
tnsping DatabaseSIDName,9001
All give "TNS-03505: Failed to resolve name".
Answer
When you use tnsping, it will source some Oracle .ora files to determine what DBs it knows about. You should see something like:
$ tnsping myDB
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 24-MAY-2007 08:55:13
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact...
You can create your own tnsnames.ora file and add that to your TNS_ADMIN environment variable to add a new DB, port, SID, etc. A full example looks like:
ORA11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 9001))
)
(CONNECT_DATA =
(SERVICE_NAME = DatabaseSIDName)
)
)
Just export TNS_ADMIN=<path to folder containing new tnsnames.ora file> and attempt the ping again. Here are some references:
http://www.orafaq.com/wiki/Tnsnames.ora
Oracle - What TNS Names file am I using?
EDIT
And per your original example/follow up comment, the simplest might be:
$ORACLE_HOME/bin/tnsping <hostname>:<port>/<sid>
There are an abundance of other options, like EZConnect and sqlplus that will test the same functionality.