RAPID TRIAGE SHELL SCRIPT FOR TROUBLESHOOTING

RAPID TRIAGE SHELL SCRIPT FOR TROUBLESHOOTING

#!/bin/bash

echo "#########################################################################################################"
echo "                                                   "
echo "                                     RAPID TRIAGE FOR TROUBLESHOOTING                                    "
echo "                                               AUTHOR~Kishan M    "
echo "#########################################################################################################"

echo "                                                   "
echo "                                                   "
echo "                                                   "

_log_(){
BLOCKING_LOG=/tmp/block.log  >/dev/null
ENQ_LOG=/tmp/enq.log>/dev/null
OWE_LOG=/tmp/wait_event.log>/dev/null
SQL_LOG=/tmp/top_sql.log>/dev/null
LATCH_LOG=/tmp/latch.log>/dev/null
DFIO_LOG=/tmp/datafileio.log >/dev/null
SEGMENT_LOG=/tmp/segment.log>/dev/null
5LOCKS_LOG=/tmp/5locks.log>/dev/null
}



_scripts_(){
BLOCK=/export/home/oracle/blocking.sql
ENQUEUE=/export/home/oracle/lock.sql
ORTA=/export/home/oracle/orta.sql
SQL=/export/home/oracle/top_sql.sql
LATCH=/export/home/oracle/latch.sql
DFIO=/export/home/oracle/datafileio.sql
SEGMENT=/export/home/oracle/segment.sql
5LOCKS_SQL=/export/home/oracle/5locks.sql
export BLOCK ENQUEUE ORTA SQL LATCH DFIO SEGMENT
}

_rapid_triage(){
echo "______________________________________________"
echo "                                     "
echo "    *** SELECT METRIC FROM THE OPTIONS ***"
echo "______________________________________________"
echo "                                     "
METRIC=("BLOCKING_SESSIONS" "OBJECT_LOCKS" "TOP_5_FOREGROUND_WAIT_EVENTS" "TOP_5_EXPENSIVE_SQL" "LATCHES" "DATAFILE_IOSTAT" "SEGMENT" "Exit")
select M in "${METRIC[@]}";do
case $M in
"BLOCKING_SESSIONS")
echo "----------------------------------------------------------------------------------------------------------------------------------";echo "                                                         *** BLOCKING SESSIONS ***";echo "----------------------------------------------------------------------------------------------------------------------------------";exit|sqlplus -S '/ as sysdba' @$BLOCK >$BLOCKING_LOG;grep 'block' $BLOCKING_LOG>/dev/null 2>&1 && cat $BLOCKING_LOG;if [ $? -ne 0 ];then echo " *** No blocking sessions ***";fi
;;
"OBJECT_LOCKS")
echo "----------------------------------------------------------------------------------------------------------------------------------";echo "                                                         *** OBJECT LOCKS ***";echo "----------------------------------------------------------------------------------------------------------------------------------";exit|sqlplus -S '/ as sysdba' @$ENQUEUE >$ENQ_LOG;grep 'sid' $ENQ_LOG && cat $ENQ_LOG;if [ $? -ne 0 ];then echo " *** No locks ***";fi
;;
"TOP_5_FOREGROUND_WAIT_EVENTS")
echo "----------------------------------------------------------------------------------------------------------------------------------";echo "                                                         *** WAIT EVENTS ***";echo "----------------------------------------------------------------------------------------------------------------------------------";exit|sqlplus -S '/ as sysdba' @$ORTA >$OWE_LOG;cat $OWE_LOG
;;
"TOP_5_EXPENSIVE_SQL")
echo "----------------------------------------------------------------------------------------------------------------------------------";echo "                                                         *** TOP SQL ***";echo "----------------------------------------------------------------------------------------------------------------------------------";exit|sqlplus -S '/ as sysdba' @$SQL >$SQL_LOG;cat $SQL_LOG
;;
"LATCHES")
echo "----------------------------------------------------------------------------------------------------------------------------------";echo "                                                         *** LATCHES ***";echo "----------------------------------------------------------------------------------------------------------------------------------";exit|sqlplus -S '/ as sysdba' @$LATCH >$LATCH_LOG;cat $LATCH_LOG
;;
"DATAFILE_IOSTAT")
echo "----------------------------------------------------------------------------------------------------------------------------------";echo "                                                         *** DATAFILE IO ***";echo "----------------------------------------------------------------------------------------------------------------------------------";exit|sqlplus -S '/ as sysdba' @$DFIO >$DFIO_LOG;cat $DFIO_LOG|grep -v 'SP';
;;
"SEGMENT")
echo "----------------------------------------------------------------------------------------------------------------------------------";echo "                                                         *** IF YOU HAVE HIGH IO ON DATAFILE, ENTER SEGMENT ID ***";echo "---------------------------------------------------------------------------------------------------------------------------------";exit|sqlplus -S '/ as sysdba' @$SEGMENT >$SEGMENT_LOG;cat $SEGMENT_LOG;
;;
"Exit")
echo "=====> End of script"; break
;;
*) echo "Enter correct option"
;;
esac
done
}


#_check_alert_log(){
#cd $
#grep 'ORA-' alert_$ORACLE_SID.log;if [ $?=0 ]; then cat alert_$ORACLE_SID.log|grep 'ORA-'|tail -1;fi
#}

_more_than_5_lock(){
exit|$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' @$5LOCKS_SQL > $5LOCKS_LOG >/dev/null 2>&1
if [ `awk '{print $4}' $5LOCKS_LOG|grep -v 'BLOCKEE'|grep -v -|grep '.'|wc -l` -gt 4 ];then
cat $5LOCKS_LOG|mailx -s "TEST:`hostname` : There are atleast 5 locks on dhsub " AT20548@hyundai-autoever.com
fi
}


_log_
_scripts_
_more_than_5_lock
_rapid_triage