Using Zabbix to monitor the operation process of Oracle table space

0. Overview

Zabbix is ​​an extremely powerful open source monitoring tool. Below I will share how Zabbix monitors tablespaces. Following this idea, monitoring other items is similar.

The prerequisite is that you already have zabbix server and zabbix agent, and zabbix agent and oracle database are running on the same machine.

1. Prepare the script on the agent

a. Define the script for 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 the script for automatic discovery of tablespaces

/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 monitoring item 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

The execution results are in /tmp/tablespace.log. 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. Create a template. You can name the template as you like. Here I define a macro

b. Create automatic discovery rules

c. Create monitoring item prototype

d. Create a trigger type

e. Create a graphic prototype

5. Testing

I created a table in a tablespace and inserted data to make it exceed 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 table space. For more relevant content about zabbix monitoring oracle table space, 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!