python监控数据库_Zabbix+Python监控Oracle数据库

一、规划监控项

监控数据库的目的是为了保障数据库稳定运行，一旦出现故障，dba能够及时发现并介入处理，监控项分为以下两类

1. 数据库空间不足或数据库发生故障，DBA需要立即处理。

监控项包括表空间、用户状态、实例状态、锁、大量等待事件、闪回区使用率等。此类监控项需要给其设置触发器，一旦出现异常，及时告警。

2. 数据库运行状态的一些统计信息，为DBA定位数据库性能问题发生的时间和类别提供参考。

监控项包括常见的等待事件发生的次数，命中率、硬解析比例等。

下面表格中列出附件中模板的监控项

二、安装配置步骤

环境说明：

实测环境Ubuntu 16 + Zabbix server 4.2 + python 3.5 + Oracle Client 19 , 被监控Oracle 11G及以上

安装oracle客户端

oracle-instantclient-basic-linuxx64.rpm

oracle-instantclient-devel-linuxx64.rpm

oracle-instantclient-sqlplus-linuxx64.rpm

配置环境变量

vi + /etc/profile

---------------------------------------------

export ORACLE_HOME=/usr/lib/oracle/19.6/client64

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$PATH:$ORACLE_HOME/bin

执行下面的命令让配置生效

source /etc/profile

添加动态库配置文件

vi + /etc/ld.so.conf.d/oracle.conf

---------------------------------------

/usr/lib/oracle/19.6/client64/lib

执行命令ldconfig

建立TNS配置文件

mkdir -p /usr/lib/oracle/19.6/client64/network/admin

vi tnsnames.ora

chown -R python：python /usr/lib/oracle/19.6/client64/network/admin

配置环境变量

vim /home/python/.bashrc

添加如下内容

export  ORACLE_HOME=/usr/lib/oracle/11.2/client64

export  TNS_ADMIN=$ORACLE_HOME/network/admin

export  NLS_LANG='simplified chinese_china'.ZHS16GBK

export  LD_LIBRARY_PATH=$ORACLE_HOME/lib

export  PATH=$ORACLE_HOME/bin:$PATH

生效环境变量：source /home/python/.bashrc

安装python相关包，

安装cx_Oracle(python连接oracle的包)

新的版本：https://github.com/oracle/python-cx_Oracle

安装指导：https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html

root@zabbix: # python -m pip install cx_Oracle --upgrade

Collecting cx_Oracle

Downloading https://files.pythonhosted.org/packages/82/a6/7cd37cdccf246/cx_Oracle-7.3.0-cp35-cp35m-manylinux1_x86_64.whl (736kB)

100% |████████████████████████████████| 737kB 1.3MB/s

Installing collected packages: cx-Oracle

Successfully installed cx-Oracle-7.3.0

You are using pip version 9.0.3, however version 20.1 is available.

You should consider upgrading via the 'pip install --upgrade pip' command.

安装argparse

wget https://bootstrap.pypa.io/2.6/get-pip.py --no-check-certificate

python get-pip.py

pip install argparse

-----------------排错pip--------------

root@zabbix:~# python3 --version

root@zabbix:~# sudo apt install python3-pip

root@zabbix:~# pip3 show argparse

You are using pip version 8.1.1, however version 20.1 is available.

You should consider upgrading via the 'pip install --upgrade pip' command.

root@zabbix:~# pip3 install --upgrade pip

Collecting pip

Downloading https://files.pythonhosted.org/packages/54/2e/df11eaec8b9/pip-20.1-py2.py3-none-any.whl (1.5MB)

100% |████████████████████████████████| 1.5MB 387kB/s

Installing collected packages: pip

Found existing installation: pip 8.1.1

Not uninstalling pip at /usr/lib/python3/dist-packages, outside environment /usr

Successfully installed pip-20.1

root@zabbix:~# pip install argparse

Collecting argparse

Downloading argparse-1.4.0-py2.py3-none-any.whl (23 kB)

Installing collected packages: argparse

Successfully installed argparse-1.4.0

root@zabbix:~# pip show argparse

Name: argparse

Version: 1.4.0

Summary: Python command-line parsing library

Home-page: https://github.com/ThomasWaldmann/argparse/

Author: Thomas Waldmann

Author-email: tw@waldmann-edv.de

License: Python Software Foundation License

Location: /usr/local/lib/python3.5/dist-packages

Requires:

Required-by:

root@zabbix:~# python get-pip.py

Collecting pip<10

Downloading https://files.pythonhosted.org/packages/ac/95/a05b57432e03/pip-9.0.3-py2.py3-none-any.whl (1.4MB)

100% |████████████████████████████████| 1.4MB 460kB/s

Collecting setuptools<37

Downloading https://files.pythonhosted.org/packages/27/f6/fabf5459/setuptools-36.8.0-py2.py3-none-any.whl (482kB)

100% |████████████████████████████████| 491kB 2.4MB/s

Collecting wheel<0.30

Downloading https://files.pythonhosted.org/packages/8a/e9/84613450/wheel-0.29.0-py2.py3-none-any.whl (66kB)

100% |████████████████████████████████| 71kB 2.4MB/s

Installing collected packages: pip, setuptools, wheel

Found existing installation: pip 20.1

Uninstalling pip-20.1:

Successfully uninstalled pip-20.1

Successfully installed pip-9.0.3 setuptools-36.8.0 wheel-0.29.0

You are using pip version 9.0.3, however version 20.1 is available.

You should consider upgrading via the 'pip install --upgrade pip' command.

上传python脚本

zabbix监控模板下载：https://github.com/YangBaohust/zabbix_templates

将附件中的pyora.py脚本放入/usr/lib/zabbix/externalscripts/目录下赋权限，让zabbix用户能够执行该脚本

chmod 755 /usr/lib/zabbix/externalscripts/pyora.py

[注意：先在被监控机的oracle数据库中创建监控用户，用户名和密码可以自己随意指定

create user zabbix identified by zabbix;

grant connect,select any dictionary to zabbix;]

环境测试脚本--

root@zabbix:#

python pyora.py --username db_user --password db_paas --address Monitor_DB_ip --port 15xx

--database db_name show_tablespaces

上面测试脚本的参数说明

username: 用户名

password: 密码

address: 被监控机ip地址

port: 端口号

database: oracle service name

有返回结果表示脚本能正常运行

zabbix监控主机中配置

在zabbix页面中，依次点击 Configuration – Hosts – Create host – Hostname (输入ip地址) – groups (选Linux servers) – Templates (选择Pyora_ExternalCheck_11G) – 点击上面的Add – Macros – 点击上面的Add添加宏，全部添加完毕后，点击下面的Add，主机即添加完毕。

查看监控数据：

依次点击 Monitoring – Latest Data – Host (选择对应的主机)，则可以看到监控的数据了