root / plugins / oracle / oracle_connections @ 17f78427
Historique | Voir | Annoter | Télécharger (3,87 ko)
| 1 | 81fd4c97 | Stig Sandbeck Mathisen | #!/bin/bash |
|---|---|---|---|
| 2 | # |
||
| 3 | # Munin plugin to monitor oracle connections w/o DBD::Oracle, or perl for that |
||
| 4 | # matter ;-) |
||
| 5 | # |
||
| 6 | # Author: Kevin Kunkel (kunkel.kevin@gmail.com) on December 11, 2007 |
||
| 7 | # (Based off the perl munin plugin by Joan Carles Soler) |
||
| 8 | # |
||
| 9 | # Licenced under GPL v2. |
||
| 10 | # |
||
| 11 | # Usage: |
||
| 12 | # |
||
| 13 | # If required, give username, password and/or oracle server |
||
| 14 | # host through environment variables. |
||
| 15 | # |
||
| 16 | # Parameters: |
||
| 17 | # autoconf |
||
| 18 | # config (required) |
||
| 19 | # |
||
| 20 | # Config variables: |
||
| 21 | # |
||
| 22 | # ORACLE_SID - Which database to use. Defaults to orcl |
||
| 23 | # oracle_user - A oracle user account with read permission to |
||
| 24 | # the v$session view. Defaults to |
||
| 25 | # 'oracle'. Anyway, Munin must be told which user |
||
| 26 | # this plugin should be run as. |
||
| 27 | # oracle_pass - The corresponding password, if |
||
| 28 | 17f78427 | Lars Kruse | # applicable. Default to undef. |
| 29 | 81fd4c97 | Stig Sandbeck Mathisen | # |
| 30 | # SHOW_ORACLE_USERS - If set to 1 show usernames and num. of connections. |
||
| 31 | # Default is not show users (0). |
||
| 32 | # Magic markers |
||
| 33 | #%# family=auto |
||
| 34 | #%# capabilities=autoconf |
||
| 35 | |||
| 36 | |||
| 37 | # Hard-code environment variables here |
||
| 38 | |||
| 39 | #oracle_user= |
||
| 40 | #oracle_pass= |
||
| 41 | #ORACLE_SID= |
||
| 42 | #ORACLE_HOME= |
||
| 43 | #SHOW_ORACLE_USERS= |
||
| 44 | |||
| 45 | # End variable hard-code |
||
| 46 | |||
| 47 | |||
| 48 | |||
| 49 | if [ -z "$ORACLE_HOME" ] ; then |
||
| 50 | # Adjust to your oratab locations |
||
| 51 | for oratab in /var/opt/oracle/oratab /etc/oratab |
||
| 52 | do |
||
| 53 | [ ! -f $oratab ] && continue |
||
| 54 | IFS=: |
||
| 55 | while read SID HOME STARTUP; |
||
| 56 | do |
||
| 57 | if [ "$SID" = "*" ] |
||
| 58 | then |
||
| 59 | ORACLE_HOME=$HOME |
||
| 60 | break |
||
| 61 | fi |
||
| 62 | done < $oratab |
||
| 63 | [ -n "$ORACLE_HOME" ] && break |
||
| 64 | break |
||
| 65 | done |
||
| 66 | fi |
||
| 67 | |||
| 68 | if [ -z "$ORACLE_SID" ] |
||
| 69 | then |
||
| 70 | ORACLE_SID="orcl" |
||
| 71 | fi |
||
| 72 | |||
| 73 | if [ -z "$oracle_user" ] |
||
| 74 | then |
||
| 75 | oracle_user="oracle" |
||
| 76 | fi |
||
| 77 | |||
| 78 | if [ -z "$SHOW_ORACLE_USERS" ] |
||
| 79 | then |
||
| 80 | SHOW_ORACLE_USERS=0 |
||
| 81 | else |
||
| 82 | if [ $SHOW_ORACLE_USERS -ne 1 ] |
||
| 83 | then |
||
| 84 | SHOW_ORACLE_USERS=0 |
||
| 85 | fi |
||
| 86 | fi |
||
| 87 | |||
| 88 | PATH=$PATH:$ORACLE_HOME/bin |
||
| 89 | export ORACLE_HOME PATH ORACLE_SID |
||
| 90 | |||
| 91 | if [ "$1" = "autoconf" ] |
||
| 92 | then |
||
| 93 | echo yes |
||
| 94 | exit 0 |
||
| 95 | fi |
||
| 96 | if [ "$1" = "config" ] |
||
| 97 | then |
||
| 98 | WARN_CRIT=`sqlplus -s ${oracle_user}/${oracle_pass}@$ORACLE_SID << EOF | \
|
||
| 99 | grep -v '^$' | awk '{print $1 * 0.7 " " $1 * 0.8 }'
|
||
| 100 | set pagesize 0 |
||
| 101 | select value from v\\$parameter where name = 'sessions'; |
||
| 102 | EOF` |
||
| 103 | echo "graph_title Oracle active connections to $ORACLE_SID" |
||
| 104 | echo "graph_args -l 0 --base 1000" |
||
| 105 | echo "graph_vlabel Connections" |
||
| 106 | afcc3e9b | dipohl | echo "graph_category db" |
| 107 | 81fd4c97 | Stig Sandbeck Mathisen | echo "graph_info Shows active oracle connections to $ORACLE_SID" |
| 108 | echo "graph_scale no" |
||
| 109 | if [ $SHOW_ORACLE_USERS -eq 1 ] |
||
| 110 | then |
||
| 111 | sqlplus -s ${oracle_user}/${oracle_pass}@$ORACLE_SID << EOF | \
|
||
| 112 | grep -v '^$' | awk '{ print $1 ".label " $1 " active connections"
|
||
| 113 | print $1 ".info " $1 " active connections" |
||
| 114 | print $1 ".type GAUGE" }; END {
|
||
| 115 | print "total.label active connections" |
||
| 116 | print "total.info active connections" |
||
| 117 | print "total.type GAUGE" |
||
| 118 | }' |
||
| 119 | set pagesize 0 |
||
| 120 | select username, count(username) from v\$session where username is not null group by username; |
||
| 121 | EOF |
||
| 122 | 17f78427 | Lars Kruse | echo $WARN_CRIT| awk '{ print "total.warning " $1 "\ntotal.critical " $2 }'
|
| 123 | 81fd4c97 | Stig Sandbeck Mathisen | else |
| 124 | echo "connections.label active connections" |
||
| 125 | echo "connections.info active connections" |
||
| 126 | echo "connections.type GAUGE" |
||
| 127 | 17f78427 | Lars Kruse | echo $WARN_CRIT| awk '{ print "connections.warning " $1 "\nconnections.critical " $2 }'
|
| 128 | 81fd4c97 | Stig Sandbeck Mathisen | fi |
| 129 | fi |
||
| 130 | |||
| 131 | |||
| 132 | 17f78427 | Lars Kruse | if [ $SHOW_ORACLE_USERS -eq 1 ] |
| 133 | 81fd4c97 | Stig Sandbeck Mathisen | then |
| 134 | sqlplus -s ${oracle_user}/${oracle_pass}@$ORACLE_SID << EOF | \
|
||
| 135 | grep -v '^$'|awk 'BEGIN { total=0 } {
|
||
| 136 | 17f78427 | Lars Kruse | print $1 ".value " $2 |
| 137 | 81fd4c97 | Stig Sandbeck Mathisen | total=total+$2 } END { print "total.value " total }'
|
| 138 | set pagesize 0 |
||
| 139 | select username, count(username) from v\$session where username is not null group by username; |
||
| 140 | EOF |
||
| 141 | else |
||
| 142 | sqlplus -s ${oracle_user}/${oracle_pass}@$ORACLE_SID << EOF | grep -v '^$'|\
|
||
| 143 | awk '{ print "connections.value " $1 }'
|
||
| 144 | set pagesize 0 |
||
| 145 | select count(username) from v\$session where username is not null; |
||
| 146 | EOF |
||
| 147 | fi |
||
| 148 | 17f78427 | Lars Kruse | |
| 149 | 81fd4c97 | Stig Sandbeck Mathisen | echo "max_connections.value" "`sqlplus -s ${oracle_user}/${oracle_pass}@$ORACLE_SID << EOF | \
|
| 150 | grep -v '^$' | awk '{print $1 }'
|
||
| 151 | set pagesize 0 |
||
| 152 | select value from v\\$parameter where name = 'sessions'; |
||
| 153 | EOF`" |