Zabbix 3.2.6 Batch Monitoring of Oracle tablespace through discovery

I. BACKGROUND

Receiving a task that requires monitoring all the table spaces of all databases, willing to encounter a lack of table space leading to the unavailability of the business database, we know that there are some, such as Orabbix or Pyora, that are used by Zabbix to monitor Oracle's data acquisition through Java, So we have to install Java, my previous article has explained the installation process, while the latter although the use of Python script monitoring, but our account password is exposed in the script, resulting in security degradation, so we are here through the script to implement.

Using Zabbix to monitor Oracle tablespace utilization, but this Oracle library has a lot of table space, and different server table space is not the same, I am afraid of exhausting, I would like to use discovery to do an automatic discovery, to achieve automatic batch to add the same item needs.

Second, monitoring mechanism

Oracle tablespace usage is monitored in real time, triggering an alarm mechanism when tablespace usage reaches 95%. The Oracle table space is divided into the system default Tablespace and user-created tablespace, and the table space has both auto-extended and non-auto-extended types, and the user (DBA) can specify whether to turn on table space Auto-scaling when creating the table space, depending on the application requirements. So here we need to analyze a monitoring strategy, that is, when Zabbix monitors a table space usage of 95%, let the trigger trigger a warning (Warning) message and send the message to the DBA or manager. Information information is triggered when a tablespace is detected that does not have automatic scaling turned on.

Whether the Oracle tablespace turns on auto-scaling and how much can be scaled up initially should be planned, sometimes specifying initial capacity and maximum capacity expansion at the same time when creating table spaces, so that the table space immediately has the maximum capacity to scale, rather than slowly increasing as the table space consumes. So even if we detect that the table space has auto-scaling turned on, it won't actually continue to expand. Of course, in most business scenarios it is generally not recommended to turn on table space Auto-expansion unless your business is non-critical. Back to monitoring here, when we receive both alarms at the same time, it is time to deal with the Oracle tablespace utilization rate greater than 95%.

Iii. Oracle Client Configuration

1. Create Script check_tablespace.sh

The Oracle tablespace information needs to be queried by the SQL statement, so we first create a raw script/home/oracle/check_tablespace.sh that gets the tablespace information, which is executed by the Oracle user, with the following script:

#!/bin/bash# tablespace usagep checksource ~/.bash_ profilefunction check {sqlplus -s  "/&NBSP;AS&NBSP;SYSDBA"  <<  EOFset  linesize 200set pagesize 200spool /tmp/ora_tablespace.txtselect a.tablespace_ Name, total, free, (Total-free)  as usage from  (select tablespace_name,  Sum (bytes)/1024/1024 as total from dba_data_files group by tablespace_name)  a,  (select tablespace_name, sum (bytes)/1024/1024 as free from dba_ Free_space group by tablespace_name)  bwhere a.tablespace_name =  B.tablespace_name;spool offset linesize 100set pagesize 100spool /tmp/ora_ Autex.txtselect tablespace_name,autoextensible from dba_data_files;spool offquiteof};check  &>/dev/null 

Execute the script and generate two files that hold the Oracle tablespace name information:/tmp/ora_tablespace.txt and/tmp/ora_autex.txt.

650) this.width=650; "title=" qq20171020101930.jpg "alt=" 04ceff54abd583da2d4985751afbdbf6.jpg "src=" https:// S1.51cto.com/oss/201710/20/04ceff54abd583da2d4985751afbdbf6.jpg "/>

We need to put the script in the Oracle user's Crontab Schedule Task table, so that the script executes every 15 minutes in the background, it is important to ensure that the scheduled task will be executed as planned, otherwise this may be monitored spoofing (the data obtained by the monitoring side remains the same).

[Email protected] ~]$ CRONTAB-L*/15 * * * */home/oracle/check_tablespace.sh

2. Create Script discovery_oracle_tablespace.sh

The name of the tablespace is obtained by script and converted to JSON format (because the data type obtained by Zabbix's Autodiscover function is in JSON format).

Here is a script that Zabbix automatically discovers the Oracle tablespace, and the script path here is/usr/local/zabbix/scripts/discovery_oracle_tablespace.sh.

#!/bin/bash#zabbix Discovery Oracle tablespacetable_spaces= (' cat/tmp/ora_tablespace.txt | sed-e ' 1,3d "-E"/^$/d "-e"/s ELECTED/D "|  awk ' {print '} ') length=${#table_spaces [@]} printf "{\ n" printf ' \ t ' "\" data\ ": [" for ((i=0;i< $length; i++)) do printf "\n\t\t{" printf "\" {#TABLESPACE_NAME}\ ": \" ${table_spaces[$i]}\ "}" if [$i-lt $[$length-1]];then printf ", "Fidone printf" \n\t]\n "printf"}\n "

3. Create monitoring Project Detection script tablespace_check.sh

Used for Zabbix to get Oracle tablespace usage, remaining amount, and checking if auto-scaling is turned on. The script is/usr/local/zabbix/scripts/tablespace_check.sh and the script reads as follows:

#!/bin/bash# oracle tablespace checkcehck_type=$1tablespace_name=$2  function  usagepre {    grep  "\b$tablespace_name\b"  /tmp/ora_tablespace.txt |  awk  ' {printf %.f\n ', ($2-$3)/$2*100} '}  function available {     grep  "\b$tablespace_name\b"  /tmp/ora_tablespace.txt | awk  ' {printf $ 3*1024*1024} '}  function check {    if grep  ' \b$TABLESPACE_ name\b " /tmp/ora_autex.txt | awk  ' {print $2} '  | uniq | grep  "YES"  &>/dev/null;then        echo 1     else        echo 0    fi}   case  $CEHCK _type in    pre)          usagepre ;;  &nBsp;  fre)         available ;;     check)         check ;;     *)         echo -e  "usage: $0  [pre|fre|check] [tablespace_name] "Esac

Add execute permissions to the created script.

Cd/usr/local/zabbix/scriptschmod +x tablespace_check.sh chmod +x discovery_oracle_tablespace.sh

Check that the script input is normal, such as:

[[email protected] ~]#/usr/local/zabbix/scripts/tablespace_check.sh pre system100[[email protected] ~]#/usr/local/ zabbix/scripts/tablespace_check.sh fre SYSTEM 25559040[[email protected] ~]#/usr/local/zabbix/scripts/tablespace_ check.sh Check SYSTEM 1

4. Increase monitoring key for Zabbix

Add the following parameters to the Zabbix client configuration file/etc/zabbix/zabbix_agentd.conf:

Include=/etc/zabbix/zabbix_agentd.conf.d/*.conf

Create a file/etc/zabbix/zabbix_agentd.conf.d/oracle_tablespace.conf with the following content:

Userparameter=discovery.oracle.tablespace,/usr/local/zabbix/scripts/discovery_oracle_ Tablespace.shuserparameter=tablespace.check[*],/usr/local/zabbix/scripts/tablespace_check.sh $

Restart the Zabbix client service.

Service Zabbix_agentd Restart

5, in the Zabbix Server test monitoring key

The test results are as follows and everything is OK.

[Email protected] bin]#/zabbix_get-s 10.0.2.64-k tablespace.check[pre,system]100[[email protected] bin]#./zabbix_ Get-s 10.0.2.64-k tablespace.check[fre,system]25559040[[email protected] bin]#./zabbix_get-s 10.0.2.64-k tablespace. Check[check,system]1

Iv. Zabbix Service-side configuration

1. Create a template and add an Autodiscover rule

650) this.width=650; "title=" qq20171020111815.jpg "src=" https://s4.51cto.com/oss/201710/20/ 6c0ce1348ef371d6d6b32f24a6ec1d5f.jpg "alt=" 6c0ce1348ef371d6d6b32f24a6ec1d5f.jpg "/>

Name:oracle tablespace Discovertype:zabbix AgentKey:discovery.oracle.tablespace

2. Create Item prototypes

Here the main added three item is we get to three indicators, I here only demonstrate the usage of the configuration, you can download my template by attachment, directly import.

650) this.width=650; "src=" Https://s5.51cto.com/oss/201710/20/ef16b91540c0a80300b5ee6688bdd77c.jpg "title=" Qq20171020112243.jpg "alt=" Ef16b91540c0a80300b5ee6688bdd77c.jpg "/>

Usage Rate:

650) this.width=650; "src=" Https://s2.51cto.com/oss/201710/20/b9a3a825d0be269b8c9d78d27e7a64ef.jpg "title=" Qq20171020112259.jpg "alt=" B9a3a825d0be269b8c9d78d27e7a64ef.jpg "/>

Name:oracle tablespace:{#TABLESPACE_NAME} usage Type:zabbix agentkey:tablespace.check[pre,{#TABLESPACE_NAME}]

3. Create TRIGGER Prototypes

Again, there are two triggers, and here I only demonstrate the configuration of information when the tablespace usage exceeds 95% alarms.

650) this.width=650; "src=" Https://s5.51cto.com/oss/201710/20/93b180ee137f23572bdd4f4339d8b551.jpg "title=" Qq20171020112757.jpg "alt=" 93b180ee137f23572bdd4f4339d8b551.jpg "/>

650) this.width=650; "src=" https://s5.51cto.com/oss/201710/20/e5ce7de6a7de7e3e0f61fe8b5c93f23e.jpg "style=" float: none; "title=" qq20171020112810.jpg "alt=" E5ce7de6a7de7e3e0f61fe8b5c93f23e.jpg "/>

4. Create Graph prototypes

650) this.width=650; "src=" Https://s4.51cto.com/oss/201710/20/bac73d007e053165c5ac2b05680a8e71.jpg "title=" Qq20171020114447.jpg "alt=" Bac73d007e053165c5ac2b05680a8e71.jpg "/>

V. Verification of results

After waiting for a period of discovery, we can see that the host has found the relevant monitoring items, and can execute the alarm information.

650) this.width=650; "src=" Https://s4.51cto.com/oss/201710/20/92eadd9765374005847aaa48d8e5063b.jpg "title=" Qq20171020113350.jpg "alt=" 92eadd9765374005847aaa48d8e5063b.jpg "/>

Icon of the Users table space:

650) this.width=650; "src=" Https://s2.51cto.com/oss/201710/20/a5d0363dbed8f9e80994f36c8eed20b3.jpg "title=" Qq20171020114558.jpg "alt=" A5d0363dbed8f9e80994f36c8eed20b3.jpg "/>

Note: The above monitoring interval and alarm level can be set according to their own actual situation, I just do the demo configuration.

650) this.width=650; "src=" https://s4.51cto.com/oss/201710/20/b3132173a157fda0f134948b2508af29.jpg "title=" QRCode _for_gh_891f5ff6ec4e_258.jpg "alt=" B3132173a157fda0f134948b2508af29.jpg "/>

This article is from the "Operation and maintenance bit record" blog, please make sure to keep this source http://wzlinux.blog.51cto.com/8021085/1974523

Zabbix 3.2.6 Bulk monitoring of Oracle tablespace through Discovery