监控Oracle 连接数

使用python脚本编写脚本,监控oracle连接数;连接Oracle需要安装cx_Oracle模块,安装完成后检查是否正常,没有任何信息返回即正常;

python -c "import cx_Oracle"

通常,还需要加载oralce的客户端配置环境和库

libclntsh.so.11.1
libnnz11.so

1、编写用于检查Oracle当前连接数、最大连接数的python脚本:zabbix_check_process.py

#!/usr/bin/python
# coding:utf-8
#
# check Oracle process
# dbsession: current connect session
# dbprocess: db max connect session
# presession: current connect session precent

import cx_Oracle
import sys

# conn to DB
conn = cx_Oracle.connect('user/passwd@orcl')
cursor = conn.cursor()

dbProcess = {}

# current connect session
sessionSQL = "select count(*) process from v$session"
cursor.execute(sessionSQL)
sessionRow = cursor.fetchone()
dbProcess['dbsession'] = sessionRow[0]

# db max connect session
processSQL = "select value count from v$parameter where name ='processes'"
cursor.execute(processSQL)
proce***ow = cursor.fetchone()
dbProcess['dbprocess'] = proce***ow[0]

# current connect session precent
presession = (dbProcess['dbsession']) / float(dbProcess['dbprocess'])
dbProcess['presession'] = '%.2f' % (presession * 100)
cursor.close()

# close db
conn.close()

#print(dbProcess)

try:
    if sys.argv[1]:
        if sys.argv[1] == 'dbsession':
            print(dbProcess['dbsession'])
        if sys.argv[1] == 'dbprocess':
            print(dbProcess['dbprocess'])
        if sys.argv[1] == 'presession':
            print(dbProcess['presession'])
except:
    print("Usage: %s dbsession |dbprocess |presession" % sys.argv[0])

验证脚本

[zabbix@DB1 script]$ python zabbix_check_process.py
Usage: zabbix_check_process.py dbsession |dbprocess |presession
[zabbix@DB1 script]$ python zabbix_check_process.py dbsession
597
[zabbix@DB1 script]$ python zabbix_check_process.py dbprocess
1000
[zabbix@DB1 script]$ python zabbix_check_process.py presession
59.40

2、在zabbix_agentd上增加UserParamete:/opt/zabbix/etc/zabbix_agentd.conf.d/oracle.conf


## db connect session
UserParameter=oracle.dbsession,source ~/.bash_profile;/usr/bin/python /opt/zabbix/share/script/zabbix_check_process.py dbsession
UserParameter=oracle.dbprocess,source ~/.bash_profile;/usr/bin/python /opt/zabbix/share/script/zabbix_check_process.py dbprocess
UserParameter=oracle.presession,source ~/.bash_profile;/usr/bin/python /opt/zabbix/share/script/zabbix_check_process.py presession

3、在服务端测试(客户端需要重启)

[root@zabbix ~]# zabbix_get -s 192.168.1.10 -p 10050 -k 'oracle.dbsession'
581
[root@zabbix ~]# zabbix_get -s 192.168.1.10 -p 10050 -k 'oracle.presession'
58.10
[root@zabbix ~]# zabbix_get -s 192.168.1.10 -p 10050 -k 'oracle.dbprocess'
1000

4、创建模板,添加监控项目

Oracel数据库当前连接数的百分比         oracle.presession    60s    30d    90d    Zabbix 客户端    Oracle连接数    已启用
Oracel数据库最大连接数                 oracle.dbprocess    60s    30d    90d    Zabbix 客户端    Oracle连接数    已启用
Oracle数据库当前连接数                 oracle.dbsession    60s    30d    90d    Zabbix 客户端    Oracle连接数    已启用

Zabbix监控Oracle 连接数

5、效果图

Zabbix监控Oracle 连接数

6、资料下载

上传的资料包含监控模板、数据库连接数监控、表空间自动发现规则(监控),下载地址