RE: listener.log analysis

Guys:

Just to clarify, you CAN select from a flat file in 8.1.7.4. If I can find the URL of the article on how to do it I'll post it up to the list.

Cheers,
Mike

-----Original Message-----
Sent: Tuesday, April 29, 2003 6:02 PM
To: Multiple recipients of list ORACLE-L

Rich,

This would really be helpful to me. Because I was also working on installing a new version of a garment design apps's oracle client on pcs and wanted to find when users login, from which machine, etc.

Would post it if I need improve it :)

Thanks
Mandar

> -----Original Message-----
> From: Jesse, Rich [mailto:Rich.Jesse_at_qtiworld.com]
> Sent: Tuesday, April 29, 2003 3:27 PM
> To: Multiple recipients of list ORACLE-L
> Subject: listener.log analysis
>
>
> 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:
>
> 1) Since we rotate our logs daily, I append all of the
> listener logs to a single file in /tmp. (see the O/S commands)
> 2) 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.
> 3) 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vergara, Michael (TEM)
  INET: mvergara_at_guidant.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).