Oracle Processes Monitor Shall Script - Generic

Processes Monitor Shall Script - Generic

Processes Monitor shall script which can get all the highly utilized session as per your coded resource process.........

  

Script ###

-- -------------------------------------------------------------------------
-- Author       : Amit Kumar Srivastava
-- Description  : Process Monitor shall script which can get all the highly 
--                utilized session as per your coded resource process......
-- Last Modified: 04-FEB-2017
-- -------------------------------------------------------------------------

[oracle@vm215 ~]$ cat process_mon.sh
for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
   do
    export ORACLE_SID

# #########################
# Getting ORACLE_HOME
# #########################
  ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|grep -v ASM|awk '{print $1}'|tail -1`
  USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`

## If OS is Linux:
if [ -f /etc/oratab ]
  then
  ORATAB=/etc/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
  ORATAB=/var/opt/oracle/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME
fi

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user's profile:
if [ -z "${ORACLE_HOME}" ]
 then
  ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
  export ORACLE_HOME
fi


PROCESSDATA=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 0 echo off feedback off heading off
SELECT CURRENT_UTILIZATION FROM V_\$RESOURCE_LIMIT WHERE RESOURCE_NAME='processes';
exit;
EOF
)


p=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 200 echo on feedback off heading on
spool /tmp/process_log.log
select * from v_\$resource_limit where resource_name in ('process','session') ;
spool off
exit;
EOF
)

s=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 200 echo on feedback off heading on
spool /tmp/session_log.log
select username, count(*), status  from v_\$session group by username, status ;
spool off
exit;
EOF
)


#echo '_______________________________________'
#cat /tmp/process_log.log
#cat /tmp/session_log.log


echo $ORACLE_SID '***************************'

 if [ "$PROCESSDATA" -ge 599  ];
  then
    echo ' USER PROCESS IS HIGH : '$PROCESSDATA
    echo ' MAIL SEND TO YOUR DL...'
    cat  /tmp/session_log.log

elif [ "$PROCESSDATA" -lt 600 ];

  then
    echo  'USER PROCESS IS NORMAL '$PROCESSDATA
 fi
done



echo '***************************'