## 系统 Centos 6
# 获取oracle session process值
############### oracle 中执行 ######################
cat session.sh
#!/bin/bash
#
# Name:oracle会话及进程数查询,session.sql,session.sh请勿删除,zabbix中可直接查询
# Version Number:1.00
# Type:bash
# Language:bash shell
# Date:2017-07-10
# Author:xiong
export ORACLE_HOME=/此处定义oracle家目录
export ORACLE_SID=此处定义oracle_sid
ORACLE_USER=帐号
ORACLE_PASSWD=密码 # 建议创建一个select查询用户
$ORACLE_HOME/bin/sqlplus $ORACLE_USER/$ORACLE_PASSWD &>/dev/null << EOF @/home/oracle/script/session.sql
EOF
[oracle@oracle script]$ cat session.sql
define fi1= '/tmp/oracle_session_process.txt'
spool &fi1
select count(*) as Sessions from v$session;
select count(*) as process from v$process;
spool off
# 在Oracle 提供spool工具实现sql结果数据输出到文件中
[oracle@oracle script]$ cat session.sql
define fi1= '/tmp/oracle_session_process.txt'
spool &fi1
select count(*) as Sessions from v$session;
select count(*) as process from v$process;
spool off
########### 文件会输出到/tmp目录下去 ###############
# 定义一个计划任务
crontab -e
#### 每分钟执行一次上面的那个脚本，输出session,process值
*/1 * * * * /bin/bash /home/oracle/script/session.sh
############### 此处在oracle本地的zabbix_agentd中定义
### 在配置文件中定义key
/usr/local/zabbix/etc/zabbix_agentd.conf
UserParameter=oracle.status[*],/usr/local/zabbix/script/oracle_session_process.sh $1
##### 获取ID脚本
[root@oracle script]# cat oracle_session_process.sh
#!/bin/bash
#
# Name:oracle会话及进程数查询 查询目录/tmp/oracle_session_process.txt
# Version Number:1.00
# Type:bash
# Language:bash shell
# Date:2017-07-10
# Author:xiong
# Email:812374156@qq.com
ORACLE_NUM1=/tmp/oracle_session_process.txt
case $1 in
Sessions)
/bin/grep '[1-9]' $ORACLE_NUM1 | /bin/awk '{print $1}' | /usr/bin/head -1 ;;
Process)
/bin/grep '[1-9]' $ORACLE_NUM1 | /bin/awk '{print $1}' | /usr/bin/tail -1 ;;
*)
echo "please Use Sessions | Process"
esac
############# 配置好之后重启服务 #################
service zabbix_agentd restart
################ zabbix_server 中先获取一下值 #############
# 如果可以直接上web端定义，不行的话先检查防火墙，iptables,selinux
zabbix_get -s 10.200.200.209 -p 20986 -k oracle.status[Sessions]
######################### oracle 状态模板 ###############################
########## 直接定义成xx.xml 然后导入就成##############
<?xml version="1.0" encoding="UTF-8"?>
<zabbix_export>
<version>3.2</version>
<date>2017-07-10T10:07:00Z</date>
<groups>
<group>
<name>Templates</name>
</group>
</groups>
<templates>
<template>
<template>Templates_oracle_status</template>
<name>Templates_oracle_status</name>
<description/>
<groups>
<group>
<name>Templates</name>
</group>
</groups>
<applications>
<application>
<name>oracles_status</name>
</application>
</applications>
<items>
<item>
<name>oracle_process</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>oracle.status[Process]</key>
<delay>90</delay>
<history>10</history>
<trends>30</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>0</delta>
<snmpv3_contextname/>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authprotocol>0</snmpv3_authprotocol>
<snmpv3_authpassphrase/>
<snmpv3_privprotocol>0</snmpv3_privprotocol>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications/>
<valuemap/>
<logtimefmt/>
</item>
<item>
<name>oracle_session</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>oracle.status[Sessions]</key>
<delay>90</delay>
<history>10</history>
<trends>30</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>0</delta>
<snmpv3_contextname/>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authprotocol>0</snmpv3_authprotocol>
<snmpv3_authpassphrase/>
<snmpv3_privprotocol>0</snmpv3_privprotocol>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications/>
<valuemap/>
<logtimefmt/>
</item>
</items>
<discovery_rules/>
<httptests/>
<macros/>
<templates/>
<screens/>
</template>
</templates>
<graphs>
<graph>
<name>oracle_status</name>
<width>900</width>
<height>200</height>
<yaxismin>0.0000</yaxismin>
<yaxismax>100.0000</yaxismax>
<show_work_period>1</show_work_period>
<show_triggers>1</show_triggers>
<type>0</type>
<show_legend>1</show_legend>
<show_3d>0</show_3d>
<percent_left>0.0000</percent_left>
<percent_right>0.0000</percent_right>
<ymin_type_1>0</ymin_type_1>
<ymax_type_1>0</ymax_type_1>
<ymin_item_1>0</ymin_item_1>
<ymax_item_1>0</ymax_item_1>
<graph_items>
<graph_item>
<sortorder>0</sortorder>
<drawtype>2</drawtype>
<color>000088</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Templates_oracle_status</host>
<key>oracle.status[Process]</key>
</item>
</graph_item>
<graph_item>
<sortorder>1</sortorder>
<drawtype>2</drawtype>
<color>AAAA00</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Templates_oracle_status</host>
<key>oracle.status[Sessions]</key>
</item>
</graph_item>
</graph_items>
</graph>
</graphs>
</zabbix_export>
