def monitor_tablespace(ora,ts_val):
try:
#Oracle主机列表查询
host_list = ora.Query("select priip1 as hostname from hosts t1 inner join options t2 on(t1.role = t2.id and t2.val = 'Oracle') order by hostname",["hostname"])
for i in host_list:
host = i['hostname']
#数据库配置取得
db_info = ora.Query('''select db_host,db_port,db_sid,db_user,db_password,ts_id from db_info where db_host = '%s' ''' % (host),["db_host","db_port","db_sid","db_user","db_password","ts_id"])
#监控表空间名取得
if db_info:
ts_list = ora.Query('''select ts_name from tablespaces where ts_id = '%s' and ctl_flag = 1 ''' % (db_info[0]['ts_id']),["ts_name"])
#需要监控的表空间有时
if ts_list:
#TNS字符串
tns = '''(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)
(HOST=%s)(PORT=%s)))
(CONNECT_DATA=(SERVICE_NAME=%s)))''' % ( db_info[0]['db_host'], db_info[0]['db_port'], db_info[0]['db_sid'])
#创建oracle连接
ora_m = cxOracle(db_info[0]['db_user'], db_info[0]['db_password'], tns)
#查询表空间的使用率
for j in ts_list:
used = ora_m.Query('''
select a.tablespace_name, round(((total - free) / total) * 100, 2) as used
from (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 as total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = '%s' ''' % (j['ts_name']),["tablespace_name","used"])
if used:
#服务器信息
id = create_id()
server_info = [id]
server_info.append(db_info[0]['db_host'])
server_info.append('4') #表空间使用率
server_info.append(j['ts_name'] + '空间使用率')
server_info.append(str(used[0]['used']) +"%")
#服务器信息插入
insert_server_info(ora,server_info)
#超过阈值时，插入服务器警告信息表
if float(used[0]['used']) > float(ts_val):
insert_server_warning_info(ora,id,ts_val)
#关闭连接
ora_m.Close()
#事务提交
ora.commit()
except Exception, e:
#事务回滚
ora.rollback()
print str(e)
def loop_mornitor_net():
#创建oracle连接
ora = cxOracle(user, pwd, tns)
#数据监控
mornitor.monitor_net(ora, ping_list)
#关闭oracle连接
ora.Close()
print "服务器监控【网络状态】"
#定时监控启动(网络状态)
Timer(1225, loop_mornitor_net).start()
def loop_mornitor_index():
#创建oracle连接
ora = cxOracle(user, pwd, tns)
#数据监控
mornitor.monitor_index(ora)
#关闭oracle连接
ora.Close()
print "服务器监控【索引失效】"
#定时监控启动(DB表空间)
Timer(625, loop_mornitor_index).start()
def loop_mornitor_ts():
#创建oracle连接
ora = cxOracle(user, pwd, tns)
#数据监控
mornitor.monitor_tablespace(ora, tablespace_val)
#关闭oracle连接
ora.Close()
print "服务器监控【DB表空间】"
#定时监控启动(DB表空间)
Timer(1845, loop_mornitor_ts).start()
def loop_mornitor_disk():
#创建oracle连接
ora = cxOracle(user, pwd, tns)
#数据监控
mornitor.monitor_disk(ora, disk_val)
#关闭oracle连接
ora.Close()
print "服务器监控【硬盘】"
#定时监控启动(硬盘)
Timer(1830, loop_mornitor_disk).start()
def loop_mornitor_status():
#创建oracle连接
ora = cxOracle(user, pwd, tns)
#数据监控
mornitor.monitor_status(ora, cpu_val, memory_val)
#关闭oracle连接
ora.Close()
print "服务器监控【CPU、内存】"
#定时监控启动(CPU、内存)
Timer(60, loop_mornitor_status).start()
def loop_mail():
#创建oracle连接
ora = cxOracle(user, pwd, tns)
#邮件发送
mail.send_mail(ora, mail_host, mail_user, mail_pass, mailto_list,
att_file_path)
#关闭oracle连接
ora.Close()
print "邮件发送"
#定时邮件启动
Timer(3600, loop_mail).start()
def monitor_index(ora):
try:
#Oracle主机列表查询
host_list = ora.Query("select priip1 as hostname from hosts t1 inner join options t2 on(t1.role = t2.id and t2.val = 'Oracle') order by hostname",["hostname"])
for i in host_list:
host = i['hostname']
#数据库配置取得
db_info = ora.Query('''select db_host,db_port,db_sid,db_user,db_password,ts_id from db_info where db_host = '%s' ''' % (host),["db_host","db_port","db_sid","db_user","db_password","ts_id"])
#失效索引查询
if db_info:
#TNS字符串
tns = '''(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)
(HOST=%s)(PORT=%s)))
(CONNECT_DATA=(SERVICE_NAME=%s)))''' % ( db_info[0]['db_host'], db_info[0]['db_port'], db_info[0]['db_sid'])
#创建oracle连接
ora_m = cxOracle(db_info[0]['db_user'], db_info[0]['db_password'], tns)
#查询SQL
sql = ''' Select owner as index_owner, index_name
From dba_indexes
where status = 'UNUSABLE'
and owner not in ('SYS',
'SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'FLOWS_030000',
'FLOWS_FILES')
union
select index_owner, index_name
from dba_ind_partitions
where status = 'UNUSABLE'
and index_owner not in ('SYS',
'SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'FLOWS_030000',
'FLOWS_FILES')
union
Select Index_Owner, Index_Name
From DBA_IND_SUBPARTITIONS
Where status = 'UNUSABLE'
and index_owner not in ('SYS',
'SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'FLOWS_030000',
'FLOWS_FILES')
'''
index_list = ora_m.Query(sql,["index_owner","index_name"])
#将失效索引插入索引信息表
for j in index_list:
#索引信息
id = create_id()
index_info = [id]
index_info.append(db_info[0]['db_host'])
index_info.append(db_info[0]['db_sid'])
index_info.append(db_info[0]['db_user'])
index_info.append(j['index_owner'])
index_info.append(j['index_name'])
#失效索引信息插入
insert_index_warning_info(ora,index_info)
#关闭连接
ora_m.Close()
#事务提交
ora.commit()
except Exception, e:
#事务回滚
ora.rollback()
print str(e)