近期维护的某系统oracle数据库发生一个故障,出现oracle的process进程被占满的情况,
报错信息如下: ORA-00020:maximum number of processes (512)
为防止应用系统因为oracle性能发生故障,及时对oracle的process使用情况进行监控和预警,特写了段python监控和预警程序,在生产系统上部署。
监控代码customer.py如下:
#!/usr/bin/python
# coding=utf-8
'''
Created on 2018-9-29
Target: monitor oracle process
@author: yujianfeng
'''
from datetime import date
import sys
import os
import commands
from common import *
def oracle_proc_chk(*args, **kw):
funcName = get_func_name() # 函数名称
check_path=args[0]
warn_num=args[1]
error_num=args[2]
msg = []
filename="sqltemp.txt"
filename1= "spool.txt"
sql_file=os.path.join(check_path, filename)
spool_file = os.path.join(check_path, filename1)
sqltext = "sqlplus / " + " <<! >/dev/null \n" \
+ "column tablespace_name format a20;\n" + "column 'used %' format a20;\n" \
+ "set echo off;\n" + "set feedback off;\n" + "set heading off;\n" \
+ "spool spool.txt;\n" \
+ "select count(*) from v\$process;\n" \
+ "select limit_value from v\$resource_limit where resource_name='processes';\n" \
+ "quit;\n" + "!\n"
sqltmp= open(sql_file, 'w')
sqltmp.write(sqltext)
sqltmp.close()
(status, output) = commands.getstatusoutput("cd /home/cxwh/xtwh/kyxjc/tmp/;sh sqltemp.txt")
# print status,output
list = []
with open(spool_file, "r") as f1:
list = f1.readlines()
for i in range(len(list)):
num = float(list[2].split("\n")[0])
max_num = float(list[5].split("\n")[0])
#print num, max_num
cur_cent = "%.3f%%" % (num/max_num*100)
#print "当前进程使用比例:%s" % cur_cent
cur_value=(num/max_num*100)
#print cur_value
if cur_value > error_num:
msg.append("E|oracle当前进程使用比例超过:%s,oracle的process最大值需要及时进行调整,请关注|%s|||" % (cur_cent, funcName))
elif cur_value> warn_num:
msg.append("W|oracle:%s|%s|||" % (cur_cent, funcName))
else:
msg.append("N|oracle当前进程使用比例正常:%s|%s|||" % (cur_cent,funcName))
return msg
if __name__ == "__main__":
msg=oracle_proc_chk("/home/cxwh/xtwh/kyxjc/tmp", 80, 90)
for s in msg:
print s
复制代码
执行情况:
#python customer.py
N|oracle当前进程使用比例正常:33%
若为故障级别
E|oracle当前进程使用比例超过:92%,oracle的process最大值需要及时进行调整,请关注!
复制代码
希望通过程序能够对系统及时预警监控,通过脚本能真正解决维护中一些问题。