Logging
This chapter describes how to control and use the log facility. It explains how to use log information to diagnose and resolve connection problems. In particular, this chapter describes:
Note: Information in this section is generic to all operating system environments. You may require further information from the Oracle operating system-specific documentation for some details of your specific operating environment.
Log files automatically record connection errors for clients, servers, listeners, and Names Servers. Logging cannot be turned off for most components. In addition, the listener log file contains Audit Trail information about all connection requests and the results of Listener Control Utility commands.
Logging for the Connection Manager and pumps and the Navigator is optional; you can turn it on and off through Oracle Network Manager. The default is for logging to be off. Logging for these components includes statistical information. You can also request the Navigator to log more detailed information.
Setting the Log Parameters
Component Configuration Files
Table 2-1 shows the four configuration files that contain the log parameters for Oracle network product components.
Table 2 - 1.
Log Parameters and Component Configuration Files
| Log Parameters Corresponding to
| Configuration File
|
| client
| SQLNET.ORA
|
| server
|
|
| listener
| LISTENER.ORA
|
| Interchange
| INTCHG.ORA
|
| Connection Manager and pumps
|
|
| Navigator
|
|
| Names Server
| NAMES.ORA
|
Although there are operating system specific default names and locations for the log files, you can specify alternative names and locations if you wish. You do so using Oracle Network Manager. Each component has one or more corresponding log parameters that determine the following:
- the log file name (optional)
- the log file directory (optional)
A summary of all log parameters is provided in the tables in Chapter 3.
See the Oracle operating system-specific documentation for your platform to determine where these files are expected to reside and where you can find sample files.
Note: The Connection Manager and Navigator log file location cannot be modified using Oracle Network Manager. Use the default location for your platform.
Format for Log Parameters
When you use Oracle Network Manager to create or edit the log parameters, you do not need to be concerned about syntax. Use Oracle Network Manager to create or edit the log parameters for all components except the server. See Chapter 5 in Oracle Network Manager Administrator's Guide
Note: There is usually a single SQLNET.ORA file for all the clients and servers on a node. That file would contain log parameters for both clients and servers. Generally all clients on a node will have similar logging requirements, as will all servers.
Editing Log Parameters
Use the following format information when you edit log parameters for the server in the SQLNET.ORA file. Use Network Manager or the SQLNET.ORA Editor to change log parameters for other components.
Log parameters are entered in the appropriate component configuration files in the form:
log_parameter_component = value
The component is one of listener name, client, server, Connection Manager, Navigator, or Names Server.
Note: All log parameters except those for the listener are entered with their component's generic name (for example, navigator). Listener log parameters are entered with the name of the specific listener (for example, LISTENER2). However, the server is the only component for which you should configure log parameters manually. All other components are configured using Oracle Network Manager.
Summary of Available Log Parameters
Parameters are available to control the log filename and location for the client, the server, the listener, and the Names Server. The Connection Manager and the Navigator have these parameters, and in addition both have a parameter that controls whether logging is done.
Logging for the MultiProtocol Interchange
You can turn on logging for both the Connection Manager and the Navigator through a single entry in the Interchange property sheet in Oracle Network Manager. The Navigator has a parameter to control logging of optional information for process and audit control. The Connection Manager has a parameter to control the frequency of logging its statistics. Logging for the pumps is included in the log files for the Connection Manager.
Log Filenames
Log files produced by different components have unique names. The default filenames are:
SQLNET.LOG Contains client and/or server information
LISTENER.LOG
Contains listener information
INTCHG.LOG
Contains Connection Manager and pump information
NAVGATR.LOG
Contains Navigator information
NAMES.LOG
Contains Names Server information
You can control the name of the log file. For each component, any valid string can be used to create a log filename. The parameters are of the form:
LOG_FILE_component = string
For example:
LOG_FILE_LISTENER = TEST
This parameter would send listener log output to a file called TEST.LOG on the server machine. On most operating systems, the .log suffix is automatically appended to the log filename. Therefore, do not include a suffix in the log filename string.
Some platforms have restrictions on the properties of a filename. See your Oracle operating system-specific manuals for platform-specific restrictions.
Log File Directories
You can control the destination location of the log file for each component. The parameters are of the form:
LOG_DIRECTORY_component = valid directory
Examples are specific to different operating systems. An example on UNIX might be:
LOG_DIRECTORY_LISTENER = /tmp/log
Some platforms have restrictions on the properties of a directory. See your Oracle operating system-specific manuals for platform-specific restrictions.
Logging for Interchange Components
You can set parameters that determine whether or not log files are produced by the Interchange components, the Connection Manager and pumps, and the Navigator. The Navigator has a parameter to control the amount of information collected into a log file. The Connection Manager has a parameter that controls the frequency of logging. These optional parameters are part of the INTCHG.ORA file.
Turning Logging On
By default, the Interchange components do not produce log files. If you want log files to be created, use the Logging page of the Interchange property sheet in Oracle Network Manager.
A single option, Level, determines whether logging is activated for the Connection Manager and the Navigator. The choices and their consequences are as follows:
OFF No logging for either the Connection Manager or the Navigator. This is the default.
ON
Logging is on for the Connection Manager (and pumps), but not the Navigator.
ERRORS
Logging is on for the Connection Manager (and pumps), and all errors of navigation are logged for the Navigator.
ALL
Logging is on for the Connection Manager (and pumps), and all navigation requests are logged for the Navigator.
Setting Logging Intervals for the Connection Manager
The parameter LOG_INTERVAL_CMANAGER indicates the length of the interval between logs of statistics by the Connection Manager, if logging is turned on for that component. Set this parameter using the Logging page of the Interchange property sheet in Oracle Network Manager.
Logging for Pumps
There are no separate log files generated for pumps. All pump log properties are determined by the corresponding Connection Manager parameters.
Note: See the Oracle Network Manager Administrator's Guide for how to set logging parameters for all the network objects.
Using Log Files
Follow these steps to track an error using a log file:
2. Starting at the bottom, look up to the first non-zero entry in the error report. This is usually the actual cause. In the example![[*]](jump.gif)
, the last non-zero entry is the "ns" error 12560.
3. Look up the first non-zero entry in later chapters of this book for its recommended cause and action. (For example, you would find the "ns" error 12560 under ORA-12560.) To understand the notation used in the error report, see the section, "Sorting Out Networking Error Messages,".
4. If that error does not provide the desired information, move up the error stack to the second to last error and so on.
5. If the cause of the error is still not clear, turn on tracing and re-execute the statement that produced the error message. The use of the trace utility is described in detail in the next chapter. Be sure to turn tracing off after you have re-executed the command.
Audit Trail
The new Audit Trail utility can be valuable to the DBA or anyone responsible for monitoring listener activity. This feature adds a block of text to the listener log file every time a connection is attempted by a client or one of the following commands is issued from the Listener Control Utility:
This feature cannot be turned off.
Format
The Audit Trail formats the block of text into the following fields:
TIMESTAMP*CONNECT DATA[* PROTOCOL INFO]*EVENT [*SID]*RETURN CODE
Each field is delimited by an asterisk (*).
Both PROTOCOL INFO and SID appear only when a connection is attempted. Only four fields are passed in response to a listener control command.
A successful connection or command returns a code of 0. A failure produces a code that maps to an error message.
Example: Reload
Upon a reload request, a typical output to the log file would look like this:
10-MAY-95 14:16:21 *(CONNECT_DATA=(CID=(PROGRAM=)(HOST=roach)(USER=reltest)
(COMMAND=reload)(ARGUMENTS=64)(SERVICE=LISTENER)
(VERSION=36704256))*reload*0
Example: Connection Request
Upon a connection request, a typical output to the log file would look like this:
10-MAY-95 14:16:21*(CONNECT_DATA=(SID=reltest)(CID=
(PROGRAM=C:\ORAWIN\BIN\PLUS31.EXE) (HOST=WINDOWSPC)(USER=CCLOW))*(ADDRESS=(PROTOCOL=tcp)
(HOST=144.25.23.246)(PORT=3366))
*establish*reltest*0
Notice that the user ID is recorded as well as the platform, protocol, and software used to make the connection.
Using Audit Trail Information
You can store Audit Trail information in a table and then collate it into a report format, thereby making it possible to view trends and user activity. Use an import utility like SQL*Loader to import the data into a table. The data is then available for reports and queries. For example, using the information from the Audit Trail, you may choose to allocate network expenses by user connections.
Use a script to load the client connection event information from the listener log file. To use it, you must have a database to accomodate it. Therefore, before running the script, create a table with the following structure:
CREATE TABLE A_TRAIL
(TIMESTAMP VARCHAR2 (20),
CONNECTDATA CHAR (80),
PROTOCOL_INFO VARCHAR2 (60),
EVENT CHAR (15),
SID CHAR (15),
RETURN_CODE NUMBER (2));
The following sample SQL*Loader script is stored as a_trail.ctl. It looks like this:
LOAD DATA
INFILE LISTENER.LOG
APPEND
INTO TABLE a_trail
FIELDS TERMINATED BY "*"
(TIMESTAMP, CONNECTDATA, PROTOCOL_INFO, EVENT, SID, RETURN_CODE)
To run the script, enter:
sqlldr username/password a_trail.ctl
