- 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.
- Can the client reach the database server?
OS> ping DB01
- Is OraNet working OK?
OS> tnsping DB01 10
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:
- Copy your sqlnet.ora to sqlnet.ora.standard
- Copy your sqlnet.ora to sqlnet.ora.trace and make your changes here.
- 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