summaryrefslogtreecommitdiffstats
path: root/plugins-scripts/check_oracle.sh
blob: d58cfbea3c2291514c2e812a49dbb3d31746f1a2 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
#!/bin/sh
#
# latigid010@yahoo.com
# 01/06/2000
#
#  This Monitoring plugin was created to check Oracle status
#

PATH="@TRUSTED_PATH@"
export PATH
PROGNAME=$(basename "$0")
PROGPATH=$(echo "$0" | sed -e 's,[\\/][^\\/][^\\/]*$,,')
REVISION="@NP_VERSION@"

. "$PROGPATH"/utils.sh


print_usage() {
  echo "Usage:"
  echo "  $PROGNAME --tns <Oracle Sid or Hostname/IP address>"
  echo "  $PROGNAME --db <ORACLE_SID>"
  echo "  $PROGNAME --login <ORACLE_SID>"
  echo "  $PROGNAME --connect <ORACLE_SID>"
  echo "  $PROGNAME --cache <ORACLE_SID> <USER> <PASS> <CRITICAL> <WARNING>"
  echo "  $PROGNAME --tablespace <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
  echo "  $PROGNAME --oranames <Hostname>"
  echo "  $PROGNAME --help"
  echo "  $PROGNAME --version"
}

print_help() {
  print_revision "$PROGNAME" "$REVISION"
  echo ""
  print_usage
  echo ""
  echo "Check Oracle status"
  echo ""
  echo "--tns SID/IP Address"
  echo "   Check remote TNS server"
  echo "--db SID"
  echo "   Check local database (search /bin/ps for PMON process) and check"
  echo "   filesystem for sgadefORACLE_SID.dbf"
  echo "--login SID"
  echo "   Attempt a dummy login and alert if not ORA-01017: invalid username/password"
  echo "--connect SID"
  echo "   Attempt a login and alert if an ORA- error is returned"
  echo "--cache"
  echo "   Check local database for library and buffer cache hit ratios"
  echo "       --->  Requires Oracle user/password and SID specified."
  echo "       		--->  Requires select on v_$sysstat and v_$librarycache"
  echo "--tablespace"
  echo "   Check local database for tablespace capacity in ORACLE_SID"
  echo "       --->  Requires Oracle user/password specified."
  echo "       		--->  Requires select on dba_data_files and dba_free_space"
  echo "--oranames Hostname"
  echo "   Check remote Oracle Names server"
  echo "--help"
  echo "   Print this help screen"
  echo "--version"
  echo "   Print version and license information"
  echo ""
  echo "If the plugin doesn't work, check that the ORACLE_HOME environment"
  echo "variable is set, that ORACLE_HOME/bin is in your PATH, and the"
  echo "tnsnames.ora file is locatable and is properly configured."
  echo ""
  echo "When checking local database status your ORACLE_SID is case sensitive."
  echo ""
  echo "If you want to use a default Oracle home, add in your oratab file:"
  echo "*:/opt/app/oracle/product/7.3.4:N"
  echo ""
  support
}

case "$1" in
1)
    cmd='--tns'
    ;;
2)
    cmd='--db'
    ;;
*)
    cmd="$1"
    ;;
esac

# Information options
case "$cmd" in
--help)
    print_help
    exit "$STATE_OK"
    ;;
-h)
    print_help
    exit "$STATE_OK"
    ;;
--version)
    print_revision "$PROGNAME" "$REVISION"
    exit "$STATE_UNKNOWN"
    ;;
-V)
    print_revision "$PROGNAME" "$REVISION"
    exit "$STATE_UNKNOWN"
    ;;
esac

# Hunt down a reasonable ORACLE_HOME
if [ -z "$ORACLE_HOME" ] ; then
    # Adjust to taste
    for oratab in /var/opt/oracle/oratab /etc/oratab
    do
    [ ! -f $oratab ] && continue
    ORACLE_HOME=`IFS=:
        while read -r SID ORACLE_HOME junk;
        do
            if [ "$SID" = "$2" ] || [ "$SID" = "*" ] ; then
                echo "$ORACLE_HOME";
                exit;
            fi;
        done < $oratab`
    [ -n "$ORACLE_HOME" ] && break
    done
fi
# Last resort
[ -z "$ORACLE_HOME" ] && [ -d "$PROGPATH"/oracle ] && ORACLE_HOME=$PROGPATH/oracle

if [ "$cmd" != "--db" ]; then
    if [ -z "$ORACLE_HOME" ] || [ ! -d "$ORACLE_HOME" ] ; then
	echo "Cannot determine ORACLE_HOME for sid $2"
        exit "$STATE_UNKNOWN"
    fi
fi
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export ORACLE_HOME PATH LD_LIBRARY_PATH

case "$cmd" in
--tns)
    tnschk=$(tnsping "$2")
    tnschk2=$(echo  "$tnschk" | grep -c OK)
    if [ "${tnschk2}" -eq 1 ] ; then
        tnschk3=${tnschk##*(}; tnschk3=${tnschk3%)*}
        echo "OK - reply time ${tnschk3} from $2"
        exit "$STATE_OK"
    else
        echo "No TNS Listener on $2"
        exit "$STATE_CRITICAL"
    fi
    ;;
--oranames)
    namesctl status "$2" | awk '
    /Server has been running for:/ {
    msg = "OK: Up"
    for (i = 6; i <= NF; i++) {
        msg = msg " " $i
    }
	status = '"$STATE_OK"'
    }
    /error/ {
        msg = "CRITICAL: " $0
        status = '"$STATE_CRITICAL"'
    }
    END {
        print msg
        exit status
    }'
    ;;
--db)
    pmonchk=$(pgrep -f "(asm|ora)_pmon_${2}$")
    if [ "${pmonchk}" -ge 1 ] ; then
        echo "${2} OK - ${pmonchk} PMON process(es) running"
        exit "$STATE_OK"
    #if [ -f $ORACLE_HOME/dbs/sga*${2}* ] ; then
         #if [ ${pmonchk} -eq 1 ] ; then
    #utime=`ls -la $ORACLE_HOME/dbs/sga*$2* | cut -c 43-55`
            #echo "${2} OK - running since ${utime}"
            #exit $STATE_OK
         #fi
    else
        echo "${2} Database is DOWN"
        exit "$STATE_CRITICAL"
    fi
    ;;
--login)
    loginchk=$(sqlplus dummy/user@"$2" < /dev/null)
    loginchk2=$(echo "$loginchk" | grep -c ORA-01017)
    if [ "${loginchk2}" -eq 1 ] ; then
        echo "OK - dummy login connected"
        exit "$STATE_OK"
    else
        loginchk3=$(echo "$loginchk" | grep "ORA-" | head -1)
        echo "CRITICAL - $loginchk3"
        exit "$STATE_CRITICAL"
    fi
    ;;
--connect)
    connectchk=$(sqlplus "$2" < /dev/null)
    connectchk2=$(echo "$connectchk" | grep -c ORA-)
    if [ "${connectchk2}" -eq 0 ] ; then
        echo "OK - login successful"
        exit "$STATE_OK"
    else
        connectchk3=$(echo "$connectchk" | grep "ORA-" | head -1)
        echo "CRITICAL - $connectchk3"
        exit "$STATE_CRITICAL"
    fi
    ;;
--cache)
    if [ "${5}" -gt "${6}" ] ; then
        echo "UNKNOWN - Warning level is less then Crit"
        exit "$STATE_UNKNOWN"
    fi
    result=`sqlplus -s "${3}"/"${4}"@"${2}" << EOF
set pagesize 0
set numf '9999999.99'
select (1-(pr.value/(dbg.value+cg.value)))*100
from v\\$sysstat pr, v\\$sysstat dbg, v\\$sysstat cg
where pr.name='physical reads'
and dbg.name='db block gets'
and cg.name='consistent gets';
EOF`

    if echo "$result" | grep -q 'ORA-' ; then
        error=$(echo "$result" | grep "ORA-" | head -1)
        echo "CRITICAL - $error"
        exit "$STATE_CRITICAL"
    fi

    buf_hr=$(echo "$result" | awk '/^[0-9\. \t]+$/ {print int($1)}')
    buf_hrx=$(echo "$result" | awk '/^[0-9\. \t]+$/ {print $1}')
    result=`sqlplus -s "${3}"/"${4}"@"${2}" << EOF
set pagesize 0
set numf '9999999.99'
select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100
from v\\$librarycache lc;
EOF`

    if echo "$result" | grep -q 'ORA-' ; then
        error=$(echo "$result" | grep "ORA-" | head -1)
        echo "CRITICAL - $error"
        exit "$STATE_CRITICAL"
    fi

    lib_hr=$(echo "$result" | awk '/^[0-9\. \t]+$/ {print int($1)}')
    lib_hrx=$(echo "$result" | awk '/^[0-9\. \t]+$/ {print $1}')

    if [ "$buf_hr" -le "${5}" ] || [ "$lib_hr" -le "${5}" ] ; then
        echo "${2} CRITICAL - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"
        exit "$STATE_CRITICAL"
    fi
    if [ "$buf_hr" -le "${6}" ] || [ "$lib_hr" -le "${6}" ] ; then
        echo "${2} WARNING  - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"
        exit "$STATE_WARNING"
    fi
    echo "${2} OK - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"

    exit "$STATE_OK"
    ;;
--tablespace)
    if [ "${6}" -lt "${7}" ] ; then
        echo "UNKNOWN - Warning level is more then Crit"
        exit "$STATE_UNKNOWN"
    fi
    result=`sqlplus -s "${3}"/"${4}"@"${2}" << EOF
set pagesize 0
set numf '9999999.99'
select NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc
from (
select tablespace_name,sum(bytes)/1024/1024 total
from dba_data_files group by tablespace_name) A
LEFT OUTER JOIN
( select tablespace_name,sum(bytes)/1024/1024 free
from dba_free_space group by tablespace_name) B
ON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='${5}';
EOF`

    if echo "$result" | grep -q 'ORA-' ; then
        error=$(echo "$result" | grep "ORA-" | head -1)
        echo "CRITICAL - $error"
        exit "$STATE_CRITICAL"
    fi

    ts_free=$(echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($1)}')
    ts_total=$(echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($2)}')
    ts_pct=$(echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($3)}')
    ts_pctx=$(echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print $3}')
    if [ "$ts_free" -eq 0 ] && [ "$ts_total" -eq 0 ] && [ "$ts_pct" -eq 0 ] ; then
        echo "No data returned by Oracle - tablespace $5 not found?"
        exit "$STATE_UNKNOWN"
    fi
    if [ "$ts_pct" -ge "${6}" ] ; then
        echo "${2} : ${5} CRITICAL - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
        exit "$STATE_CRITICAL"
    fi
    if [ "$ts_pct" -ge "${7}" ] ; then
        echo "${2} : ${5} WARNING  - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
        exit "$STATE_WARNING"
    fi
    echo "${2} : ${5} OK - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
    exit "$STATE_OK"
    ;;
*)
    print_usage
		exit "$STATE_UNKNOWN"
esac