Great idea, Lisa! I may not have much to give back to the List for all that
It's done for me, but this little ditty I just threw together might help
someone:
I've been struggling with grep, awk, and sed on the listener.log file to
help me get an idea of how we're hitting our production DBs. Without much
luck (not very good w/awk), a dim 10-watt bulb switched on when I thought of
dumping the pertinent parts of the log into a DB so we can SQL some reports
off of them. Since I rotate our listener.log daily to prevent them from
growing too large (~10K attaches/day), a simple table and accompanying
SQL*Load script ought to do the job.
First, the simple table, indexes, and a trigger:
CREATE TABLE LISTENER_LOG
(
TIMESTAMP DATE,
SIDNAME VARCHAR2(10),
PROGRAM VARCHAR2(80),
HOSTNAME VARCHAR2(32),
OSUSER VARCHAR2(32),
IPADDR VARCHAR2(15)
);
CREATE INDEX LISTENER_LOG_HOSTNAME ON LISTENER_LOG
(HOSTNAME);
CREATE INDEX LISTENER_LOG_OSUSER ON LISTENER_LOG
(OSUSER);
CREATE INDEX LISTENER_LOG_PROGRAM ON LISTENER_LOG
(PROGRAM);
REM Trigger used to remove Windohs directory specs from program name.
CREATE OR REPLACE TRIGGER LISTENER_LOG_I
BEFORE INSERT ON LISTENER_LOG
FOR EACH ROW
DECLARE
BEGIN
:NEW.program :=
LOWER(SUBSTR(:NEW.program,INSTR(:NEW.program,'\',-1)+1));
END listener_log_i;
/
SHOW ERRORS;
Obviously, the above can be modified for size, tablespace, etc. Next, we
need a SQL*Loader control file:
load data
infile '$ORACLE_HOME/network/log/listener.log'
badfile 'listener.err'
append
into table listener_log
when (43) = 'P'
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 ")"
)
I used the "WHEN" clause to only pickup those rows in the listener.log file
that actually log a connection to our production DB. For this example, our
production DB might be "PROD". The placement of the "(SID=PROD)" section
will vary depending on how the service is defined in Oracle Networking (e.g.
TNSNAMES.ORA, ONAMES, LDAP). Ours happens to start at column 43. Modify
the "WHEN" clause according to your own listener.log. Additionally, with 8i
(I think) there could be a "(SRVR=DEDICATED)" or "(SERVER=DEDICATED)" block
-- unless you're using MTS (we're not). For this, I needed to change
"skip2" to read:
skip2 FILLER char enclosed by "(SRVR=" and "=",
Finally, we need to call SQL*Loader (for me this is on 8.1.7 on HP/UX):
sqlldr MYSCHEMANAME control=listener.sqlload log=listener_sqlload.log
direct=false rows=2000 bindsize=10000000 readsize=10000000
...where MYSCHEMANAME is the same user that owns the LISTENER_LOG table.
Expect many many "Discarded - failed all WHEN clauses" in the
listener_sqlload.log file.
That's it! Afterwards, you can have all sorts of Holiday fun whilst
querying your newly poplulated table. When we get to 9i, I'm thinking that
an external table and a CTAS or other load script might do the trick, too.
Ho-Ho-Hope this can help someone! Merry Christmas! Happy New Year! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-----Original Message-----
Sent: Thursday, December 19, 2002 11:30 AM
To: Multiple recipients of list ORACLE-L
Hello everyone -
I've been trying to write more Perl lately and after showing my Perl mentor
some of the stuff I had written over the last couple of weeks the first
thing he said was "why don't you give back to the community".
So I figure if I offer up my latest creation I could 1. give back and maybe
help a person or two and 2. receive comments on my coding. My coding style
is rather simplistic and I would appreciate any comments about what I'm
doing wrong/how something could be done differently (TMTOWTDI), etc. My
environment is W2K sp2/8.1.7.2.
I slapped together a script that fires backup controlfile to trace
(including the stored proc) and ftp's it off to another server. If anyone
is interested in seeing it please email me directly.
Lisa Koivu
Oracle Diaper Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
