数据库监控系统小实现（监控系统数据库设计）

需求：通过java 去调取python 去目标数据库采集信息，然后插入到数据库，由前台UI显示出来，从而达到监控目的。

设计：通过java的Runtime.getRuntime().exec(args1)方法去调用python脚本，java 传递数据库ip地址串给python脚本，python可以通过CX_Oracle或者paramiko都可以连接到目标数据库，cx_Oracle 是Oracle OCI接口；而paramiko 是ssh协议接口，先通过ssh登录服务器,然后调用sqlplus 命令进到oracle 。

流程：java下发任务，python 接受参数，通过循环IP地址链接到主机root，然后su切换到oracle账户 通过slqplus 执行sql指标采集，然后把返回结果格式化之后写到本地sql文件，最后通过执行本地sql文件完成数据库本地持久化。

问题1：流程太过繁琐，由于sqlplus返回结果集不规则，往往需要经过大量处理之后才能正确得到结果

解决方案：所以想通过

insert into tables values as select * from tables;方式来插入数据库，这种方式可以避免sqlplus返回结果不规则的问题，也避免写文件的io支出。

这样就需要在远端数据库有一个本地的tns连接串或者在本地有一个远端数据库的一个dblink；另外还需要一个数据库账户，可以查询底层v_$的权限。

在本地有一个远端数据库的一个dblink比较好实现，但是有一个问题需要注意，采集某一个指标需要构造所有的数据库查询sql语句，应为表名发生变化。

create database link db1_lk connect to ovsee identified by crbc1234 using 'DB1';

由于dblink 会导致数据库scn暴增的bug，所以通过dblink 这个方法不通。

基于以上的考虑，所以这里使用的是cx_oracle方式。

java代码：

package java2python;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Date;
public class jtp {
 /*
 * java调用python脚本
 */
	static SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
 public static void main(String[] args) {
 try {
 
 System.out.println("start");
 String ips="[{IPS=10.241.95.250,10.241.95.40}]";
 String[] args1=new String[]{"python","E:\\APP\\eclipse-jee-oxygen-3a-win32\\workspace\\java2python\\src\\java2python\\DGonAddCheck2.py",ips,format.format(new Date())};
 Process pr=Runtime.getRuntime().exec(args1);
 
 BufferedReader in = new BufferedReader(new InputStreamReader(
 pr.getInputStream()));
 String line;
 while ((line = in.readLine()) != null) {
 System.out.println(line);
 }
 in.close();
 pr.waitFor();
 System.out.println("end");
 } catch (Exception e) {
 e.printStackTrace();
 }}
 public void test(){
 System.out.println("我的第一个方法C");
 }
 
}

python代码：由于监控数据库是一个公共的平台，所以数据库监控的sql语句 最好支持客户端上传方式，所以这里就需要python去遍历制定目录下的*.sql文件读取里面的sql语句。还有一个坑 就是在python脚本里面写数据库监控sql 需要转义，特别麻烦，所以直接读取*.sql文件可以避免这个坑。

#encoding:utf-8
#-*- coding: UTF-8 -*-
#!/usr/bin/python
#coding=UTF-8
import paramiko
import os
import time
import sys
import cx_Oracle
"""
功能：巡检数据库指标数据入库
实现方法：
1.ip地址前台传过来的，后台按照ip地址和定义好的指标去查询数据库获取数据
2.数据量比较小，但是需要及时入库，入库要可靠，执行频率高;所以需要根据场景不同设计不同的入库方案。
数据量大的话可以通过sqlplus 执行文件来实现数据入库目的
数据量小的话可以通过cx_oracle来实现数据入库目的
3.使用sqlplus插入有两种方法：（1）采用tns方式连接数据库插入数据
（ 2）采用su 切换用户使用os认证执行sqlplus插入数据
"""
"""
写文件
输入参数 list[],
文件名
"""
listinfo=[]
listerr=[]
check_times=str(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
times='' 
def write_result(liststr,filename):
 if len(liststr):
 fileType=filename.split('.')[1]
 if(fileType == 'sql'):
 #定义文件路径
 writeresult=file('/tmp/'+filename,'a+')
 for str in liststr:
 writeresult.write(str+'\n')
 else:
 #定义文件路径
 writeresult=file('/tmp/'+filename,'a+')
 for str in liststr:
 writeresult.write(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())+'['+fileType+']: '+str+'\n')
 writeresult.close()
def readfile(path):
 list=[]
 list=listdir(path,list)
 for file in list:
 f1=open(file,'r')
 values=f1.read()
 sqls=[]
 sqls=values.split(';')
 
# print sqls
 return sqls
 f1.close
 
 
def listdir(path, list_name): #传入存储的list
 for file in os.listdir(path): 
 if(file.endswith('.sql')):
 file_path = os.path.join(path, file) 
 if os.path.isdir(file_path): 
 listdir(file_path, list_name) 
 else: 
 list_name.append(file_path) 
 return list_name
 
def insertBatch(sql,nameParams,imsdb,username,passwd,port,tnsnames):
 conn=getconn(imsdb,username,passwd,port,tnsnames)
 cr=conn.cursor()
 cr.prepare(sql)
 print 'sql '+sql
 print 'para '+str(nameParams)
 cr.executemany(None,nameParams)
 conn.commit()
 cr.execute("SELECT COUNT(*) FROM dgcheck")
 
 print(cr.fetchone())
 cr.close()
 conn.close()
 
 
def getconn(username,passwd,tnsnames):
 #tnsnames命名为ip地址
# tns=cx_Oracle.makedsn(imsdb,port,tnsnames)
 #tns=''
 conn=cx_Oracle.connect(username,passwd,tnsnames)
# connstr=username+'/'+passwd+'@'+tnsnames
# conn=cx_Oracle.connect(connstr)
 return conn
 
def executedql(sql,ip):
 conn=getconn('dbmgr','123456',ip)
 cr=conn.cursor()
 
 #insertBatch(sql,para,conn)
 cr.execute(sql)
 res=cr.fetchall()
# print(res)
 return res
# for data in res:
# print data
 
 
 #print('insert batch ok.') 
 cr.close()
 conn.close()
 
 
"""
获取ssh2客户端连接服务器ip地址,返回巡检data列表
"""
def sshcmd(ip):
 #listinfo.append('ip '+ip)
 
 execmd1 = "ps -ef|grep smon |grep -v grep|grep ora_|awk '{print $1}'" #判断oracle当前属主
 v="select * from v$version"
 tbs=' SELECT a.tablespace_name ,round(b.maxbytes/1024/1024/1024,2) maxbyes_GB,round(total/1024/1024/1024,2) bytes_GB,round(free/1024/1024/1024,2) free_GB,round((total-free)/1024/1024/1024,2) use_GB, ROUND((total-free)/total,4)*100 "use_%",ROUND((total-free)/b.maxbytes,4)*100 "maxuse_%" FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name ) a, (SELECT tablespace_name,sum(case autoextensible when "YES" then maxbytes else bytes end) maxbytes,SUM(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b WHERE a.tablespace_name=b.tablespace_name order by "maxuse_%" desc'
 meminfo_sga="select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from (select '\"'SGA'\"' name,(select sum(value/1024/1024) from v\$sga) total,(select sum(bytes/1024/1024) from v\$sgastat where name='\"'free memory'\"')free from dual) order by pctused desc;"
 meminfo_pga="select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (select '\"'PGA'\"' name,(select value/1024/1024 total from v\$pgastat where name='\"'aggregate PGA target parameter'\"')total,(select value/1024/1024 used from v\$pgastat where name='\"'total PGA allocated'\"')used from dual) order by pctused desc;"
 meminfo_shared_pool="select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (select '\"'Shared pool'\"' name,(select sum(bytes/1024/1024) from v\$sgastat where pool='\"'shared pool'\"')total,(select bytes/1024/1024 from v\$sgastat where name='\"'free memory'\"' and pool='\"'shared pool'\"') free from dual) order by pctused desc;"
 meminfo_default_pool="select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (select '\"'Default pool'\"' name,( select a.cnum_repl*(select value from v\$parameter where name='\"'db_block_size'\"')/1024/1024 total from x\$kcbwds a, v\$buffer_pool p where a.set_id=p.LO_SETID and p.name='\"'DEFAULT'\"' and p.block_size=(select value from v\$parameter where name='\"'db_block_size'\"')) total,(select a.anum_repl*(select value from v\$parameter where name='\"'db_block_size'\"')/1024/1024 free from x\$kcbwds a, v\$buffer_pool p where a.set_id=p.LO_SETID and p.name='\"'DEFAULT'\"' and p.block_size=(select value from v\$parameter where name='\"'db_block_size'\"')) free from dual) order by pctused desc;"
 meminfo_keep_pool="select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (select '\"'KEEP pool'\"' name,(select a.cnum_repl*(select value from v\$parameter where name='\"'db_block_size'\"')/1024/1024 total from x\$kcbwds a, v\$buffer_pool p where a.set_id=p.LO_SETID and p.name='\"'KEEP'\"' and p.block_size=(select value from v\$parameter where name='\"'db_block_size'\"')) total,(select a.anum_repl*(select value from v\$parameter where name='\"'db_block_size'\"')/1024/1024 free from x\$kcbwds a, v\$buffer_pool p where a.set_id=p.LO_SETID and p.name='\"'KEEP'\"' and p.block_size=(select value from v$parameter where name='\"'db_block_size'\"')) free from dual) order by pctused desc;"
 meminfo_recycle_pool="select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (select '\"'RECYCLE pool'\"' name,( select a.cnum_repl*(select value from v\$parameter where name='\"'db_block_size'\"')/1024/1024 total from x\$kcbwds a, v\$buffer_pool p where a.set_id=p.LO_SETID and p.name='\"'RECYCLE'\"' and p.block_size=(select value from v$parameter where name='\"'db_block_size'\"')) total,(select a.anum_repl*(select value from v\$parameter where name='\"'db_block_size'\"')/1024/1024 free from x\$kcbwds a, v\$buffer_pool p where a.set_id=p.LO_SETID and p.name='\"'RECYCLE'\"' and p.block_size=(select value from v\$parameter where name='\"'db_block_size'\"')) free from dual) order by pctused desc;"
 meminfo_16k_buffercahce="select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(select '\"'DEFAULT 16K buffer cache\"'' name,(select a.cnum_repl*16/1024 total from x\$kcbwds a, v$buffer_pool p where a.set_id=p.LO_SETID and p.name='\"'DEFAULT'\"' and p.block_size=16384) total, (select a.anum_repl*16/1024 free from x\$kcbwds a, v\$buffer_pool p where a.set_id=p.LO_SETID and p.name='\"'DEFAULT'\"' and p.block_size=16384) free from dual) order by pctused desc;"
 meminfo_java_pool="select name,total,total-free used,free, (total-free)/total*100 pctused from (select '\"'Java Pool\"'' name,(select sum(bytes/1024/1024) total from v\$sgastat where pool='\"'java pool'\"' group by pool)total,( select bytes/1024/1024 free from v\$sgastat where pool='\"'java pool'\"' and name='\"'free memory'\"')free from dual) order by pctused desc;"
 meminfo_large_pool="select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (select '\"'Large Pool'\"' name,(select sum(bytes/1024/1024) total from v\$sgastat where pool='\"'large pool'\"' group by pool)total,( select bytes/1024/1024 free from v\$sgastat where pool='\"'large pool'\"' and name='\"'free memory'\"')free from dual) order by pctused desc;"
 
 disk_io_undo="select d.username,c.name,b.writes from v\$transaction a,v\$rollstat b,v\$rollname c,v\$session d where d.taddr=a.addr and a.xidusn=b.usn and b.usn=c.usn order by d.username;"
 disk_io_tbs="select ts.name tablespace,fs.phyrds ""\"reads""\",fs.phywrts ""\"writes""\",fs.phyblkrd ,fs.phyblkwrt,fs.readtim ""\"rtime""\",fs.writetim ""\"wtime""\" from v\$tablespace ts,v\$datafile df,v\$filestat fs where ts.ts#=df.ts# and df.file#=fs.file#;"
 disk_io_tmp="select ts.name,ts.phyrds ""\"reads""\",ts.phywrts ""\"writes""\",ts.phyblkrd,ts.phyblkwrt,ts.readtim ""\"rtime""\",ts.writetim ""\"wtime""\" from v\$tablespace ts,v\$tempfile tf ,v\$tempstat ts where ts.ts#=tf.ts# and tf.file#=ts.file#;" 
 disk_io_datafile="select name,phyrds,phywrts,readtim,writetim from v\$filestat a,v\$datafile b where a.file#=b.file# order by readtim desc;"
 
 cpu_busy_usage="select value from v\$osstat where stat_name='\"'BUSY_TIME'\"';"
 cpu_usage="select * from v\$osstat;"
 
 sqls=readfile("E:\APP\eclipse-jee-oxygen-3a-win32\workspace\java2python\src\java2python")
 #print sql
 if(ip <> ""): 
 for sql in sqls:
 if sql =='':
 continue
 else:
 print executedql(sql,ip)
 
 else:
 print "ip is NULL!"
 
 
 
"""
前台传输ip地址列表，考虑到数据有可能会非常大，所以需要批量入库
使用cx_oracle模块，可以使用绑定变量方式入库，效率会比较高；但是多了一层cx_oracle驱动包连接数据库。
如果使用sqlplus 可以直接入库，但是无法绑定变量插入数据。
""" 
def getValues(ips):
 print 'ips:'+str(ips)
 
 for ip in ips:
 try:
 sshcmd(ip)
 except Exception:
 print ip+'获取信息失败'
 continue
 
#获取java 传参 
def main():
 args1=''
 args2=''
 for i in range(1,len(sys.argv)):
 args1 = str(sys.argv[1])
 args2 = str(sys.argv[2])
 print ' args1 '+args1
 print ' args2 '+args2
 #listinfo.append('input args1 '+args1)
 #listinfo.append('input args2 '+args2)
 ips=args1.replace('[{', '').replace('IPS=','').replace('}]','').replace(' ','').split(',')
 global times
 times=args2
 filename=args2.replace(" ","")+'_dgAddoncheck.sql'
 print 'filename '+filename
 print 'ips ' +str(ips)
 #write_result(listinfo,'/dataguard_Addoncheck.info')
 #write_result(listerr,'/dataguard_Addoncheck.error') 
 getValues(ips) 
 
main()

返回结果：

[(0, 'SYSTEM', 'YES', 'NO', 'YES', None), (1, 'SYSAUX', 'YES', 'NO', 'YES', None), (2, 'UNDOTBS1', 'YES', 'NO', 'YES', None), (3, 'TEMP', 'NO', 'NO', 'YES', None), (4, 'USERS', 'YES', 'NO', 'YES', None)]

可以通过拼接sql 插入到数据库，也可以使用db link 直接插入到数据库。

dblink 方式类似 :

create database link db1_lk connect to user1 identified by crbc1234 using 'ORCL';

insert into db_info values as select name form v$tablespace@db1_lk ,

这样的好处是 不需要对结果集单独处理而直接插入到数据库。

这个是我成品的监控系统界面，后面我会把系统贴上来供大家免费使用。