Re: perl/shell script for alert log
check http://www.dba-village.com/dba/village/dvp_scripts.ScriptDetails?ScrId=993 - Original Message - From: AK To: Multiple recipients of list ORACLE-L Sent: Wednesday, August 27, 2003 10:44 PM Subject: perl/shell script for alert log I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak
perl/shell script for alert log
I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak
Re: perl/shell script for alert log
HTH #!/bin/sh # This Script search for Oracle error messages in last 100 lines in the alert log file , # keep log to a file. # You should pass name of ORACLE_SID as a parameter. #!/usr/bin/sh # # Comments: Script checks last 100 lines of # the alert log for specific # Oracle errors, e-mails depending on the error. # Parameter: ORACLE_SID # --- # # DIR=/u01/app/oracle/admin ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/8.1.7 export ORACLE_HOME ALERT_DEST=/u01/app/oracle/admin/${ORACLE_SID}/bdump LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH PATH=$PATH:$ORACLE_HOME/bin export PATH COLLECTOR=`tail -100 $ALERT_DEST/alert_${ORACLE_SID}.log |grep ORA-` if [ $COLLECTOR ] then echo echo Errors found in: $ALERT_DEST/alert_${ORACLE_SID}.log echo echo $COLLECTOR echo fi; --- AK [EMAIL PROTECTED] wrote: I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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).
RE: perl/shell script for alert log
http://www.oracledba.co.uk/ Check out the alert log script in the admin section. Doesn't repeatedly check the same segment of the alert log file. - Ethan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, August 27, 2003 12:14 PMTo: Multiple recipients of list ORACLE-LSubject: perl/shell script for alert log I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak
RE: perl/shell script for alert log
AK - Go to Google and search on oracle alert log monitor script. This will give you an array to select from. What you want will depend to a large extent on your requirements: - Something you can easily modify? - What platforms should it run on? - Email you alerts? - Which errors do you wish to be alerted to? - Do you want to be able to modify the list of errors? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 27, 2003 12:14 PM To: Multiple recipients of list ORACLE-L I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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).
RE: perl/shell script for alert log
When I grep something from the alert log, it never tell me the date and time of the error. Is there a setting for appending a timestamp on each error? -Original Message- Sent: Wednesday, August 27, 2003 1:35 PM To: Multiple recipients of list ORACLE-L HTH #!/bin/sh # This Script search for Oracle error messages in last 100 lines in the alert log file , # keep log to a file. # You should pass name of ORACLE_SID as a parameter. #!/usr/bin/sh # # Comments: Script checks last 100 lines of # the alert log for specific # Oracle errors, e-mails depending on the error. # Parameter: ORACLE_SID # --- # # DIR=/u01/app/oracle/admin ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/8.1.7 export ORACLE_HOME ALERT_DEST=/u01/app/oracle/admin/${ORACLE_SID}/bdump LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH PATH=$PATH:$ORACLE_HOME/bin export PATH COLLECTOR=`tail -100 $ALERT_DEST/alert_${ORACLE_SID}.log |grep ORA-` if [ $COLLECTOR ] then echo echo Errors found in: $ALERT_DEST/alert_${ORACLE_SID}.log echo echo $COLLECTOR echo fi; --- AK [EMAIL PROTECTED] wrote: I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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). For technical support please email [EMAIL PROTECTED] or you can call (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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).
RE: perl/shell script for alert log
If you use Connor's script you can modify it to send you the entire chunk of file it is currently checking in the body of the email. Ideally you are checking at a frequent interval so the time of the alert is usually about the same time you get the error message. One enhancement I suggest to every script is to configure the a SERVERERROR trigger to throw certain errors out to the alert log. ORA-1555 is one that will show up at the session level but not at the database level. Out of TEMP space is another that is frequent at the session level but not the database level. This way you know who is causing some important errors. Overtime I find more and more session errors that are really critical database errors. Try DDL on a table with an unusable index. Pretty big deal on most production databases but this is a session error and would not typically show up in the alert log. The risk of course is some huge loop throwing 1000's of lines into the alert log. I would suggest a governor of some sort in your servererror trigger. - Ethan -Original Message- Sent: Wednesday, August 27, 2003 2:49 PM To: Multiple recipients of list ORACLE-L When I grep something from the alert log, it never tell me the date and time of the error. Is there a setting for appending a timestamp on each error? -Original Message- Sent: Wednesday, August 27, 2003 1:35 PM To: Multiple recipients of list ORACLE-L HTH #!/bin/sh # This Script search for Oracle error messages in last 100 lines in the alert log file , # keep log to a file. # You should pass name of ORACLE_SID as a parameter. #!/usr/bin/sh # # Comments: Script checks last 100 lines of # the alert log for specific # Oracle errors, e-mails depending on the error. # Parameter: ORACLE_SID # --- # # DIR=/u01/app/oracle/admin ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/8.1.7 export ORACLE_HOME ALERT_DEST=/u01/app/oracle/admin/${ORACLE_SID}/bdump LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH PATH=$PATH:$ORACLE_HOME/bin export PATH COLLECTOR=`tail -100 $ALERT_DEST/alert_${ORACLE_SID}.log |grep ORA-` if [ $COLLECTOR ] then echo echo Errors found in: $ALERT_DEST/alert_${ORACLE_SID}.log echo echo $COLLECTOR echo fi; --- AK [EMAIL PROTECTED] wrote: I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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). For technical support please email [EMAIL PROTECTED] or you can call (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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).
RE: perl/shell script for alert log
Welcome back Ethan! An alternative is using the following lines in init.ora: event=1555 trace name errorstack level 3 event=4031 trace name errorstack level 3 event=1652 trace name processstate level 10 This catches the dreaded 01555, out of TEMP, and shared pool allocation errors *along* with the SQL/Stack from the offending process (and thus time of occurrence). And keep *all* the event lines together ;-) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Wednesday, August 27, 2003 1:14 PM To: Multiple recipients of list ORACLE-L If you use Connor's script you can modify it to send you the entire chunk of file it is currently checking in the body of the email. Ideally you are checking at a frequent interval so the time of the alert is usually about the same time you get the error message. One enhancement I suggest to every script is to configure the a SERVERERROR trigger to throw certain errors out to the alert log. ORA-1555 is one that will show up at the session level but not at the database level. Out of TEMP space is another that is frequent at the session level but not the database level. This way you know who is causing some important errors. Overtime I find more and more session errors that are really critical database errors. Try DDL on a table with an unusable index. Pretty big deal on most production databases but this is a session error and would not typically show up in the alert log. The risk of course is some huge loop throwing 1000's of lines into the alert log. I would suggest a governor of some sort in your servererror trigger. - Ethan -Original Message- Sent: Wednesday, August 27, 2003 2:49 PM To: Multiple recipients of list ORACLE-L When I grep something from the alert log, it never tell me the date and time of the error. Is there a setting for appending a timestamp on each error? -Original Message- Sent: Wednesday, August 27, 2003 1:35 PM To: Multiple recipients of list ORACLE-L HTH #!/bin/sh # This Script search for Oracle error messages in last 100 lines in the alert log file , # keep log to a file. # You should pass name of ORACLE_SID as a parameter. #!/usr/bin/sh # # Comments: Script checks last 100 lines of # the alert log for specific # Oracle errors, e-mails depending on the error. # Parameter: ORACLE_SID # --- # # DIR=/u01/app/oracle/admin ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/8.1.7 export ORACLE_HOME ALERT_DEST=/u01/app/oracle/admin/${ORACLE_SID}/bdump LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH PATH=$PATH:$ORACLE_HOME/bin export PATH COLLECTOR=`tail -100 $ALERT_DEST/alert_${ORACLE_SID}.log |grep ORA-` if [ $COLLECTOR ] then echo echo Errors found in: $ALERT_DEST/alert_${ORACLE_SID}.log echo echo $COLLECTOR echo fi; --- AK [EMAIL PROTECTED] wrote: I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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). For technical support please email [EMAIL PROTECTED] or you can call (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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
Re: perl/shell script for alert log
Hi! I would add a check for Checkpoint not complete in alert just in case as well. Tanel. One enhancement I suggest to every script is to configure the a SERVERERROR trigger to throw certain errors out to the alert log. ORA-1555 is one that will show up at the session level but not at the database level. Out of TEMP space is another that is frequent at the session level but not the database level. This way you know who is causing some important errors. Overtime I find more and more session errors that are really critical database errors. Try DDL on a table with an unusable index. Pretty big deal on most production databases but this is a session error and would not typically show up in the alert log. The risk of course is some huge loop throwing 1000's of lines into the alert log. I would suggest a governor of some sort in your servererror trigger. - Ethan -Original Message- Sent: Wednesday, August 27, 2003 2:49 PM To: Multiple recipients of list ORACLE-L When I grep something from the alert log, it never tell me the date and time of the error. Is there a setting for appending a timestamp on each error? -Original Message- Sent: Wednesday, August 27, 2003 1:35 PM To: Multiple recipients of list ORACLE-L HTH #!/bin/sh # This Script search for Oracle error messages in last 100 lines in the alert log file , # keep log to a file. # You should pass name of ORACLE_SID as a parameter. #!/usr/bin/sh # # Comments: Script checks last 100 lines of # the alert log for specific # Oracle errors, e-mails depending on the error. # Parameter: ORACLE_SID # --- # # DIR=/u01/app/oracle/admin ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/8.1.7 export ORACLE_HOME ALERT_DEST=/u01/app/oracle/admin/${ORACLE_SID}/bdump LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH PATH=$PATH:$ORACLE_HOME/bin export PATH COLLECTOR=`tail -100 $ALERT_DEST/alert_${ORACLE_SID}.log |grep ORA-` if [ $COLLECTOR ] then echo echo Errors found in: $ALERT_DEST/alert_${ORACLE_SID}.log echo echo $COLLECTOR echo fi; --- AK [EMAIL PROTECTED] wrote: I am sure you guys might have some nice perl/shell script to analyze alert log for errors or potential problem . Can you share it with me /list . Ohh thanks in advance guys . -ak __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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). For technical support please email [EMAIL PROTECTED] or you can call (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
RE: perl/shell script for alert log
Hey, awesome tip! Been here, lurking. Does that generate a trace file with more than just session id and serial# in it? Nice thing about SERVERERROR is I can get machinename, username, osuser etc and stuff it in alert log so I see who caused the error when I get the email, but your way is clearly easier to implement. - Ethan -Original Message- Sent: Wednesday, August 27, 2003 3:49 PM To: Multiple recipients of list ORACLE-L Welcome back Ethan! An alternative is using the following lines in init.ora: event=1555 trace name errorstack level 3 event=4031 trace name errorstack level 3 event=1652 trace name processstate level 10 This catches the dreaded 01555, out of TEMP, and shared pool allocation errors *along* with the SQL/Stack from the offending process (and thus time of occurrence). And keep *all* the event lines together ;-) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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).
RE: perl/shell script for alert log
Yeah, I log those. Some of the db's I support run with very small redo logs and I can't change. They get those quite frequently at times so I pretty much ignore them. My script also reacts (log,email,page, or run some script) in the event of startups, shutdowns and alter database structure. It also sends everything through a filter of known patterns and anything that is not recognized falls out. I call this the paranoid switch -p. One best practice I recommend is to automate control file backups anytime a ALTER TABLESPACE, ALTER DATABASE etc...is in the alert log. -Original Message- Sent: Wednesday, August 27, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Hi! I would add a check for Checkpoint not complete in alert just in case as well. Tanel. One enhancement I suggest to every script is to configure the a SERVERERROR trigger to throw certain errors out to the alert log. ORA-1555 is one that will show up at the session level but not at the database level. Out of TEMP space is another that is frequent at the session level but not the database level. This way you know who is causing some important errors. Overtime I find more and more session errors that are really critical database errors. Try DDL on a table with an unusable index. Pretty big deal on most production databases but this is a session error and would not typically show up in the alert log. The risk of course is some huge loop throwing 1000's of lines into the alert log. I would suggest a governor of some sort in your servererror trigger. - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan 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).