Server side checks (not platform specific):
1) Check the result on the server using tnsping to the listener address:
tnsping "(address=(protocol=TCP)(host=insert host or ip address here)(port=insert listener port here))"
Should yield 'ok'.
2) Launch lsnrctl and test both status and services:
LSNRCTL>set current_listener <listener name>
LSNRCTL>status
LSNRCTL>services
3) If the instance is running in SHARED SERVER mode, the dispatchers might be getting
spawned on random ports. If a shared client connection fails, test the same client using
a DEDICATED connection:
Example of SERVER=DEDICATED usage in a TNS connect descriptor:
ORACLE_DB=
(DESCRIPTION=
(ADDRESS=(protocol=TCP)(host=insert host or ip address here)(port=insert listener port here))
(CONNECT_DATA=(SERVICE_NAME=insert service name here)(SERVER=DEDICATED))
)
After including the (SERVER=DEDICATED) setting in the connect string, try again to connect using:
sqlplus myuser@ORACLE_DB
If this succeeds using a DEDICATED server and fails with a SHARED server, it is likely the firewall is blocking
the connection to the DISPATCHER port. It is possible to run dispatchers on pre-defined ports for shared
server usage in environments with firewalls.
Here's an example of the DISPATCHERS spfile parameter configured to spawn on ports 5000,5001 & 5002):
dispatchers="(address=(partial=true)(protocol=tcp)(host=your host)(port=5000))(dispatchers=1)"
dispatchers="(address=(partial=true)(protocol=tcp)(host=your host)(port=5002))(dispatchers=1)"
dispatchers="(address=(partial=true)(protocol=tcp)(host=your host)(port=5003))(dispatchers=1)"
Note that the client would require that these ports are open as well as the listener port in
order for a SHARED connection to be successful.
UNIX:
1) Grep for tns listener process.
ps -ef | grep tns
Confirm the listener process is present.
Confirm the listener is running under:
A) The correct name (LISTENER, LISTENER_SCANx, etc)
B) The correct $ORACLE_HOME/bin (GRID, RDBMS_HOME)
2) Check netstat output and confirm the port/address:
netstat -an | grep <listener port here>
See LISTEN process:
Example.
netstat -an | grep 1521 | grep LISTEN
LISTEN process on wildcard address using port 1521:
tcp 0 0 0.0.0.0:1521 0.0.0.0:* LISTEN
Windows:
1) Check netstat at command line:
Example:
netstat -an |find /i "LISTEN"
Confirm LISTEN process in output for IP and listener port:
TCP 0.0.0.0:1521 0.0.0.0:0 LISTENING
netstat -an | find "1521" (Replace 1521 with your actual listener port)
2) Check Control Panel Services for TNS Listener service. Confirm that it's
started and that the listener name matches listener used in LSNRCTL commands
above.
Client side checks:
1) tnsping "(address=(protocol=TCP)(host=server hostname here)(port=listener port here))"
2) Telnet host port Where host is the server where the listener is running and port is the listener's port:
Example of a failure:
C:UsersOracle>telnet myhost 1521
Connecting To myhost...Could not open connection to the host, on port 1521: Connect failed
SCAN & RAC Specific:
1)Confirm that remote clients are able to connect to BOTH SCAN listener endpoints (all 3)
and all VIP listener endpoints by using tnsping. Follow this example to test route to ALL
VIP and SCAN listener endpoints.
tnsping "(address=(protocol=TCP)(host=SCAN ip address 1 here)(port=SCAN port here))"
tnsping "(address=(protocol=TCP)(host=SCAN ip address 2 here)(port=SCAN port here))"
tnsping "(address=(protocol=TCP)(host=SCAN ip address 3 here)(port=SCAN port here))"
tnsping "(address=(protocol=TCP)(host=VIP ip address 1 here)(port=VIP listener port here))"
tnsping "(address=(protocol=TCP)(host=VIP ip address 2 here)(port=VIP listener port here))"
Each tnsping should yield an 'OK'
The following document contains a high level diagram of SCAN to VIP listener "redirection".
It is clear that the client MUST have a route via TCP to ALL listener endpoints (except physical) in
the cluster.
http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
2) Check the LOCAL_LISTENER and REMOTE_LISTENER settings for the instances and confirm that
they are correctly set and all addresses to which they point are reachable from the remote client
hosts:
SQL>show parameter LOCAL_LISTENER;
SQL>show parameter REMOTE_LISTENER;
connection when local on the DB server is successful) then check for any Network "blocks".
For example, a Firewall may be blocking the TCP communication which the System Admin would need to correct.
__________________________________________________
If Oracle support is needed to continue to troubleshoot this problem:
Generate an Oracle Net level 16 (support) client trace using the following instructions:
Add these parameters to the sqlnet.ora file that would be referenced by the client in this case:
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=<dir location>
TRACE_TIMESTAMP_CLIENT=TRUE
DIAG_ADR_ENABLED=off
Reproduce the problem and provide the client traces to support for further
investigation.
Turn client tracing OFF when finished.