使用zabbix监控oracle数据库
一、概述
本文将给大家介绍如何利用zabbix+python监控oracle数据库。
二、环境介绍
- 监控机 CentOS Linux release 7.6.1810 + Zabbix server Zabbix 4.4.10 + Python 2.7.5 (操作系统自带) + Oracle Client 11.2.0.4 (x86_64)。
- 被监控机 Oracle 11.2.0.4。
三、选择监控方式
Zabbix agent
在被监控机上面安装zabbix agent，zabbix agent将被监控机采集到的数据发送给zabbix server。这种方式最常用，一般用来采集服务器的cpu、内存等信息。
External check。
本文介绍的就是使用external check方式去监控oracle数据库。
在zabbix server上面运行查询脚本，直接查询被监控机上的数据。此种方式在被监控机上面不需要做任何部署，所有查询全部从zabbix server上面发出，所以对zabbix server的性能要求较高，官方不推荐大量使用该方式。对于少量的oracle数据库服务器，可以采用该方式。
四、规划监控项
监控数据库的目的是为了保障数据库稳定运行，一旦出现故障，dba能够及时发现并介入处理，监控项分为以下两类
- 数据库空间不足或数据库发生故障，DBA需要立即处理。
监控项包括表空间、用户状态、实例状态、锁、大量等待事件、闪回区使用率等。此类监控项需要给其设置触发器，一旦出现异常，及时告警。
- 数据库运行状态的一些统计信息，为DBA定位数据库性能问题发生的时间和类别提供参考。
监控项包括常见的等待事件发生的次数，命中率、硬解析比例等。
下面表格中列出附件中模板的监控项
五、安装。以下所有操作均在zabbix服务器上面执行
1、zabbix server已经安装，版本为4.4.10。
2、安装oracle客户端。
2.1、下载oracle客户端
Oracle客户端下载地址：https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
下载下面三个安装包并上传：
[root@web01 ~]# ll
-rw-r--r--. 1 root root 59865008 Jun 28 11:06 oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
-rw-r--r--. 1 root root 610222 Jun 28 10:36 oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
-rw-r--r--. 1 root root 828333 Jun 28 10:25 oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
2.2、使用root安装oracle客户端：
[root@zabbix ~]#rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:oracle-instantclient11.2-basic-11################################# [100%]
[root@zabbix ~]#rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:oracle-instantclient11.2-devel-11################################# [100%]
[root@zabbix ~]#rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:oracle-instantclient11.2-sqlplus-################################# [100%]
2.3、配置环境变量:
在profile中添加下面3条环境变量
[root@zabbix ~]#vim /etc/profile
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
[root@zabbix ~]#source /etc/profile
2.4、添加动态库配置文件:
[root@zabbix ~]#vim /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/11.2/client64/lib
[root@zabbix ~]#ldconfig
2.5、连接oracle进行测试:
[root@zabbix ~]#sqlplus system/123456@192.168.99.237:1521/orcl
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 15:57:48 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
出现上面提示证明oracle client安装成功。
3、安装python相关包。
3.1、安装cx_Oracle(python连接oracle的包)
注意：我们是python2.7.5所以这里找cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm包进行下载，在这里我是直接用windows下载然后上传至linux服务器。
下载地址：https://sourceforge.net/projects/cx-oracle/files/5.1.2/cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm/download
[root@zabbix ~]#ll
-rw-r--r-- 1 root root 275168 Jun 28 15:52 cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm
-rw-r--r-- 1 root root 59865008 Jun 28 11:06 oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 610222 Jun 28 10:36 oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 828333 Jun 28 10:25 oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
[root@zabbix ~]#rpm -ivh cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:cx_Oracle-5.1.2-1 ################################# [100%]
3.2、安装argparse
下载地址：https://bootstrap.pypa.io/pip/2.7/get-pip.py
[root@zabbix ~]#wget https://bootstrap.pypa.io/pip/2.7/get-pip.py --no-check-certificate
[root@zabbix ~]#ll
-rw-r--r-- 1 root root 275168 Jun 28 15:52 cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm
-rw-r--r-- 1 root root 1908226 Feb 23 02:00 get-pip.py
-rw-r--r-- 1 root root 59865008 Jun 28 11:06 oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 610222 Jun 28 10:36 oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
-rw-r--r-- 1 root root 828333 Jun 28 10:25 oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
[root@zabbix ~]#python get-pip.py
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting pip<21.0
Downloading pip-20.3.4-py2.py3-none-any.whl (1.5 MB)
|████████████████████████████████| 1.5 MB 61 kB/s
Collecting wheel
Downloading wheel-0.36.2-py2.py3-none-any.whl (35 kB)
Installing collected packages: pip, wheel
Attempting uninstall: pip
Found existing installation: pip 8.1.2
Uninstalling pip-8.1.2:
Successfully uninstalled pip-8.1.2
Successfully installed pip-20.3.4 wheel-0.36.2
[root@zabbix ~]#pip install argparse
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting argparse
Downloading argparse-1.4.0-py2.py3-none-any.whl (23 kB)
Installing collected packages: argparse
Successfully installed argparse-1.4.0
4、上传python脚本：
附件下载地址：https://github.com/YangBaohust/zabbix_templates
pyora.py，Pyora_ExternalCheck_11G.xml地址放在github上
4.1、将附件中的pyora.py脚本放入/usr/lib/zabbix/externalscripts/目录下，然后赋权限，让zabbix用户能够执行该脚本。
[root@zabbix /usr/lib/zabbix/externalscripts]#ll
total 20
-rw-r--r-- 1 root root 18470 Jun 30 11:17 pyora.py
[root@zabbix /usr/lib/zabbix/externalscripts]#chmod 755 /usr/lib/zabbix/externalscripts/pyora.py
[root@zabbix /usr/lib/zabbix/externalscripts]#ll
total 20
-rwxr-xr-x 1 root root 18470 Jun 30 11:17 pyora.py
4.2、将先在被监控机的oracle数据库中创建监控用户，用户名和密码可以自己随意指定
create user zabbix identified by zabbix;
grant connect,select any dictionary to zabbix;
4.3、测试脚本
python pyora.py --username zabbix --password zabbix --address 10.30.10.32 --port 1521 --database office show_tablespaces
上面测试脚本的参数说明:
username: 用户名
password: 密码
address: 被监控机ip地址
port: 端口号
database: oracle service name
有返回结果表示脚本能正常运行
[root@zabbix /usr/lib/zabbix/externalscripts]#python pyora.py --username zabbix --password zabbix --address 192.168.98.157 --port 1521 --database pwspdb show_users
{"data": [{"{#DBUSER}": "SYSTEM"}, {"{#DBUSER}": "SYS"}, {"{#DBUSER}": "MGMT_VIEW"}, {"{#DBUSER}": "PWSP"}, {"{#DBUSER}": "ZABBIX"}, {"{#DBUSER}": "PWS_TEST"}, {"{#DBUSER}": "SAPMES"}, {"{#DBUSER}": "DBADMIN"}, {"{#DBUSER}": "SYSMAN"}, {"{#DBUSER}": "DBSNMP"}, {"{#DBUSER}": "PWDS"}, {"{#DBUSER}": "OAPWS"}, {"{#DBUSER}": "TESTEXPORT"}]}
5、Zabbix监控中上传template文件：
5.1、将附件中的Pyora_ExternalCheck_11G.xml模板导入到zabbix server中：
在zabbix页面中，依次点击Configuration – Templates – Import – 选择文件 – Import，即完成了导入
5.2、添加机器，并链接到模板：
在zabbix页面中，依次点击Configuration – Hosts – Create host – Hostname (输入ip地址) – groups (选Linux servers) – Templates (选择Pyora_ExternalCheck_11G) – 点击上面的Add – Macros – 点击上面的Add添加宏，全部添加完毕后，点击下面的Add，主机即添加完毕
5.3、由于原模板自动发现用户时有一处问题，所以还要进行修改：
5.4、查看实时数据发现数据正常，表明oracle监控成功：
六、安装过程中遇到的错误，导致python脚本无法拉取到oracle数据。
拉取数据错误：
[root@web01 /usr/lib/zabbix/externalscripts]# python /usr/lib/zabbix/externalscripts/pyora.py --username zabbix --password zabbix --address 192.168.99.237 --port 1521 --database orcl show_tablespaces
Traceback (most recent call last):
File "/usr/lib/zabbix/externalscripts/pyora.py", line 9, in <module>
import cx_Oracle
ImportError: /usr/lib/python2.7/site-packages/cx_Oracle.so: undefined symbol: PyUnicodeUCS2_AsEncodedString
6.1、检查了很久，发现我安装的cx_Oracle版本为cx_Oracle-5.1.1-11g-py27-1.x86_64.rpm，导致python和oracle无法通讯。
[root@web01 ~]# ll
-rw-r--r--. 1 root root 258275 Nov 12 2020 cx_Oracle-5.1.1-11g-py27-1.x86_64.rpm
6.2、卸载安装的cx_Oracle-5.1.1-1.x86_64
[root@web01 ~]# rpm -qa | grep cx_Oracle
cx_Oracle-5.1.1-1.x86_64
[root@web01 ~]# yum remove cx_Oracle-5.1.1-1.x86_64
6.3、下载cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm，并进行安装：
[root@web01 ~]# ls
cx_Oracle-5.1.1-11g-py27-1.x86_64.rpm
cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm
[root@web01 ~]# rpm -ivh cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:cx_Oracle-5.1.2-1 ################################# [100%]
[root@web01 ~]# wget https://bootstrap.pypa.io/pip/2.7/get-pip.py --no-check-certificate
--2021-06-28 15:58:07-- https://bootstrap.pypa.io/pip/2.7/get-pip.py
Resolving bootstrap.pypa.io (bootstrap.pypa.io)... 151.101.72.175, 2a04:4e42:11::175
Connecting to bootstrap.pypa.io (bootstrap.pypa.io)|151.101.72.175|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1908226 (1.8M) [text/x-python]
Saving to: ‘get-pip.py.1’
47% [=====================================> ] 902,385 10.4KB/s eta 95s
^Z
[2]+ Stopped wget https://bootstrap.pypa.io/pip/2.7/get-pip.py --no-check-certificate
6.4、重新安装argparse
[root@web01 ~]# python get-pip.py
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Collecting pip<21.0
Using cached pip-20.3.4-py2.py3-none-any.whl (1.5 MB)
Installing collected packages: pip
Attempting uninstall: pip
Found existing installation: pip 20.3.4
Uninstalling pip-20.3.4:
Successfully uninstalled pip-20.3.4
Successfully installed pip-20.3.4
[root@web01 ~]# pip install argparse
DEPRECATION: Python 2.7 reached the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 is no longer maintained. pip 21.0 will drop support for Python 2.7 in January 2021. More details about Python 2 support in pip can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support pip 21.0 will remove support for this functionality.
Requirement already satisfied: argparse in /usr/lib/python2.7/site-packages (1.4.0)
6.5、用python重新拉取数据验证后取值正常：
[root@web01 /usr/lib/zabbix/externalscripts]# python pyora.py --username zabbix --password zabbix --address 192.168.99.237 --port 1521 --database orcl show_tablespaces{"data": [{"{#TABLESPACE}": "SYSTEM"}, {"{#TABLESPACE}": "SYSAUX"}, {"{#TABLESPACE}": "UNDOTBS1"}, {"{#TABLESPACE}": "USERS"}, {"{#TABLESPACE}": "CAT"}]}