News

  1. Home
  2. All posts
All posts, Oracle, Scripting by Team Ajara

Shell Script for Oracle Database Monitoring

How to monitor Oracle Database by using Shell Script.

#!/bin/ksh
#set -x
#set -v
err() {
        test -n “$@” && echo “ERROR: $@” >&2    # print error message if given
#echo “Value of ‘$@’: $@”>>$LOGFILE
                mail_msg=$@
                        echo $mail_msg >> $LOGFILE
                        echo $mail_msg >> $LOGFILE
                                mailx -s “$S_MSG1” $V_MAILEES < $LOGFILE || exit 1
                                        #rm -f $LOGFILE || exit 1
                                        }

Set the environment:

set_env() {
                                                umask 177
export DB
                                                        DTE=`date +%y_%m.%d.%H.%M`
                                                        SCRIPT_DIR=/home/oracle/scripts/EMV
ORACLE_HOME=/u00/app/oracle/product/19.0.0/db_1
export TNS_ADMIN=/home/oracle/scripts/EMV
PATH=$ORACLE_HOME/bin:$PATH
ORACLE_SID=R2D2
ORAENV_ASK=NO
. oraenv
                                                        export LOGFILE=$SCRIPT_DIR/EMV_connection_log_${DTE}.log
                                                        export CONN_ERR=$SCRIPT_DIR/EMV_connection_err_log_${DTE}.log
                                                        export ERR_LOG=$SCRIPT_DIR/EMV_ERR_LOG_${DTE}.log
                                                        export ORA_LOGFILE=$SCRIPT_DIR/EMV_ora_connection_log_${DTE}.log
  export SQLPLUS=$ORACLE_HOME/bin/sqlplus
                                                        S_MSG1=”EMV_Connectivity issue with $CONN_STR”
                                                        export V_MAILEES=admin@ajara.tech,dba@ajara.tech
                                                        }

Main Function:

main()
{
                set_env
        #. $HOME/scripts/setdb $ORACLE_SID || err “Error while initializing DB”
                #touch $LOGFILE || err “Error while creating log file $LOGFILE”
rm $LOGFILE
rm $ORA_LOGFILE
# Check the mail file
        touch $LOGFILE || err “Error in creating mail file”
cat $SCRIPT_DIR/.db_names|grep -v “^#” >$SCRIPT_DIR/.sorted_db_names
exec <$SCRIPT_DIR/.sorted_db_names
#for CONN_STR in  `cat .db_names|awk ‘{print $2}’`
l_count=`wc -l $SCRIPT_DIR/.sorted_db_names`
l=1
while read  line
do
CONN_STR=`echo $line|awk ‘{print $2}’`
echo “Conn str =${CONN_STR}”
export DB
DB=`echo $line|cut -d “@” -f2`
echo “line = $line”
SQL=`echo  $line|awk ‘{print $1}’`
echo “SQL =${SQL}”
echo “DB=${DB}”
echo “——————————————————————————–“>>$LOGFILE
echo “echo DB name : $DB”>>$LOGFILE
DB_CONN=`($SQLPLUS -s $CONN_STR <<EOF
                  –WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
                WHENEVER SQLERROR EXIT SQL.SQLCODE
                    WHENEVER OSERROR EXIT 2;
set head off
set echo off
set serveroutput on
set feedback off
spool $ORA_LOGFILE append
@$SCRIPT_DIR/$SQL
spool off
                  EXIT;
EOF
)`
STATUS=$?
if [ $STATUS -eq 0 ]
then
echo “———————————————————–“>> $LOGFILE
echo ” DB name : ${CONN_STR}”>> $LOGFILE
echo $DB_CONN>> $LOGFILE
else
echo “———————————————————–“>> $ERR_LOG
echo ” DB name : ${CONN_STR}”>> $ERR_LOG
echo $DB_CONN>>$ERR_LOG
continue
fi
done
cat  /home/oracle/scripts/EMV/EMV_Heading  $ORA_LOGFILE >  /home/oracle/scripts/EMV/EMV.csv
if [ -f ${ERR_LOG} ]
then
mailx -s “Early Morning Validations” -a /home/oracle/scripts/EMV/EMV.csv -a $ERR_LOG  $V_MAILEES < /dev/null
else
mailx -s “Early Morning Validations” -a /home/oracle/scripts/EMV/EMV.csv   $V_MAILEES < /dev/null
fi
}

Calling Main Function:

    main “$@”
See Also: