zabbix-pyora定制监控Oracle数据库

安装Pyora 监控oracle

Today Zabbix have some nice improvements like macros and Discovery, so we can set the database settings(user,password,database and such) on the host using macros and not hard code it on a script that you have to access a server to change it. Also if Zabbix already discovery disks and network interfaces why not Oracle tablespaces? Then Pyora was born:

Pyora is a clean python script that uses cxOracle library to fetch data from the database. To use Pyora you need Oracle client and the cxOracle. I’m assuming that you have both installed on your system, if you don’t have it you check this page and install it.

####################################################################################################
#运行pyora之前，需要安装以下软件
OS: 
[root@ct7 Pyora-master]# more /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)

注意stop firewalld, disable SElinux, 否则zabbix 报 no permission
[root@ct7 Pyora-master]# systemctl stop firewalld
[root@ct7 agent]# vi /etc/sysconfig/selinux
SELINUX=disabled
[root@ct7 agent]# setenforce 0


Python:
[root@ct7 Pyora-master]# python -V
Python 2.7.5

#安装Oracle instance-client:
[root@ct7 oracle]# pwd
/home/tony/oracle

[root@ct7 oracle]# ls
oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm 
oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm

[root@ct7 oracle]# yum localinstall oracle* --nogpgcheck

[root@ct7 oracle]# more /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
0.0.0.59   ct7.qa.webex.com

[root@ct7 oracle]# more /etc/profile
export ORACLE_HOME=/usr/lib/oracle/12.1/client64/
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin

[root@ct7 oracle]# mkdir -p $ORACLE_HOME/network/admin

[root@ct7 oracle]# more $ORACLE_HOME/network/admin/tnsnames.ora
cha=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=0.0.0.188)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=cha.qa.webex.com)
    )
  )

[root@ct7 oracle]# sqlplus system/pass@cha

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 8 09:39:04 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SQL>

#安装cxOracle
[root@ct7 oracle]# rpm -ivh cx_Oracle-5.2.1-12c-py27-1.x86_64.rpm

[root@ct7 python]# more con-cha.py
#!/usr/bin/python
#FileName: con_ora.py

import cx_Oracle

conn = cx_Oracle.connect('ch/pass@0.0.0.188:1521/cha.qa.webex.com')
cursor = conn.cursor()
cursor.execute("select * from dept")
row = cursor.fetchone()
print row[0]

cursor.close()
conn.close()

[root@ct7 python]# python con-cha.py
300

####################################################################################################
#安装Pyora
https://github.com/bicofino/Pyora.git

[root@ct7 Pyora-master]# ll
total 28
-rwxrwxrwx. 1 root root 20799 Jun  6 23:23 pyora.py
-rw-r--r--. 1 root root  2899 Jun  6 23:23 README.md
drwxr-xr-x. 2 root root    52 Jun  6 23:23 zabbix-template

[root@ct7 Pyora-master]# python pyora.py -h
usage: pyora.py [-h] [--username USERNAME] [--password PASSWORD]
                [--address ADDRESS] [--database DATABASE]

                {activeusercount,asm_volume_use,bufbusywaits,check_active,check_archive,commits,db_close,db_connect,dbfilesize,dbprllwrite,dbscattread,dbseqread,dbsize,dbsnglwrite,deadlocks,directread,directwrite,dsksortratio,enqueue,fra_use,freebufwaits,hparsratio,indexffs,lastapplarclog,lastarclog,latchfree,logfilesync,logonscurrent,logprllwrite,logswcompletion,netresv,netroundtrips,netsent,query_lock,query_redologs,query_rollbacks,query_sessions,query_sysmetrics,rcachehit,redowrites,rollbacks,show_asm_volumes,show_tablespaces,show_tablespaces_temp,show_users,tablespace,tablespace_abs,tablespace_temp,tblrowsscans,tblscans,uptime,user_status,version}
                ...

positional arguments:
  {activeusercount,asm_volume_use,bufbusywaits,check_active,check_archive,commits,db_close,db_connect,dbfilesize,dbprllwrite,dbscattread,dbseqread,dbsize,dbsnglwrite,deadlocks,directread,directwrite,dsksortratio,enqueue,fra_use,freebufwaits,hparsratio,indexffs,lastapplarclog,lastarclog,latchfree,logfilesync,logonscurrent,logprllwrite,logswcompletion,netresv,netroundtrips,netsent,query_lock,query_redologs,query_rollbacks,query_sessions,query_sysmetrics,rcachehit,redowrites,rollbacks,show_asm_volumes,show_tablespaces,show_tablespaces_temp,show_users,tablespace,tablespace_abs,tablespace_temp,tblrowsscans,tblscans,uptime,user_status,version}

optional arguments:
  -h, --help            show this help message and exit
  --username USERNAME
  --password PASSWORD
  --address ADDRESS
  --database DATABASE

Show the tablespaces names in a JSON format
[root@ct7 Pyora-master]# python pyora.py --username zabbix --password pass --address 0.0.0.188 --database cha.qa.webex.com show_tablespaces
{"data": [{"{#TABLESPACE}": "SYSAUX"}, {"{#TABLESPACE}": "SYSTEM"}, {"{#TABLESPACE}": "TBS_01"}, {"{#TABLESPACE}": "TBS_ZABBIX_01"}, {"{#TABLESPACE}": "TEMP"}, {"{#TABLESPACE}": "TPCCTAB"}, {"{#TABLESPACE}": "UNDOTBS1"}, {"{#TABLESPACE}": "USERS"}]}

####################################################################################################
#install zabbix agent
[root@ct7 agent]# wget http://repo.zabbix.com/zabbix/3.0/rhel/7/x86_64/zabbix-agent-3.0.4-1.el7.x86_64.rpm

[root@ct7 agent]# rpm -ivh zabbix-agent-3.0.4-1.el7.x86_64.rpm

[root@ct7 agent]# more /etc/zabbix/zabbix_agentd.conf
Server=0.0.0.55
UserParameter=pyora[*],/home/tony/zabbix/pyora/Pyora-master/pyora.py --username $1 --password $2 --address $3 --database $4 $5 $6 $7 $8

[root@ct7 agent]# systemctl start zabbix-agent

• Import the template zabbix-template/Pyora.xml to your new host

• add a new host and configure the required macros

• Now you just have to wait and check later the information, Remember that the tablespace discovery take one hour to complete.