zabbix3.2监控Oracle11G数据脚本
来源：互联网 发布：linux mv 文件夹 编辑：程序博客网 时间：2024/06/05 11:44
#!/bin/bash############################################################# $Name: chk_oracle.sh# $Version: v1.0# $Function: Oracle Status# $Author: SIKAO# $organization: http://10880347.blog.51cto.com/# $Create Date: 2017-08-14# $Description: Monitor Oracle Service Status############################################################# This zabbix plugin was created to check Oracle statusexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1export GG_HOME=/gg/productexport ORACLE_UNQNAME=qasdbexport ORACLE_SID=qasdb1export LIBPATH=/gg/product:/u01/app/oracle/product/11.2.0/db_1PROGNAME=`basename $0`PROGPATH=`echo $0 | sed -e 's,[\\/][^\\/][^\\/]*$,,'`ORA_SID=qasdb1TNS=qasdbUSER=xxxPASSWD=*****PATH=$PATH:$ORACLE_HOME/binLD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/libexport ORACLE_HOME PATH LD_LIBRARY_PATHISQLPLUS=/u01/app/oracle/product/11.2.0/db_1/bin/sqlplusora_pmon_status(){ pmonchk=`ps -ef | grep -v grep | grep -c "ora_pmon_"$ORA_SID""` if [ ${pmonchk} -ge 1 ] ; then echo "${pmonchk}" exit $STATE_OK else echo "${pmonchk}" exit $STATE_CRITICAL fi }ora_tns_status(){ tnschk=` tnsping $TNS` tnschk2=` echo $tnschk | grep -c OK` if [ ${tnschk2} -eq 1 ] ; then tnschk3=` echo $tnschk | sed -e 's/.*(//' -e 's/).*//'` echo "OK - reply time ${tnschk3} from $2" exit $STATE_OK else echo "No TNS Listener on $2" exit $STATE_CRITICAL fi}ora_inst_status(){result=`sqlplus -s $USER/$PASSWD <<EOFset feedback off;set heading off;set pages 0;select status from v\\$instance;EOF` if [ "$result" = "OPEN" ] ; then echo "1" exit $STATE_OK else echo "0" exit $STATE_CRITICAL fi}ora_dg_status(){ORACLE_DG_COMMAND=$1ora_dg_tlag(){tlag=`$SQLPLUS -s $USER/$PASSWD << ENDset pagesize 0 feedback off verify off heading off echo offSELECT substr(value,2,2)*24*60+substr(value,5,2)*60+substr(value,-5,2)FROM v\\$dataguard_stats WHERE NAME IN ('transport lag');END`tlag=`echo $tlag`echo "$tlag"}ora_dg_alag(){alag=`$SQLPLUS -s $USER/$PASSWD << ENDset pagesize 0 feedback off verify off heading off echo offSELECT substr(value,2,2)*24*60+substr(value,5,2)*60+substr(value,-5,2)FROM v\\$dataguard_stats WHERE NAME IN ('apply lag');END`alag=`echo $alag`echo "$alag" }ora_dg_drole(){drole=`$SQLPLUS -s $USER/$PASSWD << ENDset pagesize 0 feedback off verify off heading off echo offselect database_role from v\\$database;END`if [[ "$drole"x = "PHYSICAL STANDBY"x ]];then echo "1" exitelif [[ "$drole"x = "SNAPSHOT STANDBY"x ]];then echo "0" exit fi}case $ORACLE_DG_COMMAND in dg_tlag) ora_dg_tlag; ;; dg_alag) ora_dg_alag; ;; dg_drole) ora_dg_drole; esac}ora_recv_status(){result=`sqlplus -s $USER/$PASSWD << EOF>/tmp/ora_recv_statusset pagesize 0set numf '9999999'set feedback offselect FILE_TYPE,PERCENT_SPACE_USED from v\\$flash_recovery_area_usage;EOF`ORACLE_COMMAND=$1ora_control_file(){ cat /tmp/ora_recv_status|grep 'CONTROL FILE'|awk '{print $3}' }ora_redo_log(){ cat /tmp/ora_recv_status|grep 'REDO LOG'|awk '{print $3}' }ora_archived_log(){ cat /tmp/ora_recv_status|grep '^ARCHIVED LOG'|awk '{print $3}' }ora_backup_piece(){ cat /tmp/ora_recv_status|grep 'BACKUP PIECE'|awk '{print $3}' }ora_image_copy(){ cat /tmp/ora_recv_status|grep '^IMAGE COPY'|awk '{print $3}' }ora_flashback_log(){ cat /tmp/ora_recv_status|grep 'FLASHBACK LOG'|awk '{print $3}' }ora_foreign_archivedlog(){ cat /tmp/ora_recv_status|grep 'FOREIGN ARCHIVED LOG'|awk '{print $4}' }case $ORACLE_COMMAND in control_file) ora_control_file; ;; redo_log) ora_redo_log; ;; archived_log) ora_archived_log; ;; backup_piece) ora_backup_piece; ;; image_copy) ora_image_copy; ;; flashback_log) ora_flashback_log; ;; foreign_archivedlog) ora_foreign_archivedlog; esac}ora_tbs_status(){/bin/sh /home/oracle/local/bin/chktbs.sh}ora_load_status(){result=`sqlplus -s $USER/$PASSWD <<EOFset feedback off;set heading off;set pages 0;set serveroutput onDeclarev_mi_date Varchar2(200);v_hours_date Varchar2(200);v_begin_date Varchar2(200);v_end_date Varchar2(200);p_ac Varchar2(200);v_hours_date1 Varchar2(200);v_hours_date2 Varchar2(200);v_yyy_date Varchar2(200);Begin v_yyy_date := to_char(Sysdate,'yyyymmdd'); v_mi_date := to_char(Sysdate,'mi'); v_hours_date := to_char(Sysdate,'hh24'); v_hours_date1 := v_hours_date -1; v_hours_date2 := v_hours_date +1; If v_hours_date < 10 and v_mi_date <30 Then v_begin_date := v_yyy_date||'0'||v_hours_date1||'28'; v_end_date := v_yyy_date||'0'||v_hours_date||'00'; Elsif v_hours_date < 10 and v_mi_date >30 Then v_begin_date := v_yyy_date||'0'||v_hours_date||'00'; v_end_date := v_yyy_date||'0'||v_hours_date||'30' ; Elsif v_hours_date >= 10 and v_mi_date <30 Then v_begin_date := v_yyy_date||v_hours_date1||'28'; v_end_date := v_yyy_date||v_hours_date||'00' ; Elsif v_hours_date >= 10 and v_mi_date >30 Then v_begin_date := v_yyy_date||v_hours_date||'00'; v_end_date := v_yyy_date||v_hours_date||'30' ; End If;with long_time_2day as (select ss.INSTANCE_NUMBER, ss.SNAP_ID, sn.begin_interval_time, to_char(begin_interval_time, 'DY') week_n, sn.end_interval_time, ss.stat_name, ss.value end_value, lag(ss.value, 1) over(partition by ss.instance_number order by ss.instance_number, ss.snap_id) "begin_value(lag last)", round((ss.value - lag(ss.value, 1) over(partition by ss.instance_number order by ss.instance_number, ss.snap_id)) / 1000 / 1000) "DB time(s)", round((ss.value - lag(ss.value, 1) over(partition by ss.instance_number order by ss.instance_number, ss.snap_id)) / 1000 / 1000 / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))), 2) ac, extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time)) begin_end_s from dba_hist_sys_time_model ss, dba_hist_snapshot sn where sn.begin_interval_time >= trunc(sysdate) - 1 --and to_char(sn.begin_interval_time,'hh24mi') between '0800' and '2300' and ss.dbid = sn.dbid and ss.instance_number = sn.instance_number and ss.SNAP_ID = sn.SNAP_ID and ss.dbid = (select dbid from v\\$database) and ss.instance_number = (select instance_number from v\\$instance) --and ss.instance_number =1 and ss.stat_name = 'DB time' order by "DB time(s)" desc nulls last),long_time_only_day as (select * from long_time_2day where to_char(begin_interval_time, 'yyyymmddhh24mi') between v_begin_date and v_end_date order by "DB time(s)" desc)select trunc(ac) Into p_ac from long_time_only_day where rownum=1;dbms_output.put_line(p_ac);exception when no_data_found then p_ac:=0;dbms_output.put_line(p_ac); WHEN OTHERS THEN p_ac:=0;dbms_output.put_line(p_ac);End;/EOF`echo "$result"}ora_alert_status(){/bin/bash /home/oracle/local/bin/monalert.sh}ora_gg_status(){/bin/sh /home/oracle/local/bin/mongg.sh}ora_job_status(){/bin/sh /home/oracle/local/bin/chkjob.sh}ora_asm_status(){ORACLE_ASM_COMMAND=$1ora_asm1(){ASM=`sqlplus -s sys/****@asm2 as sysasm << ENDset head on feedback offset pages 0col name for a12col usage for a15col state for a10select name,100-round(100*free_mb/total_mb) "USED%" from v\\\$asm_diskgroupwhere name <> 'GG';END`echo "$ASM"|while read linedoperasm=`echo "$line"|awk '{print $2}'`name=`echo "$line"|awk '{print $1}'`if [ $perasm -gt 94 ] ; then echo "Warning,the diskgroup $name is used $perasm!" exit $STATE_CRITICAL else echo "ASM diskgroups are OK" exit $STATE_OKfidone}ora_asm2(){ASM=`sqlplus -s sys/*****@asm as sysasm << ENDset head on feedback offset pages 0col name for a12col usage for a15col state for a10select name,100-round(100*free_mb/total_mb) "USED%" from v\\\$asm_diskgroupwhere name <> 'GG';END`echo "$ASM"|while read linedoperasm=`echo "$line"|awk '{print $2}'`name=`echo "$line"|awk '{print $1}'`if [[ $perasm -gt 90 ]]; then echo "Warning,the diskgroup $name is used $perasm!" exit $STATE_CRITICAL else echo "ASM diskgroups are OK" exit $STATE_OKfidone}case $ORACLE_ASM_COMMAND in asm1) ora_asm1; ;; asm2) ora_asm2; esac} main(){ case $1 in pmon_status) ora_pmon_status $2; ;; tns_status) ora_tns_status $2 $3; ;; inst_status) ora_inst_status $2 $3; ;; dg_status) ora_dg_status $2 $3; ;; recv_status) ora_recv_status $2 $3; ;; tbs_status) ora_tbs_status $2 $3; ;; load_status) ora_load_status $2 $3; ;; alert_status) ora_alert_status $2 $3; ;; gg_status) ora_gg_status $2 $3; ;; job_status) ora_job_status $2 $3; ;; asm_status) ora_asm_status $2 $3; ;; *) echo $"Usage: $0 {pmon_status |tns_status |inst_status|dg_status key|recv_status key|tbs_status |load_status |alert_status|gg_status|job_status|asm_status|}" esac}main $1 $2 $3阅读全文
0 0
- zabbix3.2监控Oracle11G数据脚本
- zabbix3.2监控日志脚本
- ZABBIX3.2使用python脚本实现监控报表
- zabbix3.2自定义监控
- zabbix3.2端口监控
- zabbix3.2监控Mariadb
- Zabbix3.2监控RabbitMQ
- zabbix3.2监控TCP状态
- zabbix3.2监控apache状态
- zabbix3.2监控磁盘IO
- zabbix3.2监控JVM状态
- zabbix3.2监控MYSQL状态
- Zabbix3.2监控OGG延时
- 使用zabbix3.2监控apache
- zabbix3.2主动模式监控
- zabbix3.2短信告警脚本
- zabbix3.2-proxy实现分布式监控
- zabbix3.2 Discovery 自动化监控磁盘IO
- idea添加svn
- Insertion Sort List Leetcode java
- OC -基础（七） 学习中。。。
- extern "C"在DLL导出函数时有什么作用？
- nsq源码分析backend_queue.go
- zabbix3.2监控Oracle11G数据脚本
- 如何在网页标题栏title加入logo图标？
- mysql 5.6.24免安装版配置详解
- 【C程序】字符串拆分子串传入传出接口函数×2
- hihocoder 1185(SCC,路径和)
- C 基础
- 第五节总结
- datastage作业hang住
- poj1470的输入
