oracledba.help
TroubleShooting

Listener Troubleshooting

Basic

Server

  1. Is the listener working?
    OS> lsnrctl status
    OS> lsnrctl services
    OS> tail -f /NETWORK/log/listener.log
    Windows requires the Resource Toolkit to use the tail command.

Client

  1. Can the client reach the database server?
    OS> ping DB01
  2. Is OraNet working OK?
    OS> tnsping DB01 10

Advanced

Network issues are some of the toughest to troubleshoot. Happily, Oracle has the capacity to enable detailed tracing to assist in this endeavor. The Listener writes to a log file, tracking basic connection information by default. To find the location of the log file or other details on the Listener, simply run the lsnrctl status command.

The three levels of tracing are: Support, Administrator and User. Support has the most detail, Administrator a bit less and User offers the least detail but is the most readable. It should be noted that with any type of tracing enabled, there is a higher load on the server, and subsequently the corresponding trace files will grow out of control in a busy environment. Because of this you only want to turn on tracing for brief periods.

In addition to detailed tracing on the listener, you can enable overall Oracle Network tracing by modifying the \network\admin\sqlnet.ora file. As mentioned above, modifying the files by hand can lead to more problems, so I recommend the following time honored technique:

  1. Copy your sqlnet.ora to sqlnet.ora.standard
  2. Copy your sqlnet.ora to sqlnet.ora.trace and make your changes here.
  3. Copy the appropriate file over sqlnet.ora to activate and deactivate advanced tracing.

See below example for entries for your sqlnet.ora.trace file.

# Path for Files
TNSPING.TRACE_DIRECTORY=C:\Temp\tns_trace
TRACE_DIRECTORY_CLIENT=C:\Temp\tns_trace
TRACE_FILE_CLIENT=client.trc
TRACE_DIRECTORY_SERVER=C:\Temp\tns_trace
TRACE_FILE_SERVER=srv.trc
LOG_DIRECTORY_SERVER=C:\Temp\tns_trace
LOG_FILE_SERVER=srv.log

# Level of Tracing: SUPPORT, ADMIN, USER
TNSPING.TRACE_LEVEL=ADMIN
TRACE_LEVEL_CLIENT=ADMIN
TRACE_LEVEL_SERVER=ADMIN

# Common Settings
TRACE_TIMESTAMP_CLIENT=ON
TRACE_UNIQUE_CLIENT=ON
DIAG_ADR_ENABLED=OFF

<- TroubleShooting