regular shell scripts for oracle database monitoring and sending mails(generic scripts)

Tablespace monitoring script

Below script monitors and send mail if TBS is reached 80%. this is generic script works for all the databases in one server.

#!/bin/bash

OUTPUT_LOC=/u01/oracle/admin/output

SCRIPT_LOC=/u01/oracle/admin/scripts

DB_LIST=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"|grep -v ASM`

for DB in $DB_LIST ; do

echo "HOST: `hostname`  DB Name: $DB" > $OUTPUT_LOC/tbs_mon.out

export ORACLE_SID=$DB

export ORACLE_HOME=`egrep -i ":Y|:N" /etc/oratab |grep $ORACLE_SID| cut -d":" -f2 | grep -v "\#" | grep -v "\*"`

export PATH=$ORACLE_HOME/bin:$PATH

TBS=`sqlplus -s "/ as sysdba" <<EOF

set heading off feedback off verify off

select tablespace_name from dba_tablespaces where contents != 'UNDO';

exit;

EOF`

for i in $TBS;

do

if [ ! -z "$i" ]

then

TBS_SPACE=`sqlplus -s "/ as sysdba" <<EOF

set pagesize 200

set linesize 121

set heading off feedback off verify off

SELECT tablespace_name,

ROUND(SUM(max_gb) - (SUM(total_gb)-SUM(free_gb))) FREE_SPACE_GB,

ROUND(SUM(max_gb)) MAX_SZ_GB, ROUND((SUM(total_gb)-SUM(free_gb))/SUM(max_gb)*100) PCT_FULL

FROM (

SELECT tablespace_name, SUM(bytes)/1024/1024/1024 FREE_GB,

0 TOTAL_GB, 0 MAX_GB

FROM dba_free_space

GROUP BY tablespace_name

UNION

SELECT tablespace_name, 0 CURRENT_GB,

SUM(bytes)/1024/1024/1024 TOTAL_GB,

SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024/1024 MAX_GB

FROM dba_data_files

GROUP BY tablespace_name) where tablespace_name='$i'

GROUP BY tablespace_name;

exit;

EOF`

TBS_NAME="`echo $TBS_SPACE|awk '{ print $1}'`"

PCT_FULL="`echo $TBS_SPACE|awk '{ print $4}'`"

if [ ! -z $PCT_FULL ]

then

if [ $PCT_FULL -ge 80 ]

then

echo "TBS:" $i "  PCT_FULL: " $PCT_FULL >>$OUTPUT_LOC/tbs_mon.out

fi

fi

fi

done

CHECK_FILE=`grep -i PCT_FULL $OUTPUT_LOC/tbs_mon.out`

if [ ! -z  "$CHECK_FILE" ]

then

cat -v $OUTPUT_LOC/tbs_mon.out | mail -r "sender@company.com" -s "`hostname`:oracle TBS utilization in critical" "whoever@company.com whoever2@company.com"

fi

done

trace files deletion script for oracle

House keeping script to delete trace files  and xml files older than 5 days.
It reads database names from oratab and delete trace files from all the databases listed in /etc/oratab.

#!/bin/bash
DB_LIST=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`
for i in $DB_LIST ; do
export ORACLE_SID=$i
export ORACLE_HOME=`egrep -i ":Y|:N" /etc/oratab |grep $ORACLE_SID| cut -d":" -f2 | grep -v "\#" | grep -v "\*"`
export PATH=$ORACLE_HOME/bin:$PATH
TRACE_LOC=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select VALUE from v\\$diag_info where NAME='Diag Trace';
       exit
EOF`
find $TRACE_LOC -name '*.trc' -mtime +5 -exec rm -f {} \;
find $TRACE_LOC -name '*.trm' -mtime +5 -exec rm -f {} \;
ALXML_LOC=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select VALUE from v\\$diag_info where NAME='Diag Alert';
       exit
EOF`
find $ALXML_LOC -name 'log_*' -mtime +5 -exec rm -f {} \;
done

Oracle Listener log rotation script- Generic

Listener log rotation script(generic)- it works for what ever listeners currently running on the server.

#!/bin/bash
##Listener log rotation##
LSNR_NAME=`ps -ef|grep tnslsnr|grep -v grep|awk '{print $9}'`
for i in $LSNR_NAME; do
ORACLE_HOME=`ps -ef|grep tnslsnr|grep -v grep|awk '{print $8}'| rev | cut -d'/' -f3- | rev`
export PATH=$ORACLE_HOME/bin:$PATH
LSNR_LOG_LOC=`lsnrctl status ${LSNR_NAME} | grep "Listener Log File" | awk '{ print $4 }'|rev|cut -d '/' -f2-|rev`
find $LSNR_LOG_LOC -name 'log_*' -mtime +5 -exec rm -f {} \;
cd ../trace
LSNR_LOG_FILE=`ls -lrt *.log|awk '{print $9}'`
find -name '*.gz' -mtime +10 -exec rm -f {} \;
tar -cvzf $LSNR_LOG_FILE.`date '+%d%m%Y%H%M%S'`.tar.gz $LSNR_LOG_FILE
cat /dev/null > $LSNR_LOG_FILE
done

(or)

#!/bin/bash
##Listener log rotation##
LSNR_NAME=`ps -ef|grep tnslsnr|grep -v grep|awk '{print $9}'`
for i in $LSNR_NAME; do
ORACLE_HOME=`ps -ef|grep $i|grep -v grep|awk '{print $8}'| rev | cut -d'/' -f3- | rev`
export PATH=$ORACLE_HOME/bin:$PATH
LSNR_LOG_LOC=`lsnrctl <<-EOF | grep trc_directory | awk '{ print $6 }'
set displaymode normal
set current_listener ${i}
show trc_directory
EOF`
LSNR_XML_LOC=`lsnrctl <<-EOF | grep log_directory | awk '{ print $6 }'
set displaymode normal
set current_listener ${i}
show log_directory
EOF`
find $LSNR_XML_LOC -name 'log_*' -mtime +5 -exec rm -f {} \;
LSNR_LOG_FILE=`ls -lrt $LSNR_LOG_LOC/*.log|awk '{print $9}'`
find $LSNR_LOG_LOC -name '*.gz' -mtime +10 -exec rm -f {} \;
tar -cvzf $LSNR_LOG_FILE.`date '+%d%m%Y%H%M%S'`.tar.gz $LSNR_LOG_FILE
cat /dev/null > $LSNR_LOG_FILE
done

Script for ASM usage report from all the servers

Below script will be useful to gather ASM DG space usage details from all the servers and send a mail.

file1: db_serv_list.csv will have all IP/host names of the environment(you have to list all hosts or IPs in this file)
file2: check_ora_ASM_space.sh will be copied to all target server every time when ever you want to run the report and get the output by login to ASM instance.
file3: ora_ASM_space_report.sh this is the main script you suppose to run, this will gather out put from all the servers to file  ora_ASM_space_report.log


ora_ASM_space_report.sh
==========================
#!/bin/bash
rm /opt/oracle/logs/ora_ASM_space_report.log
SERV_LIST=`egrep -v "#" /u01/scripts/db_serv_list.csv`
for i in $SERV_LIST; do
scp /opt/oracle/scripts/check_ora_ASM_space.sh $i:/tmp
ssh $i "sh /tmp/check_ora_ASM_space.sh" >> /u01/logs/ora_ASM_space_report.log
done
export PATH=/usr/sbin:$PATH

awk ' BEGIN {
 print "From: goldalerts@symphonyretailai.com"
 print "To: example1@oracmp.com example2@oracmp.com"
 print "MIME-Version: 1.0"
 print "Content-Type: text/html"
 print "Subject: Your Subject"
 print "<html><body><table border=1 BORDERCOLORDARK=BLUE cellspacing=1 cellpadding=5>"
 print "<tr bgcolor=navy>"
 print "<td><b>Hostname</b></td>";
 print "<td><b>DG_NAME</b></td>";
 print "<td><b>PCT_USED</b></td>";
 print "<td><b>Avail_GB</b></td>";
 print "</tr>"
} {
 print "<tr>"
 print "<td>"$1"</td>";
 print "<td>"$2"</td>";
 print "<td>"$3"</td>";
 print "<td>"$4"</td>";
  print "</tr>"
} END {
 print "</table></body></html>"
} ' /u01/logs/ora_ASM_space_report.log | sendmail -t


check_ora_ASM_space.sh
========================
#!/bin/bash
export ORACLE_SID=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"|grep ASM`
if [ -z "$ORACLE_SID" ]
then
exit;
else
export ORACLE_HOME=`egrep -i ":Y|:N" /etc/oratab |grep $ORACLE_SID| cut -d":" -f2 | grep -v "\#" | grep -v "\*"`
export PATH=$ORACLE_HOME/bin:$PATH
DG_SPACE=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       SELECT name  group_name,total_mb/1024,(total_mb-free_mb)/1024 Avail_GB,ROUND((1- (free_mb / total_mb))*100, 2)  pct_used FROM v\\$asm_diskgroup ORDER BY name;
       exit
EOF`
echo `hostname`  $DG_SPACE
fi

shell script to monitor ASM DG space to send alert

Script to monitor ASM DG space. it will send alert if space is reached to 90% full and also if its full 80% with less than 100 GB space. schedule it to run for every 15min

-->  90% full --- send alert
-->  80% and avail space less than 100GB -- send alert


#!/bin/bash
export ORACLE_SID=`egrep -i ":Y|:N" /etc/oratab | cut -d":" -f1 | grep -v "\#" | grep -v "\*"|grep ASM`
if [ -z "$ORACLE_SID" ]
then
exit;
else
export ORACLE_HOME=`egrep -i ":Y|:N" /etc/oratab |grep $ORACLE_SID| cut -d":" -f2 | grep -v "\#" | grep -v "\*"`
export PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH
DG_LIST=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       SELECT name from v\\$asm_diskgroup ORDER BY name;
       exit
EOF`
for i in $DG_LIST; do
DG_SPACE=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       SELECT name  group_name,total_mb/1024,ROUND((free_mb)/1024) Avail_GB,ROUND((1- (free_mb / total_mb))*100)  pct_used FROM v\\$asm_diskgroup where name='$i';
       exit
EOF`
DG_NAME="`echo $DG_SPACE|awk '{ print $1}'`"
PCT="`echo $DG_SPACE|awk '{ print $4}'`"
AVL_GB="`echo $DG_SPACE|awk '{ print $3}'`"
if [  $PCT -ge 80 ] && [ $PCT -lt 90 ] 
then 
 if [ $AVL_GB -lt 100 ]
 then 
 echo `hostname`  $DG_SPACE | mail -r "sender@company.com" -s "`hostname`:GOLD-oracle ASM-DG $DG_NAME utilization in critical" "yourmail@company.com"
 fi
 else if [ $PCT -ge 90 ]
 then
 echo `hostname`  $DG_SPACE | mail -r "sender@company.com" -s "`hostname`:GOLD-oracle ASM-DG $DG_NAME  utilization in critical" "yourmail@company.com"
 fi
 fi
done
fi