Oracle 监控

Tags：Oracle

第一章 zabbix介绍

1.1 zabbix 安装

OS: CentOS7, zbbix3.0

setp 1. Installing repository configuration package

1	rpm -ivh http://repo.zabbix.com/zabbix/3.0/rhel/7/x86_64/zabbix-release-3.0-1.el7.noarch.rpm

setp 2. Installing Zabbix packages

1	yum install zabbix-server-mysql zabbix-web-mysql zabbix-agent

setp 3. Install mysql database

1. download the repo file from this URL
http://dev.mysql.com/downloads/repo/yum/
[root@ct7 zabbix]# ll
total 12
-rwxr--r--. 1 root root 9116 Aug 24 14:29 mysql57-community-release-el7-8.noarch.rpm
2. Install the downloaded release package with the following command
[root@ct7 zabbix]# yum localinstall mysql57-community-release-el7-8.noarch.rpm
3. check repo file
[root@ct7 zabbix]# yum list
[root@ct7 zabbix]# yum repolist all | grep mysql
4. remove the mariadb
[root@ct7 yum.repos.d]# yum erase mariadb
Removing:
 mariadb
Removing for dependencies:
 akonadi-mysql
 mariadb-server
 
5. Install MySQL
[root@ct7 zabbix]# yum install mysql-community-server
Installing:
 mysql-community-devel 
 mysql-community-libs
 mysql-community-libs-compat
 mysql-community-server
Installing for dependencies:
 mysql-community-client
 mysql-community-common
Updating for dependencies:
 qt 
 qt-devel
 qt-mysql 
 qt-odbc 
 qt-postgresql 
 qt-x11
 
6. Starting the MySQL Server
[root@ct7 yum.repos.d]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
[root@ct7 yum.repos.d]# service mysqld status
Redirecting to /bin/systemctl status  mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2016-08-24 16:59:12 CST; 22s ago
  Process: 17914 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 17790 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 17916 (mysqld)
   Memory: 309.1M
   CGroup: /system.slice/mysqld.service
           └─17916 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Aug 24 16:59:06 ct7.qa.webex.com systemd[1]: Starting MySQL Server...
Aug 24 16:59:12 ct7.qa.webex.com systemd[1]: Started MySQL Server.
7. modify the mysql password
A superuser account 'root'@localhost is created. A password for the superuser is set and stored in the error log file. To reveal it, use the following command:
[root@ct7 yum.repos.d]# grep 'temporary password' /var/log/mysqld.log
2016-08-24T08:59:08.455018Z 1 [Note] A temporary password is generated for root@localhost: thYxs8wwD!)W
[root@ct7 yum.repos.d]# mysql -uroot -p
Enter password:thYxs8wwD!)W
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.14
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'pass';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'CCM%lab123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on zabbix.* to zabbix@localhost identified by 'Pass1234!';

setp 4. Creating initial database

1	zcat create.sql.gz \| mysql -uroot -p zabbix

setp 5. Starting Zabbix server process

# vi /etc/zabbix/zabbix_server.conf
DBHost=localhost
DBName=zabbix
DBUser=zabbix
DBPassword=Pass1234!
# vi /etc/httpd/conf.d/zabbix.conf
php_value max_execution_time 300
php_value memory_limit 128M
php_value post_max_size 16M
php_value upload_max_filesize 2M
php_value max_input_time 300
php_value always_populate_raw_post_data -1
php_value date.timezone GMT

setp 6. troubleshooting

Issue 1: Zabbix server is not running: the information displayed may not be current
solution: Disable the Selinux
[root@ct7 selinux]# vi /etc/selinux/config
SELINUX=disabled
[root@ct7 selinux]# setenforce 0

1.2 zabbix simple check

不需要安装zabbix agent，来监控mysql service 3306 port是否work。

setp 1. configuration –> Templates –> Create Template

此处输入图片的描述

setp 2. Application (app-mysql-simple-check)

此处输入图片的描述

setp 3. Item (mysql-simple-check-runing)

此处输入图片的描述

setp 4. Trigger (mysql-simple-check-down)

此处输入图片的描述

setp 5. host 绑定 template

此处输入图片的描述

1.3 zabbix actions 重启服务

Remote commands

通过remote commands 的 ssh来实现自动重启服务

此处输入图片的描述

通过remote commands 的 Custom script来实现自动重启服务

#install zabbix-agent, Make sure that the EnableRemoteCommands parameter is set to 1 and uncommented. Restart agent daemon if changing this parameter.
[root@mydb2 ~]# more /etc/zabbix/zabbix_agentd.conf
EnableRemoteCommands = 1
[root@mydb2 ~]# service zabbix-agent restart
#Access permissions: Make sure that the 'zabbix' user has execute permissions for configured commands. One may be interested in using sudo to give access to privileged commands. To configure access, execute as root:
[root@mydb2 ~]# visudo
# allows 'zabbix' user to run all commands without password.
zabbix ALL=NOPASSWD: ALL
# allows 'zabbix' user to restart apache without password.
zabbix ALL=NOPASSWD: /etc/init.d/apache restart
#configuring a new action in Configuration→Actions:
In the Operations tab, select the Remote command operation type
Select the remote command type (Custom script)
Enter the remote command
sudo /etc/init.d/sshd restart
Note the use of sudo - Zabbix user does not have permissions to restart system services by default. See below for hints on how to configure sudo.

此处输入图片的描述

第二章 zabbix agent

2.1 安装zabbix agent

zabbix server: xx.xx.188.58
zabbix client agent: xx.xx.188.188
wget  http://repo.zabbix.com/zabbix/2.2/rhel/6/x86_64/zabbix-agent-2.2.11-1.el6.x86_64.rpm
wget  http://repo.zabbix.com/zabbix/2.2/rhel/6/x86_64/zabbix-2.2.11-1.el6.x86_64.rpm
rpm -ivh zabbix-2.2.11-1.el6.x86_64.rpm zabbix-agent-2.2.11-1.el6.x86_64.rpm
vim /etc/zabbix/zabbix_agentd.conf
### Option: Server
#       List of comma delimited IP addresses (or hostnames) of Zabbix servers.
#       Incoming connections will be accepted only from the hosts listed here.
#       If IPv6 support is enabled then '127.0.0.1', '::127.0.0.1', '::ffff:127.0.0.1' are treated equally.
#
# Mandatory: no
# Default:
# Server=
Server=xx.xx.188.58
### Option: Hostname
#       Unique, case sensitive hostname.
#       Required for active checks and must match hostname as configured on the server.
#       Value is acquired from HostnameItem if undefined.
#
# Mandatory: no
# Default:
# Hostname=
Hostname=xx.xx.188.58
service zabbix-agent status
service zabbix-agent stop
service zabbix-agent start

2.2 zabbix监控server网络流量

前提：被监控的server 需要安装 Zabbix agent

此处输入图片的描述

第三章 zabbix监控MySQL

####################################################################################################
####以下步骤是mysql client 运行
step 1, 安装zabbix-agent
[root@mtgdb yum.repos.d]# yum install openssl
Updating:
 openssl
Installing for dependencies:
 make
[root@mtgdb yum.repos.d]# yum install zabbix-agent
####################################################################################################
step 2, 查看zabbix_agentd.conf文件，检索Include
[root@ct7 zabbix]# grep Include /etc/zabbix/zabbix_agentd.conf
### Option: Include
# Include=
Include=/etc/zabbix/zabbix_agentd.d/
# Include=/usr/local/etc/zabbix_agentd.userparams.conf
# Include=/usr/local/etc/zabbix_agentd.conf.d/
# Include=/usr/local/etc/zabbix_agentd.conf.d/*.conf
[root@ct7 zabbix]# ll /etc/zabbix/zabbix_agentd.d/
total 4
-rw-r--r--. 1 root root 1531 Aug 25 17:36 userparameter_mysql.conf
####################################################################################################
step 3, 查看userparameter_mysql.conf文件， 的HOME
[root@ct7 zabbix_agentd.d]# ls
userparameter_mysql.conf
[root@ct7 zabbix_agentd.d]# more userparameter_mysql.conf
# For all the following commands HOME should be set to the directory that has .my.cnf file with password information.
# Flexible parameter to grab global variables. On the frontend side, use keys like mysql.status[Com_insert].
# Key syntax is mysql.status[variable].
UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/var/lib/zabbix mysql -N | awk '{print $$2}'
# Flexible parameter to determine database or table size. On the frontend side, use keys like mysql.size[zabbix,history,data].
# Key syntax is mysql.size[<database>,<table>,<type>].
# Database may be a database name or "all". Default is "all".
# Table may be a table name or "all". Default is "all".
# Type may be "data", "index", "free" or "both". Both is a sum of data and index. Default is "both".
# Database is mandatory if a table is specified. Type may be specified always.
# Returns value in bytes.
# 'sum' on data_length or index_length alone needed when we are getting this information for whole database instead of a single table
UserParameter=mysql.size[*],bash -c 'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from informa
tion_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=\"$2\"");" | HOME=/var/lib/zabbix mysql -N'
UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin ping | grep -c alive
UserParameter=mysql.version,mysql -V
[root@mtgdb zabbix_agentd.d]# cd /var/lib/
[root@mtgdb lib]# mkdir zabbix
[root@mtgdb lib]# cd zabbix/
####################################################################################################
#首先在客户端的mysql里添加权限，即本机使用zabbix账号连接本地的mysql
mysql> grant all on *.* to zabbix@'localhost' identified by "Pass1234!";
mysql> GRANT PROCESS,SUPER,REPLICATION CLIENT ON *.* TO zabbix@'127.0.0.1' IDENTIFIED BY 'Pass1234!'; 
mysql> flush privileges;
不赋予权限127.0.0.1，就会报ERROR 1045 (28000): Access denied for user
[root@mtgdb zabbix]# pwd
/var/lib/zabbix
[root@mtgdb zabbix]# vi .my.cnf
[client]
user=zabbix
password=Pass1234!
#注意权限的问题
[root@mtgdb zabbix]# ll -a
total 12
drwxr-xr-x   2 root root 4096 Aug 26 02:44 .
drwxr-xr-x. 20 root root 4096 Aug 26 02:34 ..
-rwxr-xr-x   1 root root   40 Aug 26 02:44 .my.cnf
####################################################################################################
#step 5. 启动 zabbix agent
[root@mtgdb zabbix]# /etc/init.d/zabbix-agent start
Starting Zabbix agent: [  OK  ]
####################################################################################################
以下步骤运行在 zabbix server
#step 6. 验证zabbix server 和 agent client 的通信
[root@ct7 zabbix_agentd.d]# zabbix_get -s xx.xx.192.113 -p10050 -k "system.cpu.load[all,avg15]";
zabbix_get [10400]: Check access restrictions in Zabbix agent configuration
报'Check access restrictions'这个错误，是因为zabbix agent client 没有配置zabbix连接的server IP地址(xx.xx.188.55)，登陆到Zabbix agent client
[root@mtgdb zabbix]# vi /etc/zabbix/zabbix_agentd.conf
##### Passive checks related
### Option: Server
#       List of comma delimited IP addresses (or hostnames) of Zabbix servers.
#       Incoming connections will be accepted only from the hosts listed here.
#       If IPv6 support is enabled then '127.0.0.1', '::127.0.0.1', '::ffff:127.0.0.1' are treated equally.
#
# Mandatory: no
# Default:
# Server=
Server=127.0.0.1,xx.xx.188.55
[root@mtgdb zabbix]# /etc/init.d/zabbix-agent restart
Shutting down Zabbix agent: [  OK  ]
Starting Zabbix agent: [  OK  ]
登陆到zabbix server 上在重启验证一下
[root@ct7 zabbix]# zabbix_get -s xx.xx.192.113 -p10050 -k "system.cpu.load[all,avg15]";
0.000000
zabbix_get -s xx.xx.192.113 -p10050 -k vfs.fs.discovery

此处输入图片的描述

第四章 zabbix+Orabbix

参考： http://www.smartmarmot.com/wiki/index.php?title=Orabbix

Orabbix是设计用来为zabbix监控Oracle数据库的插件，它提供多层次的监控，包括可用性和服务器性能指标。
它提供了从众多Oracle实例采集数据的有效机制，进而提供此信息的监控和性能指标。然后，您可以利用的zabbix的报告功能为收集的所有数据，并提供分析。目前的发行版中包含了一组预先定义的模板，包括从初始部署警报和图形功能。然而，这些可以进行微调，以满足您的需求和数据/监控要求。

此处输入图片的描述

Orabbix能监控什么？

数据库版本

归档日志与生产趋势分析

触发器，表/过程等命中率

逻辑I / O性能

物理I / O性能

PGA

SGA

共享池

Sessions

数据库大小

第一步：安装需求

Zabbix 2.2.9 Server

Java Runtime Environment 6

RHEL5.3

IP: xx.xx.188.58

DB Server:

Oracle 11g

IP: xx.xx.188.188

第二步：下载Orabbix到Zabbix Server上
http://www.smartmarmot.com/product/orabbix/download/

On the Zabbix Server(xx.xx.188.58)

#unzip Orabbix to: /opt/orabbix
[root@hzbacksto orabbix]# pwd
/opt/orabbix
[root@hzbacksto orabbix]# ll orabbix-1.2.3.zip
-rwxr--r-- 1 root root 4858160 Oct 13 06:50 orabbix-1.2.3.zip
[root@hzbacksto orabbix]# unzip orabbix-1.2.3.zip
[root@hzbacksto orabbix]# tree -L 1 ./
./
|-- conf
|-- doc
|-- init.d
|-- install.cmd
|-- lib
|-- logs
|-- orabbix-1.2.3.jar
|-- orabbix-1.2.3.zip
|-- orabbix.exe
|-- orabbixw.exe
|-- run.bat
|-- run.sh
|-- template
|-- uninstall.cmd
#Copy file /opt/orabbix/init.d/orabbix to /etc/init.d/orabbix
[root@hzbacksto orabbix]# cp /opt/orabbix/init.d/orabbix /etc/init.d/orabbix
#Grant execute permissions to the following files:
[root@hzbacksto orabbix]# chmod 755 /etc/init.d/orabbix
[root@hzbacksto orabbix]# chmod 755 /opt/orabbix/run.sh

第三步：在需要监控的DB上创建zabbix用户，使Orabbix来访问DB

#On the DB Server(xx.xx.188.188)
CREATE TABLESPACE TBS_ZABBIX_01 DATAFILE 
  '/home/oracle/app/oracle/oradata/cha/tbs_zabbix_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
CREATE USER ZABBIX
IDENTIFIED BY pass
DEFAULT TABLESPACE TBS_ZABBIX_01
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CONNECT TO ZABBIX;
GRANT RESOURCE TO ZABBIX;
ALTER USER ZABBIX DEFAULT ROLE ALL;
GRANT SELECT ANY TABLE TO ZABBIX;
GRANT CREATE SESSION TO ZABBIX;
GRANT SELECT ANY DICTIONARY TO ZABBIX;
GRANT UNLIMITED TABLESPACE TO ZABBIX;
GRANT SELECT ANY DICTIONARY TO ZABBIX;
 
exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal =>'ZABBIX', is_grant => true, privilege => 'resolve');
exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host =>'*');
commit;
#verify the above is correct by running
select utl_inaddr.get_host_name('127.0.0.1') from dual;
#On the Zabbix Server(xx.xx.188.58)
chkconfig --add orabbix
chkconfig --list

第四步：配置文件config.props

cp /opt/orabbix/conf/config.props.sample config.props
#comma separed list of Zabbix servers
ZabbixServerList=ZabbixServer1      #此处的ZabbixServer1与以下两行开头对应
ZabbixServer1.Address=xx.xx.188.58 #zabbixserver地址
ZabbixServer1.Port=10051            #zabbixserver端口
#pidFile
OrabbixDaemon.PidFile=./logs/orabbix.pid
#frequency of item's refresh
OrabbixDaemon.Sleep=300
#MaxThreadNumber should be >= than the number of your databases
OrabbixDaemon.MaxThreadNumber=100
#put here your databases in a comma separated list
DatabaseList=cha                    # 名称与该机在 zabbix 中监控的主机名称保持一致
#Configuration of Connection pool
#if not specified Orabbis is going to use default values (hardcoded)
#Maximum number of active connection inside pool
DatabaseList.MaxActive=10
#The maximum number of milliseconds that the pool will wait
#(when there are no available connections) for a connection to be returned
#before throwing an exception, or <= 0 to wait indefinitely.
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1
#define here your connection string for each database
cha.Url=jdbc:oracle:thin:@xx.xx.188.188:1521:cha   #通过jbdc连接，确保系统有jdk环境
cha.User=zabbix
cha.Password=pass
#Those values are optionals if not specified Orabbix is going to use the general values
cha.MaxActive=10
cha.MaxWait=100
cha.MaxIdle=1
cha.QueryListFile=./conf/query.props #此句指定数据查询文件，可针对不同数据库，定制不同的查询文件
#DB2.Url=jdbc:oracle:thin:@server2.domain.example.com:<LISTENER_PORT>:DB2
#DB2.User=zabbix
#DB2.Password=zabbix_password
#DB2.QueryListFile=./conf/query.props
#DB3.Url=jdbc:oracle:thin:@server3.domain.example.com:<LISTENER_PORT>:DB3
#DB3.User=zabbix
#DB3.Password=zabbix_password
#DB3.QueryListFile=./conf/query.props

第五步：启动orabbix程序

[root@hzbacksto conf]# /etc/init.d/orabbix start
[root@hzbacksto ~]# tail -f /opt/orabbix/logs/orabbix.log
 2015-10-13 03:32:32,773 [main] INFO  Orabbix - maxPoolSize=10
 2015-10-13 03:32:32,773 [main] INFO  Orabbix - maxIdleSize=1
 2015-10-13 03:32:32,773 [main] INFO  Orabbix - maxIdleTime=1800000ms
 2015-10-13 03:32:32,773 [main] INFO  Orabbix - poolTimeout=100
 2015-10-13 03:32:32,773 [main] INFO  Orabbix - timeBetweenEvictionRunsMillis=-1
 2015-10-13 03:32:32,773 [main] INFO  Orabbix - numTestsPerEvictionRun=3
 2015-10-13 03:32:33,283 [main] INFO  Orabbix - Connected as ZABBIX
 2015-10-13 03:32:33,286 [main] INFO  Orabbix - --------- on Database -> cha
 2015-10-13 03:32:34,344 [pool-1-thread-1] INFO  Orabbix - Done with dbJob on database cha QueryList elapsed time 998 ms

第六步：导入模板

将该模板 Orabbix_export_full.xml下载到本机，导入zabbix server template中

[root@hzbacksto template]# pwd
/opt/orabbix/template
[root@hzbacksto template]# ll
total 236
-rw-r--r-- 1 root root 107257 Oct 25  2011 Orabbix_export_full.xml

添加主机(名字必须跟config.props里面定义的名称的配置保持一致),所以主机名必须是cha

此处输入图片的描述

4.1 安装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
xx.xx.188.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=xx.xx.188.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@xx.xx.188.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 xx.xx.188.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=xx.xx.188.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.

4.2 定制监控 user lock

#lock the ch user
SQL> alter user ch account lock;
User altered.
SQL> select USERNAME, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE from dba_users where username = 'CH';
USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
------------------------------ -------------------------------- --------- ---------
CH                             LOCKED                           18-SEP-17
#edit the script in the x.x.x.59
[root@ct7 Pyora-master]# vim pyora.py
    def check_users(self, dbuser):
        """Determines whether a user is locked or not"""
        sql = "SELECT account_status FROM dba_users WHERE username='{0}'" \
            .format(dbuser)
        self.cur.execute(sql)
        res = self.cur.fetchall()
        for i in res:
            print i[0]
[root@ct7 Pyora-master]# python pyora.py --username system --password pass --address xx.xx.188.188 --database cha.qa.webex.com check_users CH
LOCKED

Configuration —> Templates —> item —> Create item

1
2
3

Name: Oracle/Check User status on {$DATABASE}
Key:pyora[{$USERNAME},{$PASSWORD},{$ADDRESS},{$DATABASE},check_users,{$USERS}]
Type of information: Text

此处输入图片的描述

Configuration —> Templates —> Triggers —> Create Triggers

1
2
3

Name: Oracle/Database {$DATABASE} user locked
Key:{Pyora:pyora[{$USERNAME},{$PASSWORD},{$ADDRESS},{$DATABASE},check_users,{$USERS}].regexp(LOCKED)}=1
Type of information: Text

此处输入图片的描述

第五章 zabbix Trapper and sender

zabbix获取数据有超时时间，如果一些数据需要执行比较长的时间才能获取的话，那么zabbix会出现异常，考虑到这种情况，zabbix增加了Trapper功能，客户端自己提交数据给zabbix，这个通道便是trapper.
使用trapper的步骤如下：

服务端想要接受到zabbix_sender发来的数据，先要创建 trapper item，在zabbix中配置trapper监控项

此处输入图片的描述

在所需要发送data的server上安装，zabbix sender

[root@ct7 zabbix]# wget http://repo.zabbix.com/zabbix/3.0/rhel/7/x86_64/zabbix-sender-3.0.4-1.el7.x86_64.rpm
[root@ct7 zabbix]# rpm -ivh zabbix-sender-3.0.4-1.el7.x86_64.rpm
#测试发送数据：
[root@ct7 sender]# zabbix_sender -z xx.xx.188.55 -p 10051 -s "hzvscmdb.qa.webex.com" -k topsql -o 'ttlsa.com'
info from server: "processed: 0; failed: 1; total: 1; seconds spent: 0.000034"
sent: 1; skipped: 0; total: 1
[root@ct7 zabbix]# zabbix_sender -z xx.xx.188.55 -p 10051 -s xx.xx.188.59 -k topsql -o 'ttlsa.com'
info from server: "processed: 0; failed: 1; total: 1; seconds spent: 0.000047"
sent: 1; skipped: 0; total: 1
此时发现两条数据发送都是failed， 我们来看下具体的参数意义
-z - 指定zabbix server的IP
-p - 指定zabbix server的端口，默认为10051
-s - 指定目标主机，主机名必须是配置中的hostname而不是visible name，切记
-k - 指定key，我们定义的trapper的key，这边便是我们前面定义的trap
-o - 指定要传递的数据
发现是因为 -s 的hostname是错误的，应该写pyora
[root@ct7 zabbix]# zabbix_sender -z xx.xx.188.55 -p 10051 -s "pyora" -k topsql -o 'ttlsa.com'
info from server: "processed: 1; failed: 0; total: 1; seconds spent: 0.000040"
sent: 1; skipped: 0; total: 1

查看接收到的数据

此处输入图片的描述