一、orabbix1.2.3

Orabbix 是一个用来监控 Oracle 数据库实例的 Zabbix 插件,在zabbix4版本中,orabbix1.2.3并不支持,网上也提供了解决方法。
链接:https://github.com/snickerjp/orabbix
orabbix1.2.3下载地址:https: //sourceforge.net/projects/orabbix/

二、jkd1.8安装

##1. 解压缩
# tar zvxf jdk1.8.tar.gz -C /data
# mv /data/jdk1.8.0_171/ /data/jdk1.8
##2. 添加环境变量
# vim /etc/profile.d/java.sh
export JAVA_HOME=/data/jdk1.8
export JRE_HOME=/data/jdk1.8/jre
export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
export CLASSPATH=./:$JAVA_HOME/lib:$JRE_HOME/lib
##3. 变量生效
# source /etc/profile.d/java.sh
# java -version
# javac -version

三、 安装配置orabbix

3.1、安装

# mkdir /data/orabbix
# cd /data/orabbix
# git clone https://github.com/snickerjp/orabbix.git

##1.解压orabbix-1.2.3.zip
# unzip /data/orabbix/orabbix-1.2.3.zip
##编译
# javac -cp "orabbix/orabbix-1.2.3.jar:orabbix/lib/* " com/smartmarmot/orabbix/Sender.java
# mkdir build
# cd build
# jar -xvf orabbix-1.2.3.jar com
# cp ../com/smartmarmot/orabbix/Sender.class com/smartmarmot/orabbix/Sender.class
##选择覆盖
# jar -uf orabbix-1.2.3.jar com

3.2、配置config.props

# cp /data/orabbix/conf/config.props.sample /data/orabbix/conf/config.props
# cd /data/orabbix/conf
# vim config.props
ZabbixServerList=ZabbixServer

ZabbixServer.Address=zabbix ip
ZabbixServer.Port=10051

#pidFile
OrabbixDaemon.PidFile=./logs/orabbix.pid
#frequency of item's refresh
OrabbixDaemon.Sleep=300
#MaxThreadNumber should be >= than the number of your databases
OrabbixDaemon.MaxThreadNumber=100

#put here your databases in a comma separated list
DatabaseList=192.168.1.138,192.168.1.133,192.168.1.135,192.168.1.122,192.168.1.142
DatabaseList.MaxActive=10
#The maximum number of milliseconds that the pool will wait
#(when there are no available connections) for a connection to be returned
#before throwing an exception, or <= 0 to wait indefinitely.
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1

#define here your connection string for each database
192.168.1.138.Url=jdbc:oracle:thin:@192.168.1.138:1521:PROD
192.168.1.138.User=zabbix
192.168.1.138.Password=zabbix
....
ip.Url=jdbc:oracle:thin:@10.78.1.33:1521:BIP
ip.User=zabbix
ip.Password=zabbix
....
192.168.1.142.Url=jdbc:oracle:thin:@192.168.1.142:1521:SBD
192.168.1.142.User=zabbix
192.168.1.142.Password=zabbix

###起orabbix
# chmod +x /data/orabbix/run.sh
# sh /data/orabbix/run.sh
#  ps -ef|grep orabbix
root      46977  42534  0 14:01 pts/1    00:00:00 grep --color=auto orabbix
root     115288      1  0 Oct28 ?        00:01:37 java -Duser.language=en -Duser.country=US -Dlog4j.configuration=./conf/log4j.properties -cp lib/commons-codec-1.4.jar:lib/commons-dbcp-1.4.jar:lib/commons-lang-2.5.jar:lib/commons-logging-1.1.1.jar:lib/commons-pool-1.5.4.jar:lib/hsqldb.jar:lib/log4j-1.2.15.jar:lib/ojdbc6.jar:.:./orabbix-1.2.3.jar com.smartmarmot.orabbix.bootstrap start ./conf/config.props
DatabaseList指的是被监控服务器的名称,该名称要和zabbix server界面中的机器名称保持一致,该配置文件中后续所引用的设定都以该名称为准。

DB1.Url=jdbc:oracle:thin:@ 192.168.1.121:1521:powerdes指定的是被监控服务器的连接信息(例如采用jdbc的联接方式,服务器地址是192.168.1.121,oracle的端口是1521,powerdes指的是数据库的实例名称)

四、创建oracle监控帐号,并给相应权限

如果使用的是oracle 11g,继续添加如下命令,开放ACL的访问控制,否则在监控的过程中有部份内容无法正常显示

SQL> exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description=> 'resolve acl', principal =>'ZABBIX', is_grant => true, privilege => 'resolve'); 
PL/SQLprocedure successfully completed. 
SQL> exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host =>'*'); 
PL/SQLprocedure successfully completed. 
SQL> commit; 
Commit complete. 

五、zabbix页面导入模板,关联到相应主机

5.1、模板下载

wget www.hejingping.cn/1/zbx_orabbix_templates.xml

5.2、导入模板

导模板

5.3、主机关联模板

5.4、查看最新数据

六、Oracle表空间监控

  • 在Oracle主机上操作:
    6.1 切换Oracle用户添加脚本
# cat /home/oracle/bin/check_tablespace.sh
#!/bin/bash
# tablespace usagep check
source ~/.bash_profile
function check {
sqlplus -S "system/Oracle202012345" <<  EOF
set linesize 200
set pagesize 300
spool /tmp/ora_tablespace.txt
select a.tablespace_name,a.msize allocate_g,b.msize used_m ,a.msize-b.msize free_m
  from
     (select tablespace_name,round(sum(maxbytes)/1024/1024/1024,2) mSize
       from (
            select tablespace_name,decode(maxbytes,0,bytes,maxbytes) maxbytes
               from dba_data_files
       )
          group by tablespace_name
     ) a left join
     (select tablespace_name, round(sum(bytes)/1024/1024/1024,2) mSize
       from dba_segments
          group by tablespace_name
     ) b on a.tablespace_name=b.tablespace_name
     where a.tablespace_name not in ('SYSAUX','SYSTEM','UNDOTBS1','PSAPSR3USR','PSAPUNDO','CAMPAIGN_OP','DM')
      order by 1,4 desc;
spool off
set linesize 100
set pagesize 100
spool /tmp/ora_autex.txt
select tablespace_name,autoextensible from dba_data_files;
spool off
quit
EOF
};check &>/dev/null

查询表空间:
对脚本进行修改:Oracle用户密码,并对表进行过滤

  • 添加定时任务
crontab -u oracle –l
*/5 * * * * /home/oracle/bin/check_tablespace.sh
  • 安装agent并开启远程脚本权限

修改agent配置文件
脚本配置路径参数:Include=(绝对路径,不要放在/root目录下)
权限:UnsafeUserParameters=1
将监控脚本放入路径内并赋予执行权限

表名称: discovery_oracle_tablespace.sh

# cat discovery_oracle_tablespace.sh
#!/bin/bash
#zabbix discovery oracle tablespace
table_spaces=(`cat /tmp/ora_tablespace.txt | sed -e "1,3d" -e "/^$/d" -e "/selected/d" | awk '{print $1}'`)
length=${#table_spaces[@]}
printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i<$length;i++))
do
    printf "\n\t\t{"
    printf "\"{#TABLESPACE_NAME}\":\"${table_spaces[$i]}\"}"
    if [ $i -lt $[$length-1] ];then
        printf ","
    fi
done
    printf "\n\t]\n"
printf "}\n" 

# chmod +x discovery_oracle_tablespace.sh

表空间: check_tablespace.sh

# cat check_tablespace.sh
#!/bin/bash
# oracle tablespace check
CHECK_TYPE=$1
TABLESPACE_NAME=$2
  
function ALL {
    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{print $2}'|sed '$s/%$//'
}
function USED {
    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{print $3}'|sed '$s/M$//'
}
function FREE {
    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{print $4}'|sed '$s/M$//'
}
case $CHECK_TYPE in
    all)
        ALL ;;
    used)
        USED ;;
    free)
        FREE ;;
    *)
        echo -e "Usage: $0 [all|used|free] [TABLESPACE_NAME]"
esac

# chmod +x check_tablespace.sh

脚本配置文件:sql_tablespace.conf

cat sql_tablespace.conf
UserParameter=discovery.oracle.tablespace[*],/etc/zabbix/scripts/discovery_oracle_tablespace.sh
UserParameter=tablespace.check[*],/etc/zabbix/scripts/tablespace_check.sh $1 $2
UserParameter=sql.log[*],cat /tmp/sql_log.txt | tr -cd "[0-9]"

启动zabbix agent

监控界面添加主机监控并添加模版
oracle_表空间

最后修改:2020 年 11 月 13 日 05 : 26 PM
如果觉得我的文章对你有用,请随意赞赏