Shulou(Shulou.com)06/01 Report--

Monitoring requirement

The Oracle table space utilization is monitored in real time, and the alarm mechanism is triggered when the table space utilization reaches 95%. Oracle tablespaces are divided into system default tablespaces and user-created tablespaces, and there are two types of tablespaces: automatic extension and non-automatic extension. Users (DBA) can specify whether to enable automatic tablespace expansion according to application requirements when creating tablespaces. So here we need to analyze a monitoring strategy, that is, when zabbix monitors that the utilization rate of a table space reaches 95%, then let the trigger trigger a Warning message and send an email to DBA or the manager. The Information message is triggered when it is detected that automatic extension is not turned on in the tablespace. To explain: whether the oracle table space is enabled for automatic expansion and how much the maximum expansion should be planned at the beginning, sometimes if the initial capacity and the maximum expandable capacity are specified when creating the table space, then the table space will immediately have the maximum expandable capacity, rather than slowly increase with the consumption of the table space. So even if we detect that the tablespace has auto-extension turned on, it won't actually continue to expand. Of course, it is generally not recommended to turn on tablespace auto-extension in most business scenarios, unless your business is non-critical. Back to the monitoring, when we receive these two alarm messages at the same time, we have to deal with the problem that the utilization of oracle table space is greater than 95% in time.

Monitoring mode

With the zabbix monitoring system, we can customize any monitoring project we need, first discover the oracle tablespace through the automatic discovery script, then make additional monitoring items belonging to the oracle tablespace, configure alarm triggers, and finally display the utilization and remaining amount of the oracle tablespace on the Web page of zabbix.

The original script and auto-discovery script to get the tablespace information of oracle database

The information of the Oracle tablespace needs to be queried by the SQL statement, so we first create an original script / home/oracle/bin/check_tablespace.sh to get the tablespace information, which is executed by the oracle user and written as follows:

Oracle@hmracdb1:~/bin > cat check_tablespace.sh #! / bin/bash# tablespace usagep checksource ~ / .bash_profilefunction check {sqlplus-S "/ as sysdba" / dev/null

Execute this script and generate two files with oracle tablespace name information: / tmp/ora_tablespace.txt and / tmp/ora_autex.txt

Oracle@hmracdb1:~/bin > ls-l / tmp/ora_*-rw-r--r-- 1 oracle oinstall 1030 Mar 23 20:50 / tmp/ora_autex.txt-rw-r--r-- 1 oracle oinstall 929 Mar 23 20:50 / tmp/ora_tablespace.txt oracle@hmracdb1:~/bin > cat / tmp/ora_tablespace.txtTABLESPACE_NAME TOTAL FREE USAGE -TEST11 50 49 1 SYSAUX 2048 1197 851 UNDOTBS1 2048 2036.3125 11.6875 USERS 1024 1023 1 SYSTEM 2048 1358.375 689.625 UDB_SPACE 40960 31673.875 9286.125 UNDOTBS2 2048 2035.1875 12.8125 7 rows selected. Oracle@hmracdb1:~/bin > cat / tmp/ora_autex.txtTABLESPACE_NAME AUTOEX- -SYSTEM YES SYSAUX YES UNDOTBS1 YES UNDOTBS2 YES USERS YES UDB_SPACE YES UDB_SPACE YES TEST11 YES 8 rows selected.

We need to put the script in the crontab schedule of oracle users, and let the script be executed every 5 minutes in the background. Make sure that the planned task can be executed as planned, otherwise it may be deceived by monitoring (the data obtained by the monitor will remain unchanged all the time).

Hmracdb1:~ # crontab-u oracle-l # DO NOT EDIT THIS FILE-edit the master and reinstall.# (/ tmp/crontab.XXXXj0XHiH installed on Thu Mar 23 11:38:37 2017) # (Cron version v5.0-$Id: crontab.c,v 1.12 2004-01-23 18:56:42 vixie Exp $) * / 5 * / home/oracle/bin/check_tablespace.sh

Manually execute SQL statements to get tablespace utilization

SQL > set linesize 200select ff.s tablespace_name,ff.b total, (ff.b-fr.b) usage,fr.b free, round ((ff.b-fr.b) / ff.b * 100) | |'% 'usagepfrom (select tablespace_name 's, sum (bytes) / 1024 / 1024 b from dba_data_files group by tablespace_name) ff (select tablespace_name s, sum (bytes) / 1024 / 1024 b from dba_free_space group by tablespace_name) fr 12 where ff.s = fr.s TABLESPACE_NAME TOTAL USAGE FREE USAGEP -TEST11 50 1 49 2%SYSAUX 2048 851.375 1196.625 42%UNDOTBS1 2048 12.6875 2035.3125 1%USERS 1024 1 1023 0%SYSTEM 2048 689.625 1358.375 34%UDB_SPACE 40960 9286.125 31673.875 23%UNDOTBS2 2048 12.8125 2035.1875 1% 7 rows selected.

The following is to write a script discovery_oracle_tablespace.sh that zabbix automatically discovers oracle tablespaces

Hmracdb1:~ # cat discoverydiscoverydiscoverydiscoverydiscoveryoraclefoldablespace.shangxbintablespace.shanghaixbintablespace.shangxbintablespace.shanghaixbintablespace.shangxbinapapazbix discovery oracle tablespacetable_spaces= (`Bash / tmp/ora_tablespace.txt | sed-e "1meme3D"-e "/ selected/d" | awk'{print $1}'`) length=$ {# table_spaces [@]} printf "{\ n" printf'\ t'"\" data\ ": [" for ((ipp0viciway devnull) Then echo 1 else echo 0 fi} case $CEHCK_TYPE in pre) usagepre;; fre) available;; check) check;; *) echo-e "Usage: $0 [pre | fre | check] [TABLESPACE_NAME]" esac

Verify that the output value of the script is the value taken by zabbix monitoring

Hmracdb1:~ #. / tablespace_check.sh pre SYSTEM34hmracdb1:~ #. / tablespace_check.sh fre SYSTEM1424359424hmracdb1:~ #. / tablespace_check.sh check SYSTEM1

Save all the scripts to the $ZABBIX_HOME/scpirts directory, then configure the UserParameter parameter, add the monitoring key,key name to discovery.oracle.tablespace and tablespace.check, and add two lines at the end of the following configuration file

Hmracdb1:/opt/zabbix/scripts # vim. / etc/zabbix_agentd.conf.d/userparameter_script.confUserParameter=discovery.oracle.tablespace [*], / opt/zabbix/scripts/discovery_oracle_tablespace.shUserParameter=tablespace.check [*], / opt/zabbix/scripts/tablespace_check.sh $1 $2

Restart zabbix_ahgentd

Get oracle tablespace and monitoring values on the zabbix server

[root@Zabbix_19F ~] # zabbix_get-p10050-k 'tablespace.check [pre,SYSTEM]'-s 10.xxx.xxx.xxx34 [root @ Zabbix_19F ~] # zabbix_get-p10050-k 'tablespace.check [fre,SYSTEM]'-s 10.xxx.xxx.xxx1424359424 [root @ Zabbix_19F ~] # zabbix_get-p10050-k 'tablespace.check [check,SYSTEM]'-s 10.xxx.xxx.xxx1

Zabbix Web side configuration

Create a new oracle tablespace template My_Templates_Linux_Discovery_oracle_tablespace, add auto-discovery rules, and update every 10 minutes

Add Item prototypes monitoring project, the first tablespace usage

Remaining capacity of the second tablespace

The third check whether the tablespace is enabled for automatic expansion.

Create an alarm trigger

First create a trigger that detects whether the tablespace usage is greater than 95%

Create a trigger that detects whether auto-extension is turned on

View monitoring data

At this point, zabbix monitoring oracle tablespace usage is complete.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.