Oracle : Script for Email alert for Blocking sessions in Database.

Oracle : Script for Email alert for Blocking sessions in Database.

#####################
view /u01/app/oracle/admin/orcl/scripts/DBA_Blocking_Sess.sh
#!/bin/ksh
set -x
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db_1
export ORACLE_SID=$1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.2/db_1/bin

export LOG_FILE=/u01/app/oracle/admin/orcl/scripts/log/orcl_Blocking_Sess_date +%F%s.log

sqlplus -s / as sysdba <<EOF >> $LOG_FILE
@/u01/app/oracle/admin/orcl/scripts/sql/orcl_DBA_Blocking_sess.sql
EOF

chk_count=grep "it's BLOCKING user" $LOG_FILE | wc -l

if [ $chk_count -gt 0 ]; then
mailx -s “orcl : BLOCKING SESSIONS FROM orcl” Admins@gmail123.com,support@gmail123.com < $LOG_FILE
fi

#rm -f /u01/app/oracle/admin/orcl/scripts/log/orcl_Blocking_Sess.log

find /u01/app/oracle/admin/orcl/scripts/log -name ‘orcl_Blocking_Sess_*.log’ -mmin +5 -exec rm -rf {} \;

exit

##################### Cron job for Every Minute 
*/1 * * * * /u01/app/oracle/admin/orcl/scripts/DBA_Blocking_Sess.sh orcl2 >/dev/null
#####################
view /u01/app/oracle/admin/orcl/scripts/sql/orcl_DBA_Blocking_sess.sql
set head off feed off
set pagesize 0
set linesize 200
set long 99999
select
B.USERNAME ||’ User”s Session
(‘||B.SID||’,’||B.SERIAL#||’,@’||B.INST_ID||’
from SERVER ‘||B.MACHINE||’ )
is Currently ‘||B.STATUS||’ for last ‘
||B.LAST_CALL_ET||’ Sec , BLOCKER CLIENT SYSTEM PROCESS: ‘||B.PROCESS|| ‘ and it”s BLOCKING user ‘|| W.USERNAME|| ‘ (‘||W.SID||’,
‘||W.SERIAL#||’,@’||W.INST_ID||’ from SERVER ‘||W.MACHINE||’ Since ‘|| W.SECONDS_IN_WAIT || ‘ Secs) BLOCKED CLIENT SYSTEM PROCESS:’ ||W.PROCESS
from
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET,MACHINE,
SECONDS_IN_WAIT,PROCESS,SQL_ID from gv$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET,MACHINE,TYPE,PROCESS,SQL_ID from gv$session) B
where W.USERNAME not in (‘SYS’,’SYSTEM’,’DBSNMP’)
and W.BLOCKING_INSTANCE=B.INST_ID
and W.BLOCKING_SESSION=B.SID
and W.SECONDS_IN_WAIT > 30
and B.TYPE not in (‘BACKGROUND’);

select ‘BLOCKER SQL: ‘ || C.sql_fulltext ||chr(10)||’ BLOCKED SQL: ‘ || D.sql_fulltext
from
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET,MACHINE,
SECONDS_IN_WAIT,PROCESS,SQL_ID from gv$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET,MACHINE,TYPE,PROCESS,SQL_ID from gv$session) B,
(Select SQL_ID, sql_fulltext from gv$sqlarea) C,
(Select SQL_ID, sql_fulltext from gv$sqlarea) D
where W.USERNAME not in (‘SYS’,’SYSTEM’,’DBSNMP’)
and W.BLOCKING_INSTANCE=B.INST_ID
and W.BLOCKING_SESSION=B.SID
and W.SECONDS_IN_WAIT > 30
and W.SQL_ID=C.SQL_ID
and B.SQL_ID=D.SQL_ID
and B.TYPE not in (‘BACKGROUND’);
#####################

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (16 votes, average: 5.00 out of 5)
Loading...

Add Comment