Oracle监控数据增长量，12月11日更新一周脚本

枫狐狸 · 发表于 2017-12-4 16:35:17

本帖最后由枫狐狸于 2019-4-29 16:24 编辑

算是oracle监控的一个补充，在原oracle模版的基础上添加了表空间监控，存储空间监控，备份监控及整个数据库表(7天,不含UNDOTBS)的增长量监控

修改一周数据的脚本如下
create or replace view zabbix.data_increase as
select E.name,
   C.tablespace_name,
   D."total_MB",
   D."used_MB" - C."used_MB" AS "increment_MB",
   to_char(sysdate-8,'yyyy/mm/dd') || '--' ||
      to_char(sysdate-1,'yyyy/mm/dd') "TIME"
  from (select a.tablespace_id,B.name tablespace_name,
            case
               when B.name not like 'UNDO%' then
               round(A.tablespace_size * 8 / 1024)
               when B.name like 'UNDO%' then
               round(A.tablespace_size * 8 / 1024 / 2)
            END as "total_MB",
            round(A.tablespace_usedsize * 8 / 1024) "used_MB",
            A.rtime
      from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
      where A.tablespace_id = B.TS#
         and to_char(to_date(replace(rtime, '/', null),'mmddyyyy hh24:mi:ss'),'yyyymmdd hh24:mi') =to_char(sysdate-7,'yyyymmdd hh24')||':30') C,
   (select a.tablespace_id,B.name tablespace_name,
            case
               when B.name not like 'UNDO%' then
               round(A.tablespace_size * 8 / 1024)
               when B.name like 'UNDO%' then
               round(A.tablespace_size * 8 / 1024 / 2)
            END as "total_MB",
            round(A.tablespace_usedsize * 8 / 1024) "used_MB",
            A.rtime
      from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
      where A.tablespace_id = B.TS#
         and to_char(to_date(replace(rtime, '/', null),'mmddyyyy hh24:mi:ss'),'yyyymmdd hh24:mi') =to_char(sysdate-1,'yyyymmdd hh24')||':30') D,
   v$database E
where C.tablespace_name = D.tablespace_name
and c.tablespace_name not like 'UNDO%';

1、添加数据监控用户及权限

[SQL] 纯文本查看 复制代码

##数据库添加zabbix用户，并赋权
CREATE USER zabbix
 IDENTIFIED BY zabbix
 DEFAULT TABLESPACE SYSTEM
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
 ALTER USER ZABBIX DEFAULT ROLE ALL;
 GRANT CREATE view to ZABBIX;
 GRANT CREATE SESSION,RESOURCE TO ZABBIX;
 GRANT SELECT ON DBA_USERS TO ZABBIX;
 GRANT SELECT ON DBA_REGISTRY TO ZABBIX;
 GRANT SELECT ON DBA_SEGMENTS TO ZABBIX;
 GRANT SELECT ON SYS.DBA_AUDIT_SESSION TO ZABBIX;
 GRANT SELECT ON DBA_DATA_FILES TO ZABBIX;
 GRANT SELECT ON DBA_TEMP_FILES TO ZABBIX;
 GRANT SELECT ON DBA_FREE_SPACE TO ZABBIX; 
 GRANT SELECT ON sys.dba_tablespaces TO ZABBIX;
 GRANT SELECT ON dba_data_files TO ZABBIX;
 GRANT SELECT ON dba_objects TO ZABBIX;
 GRANT SELECT ON DBA_HIST_TBSPC_SPACE_USAGE TO ZABBIX;
 GRANT SELECT ON dba_autotask_client TO ZABBIX; 
 GRANT SELECT ON v_$INSTANCE TO ZABBIX;
 GRANT SELECT ON v_$LOG_HISTORY TO ZABBIX;
 GRANT SELECT ON v_$PARAMETER TO ZABBIX; 
 GRANT SELECT ON v_$LOCK TO ZABBIX;
 GRANT SELECT ON v_$LIBRARYCACHE TO ZABBIX;
 GRANT SELECT ON v_$SYSSTAT TO ZABBIX;
 GRANT SELECT ON v_$PARAMETER TO ZABBIX;
 GRANT SELECT ON v_$LATCH TO ZABBIX;
 GRANT SELECT ON v_$PGASTAT TO ZABBIX;
 GRANT SELECT ON v_$SGASTAT TO ZABBIX;
 GRANT SELECT ON v_$LIBRARYCACHE TO ZABBIX;
 GRANT SELECT ON v_$PROCESS TO ZABBIX;
 GRANT SELECT ON v_$SYSTEM_EVENT TO ZABBIX;
 GRANT SELECT ON v_$session TO ZABBIX;
 GRANT SELECT ON v_$parameter TO ZABBIX;
 GRANT SELECT ON v_$locked_object TO ZABBIX; 
 GRANT SELECT ON v_$LOG_HISTORY TO ZABBIX; 
 GRANT SELECT ON v_$archived_log TO ZABBIX; 
 GRANT SELECT ON v_$asm_diskgroup TO ZABBIX;
 GRANT SELECT ON v_$BACKUP_FILES to ZABBIX;
 GRANT SELECT ON v_$database to ZABBIX;
 GRANT SELECT ON v_$tablespace TO ZABBIX;
 GRANT SELECT ON v_$TABLESPACE TO ZABBIX;
 GRANT SELECT ON WRM$_SNAPSHOT to zabbix;
 GRANT SELECT ON WRH$_TABLESPACE to ZABBIX;
 GRANT SELECT ON WRH$_SEG_STAT_OBJ to ZABBIX;
 GRANT SELECT ON WRH$_SEG_STAT to ZABBIX;
 GRANT SELECT ON WRH$_TABLESPACE_SPACE_USAGE TO ZABBIX;

create view zabbix.database as
select * from v$database;

create view zabbix.asm_diskgroup as 
select name,
       total_mb,
       free_mb,
       round((total_mb-free_mb) / total_mb, 3) * 100 "used %"
  from v$asm_diskgroup;
  
drop view zabbix.BACKUP_FILES;
create view zabbix.BACKUP_FILES as  
select 'DATAFILE' as name,
       TO_CHAR(sum(bytes)) as bytes,
       substr(fname, 1, instr(fname, '/', 1, 3) - 1) as fname,
       TO_CHAR(completion_time,'yyyy/mm/dd') as time
  from V$BACKUP_FILES
 where status = 'AVAILABLE'
   and backup_type = 'BACKUP SET'
   and OBSOLETE = 'NO'
   and bs_type = 'DATAFILE'
 group by substr(fname, 1, instr(fname, '/', 1, 3) - 1),TO_CHAR(completion_time,'yyyy/mm/dd')
union
select 'ARCHIVED' as name,
       TO_CHAR(sum(bytes)) as bytes,
       substr(fname, 1, instr(fname, '/', 1, 3) - 1) as fname,
       TO_CHAR(completion_time,'yyyy/mm/dd') as time
  from V$BACKUP_FILES
 where status = 'AVAILABLE'
   and backup_type = 'BACKUP SET'
   and OBSOLETE = 'NO'
   and bs_type = 'ARCHIVED LOG'
 group by substr(fname, 1, instr(fname, '/', 1, 3) - 1),TO_CHAR(completion_time,'yyyy/mm/dd');

create or replace view zabbix.database as
select "DBID","NAME","CREATED","RESETLOGS_CHANGE#","RESETLOGS_TIME","PRIOR_RESETLOGS_CHANGE#","PRIOR_RESETLOGS_TIME","LOG_MODE","CHECKPOINT_CHANGE#",
"ARCHIVE_CHANGE#","CONTROLFILE_TYPE","CONTROLFILE_CREATED","CONTROLFILE_SEQUENCE#","CONTROLFILE_CHANGE#","CONTROLFILE_TIME","OPEN_RESETLOGS","VERSION_TIME",
"OPEN_MODE","PROTECTION_MODE","PROTECTION_LEVEL","REMOTE_ARCHIVE","ACTIVATION#","SWITCHOVER#","DATABASE_ROLE","ARCHIVELOG_CHANGE#","ARCHIVELOG_COMPRESSION",
"SWITCHOVER_STATUS","DATAGUARD_BROKER","GUARD_STATUS","SUPPLEMENTAL_LOG_DATA_MIN","SUPPLEMENTAL_LOG_DATA_PK","SUPPLEMENTAL_LOG_DATA_UI","FORCE_LOGGING","PLATFORM_ID",
"PLATFORM_NAME","RECOVERY_TARGET_INCARNATION#","LAST_OPEN_INCARNATION#","CURRENT_SCN","FLASHBACK_ON","SUPPLEMENTAL_LOG_DATA_FK","SUPPLEMENTAL_LOG_DATA_ALL",
"DB_UNIQUE_NAME","STANDBY_BECAME_PRIMARY_SCN","FS_FAILOVER_STATUS","FS_FAILOVER_CURRENT_TARGET","FS_FAILOVER_THRESHOLD","FS_FAILOVER_OBSERVER_PRESENT",
"FS_FAILOVER_OBSERVER_HOST","CONTROLFILE_CONVERTED","PRIMARY_DB_UNIQUE_NAME","SUPPLEMENTAL_LOG_DATA_PL","MIN_REQUIRED_CAPTURE_CHANGE#" from v$database;

##一周增长量改为前一天的，部分数据库查不出14天前的记录
create or replace view zabbix.data_increase as 
select E.name,
       C.tablespace_name,
       D."total_MB",
       D."used_MB" - C."used_MB" AS "increment_MB",
        to_char(sysdate-8,'yyyy/mm/dd') || '--' ||
        to_char(sysdate-1,'yyyy/mm/dd') "TIME"
  from (select a.tablespace_id,B.name tablespace_name,
               case
                 when B.name not like 'UNDO%' then
                  round(A.tablespace_size * 8 / 1024)
                 when B.name like 'UNDO%' then
                  round(A.tablespace_size * 8 / 1024 / 2)
               END as "total_MB",
               round(A.tablespace_usedsize * 8 / 1024) "used_MB",
               A.rtime
          from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
         where A.tablespace_id = B.TS# 
           and to_char(to_date(replace(rtime, '/', null),'mmddyyyy hh24:mi:ss'),'yyyymmdd hh24:mi') =to_char(sysdate-7,'yyyymmdd hh24')||':30') C,
       (select a.tablespace_id,B.name tablespace_name,
               case
                 when B.name not like 'UNDO%' then
                  round(A.tablespace_size * 8 / 1024)
                 when B.name like 'UNDO%' then
                  round(A.tablespace_size * 8 / 1024 / 2)
               END as "total_MB",
               round(A.tablespace_usedsize * 8 / 1024) "used_MB",
               A.rtime
          from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
         where A.tablespace_id = B.TS# 
           and to_char(to_date(replace(rtime, '/', null),'mmddyyyy hh24:mi:ss'),'yyyymmdd hh24:mi') =to_char(sysdate-1,'yyyymmdd hh24')||':30') D,
       v$database E
 where C.tablespace_name = D.tablespace_name
   and c.tablespace_name not like 'UNDO%';

exec dbms_network_acl_admin.create_acl(acl => 'zabbix.xml',description =>'zabbix acl', principal =>'ZABBIX', is_grant => true, privilege =>'resolve');
exec dbms_network_acl_admin.assign_acl(acl=> 'zabbix.xml', host =>'*');
commit;

2、添加检查脚本
实例自动发现：data_increase_discovery.sh

[Shell] 纯文本查看 复制代码

#!/bin/bash
#data_increase_discovery.sh

su - oracle -c "sqlplus -s zabbix/zabbix  > /tmp/database_increase.log<<EOF
set line 500
SET newpage none
SET heading off
SET feedback off
SET echo off
select name,'alltablespaces',sum(\"total_MB\"),sum(\"increment_MB\"),time from data_increase group by name,time;
EOF"

INCREASE=`cat /tmp/database_increase.log |awk '{print$1}'|awk ' {print}'`
COUNT=`echo "$INCREASE" |wc -l`
INDEX=0
echo '{"data":['
echo "$INCREASE" | while read LINE; do
  echo -n '{"{#INSTANCE}":"'$LINE'"}'
  INDEX=`expr $INDEX + 1`
  if [ $INDEX -lt $COUNT ]; then
     echo ','
  fi
done
echo ']}'

实例数据增长量监控值主：data_increase_check.sh

[Shell] 纯文本查看 复制代码

#!/bin/bash
#data_increase_check.sh
EQ_DATA="$2"
ZBX_REQ_DATA_TAB="$1"
SOURCE_DATA=/tmp/database_increase.log
case $2 in
  total)        grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $3}';;
  increase)     grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $4}';;
  time)         grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $5}';;
  *) echo $ERROR_WRONG_PARAM; exit 1;;
esac
exit 0

表空间自动发现，表空间值监控（见附件oracle_sql.rar）
注意：表空间监控里因为可能会有年月表的结构，所以脚本中剔除掉了“上个月”的表名。
存储空间自动发现，存储空间值监控（见附件oracle_sql.rar）
备份自动发现，备份大小及位置监控（见附件oracle_sql.rar）

3、添加KEY，并重启服务（注意下面脚本中$符号前面有个"\"）

[Shell] 纯文本查看 复制代码

## 添加 zabbix_Oracle 监控项 ##表空间监控
cat >> /etc/zabbix/zabbix_agentd.conf <<END
UserParameter=ora.tab.discovery,/home/zabbix/orabbix/scripts/oracle_discovery.sh 2>/dev/null
UserParameter=tablespace,/home/zabbix/orabbix/scripts/oracle_check.sh \$1 \$2
END
##存储空间监控
cat >> /etc/zabbix/zabbix_agentd.conf <<END
UserParameter=ora.asm.discovery,/home/zabbix/orabbix/scripts/oracle_asm_discovery.sh 2>/dev/null
UserParameter=asmspace,/home/zabbix/orabbix/scripts/oracle_asm_check.sh \$1 \$2
END
##备份监控
cat >> /etc/zabbix/zabbix_agentd.conf <<END
UserParameter=ora.bak.discovery,/home/zabbix/orabbix/scripts/oracle_bak_discovery.sh 2>/dev/null
UserParameter=bak,/home/zabbix/orabbix/scripts/oracle_bak_check.sh \$1 \$2
END
##数据增长量监控
cat >> /etc/zabbix/zabbix_agentd.conf <<END
UserParameter=data_increase.discovery,/home/zabbix/orabbix/scripts/data_increase_discovery.sh 2>/dev/null
UserParameter=data_increase,/home/zabbix/orabbix/scripts/data_increase_check.sh \$1 \$2
END

service zabbix_agentd restart

添加完的结果如下：

#####################################################
以下是附件区

模版是基于3.X版的。2.X版可能用不了

oracle_export_templates.zip (7.57 KB, 下载次数: 14)

data_increase_check.rar (299 Bytes, 下载次数: 5)

data_increase_discovery.rar (494 Bytes, 下载次数: 7)

oracle_sql.rar (2.84 KB, 下载次数: 11)

监控用户及权限.rar (1.77 KB, 下载次数: 5)

日增长量.rar (545 Bytes, 下载次数: 5)

ddc921 · 发表于 2017-12-5 09:09:11

Thanks for your shared!

枫狐狸 · 发表于 2017-12-21 15:35:18

to_char(to_date(replace(rtime, '/', null),'mmddyyyy hh24:mi:ss'),'yyyymmdd hh24:mi') =to_char(sysdate-1,'yyyymmdd hh24')||':30')
这段可以跟据实际情况去调整，数据库中自动是半小时一次的，可以根据你数据库的情况改成只对比小时：分钟（hh24:mi）或只对比小时

kaola1209 · 发表于 2018-3-26 19:33:38

感谢楼主分享，学习了

kaola1209 · 发表于 2018-3-26 19:34:25

感谢分享，学习了

sw_yp · 发表于 2018-7-3 20:33:44

感谢分享，正好用得上，谢谢谢谢

ha00012 · 发表于 2019-2-21 18:25:31

学习了。

账号		自动登录	找回密码
密码			立即注册

VMware vcenter+vSphere 6.5 U2共享

【跟谁学】韩宇极简英语课-技术人员不得不

用Zabbix通过JMX方式监控weblogic

winhex数据恢复教程（非常巨大，内容丰富）

Symantec Backup Exec 2015 2016/2012 BE20

NetScaler VPX部署之：NetScaler Gateway调

zabbix3.4.1安装部署+微信推送信息+大屏显

[经验分享] Oracle监控数据增长量，12月11日更新一周脚本

评分

扫码加入运维网微信交流群