- 论坛徽章:
- 0
|
[原创]15.监控oracle是否有锁。\r\n#mon_db_lock.sh\r\n. .profile\r\ncd /home/mxin/mon\r\nif [ `date +%H%M` = \"0800\" ]; then\r\necho 0 > warn_count \r\nfi \r\nwarn_count=`cat warn_count`\r\nsqlplus \"/as sysdba\" <<EOF\r\nset feed off;\r\nset heading off;\r\nspool /tmp/db_lock.out1;\r\n@mon_db_lock.sql\r\nspool off;\r\nexit\r\nEOF\r\nsleep 13 \r\nsqlplus \"/as sysdba\" <<EOF\r\nset feed off;\r\nset heading off;\r\nspool /tmp/db_lock.out2;\r\n@mon_db_lock.sql\r\nspool off;\r\nexit\r\nEOF\r\nsleep 27\r\nsqlplus \"/as sysdba\" <<EOF\r\nset feed off;\r\nset heading off;\r\nspool /tmp/db_lock.out3;\r\n@mon_db_lock.sql\r\nspool off;\r\nexit\r\nEOF\r\ncat /tmp/db_lock.out1|grep -v SQL|grep [0-9] >/tmp/mon_db_lock.out1\r\ncat /tmp/db_lock.out2|grep -v SQL|grep [0-9] >/tmp/mon_db_lock.out2\r\ncat /tmp/db_lock.out3|grep -v SQL|grep [0-9] >/tmp/mon_db_lock.out3\r\ncat /tmp/mon_db_lock.out1|grep -v SQL|grep [0-9]|awk \'{print \"grep \\\"\"$0\"\\\" /tmp/mon_db_lock.out2\"}\'|sh >/tmp/db_lock1\r\nif [ `cat /tmp/db_lock1|wc -l` -gt 0 ] ; then \r\n cat /tmp/mon_db_lock.out2|grep -v SQL|grep [0-9]|awk \'{print \"grep \\\"\"$0\"\\\" /tmp/mon_db_lock.out3\"}\'|sh >/tmp/db_lock2\r\n if [ `cat /tmp/db_lock2|wc -l` -gt 0 ] ; then \r\n cat /tmp/mon_db_lock.out3|grep -v SQL|grep [0-9]|awk \'{print \"grep \\\"\"$0\"\\\" /tmp/mon_db_lock.out.old\"}\'|sh >/tmp/db_lock3\r\n if [ `cat /tmp/db_lock3|wc -l` -gt 0 ] ; then \r\n cat /tmp/db_lock3|awk \'{print \"wall db lock-------\\\" \"$0 \"\\\"!!\" }\'|sh\r\n let warn_count=$warn_count+1\r\n fi\r\n fi\r\nfi\r\ncp /tmp/mon_db_lock.out3 /tmp/mon_db_lock.out.old\r\nif [ $warn_count -gt 4 ] ; then\r\n beep.sh\r\n echo 0 > warn_count\r\nfi\r\n\r\n\r\n\r\n#mon_db_lock.sql\r\n\r\n\r\nset linesize 256\r\ncol object_name format a18\r\ncol object_id format 99999999\r\ncol Locked_Mode format a15\r\ncol SERIAL# format 9999999\r\ncol session_id format 999999\r\ncol oracle_username format a15\r\ncol os_user_name format a15\r\ncol process format 9999999\r\n \r\nSELECT substr(b.object_name,1,1 object_name,a.object_id, \r\ndecode( a.locked_mode, \r\n0, \'None\', /* Mon Lock equivalent */ \r\n1, \'Null\', /* N */ \r\n2, \'Row-S (SS)\', /* L */ \r\n3, \'Row-X (SX)\', /* R */ \r\n4, \'Share\', /* S */ \r\n5, \'S/Row-X (SSX)\', /* C */ \r\n6, \'Exclusive\', \r\na.locked_mode) Locked_Mode, /* X */ \r\nsession_id, SERIAL#,oracle_username, os_user_name, a.process \r\nFROM v$LOCKED_OBJECT a, dba_OBJECTS b,v$session c\r\nWHERE a.object_id = b.object_id and a.session_id=c.sid\r\n/ |
|