Long Time

It has been awhile, sorry to all of you.

We just have just sold a small portion of the company and to our surprise, since we haven't done it before, to carve off a section of the company is many times more work than it is to add to the company.

Which SCI FI character

I did the survey and came up with Agent Smith from the Matrix series.
Tom Kyte came up with James T. Kirk.

The oracle sponge

If you haven't you must read David Aldridge stories of his most recent holiday, you have to read all parts, this link is to part 1.

http://oraclesponge.wordpress.com/2006/09/05/three-days-two-hospitals-part-i/

Wonderfully well written, entertaining and enlightening. Being a ex-motorcycle enthusiast I know exactly what he is talking about.

Monitor Alert Log

UNIX shell script to monitor and email errors found in the alert log. Is ran as the oracle OS owner. Make sure you change the "emailaddresshere" entries to the email you want and put the check_alert.awk someplace. I have chosen $HOME for this example, in real life I put it on as mounted directory on the NAS.

if test $# -lt 1
        then
 echo You must pass a SID
        exit 
 fi
#
# ensure environment variables set
#
#set your environment here
export ORACLE_SID=$1
export ORACLE_HOME=/home/oracle/orahome
export MACHINE=`hostname`
export PATH=$ORACLE_HOME/bin:$PATH

# check if the database is running, if not exit

ckdb ${ORACLE_SID} -s
if [ "$?" -ne 0 ]
then
  echo " $ORACLE_SID is not running!!!"
 echo "${ORACLE_SID is not running!" | mailx -m -s "Oracle sid ${ORACLE_SID} is not running!" "
|emailaddresshere|" 
  exit 1
fi;

#Search the alert log, and email all of the errors
#move the alert_log to a backup copy
#cat the existing alert_log onto the backup copy

#oracle 8 or higher DB's only.
sqlplus '/ as sysdba' << EOF > /tmp/${ORACLE_SID}_monitor_temp.txt
column xxxx format a10
column value format a80
set lines 132
SELECT 'xxxx' ,value FROM  v\$parameter WHERE  name = 'background_dump_dest'
/
exit
EOF


cat /tmp/${ORACLE_SID}_monitor_temp.txt | awk '$1 ~ /xxxx/ {print $2}' > /tmp/${ORACLE_SID}_monitor_location.txt
read ALERT_DIR < /tmp/${ORACLE_SID}_monitor_location.txt
ORIG_ALERT_LOG=${ALERT_DIR}/alert_${ORACLE_SID}.log
NEW_ALERT_LOG=${ORIG_ALERT_LOG}.monitored
TEMP_ALERT_LOG=${ORIG_ALERT_LOG}.temp
cat ${ORIG_ALERT_LOG} | awk -f $HOME/check_alert.awk > /tmp/${ORACLE_SID}_check_monitor_log.log
rm /tmp/${ORACLE_SID}_monitor_temp.txt 2>/dev/null
if [ -s /tmp/${ORACLE_SID}_check_monitor_log.log ]
   then 
     echo "Found errors in sid ${ORACLE_SID}, mailed errors"
     echo "The following errors were found in the alert log for ${ORACLE_SID}" > /tmp/${ORACLE_SID}_check_monitor_log.mail
     echo "Alert log was copied into ${NEW_ALERT_LOG}" >> /tmp/${ORACLE_SID}_check_monitor_log.mail
     echo " "
     date >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     echo "--------------------------------------------------------------">>/tmp/${ORACLE_SID}_check_monitor_log.mail
     echo " "
     echo " " >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     echo " " >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     cat /tmp/${ORACLE_SID}_check_monitor_log.log >>  /tmp/${ORACLE_SID}_check_monitor_log.mail

 cat /tmp/${ORACLE_SID}_check_monitor_log.mail | mailx -m -s "on ${MACHINE}, MONITOR of Alert Log for ${ORACLE_SID} found errors" "
|emailaddresshere|" 

     mv ${ORIG_ALERT_LOG} ${TEMP_ALERT_LOG}
     cat ${TEMP_ALERT_LOG} >> ${NEW_ALERT_LOG}
     touch ${ORIG_ALERT_LOG}
     rm /tmp/${ORACLE_SID}_monitor_temp.txt 2> /dev/null
     rm /tmp/${ORACLE_SID}_check_monitor_log.log 
     rm /tmp/${ORACLE_SID}_check_monitor_log.mail 
exit
fi;

rm /tmp/${ORACLE_SID}_check_monitor_log.log > /dev/null
rm /tmp/${ORACLE_SID}_monitor_location.txt > /dev/null

The referenced awk script (check_alert.awk). You can modify it as needed to add or remove things you wish to look for. The ERROR_AUDIT is a custom entry that a trigger on DB error writes in our environment.

$0 ~ /Errors in file/ {print $0}
$0 ~ /PMON: terminating instance due to error 600/ {print $0}
$0 ~ /Started recovery/{print $0}
$0 ~ /Archival required/{print $0}
$0 ~ /Instance terminated/ {print $0}
$0 ~ /Checkpoint not complete/ {print $0}
$1 ~ /ORA-/ { print $0; flag=1 }
$0 !~ /ORA-/ {if (flag==1){print $0; flag=0;print " "} }
$0 ~ /ERROR_AUDIT/ {print $0}
  

I simply put this script into cron to run every 5 minutes passing the SID of the DB I want to monitor.

SQL*plus in windows

As you probably know from reading my blog, I don't like windows. I know how to use windows. I use windows, I have had the misfortune of administrating databases on windows, and most of our applications are made to run on windows. Windows runs the world from the everyday user.

I have windows on my laptop, but I have as little as possible running on windows and pretty much my first task in the morning is to startup my SUSE vm client on my laptop and work from there. This is of course resource intensive and with only having a D600 with the possibility of flaming batteries, resources are scarce. There are times on my 1.3ghz w/1 gig of ram laptop I am trying to run 3 oracle databases, 1 EE for windows, 1 EE for Suse in a VM client,and 1 SE for Suse in a VM client. When I do that, lets just say my laptop has difficulties keeping up with the simplest tasks, like keeping the clock in the taskbar up to date.

I was reading one of the threads going on flaming XE at Doug Burns blog -An Oracle XE user speaks and checked out the link from William Robertson. William Robertson is now my hero. I knew the capabilities existed, I had set up things like this for users. But I had never thought of doing something this simple for myself. I am talking about his wonderfully in depth but amazingly simple to do article on setting up SQL*plus on windows . I immediately went a head and configured up my windows sqlplus as he has explained.

Man... it is sometimes amazing how much the simple little things can make your life so much easier.

Oh, and for those of you waiting for an update. The fellows did their duty and dressed as described in earlier posts. I have requested pictures and I have been assured I will receive them shortly. I will of course post them as soon as I can. I was told, it was a banner day at the office, everybody that could be at the office was at the office.