SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance.

SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance.


PURPOSE
-------

The purpose of Oracle Net tracing and logging is to provide detailed
information to track and diagnose Oracle Net problems such as connectivity
issues, abnormal disconnection and connection delay. Tracing provides varying
degrees of information that describe connection-specific internal operations
during Oracle Net usage. Logging reports summary, status and error messages.

Oracle Net Services is the replacement name for the Oracle Networking product
formerly known as SQL*Net (Oracle7 [v2.x]) and Net8 (Oracle8/8i [v8.0/8.1]).
For consistency, the term Oracle Net is used thoughout this article and refers
to all Oracle Net product versions.


SCOPE & APPLICATION
-------------------

The aim of this document is to overview SQL*Net, Net8, Oracle Net Services
tracing and logging facilities. The intended audience includes novice Oracle
users and DBAs alike. Although only basic information on how to enable and
disable tracing and logging features is described, the document also serves
as a quick reference. The document provides the reader with the minimum
information necessary to generate trace and log files with a view to
forwarding them to Oracle Support Services (OSS) for further diagnosis. The
article does not intend to describe trace/log file contents or explain how to
interpret them.


LOG & TRACE PARAMETER OVERVIEW
------------------------------

The following is an overview of Oracle Net trace and log parameters.

  TRACE_LEVEL_[CLIENT|SERVER|LISTENER]     = [0-16|USER|ADMIN|SUPPORT|OFF]
  TRACE_FILE_[CLIENT|SERVER|LISTENER]      = <FILE NAME>
  TRACE_DIRECTORY_[CLIENT|SERVER|LISTENER] = <DIRECTORY>
  TRACE_UNIQUE_[CLIENT|SERVER|LISTENER]    = [ON|TRUE|OFF|FALSE]
  TRACE_TIMESTAMP_[CLIENT|SERVER|LISTENER] = [ON|TRUE|OFF|FALSE]   #Oracle8i+
  TRACE_FILELEN_[CLIENT|SERVER|LISTENER]   = <SIZE in KB>          #Oracle8i+
  TRACE_FILENO_[CLIENT|SERVER|LISTENER]    = <NUMBER>              #Oracle8i+

  LOG_FILE_[CLIENT|SERVER|LISTENER]        = <FILE NAME>
  LOG_DIRECTORY_[CLIENT|SERVER|LISTENER]   = <DIRECTORY NAME>
  LOGGING_LISTENER                         = [ON|OFF]

  TNSPING.TRACE_LEVEL                      = [0-16|USER|ADMIN|SUPPORT|OFF]
  TNSPING.TRACE_DIRECTORY                  = <DIRECTORY>

  NAMES.TRACE_LEVEL                        = [0-16|USER|ADMIN|SUPPORT|OFF]
  NAMES.TRACE_FILE                         = <FILE NAME> 
  NAMES.TRACE_DIRECTORY                    = <DIRECTORY>
  NAMES.TRACE_UNIQUE                       = [ON|OFF]
  NAMES.LOG_FILE                           = <FILE NAME>
  NAMES.LOG_DIRECTORY                      = <DIRECTORY>
  NAMES.LOG_UNIQUE                         = [ON|OFF]

  NAMESCTL.TRACE_LEVEL                     = [0-16|USER|ADMIN|SUPPORT|OFF]
  NAMESCTL.TRACE_FILE                      = <FILE NAME>
  NAMESCTL.TRACE_DIRECTORY                 = <DIRECTORY>
  NAMESCTL.TRACE_UNIQUE                    = [ON|OFF]

  Note: With the exception of parameters suffixed with LISTENER, all other
        parameter suffixes and prefixes [CLIENT|NAMES|NAMESCTL|SERVER|TNSPING]
        are fixed and cannot be changed. For parameters suffixed with LISTENER,
        the suffix name should be the actual Listener name. For example, if
        the Listener name is PROD_LSNR, an example trace parameter name would
        be TRACE_LEVEL_PROD_LSNR=OFF.


CONFIGURATION FILES
-------------------

Files required to enable Oracle Net tracing and logging features include:

  Oracle Net Listener        LISTENER.ORA                  LISTENER.TRC
  Oracle Net - Client        SQLNET.ORA on client          SQLNET.TRC
  Oracle Net - Server        SQLNET.ORA on server          SQLNET.TRC
  TNSPING Utility            SQLNET.ORA on client/Server   TNSPING.TRC
  Oracle Name Server         NAMES.ORA                     NAMES.TRC
  Oracle NAMESCTL            SQLNET.ORA on server
  Oracle Connection Manager  CMAN.ORA


CONSIDERATIONS WHEN USING LOGGING/TRACING
-----------------------------------------

1. Verify which Oracle Net configuration files are in use.
   By default, Oracle Net configuration files are sought and resolved from
   the following locations:

   TNS_ADMIN environment variable (incl. Windows Registry Key)
   /etc or /var/opt/oracle (Unix)
   $ORACLE_HOME/network/admin (Unix)
   %ORACLE_HOME%/Network/Admin or %ORACLE_HOME%/Net80/Admin (Windows)

   Note: User-specific Oracle Net parameters may also reside in
         $HOME/sqlnet.ora file.
         An Oracle Net server installation is also a client.
   
2. Oracle Net tracing and logging can consume vast quantities of disk space.
   Monitor for sufficient disk space when tracing is enabled.
   On some Unix operating systems, /tmp is used for swap space.
   Although generally writable by all users, this is not an ideal location for
   trace/log file generation.

3. Oracle Net tracing should only be enabled for the duration of the issue at
   hand. Oracle Net tracing should always be disabled after problem resolution.

4. Large trace/log files place an overhead on the processes that generate them.
   In the absence of issues, the disabling of tracing and/or logging will
   improve Oracle Net overall efficiency.
   Alternatively, regularly truncating log files will also improve efficiency.

5. Ensure that the target trace/log directory is writable by the connecting
   user, Oracle software owner and/or user that starts the Net Listener.

 WARNING! PLEASE NOTE:
   When SERVER side SQL*Net tracing is enabled, this will trace each and
   every connection that ends with this server as the target. Therefore, to
   turn OFF server-side tracing, simply turning off the trace parameters
   will NOT stop tracing and this can result in severe production
   outages due to enormous trace files. To stop server tracing then,
   the server processes started with tracing turned on need to be
   stopped themselves. This might entail stopping a few processes to
   restarting the entire database, depending on the number of sessions
   involved.


LOG & TRACE PARAMETERS
----------------------

This section provides a detailed description of each trace and log parameter.

  TRACE LEVELS

    TRACE_LEVEL_[CLIENT|SERVER|LISTENER] = [0-16|USER|ADMIN|SUPPORT|OFF]
    Determines the degree to which Oracle Net tracing is provided.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Level 0 is disabled - level 16 is the most verbose tracing level.
    Listener tracing requires the Net Listener to be reloaded or restarted
    after adding trace parameters to LISTENER.ORA.
    Oracle Net (client/server) tracing takes immediate effect after tracing
    parameters are added to SQLNET.ORA.
    By default, the trace level is OFF.
  
    OFF     (equivalent to 0) disabled - provides no tracing.
    USER    (equivalent to 4) traces to identify user-induced error conditions. 
    ADMIN   (equivalent to 6) traces to identify installation-specific problems. 
    SUPPORT (equivalent to 16) trace information required by OSS for 
            troubleshooting.

  TRACE FILE NAME

    TRACE_FILE_[CLIENT|SERVER|LISTENER] = <FILE NAME>
    Determines the trace file name.
    Any valid operating system file name.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Trace file is automatically appended with '.TRC'.
    Default trace file name is SQLNET.TRC, LISTENER.TRC.

  TRACE DIRECTORY

    TRACE_DIRECTORY_[CLIENT|SERVER|LISTENER] = <DIRECTORY>
    Determines the directory in which trace files are written.
    Any valid operating system directory name.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Directory should be writable by the connecting user and/or Oracle software
    owner.
    Default trace directory is $ORACLE_HOME/network/trace.

  UNIQUE TRACE FILES

    TRACE_UNIQUE_[CLIENT|SERVER|LISTENER] = [ON|TRUE|OFF|FALSE]
    Allows generation of unique trace files per connection.
    Trace file names are automatically appended with '_<PID>.TRC'.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Unique tracing is ideal for sporadic issues/errors that occur infrequently
    or randomly.
    Default value is OFF

  TRACE TIMING

    TRACE_TIMESTAMP_[CLIENT|SERVER|LISTENER] = [ON|TRUE|OFF|FALSE]
    A timestamp in the form of [DD-MON-YY 24HH:MI;SS] is recorded against each
    operation traced by the trace file.
    Configuration file is SQLNET.ORA, LISTENER.ORA
    Suitable for hanging or slow connection issues.
    Available from Oracle8i onwards.
    Default value is is OFF.

  MAXIMUM TRACE FILE LENGTH

    TRACE_FILELEN_[CLIENT|SERVER|LISTENER] = <SIZE>
    Determines the maximum trace file size in Kilobytes (Kb).
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Available from Oracle8i onwards.
    Default value is UNLIMITED.

  TRACE FILE CYCLING

    TRACE_FILENO_[CLIENT|SERVER|LISTENER] = <NUMBER>
    Determines the maximum number of trace files through which to perform
    cyclic tracing.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Suitable when disk space is limited or when tracing is required to be
    enabled for long periods.
    Available from Oracle8i onwards.
    Default value is 1 (file).

  LOG FILE NAME

    LOG_FILE_[CLIENT|SERVER|LISTENER] = <FILE NAME>
    Determines the log file name.
    May be any valid operating system file name.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Log file is automatically appended with '.LOG'.
    Default log file name is SQLNET.LOG, LISTENER.LOG.

  LOG DIRECTORY

    LOG_DIRECTORY_[CLIENT|SERVER|LISTENER] = <DIRECTORY NAME>
    Determines the directory in which log files are written.
    Any valid operating system directory name.
    Configuration file is SQLNET.ORA, LISTENER.ORA.
    Directory should be writable by the connecting user or Oracle software
    owner.
    Default directory is $ORACLE_HOME/network/log.
  

  DISABLING LOGGING

    LOGGING_LISTENER = [ON|OFF]
    Disables Listener logging facility.
    Configuration file is LISTENER.ORA.
    Default value is ON.


ORACLE NET TRACE/LOG EXAMPLES
-----------------------------

  CLIENT (SQLNET.ORA)
    trace_level_client = 16
    trace_file_client = cli
    trace_directory_client = /u01/app/oracle/product/9.0.1/network/trace
    trace_unique_client = on
    trace_timestamp_client = on
    trace_filelen_client = 100
    trace_fileno_client = 2
    log_file_client = cli
    log_directory_client = /u01/app/oracle/product/9.0.1/network/log
    tnsping.trace_directory = /u01/app/oracle/product/9.0.1/network/trace
    tnsping.trace_level = admin

  SERVER (SQLNET.ORA)

    trace_level_server = 16
    trace_file_server = svr
    trace_directory_server = /u01/app/oracle/product/9.0.1/network/trace
    trace_unique_server = on
    trace_timestamp_server = on
    trace_filelen_server = 100
    trace_fileno_server = 2
    log_file_server = svr
    log_directory_server = /u01/app/oracle/product/9.0.1/network/log

    namesctl.trace_level = 16
    namesctl.trace_file = namesctl
    namesctl.trace_directory = /u01/app/oracle/product/9.0.1/network/trace
    namesctl.trace_unique = on

  LISTENER (LISTENER.ORA)

    trace_level_listener = 16
    trace_file_listener = listener
    trace_directory_listener = /u01/app/oracle/product/9.0.1/network/trace
    trace_timestamp_listener = on
    trace_filelen_listener = 100
    trace_fileno_listener = 2
    logging_listener = off
    log_directory_listener = /u01/app/oracle/product/9.0.1/network/log
    log_file_listener=listener

  NAMESERVER TRACE (NAMES.ORA)

    names.trace_level = 16
    names.trace_file = names
    names.trace_directory = /u01/app/oracle/product/9.0.1/network/trace
    names.trace_unique = off

  CONNECTION MANAGER TRACE (CMAN.ORA)

    tracing = yes