Detailed explanation of how to use zabbix to monitor oracle database

1. Overview

Zabbix is ​​a very powerful and most widely used open source monitoring software. This article will introduce how to use Zabbix+Python to monitor the Oracle database.

2. Environment Introduction

The following is the environment I installed. The actual deployment does not need to be the same as mine.

1. Monitoring machine Redhat Linux 6.5 + Zabbix server 3.4.10 + Python 2.6.6 (included in the operating system) + Oracle Client 11.2 (x86_64)

2. Monitored machine Oracle 11.2.0.4

3. Choose monitoring method

There are three main types of zabbix monitoring methods:

Zabbix agent

Install zabbix agent on the monitored machine, and zabbix agent will send the data collected by the monitored machine to zabbix server. This method is the most commonly used and is generally used to collect information such as the server's CPU and memory.

SNMP

Some network devices, such as switches, cannot have Zabbix agent installed on them, so monitoring data such as port status and traffic can only be collected through snmp.

External check

Run the query script on the zabbix server to directly query the data on the monitored machine. This method does not require any deployment on the monitored machine. All queries are sent from the zabbix server, so the performance requirements of the zabbix server are relatively high. The official does not recommend the large-scale use of this method. This method can be used for a small number of Oracle database servers.

This article introduces how to use external check to monitor the Oracle database.

4. Planning monitoring items

The purpose of monitoring the database is to ensure the stable operation of the database. Once a failure occurs, the DBA can promptly discover and intervene to deal with it. The monitoring items are divided into the following two categories:

1. The database space is insufficient or the database fails, and the DBA needs to handle it immediately.

Monitoring items include table space, user status, instance status, locks, large number of wait events, flashback area usage, etc. This type of monitoring item needs to have a trigger set for it so that an alarm can be issued in time if an abnormality occurs.

2. Some statistical information about the database operation status provides a reference for the DBA to locate the time and category of database performance problems.

Monitoring items include the number of common waiting events, hit rate, hard parsing ratio, etc.

The following table lists the monitoring items of the template in the attachment

5. Installation

After the above gossip, let's enter the formal installation link. I assume that you have already installed zabbix server, so I will skip the installation steps of zabbix server here.

All the following operations are performed on the zabbix server

Install the Oracle client

Download the following three rpm packages from the official website

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

Install the Oracle client using root

rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

Configuring environment variables

vi + /etc/profile

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

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

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$PATH:$ORACLE_HOME/bin

Execute the following command to make the configuration take effect

source /etc/profile

Add dynamic library configuration file

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

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

/usr/lib/oracle/11.2/client64/lib

Run the ldconfig command

Connect to oracle for testing

SQL> sqlplus scott/tiger@192.168.56.101:1521/orcltest

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 24 18:24:28 2019

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>

The above prompt proves that Oracle Client is installed successfully

Install Python related packages

Install cx_Oracle (python package to connect to oracle)

wget http://downloads.sourceforge.net/project/cx-oracle/5.1.2/cx_Oracle-5.1.2-11g-py26-1.x86_64.rpm

rpm -ivh cx_Oracle-5.1.2-11g-py26-1.x86_64.rpm

Install argparse

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

python get-pip.py

pip install argparse

Upload Python script

Put the attached pyora.py script into the /usr/lib/zabbix/externalscripts/ directory

Grant permissions so that the zabbix user can execute the script

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

[Note: First create a monitoring user in the Oracle database of the monitored machine . The user name and password can be specified at will.

create user zabbix identified by zabbix;

grant connect, select any dictionary to zabbix;]

Test Scripts

python pyora.py --username zabbix --password zabbix --address 10.30.10.32 --port 1521 --database office show_tablespaces

Parameter description of the above test script

username: username

password: password

address: IP address of the monitored machine

port: port number

database: oracle service name

If there is a return result, it means the script can run normally.

Upload template file

Import the attached Pyora_ExternalCheck_11G.xml template into the zabbix server

On the zabbix page, click Configuration – Templates – Import – Select File – Import to complete the import.

View monitoring data

Monitoring – Latest Data – Host (select the corresponding host), then you can see the monitoring data

Reference: https://github.com/bicofino/Pyora

appendix:

pyora.py, Pyora_ExternalCheck_11G.xml address is on github

https://github.com/YangBaohust/zabbix_templates

This is the end of this article about how to use zabbix to monitor oracle database. For more information about zabbix monitoring oracle database, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!