脚本的思路是这样:取查询死锁的结果到1.txt,然后取出sid和serial#字段值到2.txt,过10分钟后,再取一次死锁的结果值到1.txt然后取sid值到3.txt. 比对2.txt和3.txt的值,如果有相同的,就进行kill操作。同时写kill日志到kill.log里。
写两个脚本文件放到/home/oracle/下,分别为1.sh和2.sh,内容如下:
cat 2.sh -->这个脚本是要sqlplus调用的,不是linux要使用运行的。
spool /tmp/1.txt #1.txt是随意指定的空文件,后面的select查询会写到这个文件里。
select b.username,b.sid,b.serial#,a.object_id,logon_time \
from v$locked_object a,v$session b where a.session_id = b.sid \
and b.machine in ('www.rdp.com','wwww.rdp.com','WIN-VSQ10G3GF83','jdbcclient','fxdb1') \
and a.object_id in (select object_id from all_objects where owner like 'RDP%') \
order by b.logon_time;
spool off
exit;
备注:b.machine in ('www.rdp.com','wwww.rdp.com','WIN-VSQ10G3GF83','jdbcclient','fxdb1') 是要排除自己人。
a.object_id in (select object_id from all_objects where owner like 'RDP%') ---后加的
cat 1.sh
#!/bin/bash
sqlplus user/password@dbip:1521/zfbh @/home/oracle/2.sh
cat /tmp/1.txt |grep -v RDP|grep -v SID|grep -v USERNAME|grep -v '--'|grep -v "no"|grep -v "row"|awk NF|awk '{print $1}' >/tmp/2.txt
sleep 600
sqlplus user/password@dbip:1521/zfbh @/home/oracle/2.sh
cat /tmp/1.txt |grep -v RDP|grep -v SID|grep -v USERNAME|grep -v '--'|grep -v "no"|grep -v "row"|awk NF|awk '{print $1}' >/tmp/3.txt
for n1 in cat /tmp/2.txt
do
for m1 in cat /tmp/3.txt
do
if [ $m1 -eq $n1 ];then
m2=cat /tmp/1.txt|grep $m1|awk '{print $2}'
m3=cat /tmp/1.txt|grep $m1|awk '{print $3}'
sleep 60
echo "datekill the lock sid:$m1,serial :$m2, object_id:$m3" >>/tmp/kill.log
sqlplus user/password@dbip:1521/zfbh <>/tmp/x.log
alter system kill session'$m1,$m2';
exit;
EOF #EOF要顶格写
break
fi
done
done
另外要注意定时任务的写法,由于 crontab里面的脚本,通常读取的是默认的环境变量,PATH里面不包含oracle数据库的路径。
解决办法:
crontab -l
/10 * source /home/oracle/.bash_profile; sh /home/oracle/1.sh
否则会出现:
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
这样的错误。