Using ZABBIX to monitor the operation flow of Oracle tablespace

Time:2021-10-21

0. General

ZABBIX is an extremely powerful open source monitoring tool. Let me share how ZABBIX monitors tablespaces. Following this idea, monitoring other items is similar.

The precondition is that you already have ZABBIX server and ZABBIX agent, and ZABBIX agent and Oracle database run on the same machine.

1. Prepare the script on the agent

a. Script to define table space usage

/home/oracle/get_tablespace_usage.sh


#!/bin/bash
# get tablespace usage
source ~/.bash_profile
function check {
sqlplus -S "/ as sysdba" <<  EOF
set linesize 200 pagesize 200
set feedback off heading off
spool /tmp/tablespace.log
select tablespace_name,round(used_percent) used_percent from dba_tablespace_usage_metrics;
spool off
quit
EOF
};
check &> /dev/null
errors=`grep ERROR /tmp/tablespace.log | wc -l`
if [ "$errors" -gt 0 ]; then
    echo "" > /tmp/tablespace.log
fi

chown oracle: get_tablespace_usage.sh

chmod 755 get_tablespace_usage.sh

b. Define scripts for automatic table space discovery

/etc/zabbix/scripts/discovery_tablespace.sh


#!/bin/bash
# zabbix auto discovery oracle tablespace
tablespaces=(`cat /tmp/tablespace.log | awk '{print $1}' | grep -v "^$"`)
length=${#tablespaces[@]}
printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i<$length;i++))
do
    printf "\n\t\t{"
    printf "\"{#TABLESPACE_NAME}\":\"${tablespaces[$i]}\"}"
    if [ $i -lt $[$length-1] ];then
        printf ","
    fi
done
    printf "\n\t]\n"
printf "}\n"

chmod 755/etc/zabbix/scripts/discovery_tablespace.sh

c. Define tablespace monitor script

/etc/zabbix/scripts/tablespace_check.sh


#!/bin/bash
# oracle tablespace check
TABLESPACE_NAME=$1
grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.log | awk '{print $2}'

chmod 755/etc/zabbix/scripts/tablespace_check.sh

2. Put script a into crontab

su – oracle

crontab -e

*/5 * * * * /home/oracle/get_tablespace_usage.sh

Check / TMP / tablespace.log for the execution results. The first column is the name of the tablespace, and the second column is the corresponding tablespace usage


EXAMPLE                                   2                                                                                                                                                             
SYSAUX                                    3                                                                                                                                                             
SYSTEM                                    5                                                                                                                                                             
TBS01                                    85                                                                                                                                                             
TEMP                                      0                                                                                                                                                             
UNDOTBS1                                  0                                                                                                                                                             
USERS                                     1

3. Edit agent parameters

vi /etc/zabbix/zabbix_agentd.d/userparameter_oracle.conf
# tablespace usage
UserParameter=discovery.tablespace,/etc/zabbix/scripts/discovery_tablespace.sh
UserParameter=tablespace.check.[*],/etc/zabbix/scripts/tablespace_check.sh $1

4. Set relevant options in the ZABBIX web interface

a. When creating a template, you can name it casually. Here I define a macro

b. Create an autodiscover rule

c. Create monitor item prototype

d. Create trigger type

e. Create a graphic prototype

5. Test

I will create a table in a table space and insert data to make it more than 80% to see if it alarms

Test passed!

This is the end of this article about the operation process of using ZABBIX to monitor Oracle tablespaces. For more information about ZABBIX monitoring Oracle tablespaces, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]