Oracle-L: listener.log analysis

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Tue, 29 Apr 2003 14:26:39 -0800
Message-ID: <F001.0058C49C.20030429142639@fatcity.com>

Hey all,

We need to change our users' SQLNET.ORA files, so I was asked to pick 10 guinea pigs. I figured I'd ask the most active users since they are more likely to encounter problems if any should arise. Since there are multiple DBs to connect to on the primary DB server, I thought I'd turn to listener.log.

The only prewritten util I've found to analyze the listener.log was an awk script posted here back in November '02, but I had problems running it on HP/UX 11.0 (sorry, John H.!), so I turned to the database. Since we're on 8.1.7.4, I don't have the chance to use external files, and I didn't want to UTL_FILE it since we rotate our listener logs daily and I also didn't want to allow devs access (via UTL_FILE_DIR init.ora parameter and an instance bounce) to accidentally write to that directory and fill our $ORACLE_HOME partition. So, I tried SQueaL*Loader. Here's what I came up with:

The DB table:

CREATE TABLE LISTENER_LOG
(
TIMESTAMP DATE,

  SIDNAME    VARCHAR2(64),
  PROGRAM    VARCHAR2(64),
  HOSTNAME   VARCHAR2(64),
  OSUSER     VARCHAR2(64),
  IPADDR     VARCHAR2(64)

)

The SQueaL*Loader file:

load data
infile '/tmp/listener.log'
badfile 'listener.err'
append
into table listener_log
when (43) = 'Q'
trailing nullcols

        (timestamp position(1:20) date "DD-MON-YYYY HH24:MI:SS",
        skip1 FILLER char terminated by '=',
        sidname enclosed by "(SID=" and ")",
        skip2 FILLER char terminated by "=",
        program enclosed by "(PROGRAM=" and ")",
        hostname enclosed by "(HOST=" and ")",
        osuser enclosed by "(USER=" and ")))",
        skip3 FILLER char terminated by '=',
        skip4 FILLER char terminated by '=',
        skip5 FILLER char terminated by ')',
        ipaddr enclosed by "(HOST=" and ")"
        )

into table listener_log
when (43) = 'M'
trailing nullcols

        (timestamp position(1:20) date "DD-MON-YYYY HH24:MI:SS",
        skip1 FILLER char terminated by '=',
        sidname enclosed by "(SID=" and ")",
        skip2 FILLER char terminated by "=",
        program enclosed by "(PROGRAM=" and ")",
        hostname enclosed by "(HOST=" and ")",
        osuser enclosed by "(USER=" and ")))",
        skip3 FILLER char terminated by '=',
        skip4 FILLER char terminated by '=',
        skip5 FILLER char terminated by ')',
        ipaddr enclosed by "(HOST=" and ")"
        )

into table listener_log
when (43) = 'E'
trailing nullcols

        (timestamp position(1:20) date "DD-MON-YYYY HH24:MI:SS",
        skip1 FILLER char terminated by '=',
        sidname enclosed by "(SID=" and ")",
        skip2 FILLER char terminated by "(CID=",
        program enclosed by "(PROGRAM=" and ")",
        hostname enclosed by "(HOST=" and ")",
        osuser enclosed by "(USER=" and ")))",
        skip3 FILLER char terminated by '=',
        skip4 FILLER char terminated by '=',
        skip5 FILLER char terminated by ')',
        ipaddr enclosed by "(HOST=" and ")"
        )



--------------------

The Par file:

control=listener.sqlload
log=listener.logs
direct=false
errors=10
rows=2000
bindsize=10000000
readsize=10000000

And finally, the O/S commands (unix-y):

cat $ORACLE_HOME/network/log/list* >/tmp/listener.log sqlldr parfile=listener.par
rm /tmp/listener.log

A few notes:

Since we rotate our logs daily, I append all of the listener logs to a single file in /tmp. (see the O/S commands)
The reason there are three entries in the SQueaL*Loader file is because this server listens for connections to three DBs. And there are different configs for each, depending on need (note the "(CID=" in the skip2 FILLER on the 'E' entry). I also want to skip over all of the other messages in the files. You'll need to customize this for your own installation.
You may also want to redo the parameter file to suit your needs. Hell, if folks can improve on it, I'm all ears. I'm no SQueaL*Loader expert by any means. This happens to work well for us.

After all's loaded, you can run fun SQLs like:

select osuser,hostname,count(*) "SESSIONS" from listener_log
having count(*) > 130
group by osuser, hostname;

Index to taste, garnish with an analyze. Serves many.

Enjoy! If you make improvements (there's lots of room for it!), I'd appreciate a post!

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Apr 29 2003 - 17:26:39 CDT