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:\Users\Oracle>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;

 

Finally, if the Client gets a "no listener" yet the Listener is running on the above address (and a tnsping against the same

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.