hagenj
asked on
CRON Scripts
I am new to the Unix OS and I have to write a CRON scritps which will search an Oracle DB alert.log for "ORA-" errors every 20 minutes. When these errors are found, I would like an e-mail sent you to a distribution list.The machine is running IRIX unix and a default C shell. What I am looking for is a script(well explained what each line does) which I could modify and learn from. If you have a script like this place post and/or sent to john.hagen@shaw.ca
In order to extract lines containing a certain string (in this case "ORA-") from a file, use the grep (Global Regular Expression Parser) command as follows, substitute the full path to your log file where I have put "/opt/oracle/alert.log" ;
grep "ORA-" /opt/oracle/alert.log
In order to mail the extracted output to yourself, or a distribution list, pipe the output of the grep command into mailx, like so;
grep "ORA-" /opt/oracle/alert.log | mailx -s "Oracle Errors" distribution@company.com
The -s switch gives you a Subject line.
You could write a script to do this, but it is only two commands. You could just put the whole command line into the crontab entry, which would look like this, assuming that it is scheduled to run at 5, 25, and 45 minutes after each hour;
05,25,45 * * * * grep "ORA-" /opt/oracle/alert.log | mailx -s "Oracle Errors" distribution@company.com
The first 5 colums in the crontab entry are;
minutes (range 00-59),
hour (range 00-23),
day of month (range 1-28 or 29 or 30 or 31 depending on month and year)
month of year (range 1-12)
day of week (sun=0, mon=01 .. sat=06)
grep "ORA-" /opt/oracle/alert.log
In order to mail the extracted output to yourself, or a distribution list, pipe the output of the grep command into mailx, like so;
grep "ORA-" /opt/oracle/alert.log | mailx -s "Oracle Errors" distribution@company.com
The -s switch gives you a Subject line.
You could write a script to do this, but it is only two commands. You could just put the whole command line into the crontab entry, which would look like this, assuming that it is scheduled to run at 5, 25, and 45 minutes after each hour;
05,25,45 * * * * grep "ORA-" /opt/oracle/alert.log | mailx -s "Oracle Errors" distribution@company.com
The first 5 colums in the crontab entry are;
minutes (range 00-59),
hour (range 00-23),
day of month (range 1-28 or 29 or 30 or 31 depending on month and year)
month of year (range 1-12)
day of week (sun=0, mon=01 .. sat=06)
blowfish:
a) I'd use full path to command (/bin/grep)
b) I'd rather create script and call the script (single command) from cron (cron line
05,25,45 * * * * /where/command/is/command
and grep & other stuff was in the script "command")
the essential one:
c)I believe hagenj meant "send me errors which occured since last
control (during last 20 minutes)". In case you have a few well known, long-time-ago errors in log, you will mail them again and again.
This makes the question a little more difficult ..
a) I'd use full path to command (/bin/grep)
b) I'd rather create script and call the script (single command) from cron (cron line
05,25,45 * * * * /where/command/is/command
and grep & other stuff was in the script "command")
the essential one:
c)I believe hagenj meant "send me errors which occured since last
control (during last 20 minutes)". In case you have a few well known, long-time-ago errors in log, you will mail them again and again.
This makes the question a little more difficult ..
ASKER
Hajek, is right I am looking for only new errors which have taken place in the last 20 minutes, and I am looking for a script. Sorry for not saying that.
ASKER
Adjusted points to 250
john,
would you please post an example line from your alert.log? you can xxx out any of the text; i'd be important, though, to see the position of the ORA-#### tags and the date/time stamp.
larry
would you please post an example line from your alert.log? you can xxx out any of the text; i'd be important, though, to see the position of the ORA-#### tags and the date/time stamp.
larry
ASKER
Tue Jul 14 00:38:04 1998
Thread 1 advanced to log sequence 28188
Current log# 3 seq# 28188 mem# 0: /*****/*****/*****/log03a. dbf
Current log# 3 seq# 28188 mem# 1: /*****/*****/*****/log03b. dbf
Tue Jul 14 00:41:17 1998
Thread 1 advanced to log sequence 28189
Current log# 1 seq# 28189 mem# 0: /*****/*****/*****/log01a. dbf
Current log# 1 seq# 28189 mem# 1: /*****/*****/*****/log01b. dbf
Thread 1 advanced to log sequence 28188
Current log# 3 seq# 28188 mem# 0: /*****/*****/*****/log03a.
Current log# 3 seq# 28188 mem# 1: /*****/*****/*****/log03b.
Tue Jul 14 00:41:17 1998
Thread 1 advanced to log sequence 28189
Current log# 1 seq# 28189 mem# 0: /*****/*****/*****/log01a.
Current log# 1 seq# 28189 mem# 1: /*****/*****/*****/log01b.
ASKER
Sat May 2 23:46:21 1998
Errors in file /*****/*****/*****/*****/* ****/***** /ora_20254 .trc:
ORA-07445: exception encountered: core dump [11] [] [] [] [] []
Errors in file /*****/*****/*****/*****/*
ORA-07445: exception encountered: core dump [11] [] [] [] [] []
ASKER
Above Blowfish wrote
grep "ORA-" /opt/oracle/alert.log | mailx -s "Oracle Errors" distribution@company.com
How whould I enter two or more e-mail address instead of the distribution list
grep "ORA-" /opt/oracle/alert.log | mailx -s "Oracle Errors" distribution@company.com
How whould I enter two or more e-mail address instead of the distribution list
Don't forget that cron jobs do not execute shell initialisation files (.profile, .cshrc,...) unless you explicitly source them in the script which you fire off. This is why it is a good idea to give a full path name for commands.
the solution very strongly depends on whether Oracle keeps the log file open (Informix does not.) Try command
fuser -u _log_file_
(when database is working) a few times.
If it does not return user, who have the file open, at least
once time, you can believe the file is not opened all time.
Now you can do in you control script for example this:
-------------------------- ---------- -----
mv log_file /tmp/log_file.new ## logs from last control
>log_file ## creates empty log file
grep _ERROR_ /tmp/log_file.new > /tmp/log_file.err ## errors to special file
NRERR=`wc -l /tmp/log_file.err|cut -f1 -d" "` ## # of errors into variable NRERR
if [ "NRERR" != "0" ] ; then ## when it is >0, send mail
cat /tmp/log_file.err|mailx -s "JESUS ! WHAT IS WRONG ??"
fi
cat /tmp/log_file.new >> log_file.controlled ## appends all just controlled new log to file log_file.controlled
-------------------------- ---------- ---------- ---
PS: script for ksh (korn shell), you may need to alter for sh
This works ONLY when ORACLE only appends to log_file and does not it keep opened all time.
fuser -u _log_file_
(when database is working) a few times.
If it does not return user, who have the file open, at least
once time, you can believe the file is not opened all time.
Now you can do in you control script for example this:
--------------------------
mv log_file /tmp/log_file.new ## logs from last control
>log_file ## creates empty log file
grep _ERROR_ /tmp/log_file.new > /tmp/log_file.err ## errors to special file
NRERR=`wc -l /tmp/log_file.err|cut -f1 -d" "` ## # of errors into variable NRERR
if [ "NRERR" != "0" ] ; then ## when it is >0, send mail
cat /tmp/log_file.err|mailx -s "JESUS ! WHAT IS WRONG ??"
fi
cat /tmp/log_file.new >> log_file.controlled ## appends all just controlled new log to file log_file.controlled
--------------------------
PS: script for ksh (korn shell), you may need to alter for sh
This works ONLY when ORACLE only appends to log_file and does not it keep opened all time.
hajek's method will work: you need to extract all messages issued since the last time your script ran. Once you have that info, you grep for errors, and if the count of lines extracted is > 0, send out the results via e-mail.
To answer your previous question regarding mailx and multiple recipients, just list them at the end of the mailx command like so:
grep "xyz" file.name | mailx -s "subject" user1@domain.com user2@domain.com user3@domain.com user4@domain.com
Alternativly, since you are writing a shell script you may want to use a variable like this;
RECIPIENT="user1@domain.co m user2@domain.com user3@domain.com user4@domain.com"
grep "xyz" file.name | mailx -s "subject" ${RECIPIENT}
You may prefer to create a distribution list in sendmail. To do this locate your sendmail aliases file, usually /etc/aliases (or /etc/mail/aliases) and add the following line to it;
ora-errors: user1@domain.com, user2@domain.com, user3@domain.com, user4@domain.com
after you save the file, run newaliases to compile the sendmail aliases database.
To test your alias, run the following sendmail command;
/usr/lib/sendmail -bv ora-errors
That command will expand the alias from the sendmail alias database and print out each recipient's email address.
If you create the distribution list in sendmail then change the mailx command like so:
grep "xyz" file.name | mailx -s "subject" ora-errors
--cheers
To answer your previous question regarding mailx and multiple recipients, just list them at the end of the mailx command like so:
grep "xyz" file.name | mailx -s "subject" user1@domain.com user2@domain.com user3@domain.com user4@domain.com
Alternativly, since you are writing a shell script you may want to use a variable like this;
RECIPIENT="user1@domain.co
grep "xyz" file.name | mailx -s "subject" ${RECIPIENT}
You may prefer to create a distribution list in sendmail. To do this locate your sendmail aliases file, usually /etc/aliases (or /etc/mail/aliases) and add the following line to it;
ora-errors: user1@domain.com, user2@domain.com, user3@domain.com, user4@domain.com
after you save the file, run newaliases to compile the sendmail aliases database.
To test your alias, run the following sendmail command;
/usr/lib/sendmail -bv ora-errors
That command will expand the alias from the sendmail alias database and print out each recipient's email address.
If you create the distribution list in sendmail then change the mailx command like so:
grep "xyz" file.name | mailx -s "subject" ora-errors
--cheers
ehmm, a little correction to my script:
..
if [ "$NRERR" != "0" ] ; then
..
I forgot the "$"...
..
if [ "$NRERR" != "0" ] ; then
..
I forgot the "$"...
ASKER
For Hajek, it looks like yours answer is going to look, so just answer the question and I will accept it, sorry Blowfish but i can only give pints to one.
That's all part of the "game", he beat me to it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER