Lesson 3	Configuring the Oracle log and trace files
Objective	Modify the trace and log file parameters

Configuring Oracle log trace files

The Oracle Net Services log and trace files were controlled by parameters in the parameter files. For an Oracle server, the logging and tracing files are governed by changes to the listener.ora file. For Net8 clients, logging and tracing are controlled by changes to the sqlnet.ora file. Remember that there is a sqlnet.ora file on both the client and on the server.

dilbert > lsnrctl stat  

LSNRCTL for IBM/AIX RISC System/6000: Version 8.0.5.0.0 - Production on 19-APR-9
9 12:50:08

(c) Copyright 1997 Oracle Corporation.  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 8.0.5.0.
0 - Production
Start Date                15-MAR-99 09:01:13
Uptime                    35 days 2 hr. 48 min. 26 sec
Trace Level               off
Security                  ON
SNMP                      ON
Listener Parameter File   /u01/oracle/product/8.0.5/network/admin/listener.ora
Listener Log File         /u01/oracle/product/8.0.5/network/log/listener.log
Services Summary...
  extproc       has 1 service handler(s)
  fred          has 1 service handler(s)
  tom           has 1 service handler(s)
The command completed successfully

Oracle Server

On an Oracle server, the easiest way to see these parameters is with the lsnrctl stat command. Click the View Code button to see an example. Note that the output from this command identifies the location of the listener log file. This is the fastest way to see the location of the log file if you need to reference it to diagnose a connection problem. The only other way to see the location of the listener log files is to look at the parameters in the listener.ora file. All errors encountered in Oracle network products are appended to the listener or SQL*Net log files. As stated previously, the log file provides additional information for an administrator when the error message on the screen is inadequate to understand the failure.
The default log file names are as follows:

Client:

The file named sqlnet.log will write all SQL*Net or Net8 connection information. This file is local to each WINDOWS client.

Server:

Oracle Network Service connections (formerly SQL*Net and Net8 connections) are logged (by default) in a file called sqlnet.log in a directory named$ORACLE_HOME/network/log. The listener messages are written to listener.log; the connection-manager messages are written to cman.log; and Oracle names messages are written to a file called names.log. The connection-manager messages are written to CMAN_PID.TRC and CMADM_PID.TRC (where PID is ID of service).

Note: If you do not set a standard log directory in your sqlnet.log file, Oracle will place multiple sqlnet.log files on your client. By default, Oracle will place sqlnet.log files in the current application directories on your WINDOWS client. For example, a failed Microsoft Access session might be found in c:\access\sqlnet.log, while a failed SQL*Plus session would log to

c:\orant\bin\sqlnet.log.

Make sure that you avoid this problem by setting the log_directory_client sqlnet.ora parameter. The next lesson discusses the Oracle tracing parameters.