Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: any script to parse alert log ?!

Re: any script to parse alert log ?!

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Wed, 09 Jul 2003 16:15:57 -0700
Message-ID: <F001.005C41FD.20030709160924@fatcity.com> 





Here is a good one:


create or replace directory bdump as '/oracle/admin/o9i/bdump'
/


grant read on directory bdump to public


/


create table alert_log_ext (


line varchar2(2000) )


organization external


(


type oracle_loader


default directory bdump


access parameters


  (

      records delimited by newline
      nobadfile nologfile nodiscardfile
      fields (line char(132)




  )


)


location('alert_o9i.log') )


reject limit unlimited


/


You can now write a PL/SQL or Java procedure to parse alert_log_ext table
if your heart so desires. There is only one problem: this doesn't work
if you don't have 9iR2.


On 2003.07.09 11:49, George Leonard wrote:
Hi there



Hope this helps.



First the ksh file.



#!/bin/ksh

#

# File          : check_alertlog.ksh

# Created       : 01/02/2002

# Modified      : 04/02/2002

#               : 20/02/2002     Removed the CC for the email

#

# By            : George Leonard ([EMAIL PROTECTED])

#                                          ([EMAIL PROTECTED])


#

# Description   : Script checks last 200 lines of

#               : the alert log for specific

#               : Oracle errors, then pages or e-mails depending #on the

error.


#

# Parameter     : ORACLE_SID#

#

# Format for    : 0,5,10,15,20,25,30,35,40,45,50,55 * * * *

/export/home/oracle/oracle/scripts/check_alertlog.ksh $ORACLE_SID


# crontab entry

#

# Dependencies  : paternfile (File with ORA errors to check for)

#

# Make sure the DS tag has been set in /etc/mail/sendmail, if the

LEMAIL=1



export ORACLE_SID=$1;


export DIR=/export/home/oracle/scripts


export ORACLE_HOME=/oracle/app/oracle/product/8.1.6
export ALERT_DEST=/oracle/admin/$ORACLE_SID/bdump
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LEMAIL=1


export [EMAIL PROTECTED]


COLLECTOR=`tail -20 $ALERT_DEST/alert_$ORACLE_SID.log
|/usr/xpg4/bin/grep -f $DIR/paternfile"`
echo $ORACLE_SID: Alert Log last Checked at `date '+%m/%d/%y %H:%M'` >
$DIR/lastran_$ORACLE_SID.log


for i in $COLLECTOR


do


   ORAERR=`echo $i | grep ORA-...`


   if [ $ORAERR ]


   then

      i=`echo $i | awk '{print $1}'`
      y=`cat $DIR/tmpalert$ORACLE_SID.log | egrep -ch $i`
      if [ $y -ge 2 ]
      then
         continue
      else
         echo $i Paged at `date '+%m/%d/%y %H:%M'` >>
$DIR/tmpalert$ORACLE_SID.log
         echo "Oracle errors in alert log on $ORACLE_SID"
         if [ $LEMAIL ]
         then
            echo "Oracle errors in alert log on $ORACLE_SID" |
/usr/bin/mailx -s "check alert_$ORACLE_SID.log" $EMAIL
         fi;
      fi;




   fi;


done


Now the patternfile referred to


ORA-255
ORA-255:
ORA-0255
ORA-214
ORA-214:
ORA-00214
ORA-270
ORA-270:
ORA-00270
ORA-272
ORA-272:
ORA-00272
ORA-600
ORA-600:
ORA-00600
ORA-1122
ORA-1122:
ORA-01122
ORA-1578




ORA1578:


ORA-01578
ORA-1628
ORA-1628:
ORA-01628
ORA-1630
ORA-1630:
ORA-01630




ORA1631


ORA-1631:
ORA-01631
ORA-1632
ORA-1632:
ORA-01632
ORA-1650
ORA-1650:




ORA01650


ORA-1652
ORA-1652:
ORA-01652
ORA-1653
ORA-1653:
ORA-01653
ORA-1654




ORA1654:


ORA-01654
ORA-1655
ORA-1655:
ORA-01655






George




George Leonard


Oracle Database Administrator


Dimension Data (Pty) Ltd


(Reg. No. 1987/006597/07)


Cell: (+27) 82 655 2466


Tel: (+27 11) 575 0573


Fax: (+27 11) 576 0573


E-mail:[EMAIL PROTECTED]


Web:   http://www.didata.co.za


You Have The Obligation to Inform One Honestly of the risk, And As a
Person


You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
Kill or Injure Themselves as They See Fit!
-----Original Message-----


Sent: 07 July 2003 11:39 AM


To: Multiple recipients of list ORACLE-L
Guys,



I need a parse alert log and mail me if there is any error.
Hope u guys would have some script for the same.
i have 8i and 9i instances spread across windows 2000 network.



even if u guys have script for unix,just pass it on to me.
let me modify and try it.


Thanks and Regards,


Jp.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
  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).


This email and all contents are subject to the following disclaimer:

"http://www.didata.com/disclaimer.asp"

begin 666 check_alertlog.ksh
M(R$O8FEN+VMS: HC"B,@1FEL92 @(" @(" @(" Z(&-H96-K7V%L97)T;&]G
M+FMS: HC($-R96%T960@(" @(" @.B P,2\P,B\R,# R"B,@36]D:69I960@
M(" @(" Z(# T+S R+S(P,#(*(R @(" @(" @(" @(" @(#H@,C O,#(O,C P
M,B @(" @4F5M;W9E9"!T:&[EMAIL PROTECTED],@9F]R('1H92!E;6%I; HC"B,@0GD@(" @
M(" @(" @(" Z($=E;W)G92!,96]N87)D("AG96]R9V4N;&5O;F%R9$!Z82YD
M:61A=&$N8V]M*0HC"B,@1&5S8W)I<'1I;VX@(" Z(%-C<FEP="!C:&5C:W,@
M;&%S=" R,# @;&EN97,@;V8*(R @(" @(" @(" @(" @([EMAIL PROTECTED]&AE(&%L97)T
M(&QO9R!F;W(@<W!E8VEF:6,*(R @(" @(" @(" @(" @([EMAIL PROTECTED])A8VQE(&5R
M<F]R<[EMAIL PROTECTED]&AE;B!P86=E<R!O<B!E+6UA:6QS(&1E<&5N9&EN9R C;[EMAIL PROTECTED]&AE
M(&5R<[EMAIL PROTECTED]"B,@4&%R86UE=&5R(" @(" Z($]204-,15]3240C"B,*(R!&
M;W)[EMAIL PROTECTED](" @(#H@,"PU+#$P+#$U+#(P+#(U+#,P+#,U+#0P+#0U+#4P
M+#4U("[EMAIL PROTECTED] J("[EMAIL PROTECTED]<&]R="]H;VUE+V]R86-L92]O<F%C;&4O<V-R:7!T
M<R]C:&5C:U]A;&5R=&QO9RYK<V@@)$]204-,15]3240*(R!C<F]N=&%B(&5N
M=')Y"B,*(R!$97!E;F1E;F-I97,@(#H@<&%T97)N9FEL92 H1FEL92!W:71H
M($]202!E<G)O<G,@=&[EMAIL PROTECTED]@9F]R*0HC"B,@36%K92!S=7)E('1H92!$
M4R!T86<@:&%S(&)E96X@<V5T(&EN("]E=&,O;6%I;"]S96YD;6%I;"P@:68@
M=&AE($Q%34%)3#TQ"@H*97AP;W)T($]204-,15]3240])#$["F5X<&]R="!$
M25(]+V5X<&]R="]H;VUE+V]R86-L92]S8W)I<'1S"F5X<&]R="!/4D%#3$5?
M2$]-13TO;W)A8VQE+V%P<"]O<F%C;&4O<')O9'5C="[EMAIL PROTECTED]>'!O<G0@
M04Q%4E1?1$535#TO;W)A8VQE+V%D;6EN+R1/4D%#3$5?4TE$+V)D=6UP"F5X
M<&]R="!,1%],24)205)[EMAIL PROTECTED])$]204-,15](3TU%+VQI8CHD3$1?3$E"
M4D%265]0051("F5X<&]R="!,14U!24P],0IE>'!O<[EMAIL PROTECTED];&5O;F%R
M9&= 9F%R;F5L;&-O;7!O;F5N=',N8V\N=6L*"D-/3$Q%0U1/[EMAIL PROTECTED]&%I;" M
M,C @)$%,15)47T1%4U0O86QE<G1?)$]204-,15]3240N;&]G('PO=7-R+WAP
M9S0O8FEN+V=R97 @+68@)$1)4B]P871E<FYF:6QE(F *96-H;R D3U)!0TQ%
M7U-)[EMAIL PROTECTED]<[EMAIL PROTECTED]&]G(&QA<[EMAIL PROTECTED]"!A="[EMAIL 
PROTECTED]&%T92 G*R5M+R5D
M+R5Y("5(.B5-)V @/B D1$E2+VQA<W1R86Y?)$]204-,15]3240N;&]G"F9O
M<B!I(&EN("1#3TQ,14-43U(*9&\*(" @3U)!15)2/6!E8VAO("1I('[EMAIL PROTECTED])E
M<"!/4D$M+BXN8 H@("!I9B!;("1/4D%%4E(@70H@("!T:&5N"B @(" @(&D]
M8&5C:&\@)&[EMAIL PROTECTED]"!A=VL@)WMP<FEN=" D,7TG8 H@(" @("!Y/6!C870@)$1)
M4B]T;7!A;&5R="1/4D%#3$5?4TE$+FQO9R!\(&5G<F5P("UC:" D:6 *(" @
M(" @:[EMAIL PROTECTED] D>2 M9V4@,B!="B @(" @('1H96X*(" @(" @(" @8V]N=&EN
M=64*(" @(" @96QS90H@(" @(" @("!E8VAO("1I(%!A9V5D(&%T(&!D871E
M("<K)6TO)60O)7D@)[EMAIL PROTECTED])4TG8" ^/B D1$E2+W1M<&%L97)T)$]204-,15]3
M240N;&]G"B @(" @(" @(&5C:&\@(D]R86-L92!E<G)O<G,@:[EMAIL PROTECTED]<G0@
M;&]G(&]N("1/4D%#3$5?4TE$(@H@(" @(" @("!I9B!;("1,[EMAIL PROTECTED]@
M(" @(" @("!T:&5N"B @(" @(" @(" @(&5C:&\@(D]R86-L92!E<G)O<G,@
M:[EMAIL PROTECTED]<G0@;&]G(&]N("1/4D%#3$5?4TE$(B!\("]U<W(O8FEN+VUA:6QX
M("US(")C:&5C:R!A;&5R=%\D3U)!0TQ%7U-)1"YL;V<B("1%34%)3 H@(" @
?(" @("!F:3L*(" @(" @9FD["B @(&9I.PID;VYE"@``
`
end
begin 666 paternfile.dat
M3U)!+3(U-0I/4D$M,[EMAIL PROTECTED]/4D$M,#(U-0I/4D$M,C$T"D]202TR,30Z"D]2
M02TP,#(Q- I/4D$M,C<P"D]202TR-S Z"D]202TP,#(W, I/4D$M,C<R"D]2
M02TR-S(Z"D]202TP,#(W,@I/4D$M-C P"D]202TV,# Z"D]202TP,#8P, I/
M4D$M,3$R,@I/4D$M,3$R,CH*3U)!+3 Q,3(R"D]202TQ-3<X"[EMAIL PROTECTED]
M"D]202TP,34W. I/4D$M,38R. I/4D$M,38R.#H*3U)!+3 Q-C(X"D]202TQ
M-C,P"D]202TQ-C,[EMAIL PROTECTED]/4D$M,#$V,S *3U)!,38S,0I/4D$M,38S,3H*3U)!
M+3 Q-C,Q"D]202TQ-C,R"D]202TQ-C,[EMAIL PROTECTED]/4D$M,#$V,S(*3U)!+3$V-3 *
M3U)!+3$V-3 Z"D]203 Q-C4P"D]202TQ-C4R"[EMAIL PROTECTED]/4D$M,#$V
M-3(*3U)!+3$V-3,*3U)!+3$V-3,Z"D]202TP,38U,PI/4D$M,38U- I/4D$Q
[EMAIL PROTECTED]/4D$M,#$V-30*3U)!+3$V-34*3U)!+3$V-34Z"D]202TP,38U-0H`
`
end
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: George Leonard
  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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 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).


Received on Wed Jul 09 2003 - 18:15:57 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US