Oracle Network Products Troubleshooting Guide
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