This lesson will explore the steps required to turn on logging and tracing for Oracle*Net.  To enable tracing you may changer the listener.ora and sqlnet.ora files or you can use the Enterprise Manager component control utilities.  We will begin by identified the parameters and them show the different ways to control the levels of tracing and the locations of the trace files.

File Locations

You can control the name of the trace file for each component. Any valid string can be used as a trace filename.  You can control the destination directory of the trace file for sqlnet.ora and listener.ora through the Oracle Network Manager or you can directly edit the file to change the default trace file directory.

Oracle Server - The server-side trace files for Oracle*Net are located by default in the $ORACLE_HOME/network/trace directory.  You can change this default by changing the listener.ora parameter trace_directory_listener.  For example, to directory all listener trace files to the /tmp/listener directory, enter trace_directory_listener=/tmp/listener

Oracle Client - Unfortunately, there is no default directory name specified for SQL*Net client logs, and many PC clients will have sqlnet.log files in many locations.  To change the directory location, change the sqlnet.ora parameter called trace_directory_client.  For example, to direct all client traces to c:\temp\oracle, enter trace_directory_client=c:\temp\oracle.  To set tracing parameters using sqlnet.ora files, specify the following parameters in your sqlnet.ora file:

trace_level_client=user
trace_directory_client=/tmp/user

Note: To activate tracing for the listener you can enter the "lsnrctl trace" command without changing the listener.ora file.  Otherwise, if you modified the sqlnet.ora or listener.ora files while the application was running, start or restart the application to enable the changed parameters.

Normally, when a new trace file is created for a client, it overwrites any existing trace file for that component. If you want to save an existing trace file, you must change its name before running the trace facility again. However, if the trace_unique_client parameter is set to ON, the trace facility appends a process identifier to the name of each trace file generated so that several files can co-exist in the directory. 

Remember, there are alternatives to editing the parameter files.   In Oracle Network Manager you can set the listener trace level, and non-default name and location for the trace file in the LISTENER.ORA file. To turn tracing on and off, use the TRACE command of the Listener Control Utility.

Turning-on Tracing

The steps used to invoke tracing are outlined here. Each step is fully described in subsequent sections.  The first step is to choose the component to be traced.  You may trace a client, a server, or a listener.  The steps to enable tracing are very simple:

- For any component, you can invoke tracing by editing the component configuration file that corresponds to the component traced. The component configuration files are listener.ora, sqlnet.ora or names.ora.

- Execute or start the component to be traced. If the trace component configuration files are modified while the component is running, the modified trace parameters will take effect the next time the component is invoked or restarted. Instructions for each component follow:

- Be sure to turn tracing off when you do not need it for a specific diagnostic purpose.

Trace Levels

You can set trace levels to provide different amounts of trace information. For each component, there are three levels of trace information:

OFF - This default setting signifies that no trace output should be generated.

trace_level_client = off

USER - This setting is intended for DBAs who do not have extensive network knowledge. The USER trace level generates only English sentences describing high-level significant networking events.

trace_level_client = user

ADMIN - This setting is intended for network administrators with more knowledge of network terms and concepts. The ADMIN trace level generates all of the USER level statements plus additional information such as TNS addresses and detailed events.

Remember, the trace files are very large. If this parameter is set to "user" or "admin", the number of large trace files created could interfere with the available disk space on your machine. Therefore, turn this parameter on only for specific reasons and delete the resulting files when they are no longer useful.

Note: If an existing trace file from a previous trace session exists and you need to retain the information in it, you will need to save that file under another name. This is because Oracle will overwrite the existing trace file.

Tip: If you are having trouble pinging a server, and need a detailed trace, you can manually add the following two tracing parameters to your sqlnet.ora file to perform tracing on the TNSPING utility:

TNSPING.TRACE_LEVEL
TNSPING.TRACE_DIRECTORY

Viewing the log and trace file locations

The easiest way to see the location of server-side log and trace files is to issue the listener control status command:

dilbert> lsnrctl stat 

LSNRCTL for IBM/AIX RISC System/6000: Version 2.3.3.0.0 - Production on 05-APR-9
9 15:42:31 

Copyright (c) Oracle Corporation 1994.  All rights reserved. 

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=fred))

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 2.3.3.0.
0 - Production
Start Date                22-MAR-99 14:40:16
Uptime                    14 days 0 hr. 0 min. 15 sec
Trace Level               off
Security                  OFF
SNMP                      ON
Listener Parameter File   /etc/listener.ora
Listener Log File         /ora8/home/8.0.5/network/log/listener.log
Services Summary...
  lamp          has 1 service handler(s)
  i8itall       has 1 service handler(s)

Here we see the exact directory and file have for our listener log file.

To view client parameters, we must display the local sqlnet.ora file to see the locations of the log and trace files.

Now that we understand the basic files locations and purposes, let's look at the uses of these files.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.