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).
