Lesson 4	Oracle tracing parameters
Objective	Describe functions of the Oracle trace parameters.

Oracle Tracing Parameters

As you may recall, tracing can be used to examine and diagnose application connections across the network. The trace facility allows you to obtain more information on the internal operations of the components of an Oracle application network than is provided in a standard sqlnet.log file. A Net8 trace provides a detailed sequence of statements that describe every connection event as it is executed. All trace output is directed to trace output files that can be evaluated to identify the event that led to an error. Default trace files are:

Client traces kept in sqlnet.trc

Client traces are kept in the sqlnet.trc file in the directory named by the trace_directory_client sqlnet.ora parameter. If you have set the trace_unique_client parameters in your sqlnet.ora file, you will have a sqlnet_nnn_.trc file (where nnn is the thread number).
The default server trace file directory is $ORACLE_HOME/network/log. Here you will see the listener.trc file for listener traces, cman_PID.trc for connection manager traces, names_nnn_.trc files (where nnn is the thread number) for Oracle Names traces, and general server traces as SVR_PID.trc.

When a WINDOWS client application establishes the first Net8 connection, the Net8 parameters are read from the WINDOWS sqlnet.ora file (usually in c:\orant\network\admin\sqlnet.ora). To change client trace parameters, you must change our local sqlnet.ora file, exit the client application and then re-start the client application. If the same client application establishes some more Network Services connections, the sqlnet.ora is not re-read and any changes that were made to the sqlnet.ora file after the first Net8 connection will not be used.
Now that you understand the trace and log file locations, let us look at how tracing and logging are enabled within Oracle.

Standard Oracle log and trace files

SQL > show parameters dump
NAME                                TYPE    VALUE
----------------------------------- ------- ------------------------------
background_core_dump                string  full                          
background_dump_dest                string  ?/rdbms/log
core_dump_dest                      string  ?/dbs
max_dump_file_size                  string  10240                         
shadow_core_dump                    string  full                          
user_dump_dest                      string  ?/rdbms/log

Trace and dump Files

The location of Oracle's database trace and dump files are determined by parameters within the init.ora file. The easiest way to know the location of Oracle’s trace and dump files is to use the server manager utility. As you can see by clicking the View Code button at left, server manager can be used to display all of the dump parameters. These same parameters are used in the init.ora file to define the log and trace options.

dilbert>cd $ORACLE_HOME/rdbms/log
dilbert> ls
alert_fred.log
reco_34326.trc
smon_57560.trc
snp1_54580.trc
ora_58718.trc
dbwr_38406.trc
lgwr_36106.trc
pmon_52070.trc

Note that the parameters that are suffixed with dest tell us where the files are located. In the example above you see that the background_dump_dest and the user_dump_dest are both set to $ORACLE_HOME/rdbms/log. If you go to this directory, you will find a host of different alert and trace files:
Also note that the Oracle alert log is located in the background_dump_dest directory. Here you see the main log for the fred database, called alert_fred.log. The alert log will contain all relevant Oracle messages, including startup and shutdown messages, as well as redo log switches and serious errors.
For each trace file in the directory, you see that they are prefixed by the main process that spawned the error. You see traces for the process monitor process (pmon), the Recoverer process (reco), the log writer (lgwr), the database writer (dbwr), and the system monitor process (smon). Generic Oracle trace files are always prefixed with “ora” and end with "trc".

listener.ora Trace Parameters

Table 7-4 describes the trace parameters settings for the listener that can be set in the listener.ora file.
Table 7-4 listener.ora Trace Parameters

listener.ora Parameter	OEM Cloud Control/Net Manager Field	Description
TRACE_LEVEL_listener_name	Select a trace level/Trace Level	The level of detail the trace facility records for the listener. The trace level value can either be a value within the range of 0 (zero) to 16 where 0 is no tracing and 16 represents the maximum amount of tracing or one of the following values: off (equivalent to 0) provides no tracing. user (equivalent to 4) traces to identify userinduced error conditions. admin (equivalent to 6) traces to identify installation-specific problems. support (equivalent to 16) provides trace information for troubleshooting by Oracle Support Services.
TRACE_DIRECTORY_listener_name TRACE_FILE_listener_name	Trace File	The destination directory and file for the trace file. By default, the directory is ORACLE_HOME/network/trace, and the file name is listener.trc
TRACE_FILEAGE_listener_name	You must set this parameter manually.	The maximum age of listener trace files in minutes. When the age limit is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_listener_name parameter.
TRACE_FILEAGE_SERVER	You must set this parameter manually.	Specifies the maximum age of server trace files in minutes. When the age limit is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_SERVER parameter.
TRACE_FILELEN_listener_name	You must set this parameter manually.	The size of the listener trace files in KB. When the size is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_listener_name parameter
TRACE_FILENO_listener_name	You must set this parameter manually.	The number of trace files for listener tracing. When this parameter is set along with the TRACE_FILELEN_listener_name parameter, trace files are used in a cyclical fashion. The first file is filled, then the second file, and so on. When the last file has been filled, the first file is re-used, and so on. The trace file names are distinguished from one another by their sequence number. For example, if the default trace file of listener.trc is used, and this parameter is set to 3, then the trace files would be named listener1.trc, listener2.trc and listener3.trc. In addition, trace events in the trace files are preceded by the sequence number of the file. When this parameter is set with the TRACE_FILEAGE_listener_name parameter, trace files are cycled based on the age of the trace file. The first file is used until the age limit is reached, then the second file is use, etc.. When the last file's age limit is reached, the first file is re-used. When this parameter is set with both the TRACE_FILELEN_listener_name and TRACE_FILEAGE_listener_name parameters, trace files are cycled when either the size limit or the age limit is reached.
TRACE_TIMESTAMP_listener_name	You must set this parameter manually.	A time stamp in the form of dd-mon-yyyy hh:mi:ss:mil for every trace event in the listener trace file.

Log Trace Files - Quiz

Before moving on to the next lesson, click the Quiz link below to test your mastery of the basic concepts surrounding logging and tracing.
Log Trace Files - Quiz