Monday, January 12, 2009

Site News And Simple Oracle Monitoring For Linux And Unix

Hey there,

Just wanted to let everyone know that the site move is starting to come along somewhat nicely (you'd be amazed at how much of a PITA having a decent site page cross-linking setup can be when moving between two completely different platforms). It's my hope that, by the end of this week, we'll be broadcasting in stereo with the new site up in "beta" form (we skipped alpha; it was slowing us down ;). When that happens, I'll be sure to email everyone, who requested to be notified, with the new site address. For, give or take, two weeks after that, we'll be publishing at both locations simultaneously while we work out all the bugs we spent so many hours working in ;) There will be no link directly from this blog to the newer version of the blog and (if the spiders and robots honor our requests to not index any pages, follow any links, or even recognize our new site) we, hopefully, won't end up getting slammed for duplicate content.

Just wanted to let everyone know that the site move is starting to come along somewhat nicely (you'd be amazed at how much of a PITA having a decent site page cross-linking setup can be when moving between two completely different platforms). It's my hope that, by the end of this week, we'll be broadcasting in stereo with the new site up in "beta" form (we skipped alpha; it was slowing us down ;). When that happens, I'll be sure to email everyone, who requested to be notified, with the new site address. For, give or take, two weeks after that, we'll be publishing at both locations simultaneously while we work out all the bugs we spent so many hours working in ;) There will be no link directly from this blog to the newer version of the blog and (if the spiders and robots honor our requests to not index any pages, follow any links, or even recognize our new site) we, hopefully, won't end up getting slammed for duplicate content.

For everyone who's wondering; yes, that second paragraph was exactly the same as the first. It was a cheap duplicate-content joke, I admit, and only funny if you're into movies like Airplane and The Naked Gun. I can't get enough of that kind of humour. Alas, it's not for everyone. Pardon my indulgence. ...and feel free to copy and paste that paragraph all over the Internet ;) In any event, if the posts get a little strange(r) this week, it's because my mind's on setting up the whole new site while still trying my hardest not to go soft on the posts. I could do a whole week of showcasing other people's stuff, but it just wouldn't seem right. I am serious ...And don't call me Shirley ;)

This week's Monday script (written in ksh because I write everything in bash now and I could swear it used to be my favorite shell ;) is fairly simple in concept, and can actually be used to monitor pretty much any product composed of a set of processes (or, just any process), with minor modifications. For our purposes, today, we're using it to keep the Oracle DBA's up all night getting pages about every burp and hiccup the database manages to spit and sputter out. Although this script does serve a sinister purpose, as a side effect, it's not meant to equalize the amount of time sys-admins and DBA's spend waking up at odd hours of the night to investigate issues (whether they be actual issues or not ;). This script was written, very simply, to make sure that the DBA's know when there may be a problem with their DB's. If the databases' uptime is only an issue during the day, just don't include the evening hours when you throw this thing into your crontab :)

There's only one prerequisite to running this script, which I didn't want to actually put in the script (although it could be done if I were able to think more clearly). Before you put this script into your crontab, or just run it manually, execute the following at the command line (Assuming you're logged in as oracle and have all the standard environment variables set and populated):

host # grep "[O]RA-" $ORACLE_BASE/admin/$ORACLE_SID/yourDumpDir/alert_$ORACLE_SID.log > $ORACLE_BASE/admin/$ORACLE_SID_alert.prev

This will ensure that you create the basic file that this script checks, initially, when you run it the first time (and every time after that). I decided to do that one step manually, since it keeps down on the clutter. Plus it ensures that this simple check doesn't bother you about an error that happened last week. My actual vision, at this point, is much worse than 20/20, and the way I actually implement this is to run the above command line every night at 23:59 in cron, with the monitoring script running every 15 minutes all day, every day ;)

For everyone who's wondering; yes, the bad jokes will get worse as I grow tired and begin to hallucinate from sleep deprivation over the course of the next few weeks ;) Expect more typos thAn uzual, two ;)

The script also expects that you have the files .dbaoncall and .dbaemail set up (the script can be retooled to have these files located wherever you'd like and called whatever you'd prefer - or, if you're working on a smaller site, you could just include the information, that you would put in these files, in the script itself). Basically, the .dbaoncall file would contain a list of one or more cell-phone/pager email addresses and the .dbaemail file would contain a list of one or more standard email addresses, for notification purposes.

Here's hoping this script helps you out in some way (even if it just makes you feel better about that Oracle monitoring script you wrote yourself, but were having doubts about until you had a look at this incredibly inferior piece of work ;) - Also, for those of you who are curious about the format of the "grep" lines, check out our old post on keeping grep out of your grep output. <-- There's another cross-link I'm going to have to reconcile... When will I learn?!?!?! :)

Cheers,


Creative Commons License


This work is licensed under a
Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License

#!/bin/ksh

#
# oralert.sh - Consult the great Oracle and, perhaps, find out what's wrong ;)
# Double check the values of all variables as these represent a standard setup structure that may not agree with yours!
#
# 2009 - Mike Golvach - eggi@comcast.net
#
# Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License
#

. /usr/local/bin/oraenv

LOGFILE=$ORACLE_BASE/admin/$ORACLE_SID/yourDumpDir/alert_$ORACLE_SID.log
PREV=$ORACLE_BASE/admin/"$ORACLE_SID"_alert.prev
CURR=$ORACLE_BASE/admin/"$ORACLE_SID"_alert.curr

DBAONCALL=`cat $ORACLE_BASE/admin/.dbaoncall`
DBAEMAIL=`cat $ORACLE_BASE/admin/.dbaemail`
SENDMAIL=/usr/lib/sendmail # Change to /usr/sbin/sendmail if necessary
SCHEMA="prd_schema" # Change this to your schema name

grep "ORA-" $LOGFILE > $CURR

if ! diff $PREV $CURR
then
ERR=`diff $PREV $CURR`
print $ERR > $ORACLE_BASE/admin/alertlogerr.`date +%b%e%T`
print "Subject: `hostname` $ORACLE_SID alert_log error \n$ERR" | $SENDMAIL $DBAEMAIL
print "Subject: `hostname` $ORACLE_SID alert_log error \n$ERR" | $SENDMAIL $DBAONCALL
cp $CURR $PREV
fi

if ! ps -ef |grep $SCHEMA|grep "[o]ra_dbw"
then
print "Subject: ERROR ON `hostname` DBWR is down" | $SENDMAIL $DBAEMAIL
print "Subject: ERROR ON `hostname` DBWR is down" | $SENDMAIL $DBAONCALL
fi

if ! ps -ef |grep $SCHEMA|grep "[o]ra_lgwr"
then
print "Subject: ERROR ON `hostname` LGWR is down" | $SENDMAIL $DBAEMAIL
print "Subject: ERROR ON `hostname` LGWR is down" | $SENDMAIL $DBAONCALL
fi

if ! ps -ef |grep $SCHEMA|grep "[o]ra_arc"
then
print "Subject: ERROR ON `hostname` ARC is down" | $SENDMAIL $DBAEMAIL
print "Subject: ERROR ON `hostname` ARC is down" | $SENDMAIL $DBAONCALL
fi

if ! ps -ef |grep $SCHEMA|grep "[o]ra_pmon"
then
print "Subject: ERROR ON `hostname` PMON is down" | $SENDMAIL $DBAEMAIL
print "Subject: ERROR ON `hostname` PMON is down" | $SENDMAIL $DBAONCALL
fi

if ! ps -ef |grep $SCHEMA|grep "[o]ra_smon"
then
print "Subject: ERROR ON `hostname` SMON is down" | $SENDMAIL $DBAEMAIL
print "Subject: ERROR ON `hostname` SMON is down" | $SENDMAIL $DBAONCALL
fi


, Mike




Discover the Free Ebook that shows you how to make 100% commissions on ClickBank!



Please note that this blog accepts comments via email only. See our Mission And Policy Statement for further details.