Oracle Script Runner

I have been very busy with varous projects hence this is my first posting in 2009.

As usual, I thought of having a UNIX script I can use to call any Oracle script and generate either text or html output. I came up with this draft and have been using it. Please use it with caution because it is under continous development. It needs a lot of plushing as you can see. It is not the best, but very useful.


#!/bin/ksh
##############################################################
#
# This is a Generic Script that will call any SQL script and run it on the specified
# SID or ALL Databases on the Server
#
# It will require passing the name of the
# SCRIPT,USERID,OUTPUTTYPE,SENDEMAIL,DELOLDFILE, ORACLESID
# Where SCRIPT is the name of the script to run.
# USERID is oracle username/password
# OUTPUTTYPE is either to send output file as html or text
# SENDEMAIL (Y or N) is whether to email result to specified DBAs
# Requires that mail system is working on this client
# DELOLDFILE (Y or N) is whether to Delete old versions of the same report
# ORACLESID is either the database SID to run the script against
# or ALL (run against all SID marked Y in oratab and started on the server)
#
# Example script_runner.sh test.sql user1/pass1 html Y Y testdb
# This produces a compressed html report for only testdb
# test.sql.testdb.200205061200.html.gz
# If testdb is replace with ALL, it produces the report for each SID
# in oratab marked Y and running on the server
#
# Report file is in the format script_name.sid.yyyymmddhhmi.outputtype.gz
#
# To run this script successfully, the following must be changed as requried
# ORACLEDB_HOME, ORATAB, SCRIPTPATH, and DBAS
#
# This was developed for my personal use and has only been Tested on Solaris.
# Please use it with care.
# You can modify it for your own use, but you must leave this header information
# in it as a permission from DBMS Direct to reuse it
# Send us you comments about the script.
# Thanks http://www.dbmsdirect.com/
# http://www.unictechnologies.com/
# http://dbmsdirect.blogspot.com/
#
# Script Name - script_runner.sh
#
# Update History
#
# Date Author Company Version
# 21/06/2002 DBMS Direct 1.0
# 06/05/2008 DBMS Direct 2.0
#
##############################################################

###################################
# Customisable Initialisation
#
export SCRIPTFILE=$1
export USERID=$2
export OUTPUTTYPE=$3
export SENDEMAIL=$4
export DELOLDFILE=$5
export ORACLESID=$6
export ORACLEDB_HOME=/u01/app/oracle/product/10.2.0.2
export ORATAB=/var/opt/oracle/oratab
export SCRIPTPATH=/export/home/oracle/dbmsdirect
export SCRIPT=$SCRIPTPATH/$SCRIPTFILE
export DBAS='dbms.direct@unictechnologies.com'
export TDAY=`date '+%Y%m%d%H%M'`
export MDAY=`date '+%d-%m-%Y %H-%M'`
#
# End of Customisable Initialisation
###################################

# Get all running instances

instances_running()
{
if [ "$ORACLESID" == ALL ];
then
for sid in `grep -v '^#' ${ORATAB} grep ':Y$' awk '{FS=":"} {print $1}'`
do
for i in `ps -efegrep 'ora_...._'${sid}grep -v grepawk '{print $NF}'`
do

echo $i
done sed 's/ora_...._//' sort -u
done
else
echo $ORACLESID
fi
}

# Run Daily Check on all Running Instances

run_script()
{
for i in `instances_running`
do
REPORTH=${SCRIPT}.$i.${TDAY}.${OUTPUTTYPE}
REPORTZ=${REPORTH}.gz

if [ "$DELOLDFILE" == Y ];
then
# Delete old versions of the same report
/usr/bin/rm ${SCRIPT}.$i.*.${OUTPUTTYPE}.gz
fi

echo $i . /usr/local/bin/oraenv >/dev/null
ORACLE_HOME=${ORACLEDB_HOME}
if [ "$OUTPUTTYPE" == text ];
then
${ORACLE_HOME}/bin/sqlplus -s ${USERID} << EOF
WHENEVER SQLERROR EXIT
SET MARKUP html OFF
SET TERM ON
SET HEAD ON
SET FEEDBACK OFF
SET PAGESIZE 9999
SET LINESIZE 200
SPOOL ${REPORTH}
@${SCRIPT} \
SPOOL OFF
EOF
elif [ "$OUTPUTTYPE" == html ];
then
${ORACLE_HOME}/bin/sqlplus -s ${USERID} << EOF
WHENEVER SQLERROR EXIT
SET MARKUP html ON
SET TERM ON
SET HEAD ON
SET FEEDBACK OFF
SET PAGESIZE 9999
SET LINESIZE 200
SPOOL ${REPORTH}
@${SCRIPT} \
SPOOL OFF
EOF
fi
# Create Zip file

/usr/bin/gzip -f ${REPORTH}

# Mail Zip file to DBA if requested

if [ "$SENDEMAIL" == Y ];
then
#/usr/bin/mailx -s "${$i} - ${SUBJECT}" ${DBAS} < $REPORTH
/usr/bin/uuencode ${REPORTZ} ${REPORTZ} /usr/bin/mailx -s "$i - Database Result - $MDAY" ${DBAS}
fi
done
}

# Begin Running Script
run_script