#!/bin/bash
#### Script for sending mail when lock in the database #####
### For adding new database tnsname.ora should be updated and add SID value for the database
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:$PATH
export SCRIPT_DIR=/u01/app/oracle/util/scripts
export LOG_DIR=/u01/app/oracle/util/log
export TNS_ADMIN=/u01/app/oracle/util/scripts
CURR_DATE=`date '+%m/%d/%y_%H:%M'`
if [ -e ${LOG_DIR}/lock_status.txt ]
then
`rm ${LOG_DIR}/lock_status.txt `
fi
if [ -e ${LOG_DIR}/lock_output.log ]
then
`rm ${LOG_DIR}/lock_output.log `
fi
html_head="Subject: All DB LOCK Report for ${CURR_DATE}
\nMIME-Version: 1.0 \nContent-Type: text/html \nContent-Disposition: inline \n
<html>
<head>
<meta http-equiv=\"Content-Type\" content=text/html; charset=\"UTF-8\">
<meta name=\"generator\" content=\"SQL*Plus 11.2.0\">
<style type=\"text/css\">
body {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif;
color:Black; background:#f7f7e7;
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif;
color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:13pt Arial,Helvetica,Geneva,sans-serif;
color:#336699;text-decoration:underline;background-color:White;
border-bottom:1px solid #cccc99;
margin-top:0pt; margin-bottom:0pt;
padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;
color:#336699; background-color:White;
margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff;
margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>
<title>
lock Report on ${CURR_DATE}
</title>
</head>"
html_tail='<br><a><br>
<br> </a></body></html>'
touch ${LOG_DIR}/lock_status.txt
echo "<br><br>" >> ${LOG_DIR}/lock_status.txt
for SID in DBSID1 DBSID2 DBSID3
do
echo $SID
sqlplus -s username/password@${SID} << EOF
set feed off term off trims on linesize 300 pages 300 echo off underline off heading on
set markup html on
spool ${LOG_DIR}/lock_output.log
SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
|| ' User '
||s1.username
|| '@'
|| s1.machine
|| ' ( SID= '
|| s1.sid
|| ' ) with the statement: '
|| sqlt2.sql_text
||' is blocking the SQL statement on '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.sid
|| ' ) blocked SQL -> '
||sqlt1.sql_text AS blocking_status
FROM v\$lock l1, v\$session s1, v\$lock l2 ,
v\$session s2,v\$sql sqlt1, v\$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2
/
spool off
exit
EOF
reported_var=`grep 'is blocking' ${LOG_DIR}/lock_output.log | wc -l`
if [[ $reported_var > 0 ]];
then
found_error=1
echo "<br><h1>THE lock report for the Database: ${SID} </h1><br>" >> ${LOG_DIR}/lock_status.txt
cat ${LOG_DIR}/lock_output.log |grep -v 'SQL>'|grep -v 'SQL>' >> ${LOG_DIR}/lock_status.txt
fi
done
if [ -e ${LOG_DIR}/LOCK_REPORT.html ]
then
`rm ${LOG_DIR}/LOCK_REPORT.html `
fi
echo -e $html_head > ${LOG_DIR}/LOCK_REPORT.html
cat ${LOG_DIR}/lock_status.txt >> ${LOG_DIR}/LOCK_REPORT.html
echo $html_tail >> ${LOG_DIR}/LOCK_REPORT.html
rm ${LOG_DIR}/lock_status.txt
rm ${LOG_DIR}/lock_output.log
if [[ $found_error > 0 ]];
then
sendmail -v username@mailid.com < ${LOG_DIR}/LOCK_REPORT.html
fi
No comments:
Post a Comment