使用zabbix自动发现规则监控oracle分区表的分区键的合法

一、 创建从数据库查询原始数据脚本

1. 编写脚本query_table.sh

vim query_table.sh

#!/bin/bash
temp_outfile="/tmp/table.source"
outfile="/tmp/table.txt"
source /home/oracle/.bash_profile

sqlplus -s zabbix/zabbix>${temp_outfile}<<EOF
col table_owner for a10
col table_name for a10
col partition_name for a10
col high_value for a150
set long 999999
set linesize 200
set pagesize 10000
set heading off
set feedback off
set termout off
set echo off
select rn,table_owner,table_name,partition_name,high_value from 
(select table_owner,table_name,partition_name,high_value,row_number()over(partition by table_owner,table_name order by partition_position desc) as rn from dba_tab_partitions where interval<>'YES' and table_owner not in ('SYS','SYSTEM','AUDSYS','MDSYS'))
where rn<3 order by 2,3,1;
quit;
EOF

sed -i '/^$/d' $temp_outfile

function high_value_convert(){
if [[ "$1" =~ [0-9]+-[0-9]+-[0-9]+  ]];then
 echo  $(echo "$1"|perl -pe "s/.*?([0-9]+)-([0-9]+)-([0-9]+).*/\1\2/")
elif [[ "$1" =~ [0-9]{8} ]]||[[ "$1" =~ [0-9]{6} ]];then
 echo $(echo "$1"|perl -pe "s/.*?([0-9]{6}).*/\1/")
fi
}

function get_result(){
current_date=$(date '+%Y%m')
if [ $(high_value_convert "$var_high_value") -le "$current_date" ];then
echo $line
fi
}


flag_id=0
while read line 
do
var_high_value=$(echo $line|awk '{for(i=5;i<=NF;i++){if(i<NF){printf $i}else{print $i}}}')
var_rn=$(echo $line|awk '{print $1}')
if [[ "$var_high_value" == "MAXVALUE" ]];then
flag_id=$((flag_id+1))
continue
elif [[ "$var_high_value" != "MAXVALUE" ]]&&[[ "$var_rn" == "1" ]];then
get_result 
elif [[ "$var_high_value" != "MAXVALUE" ]]&&[[ "$var_rn" == "2" ]]&&[[ "$flag_id" == "1" ]];then
get_result
flag_id=$((flag_id-1))
fi
done<$temp_outfile>$outfile

rm -fr $temp_outfile

sed -i 's/^[1-2]//' $outfile

chmod a+x /home/oracle/shellscripts/query_table.sh

2. 脚本执行后的截图
   

3. 将脚本添加到crontab中
   crontabl -e

*/1 * * * * /home/oracle/shellscripts/query_table.sh > /home/oracle/shellscripts/oracle_cron.sh.log 2>&1

二、 创建自定义发现规则脚本

创建生成json数据的脚本
vim /etc/zabbix/scripts/oracle_discovery_partition.sh

#!/bin/bash
table_name=(`cat /tmp/table.txt|awk '{print $1"."$2}'`)
length=${#table_name[@]}
 
printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i<$length;i++))
do
    printf "\n\t\t{"
    printf "\"{#TABLE_NAME}\":\"${table_name[$i]}\"}"
    if [ $i -lt $[$length-1] ];then
        printf ","
    fi
done
    printf "\n\t]\n"
printf "}\n"

chmod a+x /etc/zabbix/scripts/oracle_discovery_partition.sh
执行结果如图

三、 创建自定义监控原型脚本

vim /etc/zabbix/scripts/oracle_check_partition.sh

#!/bin/bash
TABLE_NAME="$1"
table_owner=${TABLE_NAME%.*}
table_name=${TABLE_NAME#*.}
SOURCE_DATA=/tmp/table.txt
grep "\b${table_owner}\b" ${SOURCE_DATA} |grep "\b${table_name}\b"

chmod a+x /etc/zabbix/scripts/oracle_check_partition.sh
测试脚本

四、 配置zabbix agent端

vim /etc/zabbix/zabbix_agentd.d/userparameter_oracle.conf

UserParameter=table.discovery,/etc/zabbix/scripts/oracle_discovery_partition.sh
UserParameter=table.check[*],/etc/zabbix/scripts/oracle_check_partition.sh "$1"

重启agent服务
systemctl restart zabbix-agent

在zabbix server端测试键值配置是否正确

zabbix_get -s 192.168.1.137 -k "table.discovery"
 
zabbix_get -s 192.168.1.137 -k "table.check[TXB.T01]"

五、 在zabbix中web界面配置

1. 在被监控的主机中创建自动发现规则
   

2. 在自动发现规则下创建监控原型
   

3. 在监视中查看最新数据展示

六、 oracle分区表测试数据模版

select owner,table_name from dba_tables where owner='TXB';
select 'drop table '||owner||'.'||table_name||';' from dba_tables where owner='TXB';

当分区键是日期类型时候
create table txb.t01
(
    id integer primary key,
    retail_date date,
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than(to_date('2024-01-01','yyyy-mm-dd')) tablespace USERS,
   partition par_02 values less than(to_date('2024-02-01','yyyy-mm-dd')) tablespace USERS
);

create table txb.t02
(
    id integer primary key,
    retail_date date,
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than(to_date('2024-01-01','yyyy-mm-dd')) tablespace USERS,
   partition par_02 values less than(to_date('2024-04-01','yyyy-mm-dd')) tablespace USERS
);

create table txb.t03
(
    id integer primary key,
    retail_date date,
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than(to_date('2024-01-01','yyyy-mm-dd')) tablespace USERS,
   partition par_02 values less than(to_date('2024-03-01','yyyy-mm-dd')) tablespace USERS,
   partition par_03 values less than (maxvalue) tablespace USERS
);

create table txb.t04
(
    id integer primary key,
    retail_date date,
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than(to_date('2024-01-01','yyyy-mm-dd')) tablespace USERS,
   partition par_02 values less than(to_date('2024-05-01','yyyy-mm-dd')) tablespace USERS,
   partition par_03 values less than (maxvalue) tablespace USERS
);

create table txb.t05
(
    id integer primary key,
    retail_date date,
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than(to_date('20240201','YYYYMMDD')) tablespace USERS,
   partition par_02 values less than(to_date('20240301','YYYYMMDD')) tablespace USERS
);

范围分区分区键数据类型是数字
create table txb.t06
(
    id integer primary key,
    retail_date number,
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than(20240101) tablespace USERS,
   partition par_02 values less than(20240301) tablespace USERS
);

create table txb.t07
(
    id integer primary key,
    retail_date number,
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than(20240101) tablespace USERS,
   partition par_02 values less than(20240401) tablespace USERS
);

当分区键的数据类型是字符串的时候
create table txb.t08
(
    id integer primary key,
    retail_date varchar2(50),
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than('20240101') tablespace USERS,
   partition par_02 values less than('20240301') tablespace USERS
);

create table txb.t09
(
    id integer primary key,
    retail_date varchar2(50),
    ware_name varchar2(50)
)
partition by range(retail_date)
(
   partition par_01 values less than('20240101') tablespace USERS,
   partition par_02 values less than('20240328') tablespace USERS
);