?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Cleanup config.nodes_of
[check_mk.git] / agents / plugins / mk_oracle
blob0237ac62b7cdf35aa9d025021764bcb3f52a62f2
1 #!/bin/bash
2 # +------------------------------------------------------------------+
3 # | ____ _ _ __ __ _ __ |
4 # | / ___| |__ ___ ___| | __ | \/ | |/ / |
5 # | | | | '_ \ / _ \/ __| |/ / | |\/| | ' / |
6 # | | |___| | | | __/ (__| < | | | | . \ |
7 # | \____|_| |_|\___|\___|_|\_\___|_| |_|_|\_\ |
8 # | |
9 # | Copyright Mathias Kettner 2014 mk@mathias-kettner.de |
10 # +------------------------------------------------------------------+
11 #
12 # This file is part of Check_MK.
13 # The official homepage is at http://mathias-kettner.de/check_mk.
14 #
15 # check_mk is free software; you can redistribute it and/or modify it
16 # under the terms of the GNU General Public License as published by
17 # the Free Software Foundation in version 2. check_mk is distributed
18 # in the hope that it will be useful, but WITHOUT ANY WARRANTY; with-
19 # out even the implied warranty of MERCHANTABILITY or FITNESS FOR A
20 # PARTICULAR PURPOSE. See the GNU General Public License for more de-
21 # tails. You should have received a copy of the GNU General Public
22 # License along with GNU Make; see the file COPYING. If not, write
23 # to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor,
24 # Boston, MA 02110-1301 USA.
25
26 # Check_MK agent plugin for monitoring ORACLE databases
27 # This plugin is a result of the common work of Thorsten Bruhns
28 # and Mathias Kettner. Thorsten is responsible for the ORACLE
29 # stuff, Mathias for the shell hacking...
30
31 # This plugin works for Linux, Solaris and AIX.
32 # See http://mathias-kettner.de/cms_monitoring_oracle.html
33 # for more details regarding configuration.
34
35
36 OS_TYPE=$(uname -s)
37
38
39 display_usage () {
40 echo ""
41 echo "USAGE:"
42 echo " mk_oracle [OPTIONS]"
43 echo ""
44 echo "DESCRIPTION:"
45 echo " Check_MK agent plugin for monitoring ORACLE databases."
46 echo " This plugin works for Linux, Solaris and AIX."
47 echo " See http://mathias-kettner.de/cms_monitoring_oracle.html"
48 echo " for more details regarding configuration."
49 echo ""
50 echo "OPTIONS:"
51 echo " -h, --help Shows this help message and exit"
52 echo " -d Enable full bash debugging"
53 echo " -t Just check the connection"
54 echo " -l, --log Logs certain steps while execution"
55 echo " ('flock' needs to be installed)"
56 echo " -s, --sections S1,S2,.. Only execute stated sections"
57 echo " Note: asynchronous sections are execute"
58 echo " as synchronous sections"
59 echo ""
60 }
61
62
63 while test -n "$1"; do
64 case "$1" in
65 -h|--help)
66 display_usage >&2
67 exit 0
68 ;;
69
70 -d)
71 set -xv
72 MK_ORA_DEBUG=1
73 shift
74 ;;
75
76 -t)
77 MK_ORA_DEBUG_CONNECT=1
78 shift
79 ;;
80
81 -l|--log)
82 if type flock > /dev/null 2>&1; then
83 MK_ORA_LOGGING=1
84 export MK_ORA_LOGGING
85 fi
86 shift
87 ;;
88
89 -s|--sections)
90 shift
91 MK_ORA_SECTIONS=($(echo "$1" | tr ',' '\n'))
92 shift
93 ;;
94
95 *)
96 shift
97 ;;
98 esac
99 done
100
101
102 if [ ! "$MK_CONFDIR" ]; then
103 echo "MK_CONFDIR not set!" >&2
104 exit 1
105 fi
106
107
108 if [ ! "$MK_VARDIR" ]; then
109 #TODO Which default? (run_cached/ mk_oracle.found, log file)
110 export MK_VARDIR=$MK_CONFDIR
111 fi
112
113
114 if [ ! -d "$MK_VARDIR/log" ]; then
115 mkdir "$MK_VARDIR/log"
116 fi
117
118
119 #TODO ma: Logging texte + stellen pruefen
120 #TODO si: fehlerhandling raus => check plugins
121 #TODO si: log file size ??
122 #TODO mk_ora_db_connect: aufraeumen und def local vars
123
124
125 # .--OS env--------------------------------------------------------------.
126 # | ___ ____ |
127 # | / _ \/ ___| ___ _ ____ __ |
128 # | | | | \___ \ / _ \ '_ \ \ / / |
129 # | | |_| |___) | | __/ | | \ V / |
130 # | \___/|____/ \___|_| |_|\_/ |
131 # | |
132 # '----------------------------------------------------------------------'
133
134 file_mtime () {
135 /usr/bin/perl -e 'if (! -f $ARGV[0]){die "0000000"};$mtime=(stat($ARGV[0]))[9];print ($mtime);' "$1"
136 }
137
138
139 set_os_env () {
140 if [ "$OS_TYPE" = 'Linux' ]; then
141 GREP=$(which grep)
142 AWK=$(which awk)
143 STATCX='stat -c %X'
144 STATCY='stat -c %Y'
145
146 elif [ "$OS_TYPE" = 'SunOS' ]; then
147 # expand the PATH for inetd. Otherwise some stuff in /opt/sfw/bin is not found!
148 export PATH=$PATH:/usr/ucb:/usr/proc/bin:opt/sfw/bin:/opt/sfw/sbin:/usr/sfw/bin:/usr/sfw/sbin:/opt/csw/bin
149 GREP="/usr/xpg4/bin/grep"
150 if [ ! -x "$GREP" ]; then
151 logging -c "[set_os_env]" "Please make sure that ${GREP} is existing on Solaris. Aborting mk_oracle plugin."
152 echo "Please make sure that $GREP is existing on Solaris!" >&2
153 echo "Aborting mk_oracle plugin." >&2
154 exit 127
155 fi
156
157 AWK=$(which nawk)
158 STATCX='file_mtime'
159 STATCY='file_mtime'
160
161 elif [ "$OS_TYPE" = 'AIX' ]; then
162 GREP=$(which grep)
163 AWK=$(which awk)
164 STATCX='file_mtime'
165 STATCY='file_mtime'
166
167 elif [ "$OS_TYPE" = 'HP-UX' ]; then
168 GREP=$(which grep)
169 AWK=$(which awk)
170 STATCX="perl -le 'print((stat shift)[8])'"
171 STATCY="perl -le 'print((stat shift)[9])'"
172
173 else
174 logging -c "[set_os_env]" "Unsupported OS: ${OS_TYPE}"
175 echo "Unsupported OS: $OS_TYPE" >&2
176 exit 1
177
178 fi
179
180 export GREP AWK STATCX STATCY
181 }
182
183 #.
184 # .--config--------------------------------------------------------------.
185 # | __ _ |
186 # | ___ ___ _ __ / _(_) __ _ |
187 # | / __/ _ \| '_ \| |_| |/ _` | |
188 # | | (_| (_) | | | | _| | (_| | |
189 # | \___\___/|_| |_|_| |_|\__, | |
190 # | |___/ |
191 # '----------------------------------------------------------------------'
192
193
194 # Sections that run fast and do no caching
195 SYNC_SECTIONS="instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance locks"
196
197 # Sections that are run in the background and at a larger interval.
198 # Note: sections not listed in SYNC_SECTIONS or ASYNC_SECTIONS will not be
199 # executed at all!
200 ASYNC_SECTIONS="tablespaces rman jobs ts_quotas resumable"
201
202 # Sections that are run in the background and at a larger interval.
203 # Note: _ASM_ sections are only executed when SID starts with '+'
204 # sections listed in SYNC_SECTIONS or ASYNC_SECTIONS are not
205 # executed for ASM.
206 SYNC_ASM_SECTIONS="instance processes"
207 ASYNC_ASM_SECTIONS="asm_diskgroup"
208
209 # Interval for running async checks (in seconds)
210 CACHE_MAXAGE=600
211
212 # set OLRLOC=/dev/null in mk_oracle.cfg to disable the discovery from GI/Restart.
213 # A fully configured oratab is mandatory, when this has been disabled.
214 # Be aware! Please disable OLRLOC for failover cluster with custom
215 # clusterware resources.
216 OLRLOC=/etc/oracle/olr.loc
217
218 # You can specify a list of SIDs to monitor. Those databases will
219 # only be handled, if they are found running, though!
220 #
221 # ONLY_SIDS="XE ORCL FOO BAR"
222 #
223 # You can specify a list of SIDs to be skipped.
224 #
225 # SKIP_SIDS="FOO BAR"
226 #
227 # It is possible to filter SIDS negatively. Just add the following to
228 # the mk_oracle.cfg file:
229 #
230 # EXCLUDE_<sid>="ALL"
231 #
232 # Another option is to filter single checks for SIDS. Just add
233 # lines as follows to the mk_oracle.cfg file. One service per
234 # line:
235 #
236 # EXCLUDE_<sid>="<service>"
237 #
238 # For example skip oracle_sessions and oracle_logswitches checks
239 # for the instance "mysid".
240 #
241 # EXCLUDE_mysid="sessions logswitches"
242 #
243 # NOTE: SKIP_SIDS same as 'EXCLUDE_<SID>=ALL' but
244 # also +ASM instances are possible to be excluded:
245 # SKIP_SIDS="+ASM1 +ASM2"
246 # vs.
247 # EXCLUDE_+ASM=ALL
248 # => No valid variable indirection
249
250
251 # Source the optional configuration file for this agent plugin
252 if [ -e "$MK_CONFDIR/mk_oracle.cfg" ]; then
253 # shellcheck disable=SC1090
254 . "$MK_CONFDIR/mk_oracle.cfg"
255 fi
256
257
258 #.
259 # .--logging-------------------------------------------------------------.
260 # | _ _ |
261 # | | | ___ __ _ __ _(_)_ __ __ _ |
262 # | | |/ _ \ / _` |/ _` | | '_ \ / _` | |
263 # | | | (_) | (_| | (_| | | | | | (_| | |
264 # | |_|\___/ \__, |\__, |_|_| |_|\__, | |
265 # | |___/ |___/ |___/ |
266 # '----------------------------------------------------------------------'
267
268 # How to use logging function:
269 # Basic form:
270 # logging "[WHEREIAM]"
271 #
272 # Logging without ID ELSEWHERE:
273 # logging "[ELSEWHERE]"
274 #
275 # Logging with ID:
276 # logging "[ID]"
277 #
278 # Logging with ID in function:
279 # logging "[ID] [FUNC-NAME]"
280 #
281 # Logging with ID elsewhere:
282 # logging "[ID] [ELSEWHERE]"
283 #
284 # Add some useful messages:
285 # logging "" "Set ORACLE_HOME=${ORACLE_HOME}"
286 #
287 # There are some optional flags for prefix in log lines:
288 # Flag Criticality Meaning
289 # --------------------------------------------------------------
290 # '-o' 0 (OK) clear
291 # '-w' 1 (WARNING) error but plugin goes on
292 # '-c' 2 (CRITICAL) error and plugin exits
293 # '-u' 3 (UNKNOWN) ?
294 #
295 # Add some useful messages:
296 # logging "" "Set OS environment" "OS: ${OS_TYPE}" "GREP: ${GREP}"
297 #
298 # Examples:
299 # logging "[${SID}] [set_ora_env]" "Found local ORACLE_HOME: ${ORACLE_HOME}"
300 # >>> 2018-05-15 16:27:43 [0] [352] [this-sid] [set_ora_env] Found local ORACLE_HOME: /path/to/ora/home
301 #
302 # logging "[preliminaries]" "Set OS environment" "OS: ${OS_TYPE}" "GREP: ${GREP}" "AWK: ${AWK}"
303 # >>> 2018-05-15 16:27:43 [0] [1748] [preliminaries] Set OS environment
304 # >>> 2018-05-15 16:27:44 [0] [1748] [preliminaries] OS: Linux
305 # >>> 2018-05-15 16:27:45 [0] [1748] [preliminaries] GREP: /bin/grep
306 # >>> 2018-05-15 16:27:46 [0] [1748] [preliminaries] AWK: /usr/bin/awk
307 #
308 # logging -c "[${SID}] [set_ora_env]" "ORA-99999 oratab not found in local mode"
309 # >>> 2018-05-15 16:27:43 [2] [362] [this-sid] [set_ora_env] ORA-99999 oratab not found in local mode
310 #
311 # If you need to declare some headers:
312 # logging "" "-- Set OS environment --" "OS: ${OS_TYPE}" "GREP: ${GREP}"
313 # >>> 2018-05-15 16:27:43 [0] [1748] [preliminaries] -- Set OS environment --
314 # >>> 2018-05-15 16:27:44 [0] [1748] [preliminaries] OS: Linux
315 # >>> 2018-05-15 16:27:45 [0] [1748] [preliminaries] GREP: /bin/grep
316
317
318 logging () {
319 if [ "$MK_ORA_LOGGING" == "1" ]; then
320 (
321 flock 200
322
323 local log_file="$MK_VARDIR/log/mk_oracle.log"
324 local criticality=
325 local args=
326 local header=
327
328 let i=0
329 while test -n "$1"; do
330 case "$1" in
331 -o)
332 criticality="0" # OK, default
333 shift
334 ;;
335
336 -w)
337 criticality="1" # WARNING
338 shift
339 ;;
340
341 -c)
342 criticality="2" # CRITICAL
343 shift
344 ;;
345
346 -u)
347 criticality="3" # UNKNOWN
348 shift
349 ;;
350
351 *)
352 args[$i]="$1"
353 i=$((i+1))
354 shift
355 ;;
356 esac
357 done
358
359 if [ -z "${criticality}" ]; then
360 criticality="0"
361 fi
362
363 header="$(perl -MPOSIX -le 'print strftime "%F %T", localtime $^T') [${criticality}] ${args[0]}"
364
365 if [ "${#args[@]}" -le 1 ]; then
366 echo "$header" >> "$log_file"
367 else
368 for arg in "${args[@]:1}"; do
369 echo "${header} $arg" >> "$log_file"
370 done
371 fi
372
373 ) 200>|/tmp/mk_oracle.lock
374 fi
375 }
376
377
378 #.
379 # .--ORA env-------------------------------------------------------------.
380 # | ___ ____ _ |
381 # | / _ \| _ \ / \ ___ _ ____ __ |
382 # | | | | | |_) | / _ \ / _ \ '_ \ \ / / |
383 # | | |_| | _ < / ___ \ | __/ | | \ V / |
384 # | \___/|_| \_\/_/ \_\ \___|_| |_|\_/ |
385 # | |
386 # '----------------------------------------------------------------------'
387
388 set_ora_env () {
389 local sid=${1}
390 ORACLE_SID="$sid"
391 unset ORA_HOME_SOURCE
392
393 if [[ "$sid" =~ ^REMOTE_INSTANCE_.* ]]; then
394 # we get the ORACLE_HOME from mk_oracle.cfg for REMOTE execution
395 ORACLE_HOME=${ORACLE_HOME:-${REMOTE_ORACLE_HOME}}
396 export ORA_HOME_SOURCE="(remote):"
397
398 else
399 # we work in local mode
400 # GI/Restart installed?
401 if [ -f ${OLRLOC} ]; then
402 # oratab is not supported in Grid-Infrastructure 12.2+
403 # => fetch ORACLE_HOME from cluster repository for all GI/Restart Environments!
404 # OLRLOC holds CRS_HOME
405 . ${OLRLOC}
406 export ORA_HOME_SOURCE="(GI): "
407
408 # set ORACLE_HOME = crs_home for ASM
409 if [ "${ORACLE_SID:0:1}" = '+' ]; then
410 ORACLE_HOME=${crs_home}
411 else
412 # get ORACLE_HOME with crsctl from Oracle Grid Infrastructure / Restart
413 ORACLE_HOME=$(${crs_home}/bin/crsctl stat res -p -w "((TYPE = ora.database.type) and (GEN_USR_ORA_INST_NAME = ${ORACLE_SID}))" | grep "^ORACLE_HOME=" | cut -d"=" -f2)
414 fi
415 else
416 # Single Instance with oratab
417 test -f /etc/oratab && ORATAB=/etc/oratab
418 # /var/opt/oracle/oratab is needed for Oracle Solaris
419 test -f /var/opt/oracle/oratab && ORATAB=/var/opt/oracle/oratab
420 if ! test -f ${ORATAB:-""}; then
421 logging -c "[${sid}] [set_ora_env]" "ORA-99999 oratab not found in local mode"
422 echo "ORA-99999 oratab not found" >&2
423 exit 1
424 fi
425 ORACLE_HOME=$("${GREP}" "^${ORACLE_SID}:" <"${ORATAB}" | cut -d":" -f2)
426 export ORA_HOME_SOURCE="(oratab):"
427 fi
428
429 if [ -z "${ORACLE_HOME}" ]; then
430 # cut last number from SID for Oracle RAC to find entry in oratab
431 ORACLE_HOME=$("${GREP}" "^${ORACLE_SID/%[0-9]/}:" <"${ORATAB}" | cut -d":" -f2)
432 fi
433 fi
434
435 LD_LIBRARY_PATH=$ORACLE_HOME/lib
436 if [ ! -d "${ORACLE_HOME:-'not_found'}" ]; then
437 logging -c "[${sid}] [set_ora_env]" "ORA-99999 ORACLE_HOME for ORACLE_SID=${ORACLE_SID} not found or not existing!"
438 echo "ORA-99999 ORACLE_HOME for ORACLE_SID=${ORACLE_SID} not found or not existing!" >&2
439 return 2
440 fi
441
442 TNS_ADMIN=${TNS_ADMIN:-$MK_CONFDIR}
443 if ! test -f "${TNS_ADMIN}/sqlnet.ora"; then
444 logging -c "[${sid}] [set_ora_env]" "TNS_ADMIN/sqlnet.ora: ${TNS_ADMIN}/sqlnet.ora"
445 echo "ORA-99998 Couldn't find ${TNS_ADMIN}/sqlnet.ora" >&2
446 exit 1
447 fi
448 export ORACLE_HOME TNS_ADMIN ORACLE_SID LD_LIBRARY_PATH
449 }
450
451
452 set_ora_version () {
453 ORACLE_VERSION="$1"
454 NUMERIC_ORACLE_VERSION=${ORACLE_VERSION//./}
455 export NUMERIC_ORACLE_VERSION
456
457 }
458
459 #.
460 # .--SQL Queries---------------------------------------------------------.
461 # | ____ ___ _ ___ _ |
462 # | / ___| / _ \| | / _ \ _ _ ___ _ __(_) ___ ___ |
463 # | \___ \| | | | | | | | | | | |/ _ \ '__| |/ _ \/ __| |
464 # | ___) | |_| | |___ | |_| | |_| | __/ | | | __/\__ \ |
465 # | |____/ \__\_\_____| \__\_\\__,_|\___|_| |_|\___||___/ |
466 # | |
467 # +----------------------------------------------------------------------+
468 # | The following functions create SQL queries for ORACLE and output |
469 # | them to stdout. All queries output the database name or the instane |
470 # | name as first column. |
471 # '----------------------------------------------------------------------'
472
473 #TODO Create subsections in query and parse them in related check plugin.
474 sql_performance () {
475
476
477 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
478 echo 'PROMPT <<<oracle_performance:sep(124)>>>'
479 echo "select upper(i.INSTANCE_NAME)
480 ||'|'|| 'sys_time_model'
481 ||'|'|| S.STAT_NAME
482 ||'|'|| Round(s.value/1000000)
483 from v\$instance i,
484 v\$sys_time_model s
485 where s.stat_name in('DB time', 'DB CPU')
486 order by s.stat_name;
487 select upper(i.INSTANCE_NAME ||'.'||vd.name)
488 ||'|'|| 'sys_time_model'
489 ||'|'|| S.STAT_NAME
490 ||'|'|| Round(s.value/1000000)
491 from v\$instance i
492 join v\$con_sys_time_model s on s.stat_name in('DB time', 'DB CPU')
493 join v\$containers vd on vd.con_id = s.con_id
494 join v\$database d on d.cdb = 'YES'
495 where vd.con_id <> 2
496 order by s.stat_name;
497 select upper(i.INSTANCE_NAME)
498 ||'|'|| 'buffer_pool_statistics'
499 ||'|'|| b.name
500 ||'|'|| b.db_block_gets
501 ||'|'|| b.db_block_change
502 ||'|'|| b.consistent_gets
503 ||'|'|| b.physical_reads
504 ||'|'|| b.physical_writes
505 ||'|'|| b.FREE_BUFFER_WAIT
506 ||'|'|| b.BUFFER_BUSY_WAIT
507 from v\$instance i, V\$BUFFER_POOL_STATISTICS b;
508 select upper(i.INSTANCE_NAME)
509 ||'|'|| 'SGA_info'
510 ||'|'|| s.name
511 ||'|'|| s.bytes
512 from v\$sgainfo s, v\$instance i;
513 select upper(i.INSTANCE_NAME)
514 ||'|'|| 'librarycache'
515 ||'|'|| b.namespace
516 ||'|'|| b.gets
517 ||'|'|| b.gethits
518 ||'|'|| b.pins
519 ||'|'|| b.pinhits
520 ||'|'|| b.reloads
521 ||'|'|| b.invalidations
522 from v\$instance i, V\$librarycache b;"
523
524 elif [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
525 echo "PROMPT <<<oracle_performance:sep(124)>>>"
526 echo "select upper(i.INSTANCE_NAME)
527 ||'|'|| 'sys_time_model'
528 ||'|'|| S.STAT_NAME
529 ||'|'|| Round(s.value/1000000)
530 from v\$instance i,
531 v\$sys_time_model s
532 where s.stat_name in('DB time', 'DB CPU')
533 order by s.stat_name;
534 select upper(i.INSTANCE_NAME)
535 ||'|'|| 'buffer_pool_statistics'
536 ||'|'|| b.name
537 ||'|'|| b.db_block_gets
538 ||'|'|| b.db_block_change
539 ||'|'|| b.consistent_gets
540 ||'|'|| b.physical_reads
541 ||'|'|| b.physical_writes
542 ||'|'|| b.FREE_BUFFER_WAIT
543 ||'|'|| b.BUFFER_BUSY_WAIT
544 from v\$instance i, V\$BUFFER_POOL_STATISTICS b;
545 select upper(i.INSTANCE_NAME)
546 ||'|'|| 'SGA_info'
547 ||'|'|| s.name
548 ||'|'|| s.bytes
549 from v\$sgainfo s, v\$instance i;
550 select upper(i.INSTANCE_NAME)
551 ||'|'|| 'librarycache'
552 ||'|'|| b.namespace
553 ||'|'|| b.gets
554 ||'|'|| b.gethits
555 ||'|'|| b.pins
556 ||'|'|| b.pinhits
557 ||'|'|| b.reloads
558 ||'|'|| b.invalidations
559 from v\$instance i, V\$librarycache b;"
560 fi
561 }
562
563
564 sql_tablespaces () {
565 echo "PROMPT <<<oracle_tablespaces:sep(124)>>>"
566 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
567
568 echo "select upper(decode(${IGNORE_DB_NAME:-0}
569 , 0, decode(vp.con_id, null, d.NAME
570 ,d.NAME||'.'||vp.name)
571 , i.instance_name))
572 || '|' || dbf.file_name
573 || '|' || dbf.tablespace_name
574 || '|' || dbf.fstatus
575 || '|' || dbf.AUTOEXTENSIBLE
576 || '|' || dbf.blocks
577 || '|' || dbf.maxblocks
578 || '|' || dbf.USER_BLOCKS
579 || '|' || dbf.INCREMENT_BY
580 || '|' || dbf.ONLINE_STATUS
581 || '|' || dbf.BLOCK_SIZE
582 || '|' || decode(tstatus,'READ ONLY', 'READONLY', tstatus)
583 || '|' || dbf.free_blocks
584 || '|' || dbf.contents
585 || '|' || i.version
586 from v\$database d
587 join v\$instance i on 1=1
588 join (
589 select f.con_id, f.file_name, f.tablespace_name, f.status fstatus, f.AUTOEXTENSIBLE,
590 f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY,
591 f.ONLINE_STATUS, t.BLOCK_SIZE, t.status tstatus, nvl(sum(fs.blocks),0) free_blocks, t.contents
592 from cdb_data_files f
593 join cdb_tablespaces t on f.tablespace_name = t.tablespace_name
594 and f.con_id = t.con_id
595 left outer join cdb_free_space fs on f.file_id = fs.file_id
596 and f.con_id = fs.con_id
597 group by f.con_id, f.file_name, f.tablespace_name, f.status, f.autoextensible,
598 f.blocks, f.maxblocks, f.user_blocks, f.increment_by, f.online_status,
599 t.block_size, t.status, t.contents
600 ) dbf on 1=1
601 left outer join v\$pdbs vp on dbf.con_id = vp.con_id
602 where d.database_role = 'PRIMARY';
603
604 select upper(decode(${IGNORE_DB_NAME:-0}
605 , 0, decode(dbf.con_id, null, d.NAME
606 ,dbf.name)
607 , i.instance_name))
608 || '|' || dbf.file_name
609 || '|' || dbf.tablespace_name
610 || '|' || dbf.fstatus
611 || '|' || dbf.AUTOEXTENSIBLE
612 || '|' || dbf.blocks
613 || '|' || dbf.maxblocks
614 || '|' || dbf.USER_BLOCKS
615 || '|' || dbf.INCREMENT_BY
616 || '|' || dbf.ONLINE_STATUS
617 || '|' || dbf.BLOCK_SIZE
618 || '|' || decode(tstatus,'READ ONLY', 'READONLY', tstatus)
619 || '|' || dbf.free_blocks
620 || '|' || 'TEMPORARY'
621 || '|' || i.version
622 FROM v\$database d
623 JOIN v\$instance i ON 1 = 1
624 JOIN (
625 SELECT vp.name,
626 vp.con_id,
627 f.file_name,
628 t.tablespace_name,
629 f.status fstatus,
630 f.autoextensible,
631 f.blocks,
632 f.maxblocks,
633 f.user_blocks,
634 f.increment_by,
635 'ONLINE' online_status,
636 t.block_size,
637 t.status tstatus,
638 f.blocks - nvl(SUM(tu.blocks),0) free_blocks,
639 t.contents
640 FROM cdb_tablespaces t
641 JOIN (
642 SELECT vp.con_id
643 ,d.name || '.'|| vp.name name
644 FROM v\$containers vp
645 JOIN v\$database d ON 1 = 1
646 WHERE d.cdb = 'YES'
647 AND vp.con_id <> 2
648 UNION ALL
649 SELECT 0
650 ,name
651 FROM v\$database
652 ) vp ON t.con_id = vp.con_id
653 LEFT OUTER JOIN cdb_temp_files f ON t.con_id = f.con_id
654 AND t.tablespace_name = f.tablespace_name
655 LEFT OUTER JOIN gv\$tempseg_usage tu ON f.con_id = tu.con_id
656 AND f.tablespace_name = tu.tablespace
657 AND f.RELATIVE_FNO = tu.SEGRFNO#
658 WHERE t.contents = 'TEMPORARY'
659 GROUP BY vp.name,
660 vp.con_id,
661 f.file_name,
662 t.tablespace_name,
663 f.status,
664 f.autoextensible,
665 f.blocks,
666 f.maxblocks,
667 f.user_blocks,
668 f.increment_by,
669 t.block_size,
670 t.status,
671 t.contents
672 ) dbf ON 1 = 1
673 where d.database_role = 'PRIMARY';"
674
675 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
676 echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
677 || '|' || file_name ||'|'|| tablespace_name ||'|'|| fstatus ||'|'|| AUTOEXTENSIBLE
678 ||'|'|| blocks ||'|'|| maxblocks ||'|'|| USER_BLOCKS ||'|'|| INCREMENT_BY
679 ||'|'|| ONLINE_STATUS ||'|'|| BLOCK_SIZE
680 ||'|'|| decode(tstatus,'READ ONLY', 'READONLY', tstatus) || '|' || free_blocks
681 ||'|'|| contents
682 ||'|'|| iversion
683 from v\$database d , v\$instance i, (
684 select f.file_name, f.tablespace_name, f.status fstatus, f.AUTOEXTENSIBLE,
685 f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY,
686 f.ONLINE_STATUS, t.BLOCK_SIZE, t.status tstatus, nvl(sum(fs.blocks),0) free_blocks, t.contents,
687 (select version from v\$instance) iversion
688 from dba_data_files f, dba_tablespaces t, dba_free_space fs
689 where f.tablespace_name = t.tablespace_name
690 and f.file_id = fs.file_id(+)
691 group by f.file_name, f.tablespace_name, f.status, f.autoextensible,
692 f.blocks, f.maxblocks, f.user_blocks, f.increment_by, f.online_status,
693 t.block_size, t.status, t.contents)
694 where d.database_role = 'PRIMARY';
695
696 select upper(decode(${IGNORE_DB_NAME:-0}
697 , 0, dbf.name
698 , i.instance_name))
699 || '|' || dbf.file_name
700 || '|' || dbf.tablespace_name
701 || '|' || dbf.fstatus
702 || '|' || dbf.AUTOEXTENSIBLE
703 || '|' || dbf.blocks
704 || '|' || dbf.maxblocks
705 || '|' || dbf.USER_BLOCKS
706 || '|' || dbf.INCREMENT_BY
707 || '|' || dbf.ONLINE_STATUS
708 || '|' || dbf.BLOCK_SIZE
709 || '|' || decode(tstatus,'READ ONLY', 'READONLY', tstatus)
710 || '|' || dbf.free_blocks
711 || '|' || 'TEMPORARY'
712 || '|' || i.version
713 FROM v\$database d
714 JOIN v\$instance i ON 1 = 1
715 JOIN (
716 SELECT vp.name,
717 f.file_name,
718 t.tablespace_name,
719 f.status fstatus,
720 f.autoextensible,
721 f.blocks,
722 f.maxblocks,
723 f.user_blocks,
724 f.increment_by,
725 'ONLINE' online_status,
726 t.block_size,
727 t.status tstatus,
728 f.blocks - nvl(SUM(tu.blocks),0) free_blocks,
729 t.contents
730 FROM dba_tablespaces t
731 JOIN ( SELECT 0
732 ,name
733 FROM v\$database
734 ) vp ON 1=1
735 LEFT OUTER JOIN dba_temp_files f ON t.tablespace_name = f.tablespace_name
736 LEFT OUTER JOIN gv\$tempseg_usage tu ON f.tablespace_name = tu.tablespace
737 AND f.RELATIVE_FNO = tu.SEGRFNO#
738 WHERE t.contents = 'TEMPORARY'
739 GROUP BY vp.name,
740 f.file_name,
741 t.tablespace_name,
742 f.status,
743 f.autoextensible,
744 f.blocks,
745 f.maxblocks,
746 f.user_blocks,
747 f.increment_by,
748 t.block_size,
749 t.status,
750 t.contents
751 ) dbf ON 1 = 1;"
752
753 elif [ "$NUMERIC_ORACLE_VERSION" -ge 92 ]; then
754 echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
755 || '|' || file_name ||'|'|| tablespace_name ||'|'|| fstatus ||'|'|| AUTOEXTENSIBLE
756 ||'|'|| blocks ||'|'|| maxblocks ||'|'|| USER_BLOCKS ||'|'|| INCREMENT_BY
757 ||'|'|| ONLINE_STATUS ||'|'|| BLOCK_SIZE
758 ||'|'|| decode(tstatus,'READ ONLY', 'READONLY', tstatus) || '|' || free_blocks
759 ||'|'|| contents
760 from v\$database d , v\$instance i, (
761 select f.file_name, f.tablespace_name, f.status fstatus, f.AUTOEXTENSIBLE,
762 f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY,
763 'ONLINE' ONLINE_STATUS, t.BLOCK_SIZE, t.status tstatus, nvl(sum(fs.blocks),0) free_blocks, t.contents
764 from dba_data_files f, dba_tablespaces t, dba_free_space fs
765 where f.tablespace_name = t.tablespace_name
766 and f.file_id = fs.file_id(+)
767 group by f.file_name, f.tablespace_name, f.status, f.autoextensible,
768 f.blocks, f.maxblocks, f.user_blocks, f.increment_by, 'ONLINE',
769 t.block_size, t.status, t.contents
770 UNION
771 select f.file_name, f.tablespace_name, 'ONLINE' status, f.AUTOEXTENSIBLE,
772 f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY, 'TEMP',
773 t.BLOCK_SIZE, 'TEMP' status, sum(sh.blocks_free) free_blocks, 'TEMPORARY'
774 from v\$thread th, dba_temp_files f, dba_tablespaces t, v\$temp_space_header sh
775 WHERE f.tablespace_name = t.tablespace_name and f.file_id = sh.file_id
776 GROUP BY th.instance, f.file_name, f.tablespace_name, 'ONLINE',
777 f.autoextensible, f.blocks, f.maxblocks, f.user_blocks, f.increment_by,
778 'TEMP', t.block_size, t.status);"
779 fi
780 }
781
782
783 sql_dataguard_stats () {
784 if [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
785 echo 'PROMPT <<<oracle_dataguard_stats:sep(124)>>>'
786 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
787 ||'|'|| upper(d.DB_UNIQUE_NAME)
788 ||'|'|| d.DATABASE_ROLE
789 ||'|'|| ds.name
790 ||'|'|| ds.value
791 ||'|'|| d.SWITCHOVER_STATUS
792 ||'|'|| d.DATAGUARD_BROKER
793 ||'|'|| d.PROTECTION_MODE
794 ||'|'|| d.FS_FAILOVER_STATUS
795 ||'|'|| d.FS_FAILOVER_OBSERVER_PRESENT
796 ||'|'|| d.FS_FAILOVER_OBSERVER_HOST
797 ||'|'|| d.FS_FAILOVER_CURRENT_TARGET
798 ||'|'|| ms.status
799 FROM v\$database d
800 JOIN v\$parameter vp on 1=1
801 JOIN v\$instance i on 1=1
802 left outer join V\$dataguard_stats ds on 1=1
803 left outer join v\$managed_standby ms on ms.process = 'MRP0'
804 WHERE vp.name = 'log_archive_config'
805 AND vp.value is not null
806 ORDER BY 1;"
807 fi
808 }
809
810
811 sql_recovery_status () {
812 echo 'PROMPT <<<oracle_recovery_status:sep(124)>>>'
813 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
814 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}
815 , 0, decode(vp.con_id, null, d.NAME
816 ,d.NAME||'.'||vp.name)
817 , i.instance_name))
818 ||'|'|| d.DB_UNIQUE_NAME
819 ||'|'|| d.DATABASE_ROLE
820 ||'|'|| d.open_mode
821 ||'|'|| dh.file#
822 ||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
823 ||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
824 ||'|'|| dh.STATUS
825 ||'|'|| dh.RECOVER
826 ||'|'|| dh.FUZZY
827 ||'|'|| dh.CHECKPOINT_CHANGE#
828 ||'|'|| vb.STATUS
829 ||'|'|| round((sysdate-vb.TIME)*24*60*60)
830 FROM V\$datafile_header dh
831 JOIN v\$database d on 1=1
832 JOIN v\$instance i on 1=1
833 JOIN v\$backup vb on 1=1
834 LEFT OUTER JOIN V\$PDBS vp on dh.con_id = vp.con_id
835 WHERE vb.file# = dh.file#
836 ORDER BY dh.file#;"
837 elif [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
838 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
839 ||'|'|| d.DB_UNIQUE_NAME
840 ||'|'|| d.DATABASE_ROLE
841 ||'|'|| d.open_mode
842 ||'|'|| dh.file#
843 ||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
844 ||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
845 ||'|'|| dh.STATUS
846 ||'|'|| dh.RECOVER
847 ||'|'|| dh.FUZZY
848 ||'|'|| dh.CHECKPOINT_CHANGE#
849 FROM V\$datafile_header dh, v\$database d, v\$instance i
850 ORDER BY dh.file#;
851 "
852 elif [ "$NUMERIC_ORACLE_VERSION" -ge 92 ]; then
853 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
854 ||'|'|| upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
855 ||'|'|| d.DATABASE_ROLE
856 ||'|'|| d.open_mode
857 ||'|'|| dh.file#
858 ||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
859 ||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
860 ||'|'|| dh.STATUS
861 ||'|'|| dh.RECOVER
862 ||'|'|| dh.FUZZY
863 ||'|'|| dh.CHECKPOINT_CHANGE#
864 FROM V\$datafile_header dh, v\$database d, v\$instance i
865 ORDER BY dh.file#;
866 "
867 fi
868 }
869
870
871 sql_rman () {
872 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
873
874 echo 'PROMPT <<<oracle_rman:sep(124)>>>'
875 echo "select /*"$HINT_RMAN" check_mk rman1 */ upper(name)
876 || '|'|| 'COMPLETED'
877 || '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
878 || '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
879 || '|'|| case when INCREMENTAL_LEVEL IS NULL
880 then 'DB_FULL'
881 else 'DB_INCR'
882 end
883 || '|'|| INCREMENTAL_LEVEL
884 || '|'|| round(((sysdate-COMPLETION_TIME) * 24 * 60), 0)
885 || '|'|| INCREMENTAL_CHANGE#
886 from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name)) name
887 , bd2.INCREMENTAL_LEVEL, bd2.INCREMENTAL_CHANGE#, min(bd2.COMPLETION_TIME) COMPLETION_TIME
888 from (select bd.file#, bd.INCREMENTAL_LEVEL, max(bd.COMPLETION_TIME) COMPLETION_TIME
889 from v\$backup_datafile bd
890 join v\$datafile_header dh on dh.file# = bd.file#
891 where dh.status = 'ONLINE'
892 and dh.con_id <> 2
893 group by bd.file#, bd.INCREMENTAL_LEVEL
894 ) bd
895 join v\$backup_datafile bd2 on bd2.file# = bd.file#
896 and bd2.COMPLETION_TIME = bd.COMPLETION_TIME
897 join v\$database vd on vd.RESETLOGS_CHANGE# = bd2.RESETLOGS_CHANGE#
898 join v\$instance i on 1=1
899 group by upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
900 , bd2.INCREMENTAL_LEVEL
901 , bd2.INCREMENTAL_CHANGE#
902 order by name, bd2.INCREMENTAL_LEVEL);
903
904 select /*"$HINT_RMAN" check_mk rman2 */ name
905 || '|' || 'COMPLETED'
906 || '|'
907 || '|' || to_char(CHECKPOINT_TIME, 'yyyy-mm-dd_hh24:mi:ss')
908 || '|' || 'CONTROLFILE'
909 || '|'
910 || '|' || round((sysdate - CHECKPOINT_TIME) * 24 * 60)
911 || '|' || '0'
912 from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
913 ,max(bcd.CHECKPOINT_TIME) CHECKPOINT_TIME
914 from v\$database d
915 join V\$BACKUP_CONTROLFILE_DETAILS bcd on d.RESETLOGS_CHANGE# = bcd.RESETLOGS_CHANGE#
916 join v\$instance i on 1=1
917 group by upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
918 );
919
920 select /*"$HINT_RMAN" check_mk rman3 */ name
921 || '|COMPLETED'
922 || '|'|| to_char(sysdate, 'YYYY-mm-dd_HH24:MI:SS')
923 || '|'|| to_char(completed, 'YYYY-mm-dd_HH24:MI:SS')
924 || '|ARCHIVELOG||'
925 || round((sysdate - completed)*24*60,0)
926 || '|'
927 from (
928 select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
929 , max(a.completion_time) completed
930 , case when a.backup_count > 0 then 1 else 0 end
931 from v\$archived_log a, v\$database d, v\$instance i
932 where a.backup_count > 0
933 and a.dest_id in
934 (select b.dest_id
935 from v\$archive_dest b
936 where b.target = 'PRIMARY'
937 and b.SCHEDULE = 'ACTIVE'
938 )
939 group by d.NAME, i.instance_name
940 , case when a.backup_count > 0 then 1 else 0 end);"
941
942 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
943 echo 'PROMPT <<<oracle_rman:sep(124)>>>'
944 echo "select /*${HINT_RMAN} check_mk rman1 */ upper(name)
945 || '|'|| 'COMPLETED'
946 || '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
947 || '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
948 || '|'|| case when INCREMENTAL_LEVEL IS NULL
949 then 'DB_FULL'
950 else 'DB_INCR'
951 end
952 || '|'|| INCREMENTAL_LEVEL
953 || '|'|| round(((sysdate-COMPLETION_TIME) * 24 * 60), 0)
954 || '|'|| INCREMENTAL_CHANGE#
955 from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name)) name
956 , bd2.INCREMENTAL_LEVEL, bd2.INCREMENTAL_CHANGE#, min(bd2.COMPLETION_TIME) COMPLETION_TIME
957 from (select bd.file#, bd.INCREMENTAL_LEVEL, max(bd.COMPLETION_TIME) COMPLETION_TIME
958 from v\$backup_datafile bd
959 join v\$datafile_header dh on dh.file# = bd.file#
960 where dh.status = 'ONLINE'
961 group by bd.file#, bd.INCREMENTAL_LEVEL
962 ) bd
963 join v\$backup_datafile bd2 on bd2.file# = bd.file#
964 and bd2.COMPLETION_TIME = bd.COMPLETION_TIME
965 join v\$database vd on vd.RESETLOGS_CHANGE# = bd2.RESETLOGS_CHANGE#
966 join v\$instance i on 1=1
967 group by upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
968 , bd2.INCREMENTAL_LEVEL
969 , bd2.INCREMENTAL_CHANGE#
970 order by name, bd2.INCREMENTAL_LEVEL);
971
972 select /*${HINT_RMAN} check_mk rman2 */ name
973 || '|' || 'COMPLETED'
974 || '|'
975 || '|' || to_char(CHECKPOINT_TIME, 'yyyy-mm-dd_hh24:mi:ss')
976 || '|' || 'CONTROLFILE'
977 || '|'
978 || '|' || round((sysdate - CHECKPOINT_TIME) * 24 * 60)
979 || '|' || '0'
980 from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
981 ,max(bcd.CHECKPOINT_TIME) CHECKPOINT_TIME
982 from v\$database d
983 join V\$BACKUP_CONTROLFILE_DETAILS bcd on d.RESETLOGS_CHANGE# = bcd.RESETLOGS_CHANGE#
984 join v\$instance i on 1=1
985 group by upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
986 );
987
988 select /*${HINT_RMAN} check_mk rman3 */ name
989 || '|COMPLETED'
990 || '|'|| to_char(sysdate, 'YYYY-mm-dd_HH24:MI:SS')
991 || '|'|| to_char(completed, 'YYYY-mm-dd_HH24:MI:SS')
992 || '|ARCHIVELOG||'
993 || round((sysdate - completed)*24*60,0)
994 || '|'
995 from (
996 select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
997 , max(a.completion_time) completed
998 , case when a.backup_count > 0 then 1 else 0 end
999 from v\$archived_log a, v\$database d, v\$instance i
1000 where a.backup_count > 0
1001 and a.dest_id in
1002 (select b.dest_id
1003 from v\$archive_dest b
1004 where b.target = 'PRIMARY'
1005 and b.SCHEDULE = 'ACTIVE'
1006 )
1007 group by d.NAME, i.instance_name
1008 , case when a.backup_count > 0 then 1 else 0 end);"
1009 fi
1010 }
1011
1012
1013 sql_recovery_area () {
1014 if [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1015 echo 'PROMPT <<<oracle_recovery_area:sep(124)>>>'
1016 echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
1017 ||'|'|| round((SPACE_USED-SPACE_RECLAIMABLE)/
1018 (CASE NVL(SPACE_LIMIT,1) WHEN 0 THEN 1 ELSE SPACE_LIMIT END)*100)
1019 ||'|'|| round(SPACE_LIMIT/1024/1024)
1020 ||'|'|| round(SPACE_USED/1024/1024)
1021 ||'|'|| round(SPACE_RECLAIMABLE/1024/1024)
1022 ||'|'|| d.FLASHBACK_ON
1023 from V\$RECOVERY_FILE_DEST, v\$database d, v\$instance i;"
1024 fi
1025 }
1026
1027
1028 sql_undostat () {
1029 echo 'PROMPT <<<oracle_undostat:sep(124)>>>'
1030 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1031 echo "select decode(vp.con_id, null, upper(i.INSTANCE_NAME)
1032 ,upper(i.INSTANCE_NAME || '.' || vp.name))
1033 ||'|'|| ACTIVEBLKS
1034 ||'|'|| MAXCONCURRENCY
1035 ||'|'|| TUNED_UNDORETENTION
1036 ||'|'|| maxquerylen
1037 ||'|'|| NOSPACEERRCNT
1038 from v\$instance i
1039 join
1040 (select * from v\$undostat
1041 where TUNED_UNDORETENTION > 0
1042 order by end_time desc
1043 fetch next 1 rows only
1044 ) u on 1=1
1045 left outer join v\$pdbs vp on vp.con_id = u.con_id;
1046 "
1047
1048 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1049 echo "select upper(i.INSTANCE_NAME)
1050 ||'|'|| ACTIVEBLKS
1051 ||'|'|| MAXCONCURRENCY
1052 ||'|'|| TUNED_UNDORETENTION
1053 ||'|'|| maxquerylen
1054 ||'|'|| NOSPACEERRCNT
1055 from v\$instance i,
1056 (select * from (select *
1057 from v\$undostat order by end_time desc
1058 )
1059 where rownum = 1
1060 and TUNED_UNDORETENTION > 0
1061 );"
1062
1063 elif [ "$NUMERIC_ORACLE_VERSION" -ge 92 ]; then
1064 # TUNED_UNDORETENTION and ACTIVEBLKS are not availibe in Oracle <=9.2!
1065 # we sent a -1 for filtering in check_undostat
1066 echo "select upper(i.INSTANCE_NAME)
1067 ||'|-1'
1068 ||'|'|| MAXCONCURRENCY
1069 ||'|-1'
1070 ||'|'|| maxquerylen
1071 ||'|'|| NOSPACEERRCNT
1072 from v\$instance i,
1073 (select * from (select *
1074 from v\$undostat order by end_time desc
1075 )
1076 where rownum = 1
1077 );"
1078 fi
1079 }
1080
1081
1082 sql_resumable () {
1083 echo 'PROMPT <<<oracle_resumable:sep(124)>>>'
1084 echo "select upper(i.INSTANCE_NAME)
1085 ||'|'|| u.username
1086 ||'|'|| a.SESSION_ID
1087 ||'|'|| a.status
1088 ||'|'|| a.TIMEOUT
1089 ||'|'|| round((sysdate-to_date(a.SUSPEND_TIME,'mm/dd/yy hh24:mi:ss'))*24*60*60)
1090 ||'|'|| a.ERROR_NUMBER
1091 ||'|'|| to_char(to_date(a.SUSPEND_TIME, 'mm/dd/yy hh24:mi:ss'),'mm/dd/yy_hh24:mi:ss')
1092 ||'|'|| a.RESUME_TIME
1093 ||'|'|| a.ERROR_MSG
1094 from dba_resumable a, v\$instance i, dba_users u
1095 where a.INSTANCE_ID = i.INSTANCE_NUMBER
1096 and u.user_id = a.user_id
1097 and a.SUSPEND_TIME is not null
1098 union all
1099 select upper(i.INSTANCE_NAME)
1100 || '|||||||||'
1101 from v\$instance i;"
1102 }
1103
1104
1105 sql_jobs () {
1106 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1107
1108 echo 'PROMPT <<<oracle_jobs:sep(124)>>>'
1109 echo "SELECT upper(vp.name)
1110 ||'|'|| j.OWNER
1111 ||'|'|| j.JOB_NAME
1112 ||'|'|| j.STATE
1113 ||'|'|| ROUND((TRUNC(sysdate) + j.LAST_RUN_DURATION - TRUNC(sysdate)) * 86400)
1114 ||'|'|| j.RUN_COUNT
1115 ||'|'|| j.ENABLED
1116 ||'|'|| NVL(j.NEXT_RUN_DATE, to_date('1970-01-01', 'YYYY-mm-dd'))
1117 ||'|'|| NVL(j.SCHEDULE_NAME, '-')
1118 ||'|'|| jd.STATUS
1119 FROM cdb_scheduler_jobs j
1120 JOIN ( SELECT vp.con_id
1121 ,d.name || '|' || vp.name name
1122 FROM v\$containers vp
1123 JOIN v\$database d on 1=1
1124 WHERE d.cdb = 'YES' and vp.con_id <> 2
1125 UNION ALL
1126 SELECT 0, name
1127 FROM v\$database
1128 ) vp on j.con_id = vp.con_id
1129 left outer join (SELECT con_id, owner, job_name, max(LOG_ID) log_id
1130 FROM cdb_scheduler_job_run_details dd
1131 group by con_id, owner, job_name
1132 ) jm on jm.JOB_NAME = j.JOB_NAME
1133 and jm.owner=j.OWNER
1134 and jm.con_id = j.con_id
1135 left outer join cdb_scheduler_job_run_details jd
1136 on jd.con_id = jm.con_id
1137 AND jd.owner = jm.OWNER
1138 AND jd.JOB_NAME = jm.JOB_NAME
1139 AND jd.LOG_ID = jm.LOG_ID;
1140 "
1141
1142 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1143 echo 'PROMPT <<<oracle_jobs:sep(124)>>>'
1144 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
1145 ||'|'|| j.OWNER
1146 ||'|'|| j.JOB_NAME
1147 ||'|'|| j.STATE
1148 ||'|'|| ROUND((TRUNC(sysdate) + j.LAST_RUN_DURATION - TRUNC(sysdate)) * 86400)
1149 ||'|'|| j.RUN_COUNT
1150 ||'|'|| j.ENABLED
1151 ||'|'|| NVL(j.NEXT_RUN_DATE, to_date('1970-01-01', 'YYYY-mm-dd'))
1152 ||'|'|| NVL(j.SCHEDULE_NAME, '-')
1153 ||'|'|| jd.STATUS
1154 FROM dba_scheduler_jobs j
1155 join v\$database vd on 1 = 1
1156 join v\$instance i on 1 = 1
1157 left outer join (SELECT owner, job_name, max(LOG_ID) log_id
1158 FROM dba_scheduler_job_run_details dd
1159 group by owner, job_name
1160 ) jm on jm.JOB_NAME = j.JOB_NAME
1161 and jm.owner=j.OWNER
1162 left outer join dba_scheduler_job_run_details jd
1163 on jd.owner = jm.OWNER
1164 AND jd.JOB_NAME = jm.JOB_NAME
1165 AND jd.LOG_ID = jm.LOG_ID
1166 WHERE j.auto_drop = 'FALSE';"
1167 fi
1168 }
1169
1170
1171 sql_ts_quotas () {
1172 echo 'PROMPT <<<oracle_ts_quotas:sep(124)>>>'
1173 echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
1174 ||'|'|| Q.USERNAME
1175 ||'|'|| Q.TABLESPACE_NAME
1176 ||'|'|| Q.BYTES
1177 ||'|'|| Q.MAX_BYTES
1178 from dba_ts_quotas Q, v\$database d, v\$instance i
1179 where max_bytes > 0
1180 union all
1181 select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
1182 ||'|||'
1183 from v\$database d, v\$instance i
1184 order by 1;"
1185 }
1186
1187
1188 sql_version () {
1189 echo 'PROMPT <<<oracle_version>>>'
1190 echo "select upper(i.INSTANCE_NAME)
1191 || ' ' || banner
1192 from v\$version, v\$instance i
1193 where banner like 'Oracle%';"
1194 }
1195
1196
1197 sql_instance () {
1198 echo 'prompt <<<oracle_instance:sep(124)>>>'
1199 if [ "${ORACLE_SID:0:1}" = '+' ]; then
1200 # ASM
1201 echo "select upper(i.instance_name)
1202 || '|' || i.VERSION
1203 || '|' || i.STATUS
1204 || '|' || i.LOGINS
1205 || '|' || i.ARCHIVER
1206 || '|' || round((sysdate - i.startup_time) * 24*60*60)
1207 || '|' || '0'
1208 || '|' || 'NO'
1209 || '|' || 'ASM'
1210 || '|' || 'NO'
1211 || '|' || i.instance_name
1212 from v\$instance i;"
1213
1214 elif [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1215 echo "select upper(instance_name)
1216 || '|' || version
1217 || '|' || status
1218 || '|' || logins
1219 || '|' || archiver
1220 || '|' || round((sysdate - startup_time) * 24*60*60)
1221 || '|' || dbid
1222 || '|' || log_mode
1223 || '|' || database_role
1224 || '|' || force_logging
1225 || '|' || name
1226 || '|' || to_char(created, 'ddmmyyyyhh24mi')
1227 || '|' || upper(value)
1228 || '|' || con_id
1229 || '|' || pname
1230 || '|' || pdbid
1231 || '|' || popen_mode
1232 || '|' || prestricted
1233 || '|' || ptotal_time
1234 || '|' || precovery_status
1235 || '|' || round(nvl(popen_time, -1))
1236 || '|' || pblock_size
1237 from(
1238 select i.instance_name, i.version, i.status, i.logins, i.archiver
1239 ,i.startup_time, d.dbid, d.log_mode, d.database_role, d.force_logging
1240 ,d.name, d.created, p.value, vp.con_id, vp.name pname
1241 ,vp.dbid pdbid, vp.open_mode popen_mode, vp.restricted prestricted, vp.total_size ptotal_time
1242 ,vp.block_size pblock_size, vp.recovery_status precovery_status
1243 ,(cast(systimestamp as date) - cast(open_time as date)) * 24*60*60 popen_time
1244 from v\$instance i
1245 join v\$database d on 1=1
1246 join v\$parameter p on 1=1
1247 join v\$pdbs vp on 1=1
1248 where p.name = 'enable_pluggable_database'
1249 union all
1250 select
1251 i.instance_name, i.version, i.status, i.logins, i.archiver
1252 ,i.startup_time, d.dbid, d.log_mode, d.database_role, d.force_logging
1253 ,d.name, d.created, p.value, 0 con_id, null pname
1254 ,0 pdbis, null popen_mode, null prestricted, null ptotal_time
1255 ,0 pblock_size, null precovery_status, null popen_time
1256 from v\$instance i
1257 join v\$database d on 1=1
1258 join v\$parameter p on 1=1
1259 where p.name = 'enable_pluggable_database'
1260 order by con_id
1261 );
1262 "
1263 else
1264 # normal Instance
1265 echo "select upper(i.instance_name)
1266 || '|' || i.VERSION
1267 || '|' || i.STATUS
1268 || '|' || i.LOGINS
1269 || '|' || i.ARCHIVER
1270 || '|' || round((sysdate - i.startup_time) * 24*60*60)
1271 || '|' || DBID
1272 || '|' || LOG_MODE
1273 || '|' || DATABASE_ROLE
1274 || '|' || FORCE_LOGGING
1275 || '|' || d.name
1276 || '|' || to_char(d.created, 'ddmmyyyyhh24mi')
1277 from v\$instance i, v\$database d;"
1278 fi
1279 }
1280
1281
1282 sql_sessions () {
1283 echo 'prompt <<<oracle_sessions:sep(124)>>>'
1284
1285 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1286 echo "SELECT upper(vp.name)
1287 || '|' || ltrim(COUNT(1))
1288 || decode(vp.con_id
1289 , 0, '|'||ltrim(rtrim(LIMIT_VALUE))||'|-1')
1290 FROM ( SELECT vp.con_id
1291 ,i.instance_name || '.' || vp.name name
1292 FROM v\$containers vp
1293 JOIN v\$instance i ON 1 = 1
1294 JOIN v\$database d on 1=1
1295 WHERE d.cdb = 'YES' and vp.con_id <> 2
1296 UNION ALL
1297 SELECT 0, instance_name
1298 FROM v\$instance
1299 ) vp
1300 JOIN v\$resource_limit rl on RESOURCE_NAME = 'sessions'
1301 LEFT OUTER JOIN v\$session vs ON vp.con_id = vs.con_id
1302 GROUP BY vp.name, vp.con_id, rl.LIMIT_VALUE
1303 ORDER BY 1;"
1304
1305 else
1306 echo "select upper(i.instance_name)
1307 || '|' || CURRENT_UTILIZATION
1308 || '|' || ltrim(LIMIT_VALUE)
1309 || '|' || MAX_UTILIZATION
1310 from v\$resource_limit, v\$instance i
1311 where RESOURCE_NAME = 'sessions';"
1312 fi
1313 }
1314
1315
1316 sql_processes () {
1317 echo 'prompt <<<oracle_processes:sep(124)>>>'
1318 echo "select upper(i.instance_name)
1319 || '|' || CURRENT_UTILIZATION
1320 || '|' || ltrim(rtrim(LIMIT_VALUE))
1321 from v\$resource_limit, v\$instance i
1322 where RESOURCE_NAME = 'processes';"
1323 }
1324
1325
1326 sql_logswitches () {
1327 echo 'prompt <<<oracle_logswitches:sep(124)>>>'
1328 echo "select upper(i.instance_name)
1329 || '|' || logswitches
1330 from v\$instance i ,
1331 (select count(1) logswitches
1332 from v\$loghist h , v\$instance i
1333 where h.first_time > sysdate - 1/24
1334 and h.thread# = i.instance_number
1335 );"
1336 }
1337
1338
1339 sql_locks () {
1340 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1341 echo 'prompt <<<oracle_locks:sep(124)>>>'
1342 echo "select upper(vp.name)
1343 || '|' || b.sid
1344 || '|' || b.serial#
1345 || '|' || b.machine
1346 || '|' || b.program
1347 || '|' || b.process
1348 || '|' || b.osuser
1349 || '|' || b.username
1350 || '|' || b.SECONDS_IN_WAIT
1351 || '|' || b.BLOCKING_SESSION_STATUS
1352 || '|' || bs.inst_id
1353 || '|' || bs.sid
1354 || '|' || bs.serial#
1355 || '|' || bs.machine
1356 || '|' || bs.program
1357 || '|' || bs.process
1358 || '|' || bs.osuser
1359 || '|' || bs.username
1360 from v\$session b
1361 join gv\$session bs on bs.inst_id = b.BLOCKING_INSTANCE
1362 and bs.sid = b.BLOCKING_SESSION
1363 and bs.con_id = b.con_id
1364 join ( SELECT vp.con_id
1365 ,i.instance_name || '.' || vp.name name
1366 FROM v\$containers vp
1367 JOIN v\$instance i ON 1 = 1
1368 JOIN v\$database d on 1=1
1369 WHERE d.cdb = 'YES' and vp.con_id <> 2
1370 UNION ALL
1371 SELECT 0, instance_name
1372 FROM v\$instance
1373 ) vp on b.con_id = vp.con_id
1374 where b.BLOCKING_SESSION is not null;
1375
1376 SELECT upper(i.instance_name || '.' || vp.name)
1377 || '|||||||||||||||||'
1378 FROM v\$containers vp
1379 JOIN v\$instance i ON 1 = 1
1380 JOIN v\$database d on 1=1
1381 WHERE d.cdb = 'YES' and vp.con_id <> 2
1382 UNION ALL
1383 SELECT upper(i.instance_name)
1384 || '|||||||||||||||||'
1385 FROM v\$instance i;
1386 "
1387
1388 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1389 echo 'prompt <<<oracle_locks:sep(124)>>>'
1390 echo "select upper(i.instance_name)
1391 || '|' || b.sid
1392 || '|' || b.serial#
1393 || '|' || b.machine
1394 || '|' || b.program
1395 || '|' || b.process
1396 || '|' || b.osuser
1397 || '|' || b.username
1398 || '|' || b.SECONDS_IN_WAIT
1399 || '|' || b.BLOCKING_SESSION_STATUS
1400 || '|' || bs.inst_id
1401 || '|' || bs.sid
1402 || '|' || bs.serial#
1403 || '|' || bs.machine
1404 || '|' || bs.program
1405 || '|' || bs.process
1406 || '|' || bs.osuser
1407 || '|' || bs.username
1408 from v\$session b
1409 join v\$instance i on 1=1
1410 join gv\$session bs on bs.inst_id = b.BLOCKING_INSTANCE
1411 and bs.sid = b.BLOCKING_SESSION
1412 where b.BLOCKING_SESSION is not null;
1413 select upper(i.instance_name)
1414 || '|||||||||||||||||'
1415 from v\$instance i;"
1416 fi
1417 }
1418
1419
1420 sql_locks_old () {
1421 if [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
1422 echo 'prompt <<<oracle_locks:sep(124)>>>'
1423 echo "SET SERVEROUTPUT ON feedback off
1424 DECLARE
1425 type x is table of varchar2(20000) index by pls_integer;
1426 xx x;
1427 begin
1428 begin
1429 execute immediate 'select upper(i.instance_name)
1430 || ''|'' || a.sid
1431 || ''|'' || b.serial#
1432 || ''|'' || b.machine
1433 || ''|'' || b.program
1434 || ''|'' || b.process
1435 || ''|'' || b.osuser
1436 || ''|'' || a.ctime
1437 || ''|'' || decode(c.owner,NULL,''NULL'',c.owner)
1438 || ''|'' || decode(c.object_name,NULL,''NULL'',c.object_name)
1439 from V\$LOCK a, v\$session b, dba_objects c, v\$instance i
1440 where (a.id1, a.id2, a.type)
1441 IN (SELECT id1, id2, type
1442 FROM GV\$LOCK
1443 WHERE request>0
1444 )
1445 and request=0
1446 and a.sid = b.sid
1447 and a.id1 = c.object_id (+)
1448 union all
1449 select upper(i.instance_name) || ''|||||||||''
1450 from v\$instance i'
1451 bulk collect into xx;
1452 if xx.count >= 1 then
1453 for i in 1 .. xx.count loop
1454 dbms_output.put_line(xx(i));
1455 end loop;
1456 end if;
1457 exception
1458 when others then
1459 for cur1 in (select upper(i.instance_name) instance_name from v\$instance i) loop
1460 dbms_output.put_line(cur1.instance_name || '|||||||||'||sqlerrm);
1461 end loop;
1462 end;
1463 END;
1464 /
1465 set serverout off"
1466 fi
1467 }
1468
1469
1470 sql_longactivesessions () {
1471 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1472
1473 echo 'prompt <<<oracle_longactivesessions:sep(124)>>>'
1474 echo "select upper(vp.name)
1475 || '|' || s.sid
1476 || '|' || s.serial#
1477 || '|' || s.machine
1478 || '|' || s.process
1479 || '|' || s.osuser
1480 || '|' || s.program
1481 || '|' || s.last_call_et
1482 || '|' || s.sql_id
1483 from v\$session s
1484 join ( SELECT vp.con_id
1485 ,i.instance_name || '.' || vp.name name
1486 FROM v\$containers vp
1487 JOIN v\$instance i ON 1 = 1
1488 JOIN v\$database d on 1=1
1489 WHERE d.cdb = 'YES' and vp.con_id <> 2
1490 UNION ALL
1491 SELECT 0, instance_name
1492 FROM v\$instance
1493 ) vp on 1=1
1494 where s.status = 'ACTIVE'
1495 and s.type != 'BACKGROUND'
1496 and s.username is not null
1497 and s.username not in('PUBLIC')
1498 and s.last_call_et > 60*60;
1499
1500 SELECT upper(i.instance_name || '.' || vp.name)
1501 || '||||||||'
1502 FROM v\$containers vp
1503 JOIN v\$instance i ON 1 = 1
1504 JOIN v\$database d on 1=1
1505 WHERE d.cdb = 'YES' and vp.con_id <> 2
1506 UNION ALL
1507 SELECT upper(i.instance_name)
1508 || '||||||||'
1509 FROM v\$instance i;
1510 "
1511
1512 elif [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
1513 echo 'prompt <<<oracle_longactivesessions:sep(124)>>>'
1514 echo "select upper(i.instance_name)
1515 || '|' || s.sid
1516 || '|' || s.serial#
1517 || '|' || s.machine
1518 || '|' || s.process
1519 || '|' || s.osuser
1520 || '|' || s.program
1521 || '|' || s.last_call_et
1522 || '|' || s.sql_id
1523 from v\$session s, v\$instance i
1524 where s.status = 'ACTIVE'
1525 and type != 'BACKGROUND'
1526 and s.username is not null
1527 and s.username not in('PUBLIC')
1528 and s.last_call_et > 60*60
1529 union all
1530 select upper(i.instance_name)
1531 || '||||||||'
1532 from v\$instance i;"
1533 fi
1534 }
1535
1536
1537 sql_asm_diskgroup () {
1538 echo 'prompt <<<oracle_asm_diskgroup:sep(124)>>>'
1539 if [ "$NUMERIC_ORACLE_VERSION" -ge 112 ]; then
1540
1541 echo "SELECT g.state
1542 || '|' || g.type
1543 || '|' || g.name
1544 || '|' || g.BLOCK_SIZE
1545 || '|' || g.ALLOCATION_UNIT_SIZE
1546 || '|' || g.REQUIRED_MIRROR_FREE_MB
1547 || '|' || sum(d.total_mb)
1548 || '|' || sum(d.free_mb)
1549 || '|' || d.failgroup
1550 || '|' || d.VOTING_FILE
1551 || '|' || d.FAILGROUP_TYPE
1552 || '|' || g.offline_disks
1553 || '|' || min(decode(d.REPAIR_TIMER, 0, 8640000, d.REPAIR_TIMER))
1554 || '|' || count(*)
1555 FROM v\$asm_diskgroup g
1556 LEFT OUTER JOIN v\$asm_disk d on d.group_number = g.group_number
1557 and d.group_number = g.group_number
1558 and d.group_number <> 0
1559 GROUP BY g.name
1560 , g.state
1561 , g.type
1562 , d.failgroup
1563 , d.VOTING_FILE
1564 , g.BLOCK_SIZE
1565 , g.ALLOCATION_UNIT_SIZE
1566 , g.REQUIRED_MIRROR_FREE_MB
1567 , g.offline_disks
1568 , d.FAILGROUP_TYPE
1569 , d.REPAIR_TIMER
1570 ORDER BY g.name, d.failgroup;"
1571
1572 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1573 echo "select STATE
1574 || '|' || TYPE
1575 || '|' || 'N'
1576 || '|' || sector_size
1577 || '|' || block_size
1578 || '|' || allocation_unit_size
1579 || '|' || total_mb
1580 || '|' || free_mb
1581 || '|' || required_mirror_free_mb
1582 || '|' || usable_file_mb
1583 || '|' || offline_disks
1584 || '|' || 'N'
1585 || '|' || name || '/'
1586 from v\$asm_diskgroup;"
1587 fi
1588 }
1589
1590 #.
1591 # .--custom SQL----------------------------------------------------------.
1592 # | _ ____ ___ _ |
1593 # | ___ _ _ ___| |_ ___ _ __ ___ / ___| / _ \| | |
1594 # | / __| | | / __| __/ _ \| '_ ` _ \ \___ \| | | | | |
1595 # | | (__| |_| \__ \ || (_) | | | | | | ___) | |_| | |___ |
1596 # | \___|\__,_|___/\__\___/|_| |_| |_| |____/ \__\_\_____| |
1597 # | |
1598 # '----------------------------------------------------------------------'
1599
1600 # ==== Configuration of custom SQLs
1601 # == Globals ==
1602 # SQLS_SECTIONS=secA,secB,... (comma separated)
1603 #
1604 # == Globals and locals ==
1605 # SQLS_SECTION_NAME=sec_name (optional; if not stated, 'oracle_sql' is used)
1606 # SQLS_SECTION_SEP=separator (optional; only useful if SQLS_SECTION_NAME is stated;
1607 # ASCII code, eg. '58' means ':')
1608 # SQLS_SID=sid1,sid2,... (comma separated)
1609 # SQLS_DIR=/path/to/dir (no trailing '/')
1610 # SQLS_SQL=name.sql ()
1611 # SQLS_PARAMETERS=params (parameters as string, double quotes are removed)
1612 # SQLS_MAX_CACHE_AGE=X (x in seconds)
1613 #
1614 # == Locals ==
1615 # SQLS_ITEM_NAME=item_name (optional, if not stated, sql name is used)
1616 #
1617 # A section is a function which may contain some of above parameters, eg:
1618 # my_sec () {
1619 # SQLS_SID=sid1,sid2
1620 # SQLS_SQL=name.sql
1621 # }
1622 # and is configure in mk_oracle.cfg
1623 #
1624 # == SQLS_SECTION_NAME ==
1625 # If specified then data has to be evaluated on
1626 # Check_MK side by related check plugin.
1627 # Otherwise "<<<oracle_sql:sep(58)>>>"-check plugin
1628 # is used which process a fixed format, ie.
1629 # [[[SID-1|SQL-A]]]
1630 # details:DETAILS
1631 # perfdata:NAME=VAL;WARN;CRIT;MIN;MAX NAME=VAL;WARN;CRIT;MIN;MAX ...
1632 # long:LONG
1633 # long:LONG
1634 # ...
1635 # exit:CODE
1636 # [[[SID-2|SQL-B]]]
1637 # details:DETAILS
1638 # perfdata:
1639 # long:LONG
1640 # long:LONG
1641 # ...
1642 # exit:CODE
1643 # ...
1644
1645
1646 unset_custom_sqls_vars () {
1647 unset SQLS_SECTION_NAME SQLS_SECTION_SEP SQLS_SIDS SQLS_DIR SQLS_SQL SQLS_PARAMETERS SQLS_MAX_CACHE_AGE SQLS_ITEM_NAME MK_CUSTOM_SQLS_SECTION MK_CUSTOM_SQLS_SECTION_HEADER MK_CUSTOM_SQLS_ITEM MK_CUSTOM_SQLS_SECTION_QUERY
1648 unset SQLS_DBUSER SQLS_DBPASSWORD SQLS_DBSYSCONNECT SQLS_TNSALIAS
1649 }
1650
1651
1652 do_custom_sqls () {
1653 for section in "${custom_sqls_sections_arr[@]}"; do
1654 if [ "$(do_section "$section")" = "no" ]; then
1655 logging -w "[${MK_SID}] [${section}] [custom_sql]"\
1656 "The option '--sections' does not include section '${section}'"
1657 continue
1658 fi
1659
1660 if ! type "$section" > /dev/null 2>&1; then
1661 logging -w "[${MK_SID}] [${section}] [custom_sql]"\
1662 "Definition of '${section}' not found in configuration"
1663 continue
1664 fi
1665
1666 $section
1667
1668 local sids=${SQLS_SIDS:-$custom_sqls_sids}
1669 sids=${sids//,/ }
1670
1671 # If SID is not part of sids we can skip the rest
1672 if ! echo "$sids" | "${GREP}" -q "$MK_SID"; then
1673 logging -w "[${MK_SID}] [${section}] [custom_sql]"\
1674 "SID '${MK_SID}' is not part of stated SIDs '$sids'"
1675 unset_custom_sqls_vars
1676 continue
1677 fi
1678
1679 local section_name=${SQLS_SECTION_NAME:-$custom_sqls_section_name}
1680 local sql_dir=${SQLS_DIR:-$custom_sqls_dir}
1681 local sql=${SQLS_SQL:-$custom_sqls_sql}
1682
1683 # If no section name, SQL dir or file is stated or SQL file does not exist we skip the rest
1684 if [ -z "$section_name" ]; then
1685 logging -w "[${MK_SID}] [${section}] [custom_sql]" "Empty section name"
1686 unset_custom_sqls_vars
1687 continue
1688 fi
1689
1690 if [ ! -d "$sql_dir" ] || [ ! -r "$sql_dir" ] ; then
1691 logging -w "[${MK_SID}] [${section}] [custom_sql]"\
1692 "SQL folder '${sql_dir}' not found or not readable"
1693 unset_custom_sqls_vars
1694 continue
1695 fi
1696
1697 if [ ! -f "${sql_dir}/$sql" ] || [ ! -r "${sql_dir}/$sql" ]; then
1698 logging -w "[${MK_SID}] [${section}] [custom_sql]"\
1699 "SQL file '${sql_dir}/$sql' not found or not readable"
1700 unset_custom_sqls_vars
1701 continue
1702 fi
1703
1704 if [ -n "$SQLS_SECTION_SEP" ]; then
1705 local section_sep="$SQLS_SECTION_SEP"
1706 elif [ -n "$custom_sqls_section_sep" ]; then
1707 local section_sep="$custom_sqls_section_sep"
1708 else
1709 local section_sep=
1710 fi
1711
1712 if [ "$section_name" == "oracle_sql" ]; then
1713 local section_header="${section_name}:sep(58)"
1714 elif [ -n "${section_sep}" ]; then
1715 local section_header="${section_name}:sep(${section_sep})"
1716 else
1717 local section_header="$section_name"
1718 fi
1719
1720 if [ "$section_name" = "oracle_sql" ]; then
1721 if [ -n "$SQLS_ITEM_NAME" ]; then
1722 local item="${MK_SID}|${SQLS_ITEM_NAME}"
1723 else
1724 local item="${MK_SID}|${sql}"
1725 fi
1726 else
1727 local item=
1728 fi
1729
1730 local parameters=${SQLS_PARAMETERS:-$custom_sqls_parameters}
1731
1732 if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
1733 local max_cache_age=
1734 elif [ -n "$SQLS_MAX_CACHE_AGE" ]; then
1735 local max_cache_age="$SQLS_MAX_CACHE_AGE"
1736 else
1737 local max_cache_age="$custom_sqls_max_cache_age"
1738 fi
1739
1740 # set custom credentials from section
1741 db_connect=$(mk_ora_db_connect "$sid")
1742 export MK_DB_CONNECT=$db_connect
1743
1744 unset_custom_sqls_vars
1745
1746 MK_CUSTOM_SQLS_SECTION="$section"
1747 export MK_CUSTOM_SQLS_SECTION_HEADER="$section_header"
1748 export MK_CUSTOM_SQLS_ITEM="$item"
1749 MK_CUSTOM_SQLS_SECTION_QUERY=$(custom_sql_section "$sql_dir" "$sql" "$parameters")
1750 export MK_CUSTOM_SQLS_SECTION_QUERY
1751
1752 logging "[${MK_SID}] [${section}] [custom_sql]"\
1753 "Section name: $MK_CUSTOM_SQLS_SECTION" "Section header: $MK_CUSTOM_SQLS_SECTION_HEADER"\
1754 "Section item: $MK_CUSTOM_SQLS_ITEM" "Custom SQLS query: $MK_CUSTOM_SQLS_SECTION_QUERY"\
1755 "Max cache age: $max_cache_age"
1756
1757 if [ "$MK_ORA_DEBUG_CONNECT" = "1" ]; then
1758 do_testmode_custom_sql
1759 else
1760 if [ -z "$max_cache_age" ]; then
1761 local output=
1762 output=$(echo -e "$MK_CUSTOM_SQLS_SECTION_QUERY" | mk_ora_sqlplus "do_custom_sqls" "yes")
1763 logging "[${MK_SID}] [${section}] [custom_sql]" "Custom SQLS query output: $output"
1764 handle_custom_sql_errors "$output"
1765 else
1766 run_cached "$max_cache_age" do_async_custom_sqls "_custom_sql_${MK_CUSTOM_SQLS_SECTION}"
1767 fi
1768 fi
1769 done
1770 }
1771
1772
1773 custom_sql_section () {
1774 local sql_dir="$1"
1775 local sql="$2"
1776 local params="$3"
1777 local sql_content=
1778
1779 echo "PROMPT <<<${MK_CUSTOM_SQLS_SECTION_HEADER}>>>"
1780 if [ -n "$MK_CUSTOM_SQLS_ITEM" ]; then
1781 echo "PROMPT [[[${MK_CUSTOM_SQLS_ITEM}]]]"
1782 fi
1783
1784 echo -e "SET VERIFY OFF;"
1785 echo -e "SET TERMOUT ON;"
1786 echo -e "SET serveroutput ON;"
1787
1788 if [ -n "$params" ]; then
1789 echo -e "$params"
1790 fi
1791
1792 sql_content=$(cat "$sql_dir/$sql")
1793 echo -e "$sql_content"
1794 }
1795
1796
1797 do_async_custom_sqls () {
1798 local output=
1799 output=$(echo -e "$MK_CUSTOM_SQLS_SECTION_QUERY" | mk_ora_sqlplus "do_async_custom_sqls" "yes")
1800 logging "[${MK_SID}] [${MK_CUSTOM_SQLS_SECTION_QUERY}] [do_async_custom_sql]" "Output: $output"
1801 handle_custom_sql_errors "$output"
1802 }
1803
1804
1805 handle_custom_sql_errors () {
1806 local output="$1"
1807 local errors=
1808 errors=$(echo -e "$output" | ${GREP} -e "ERROR at line" -e "ORA-" -e "SP2-" | tr '\n' ' ')
1809 if [ -n "$errors" ]; then
1810 echo "<<<${MK_CUSTOM_SQLS_SECTION_HEADER}>>>"
1811 if [ -n "$MK_CUSTOM_SQLS_ITEM" ]; then
1812 echo "[[[${MK_CUSTOM_SQLS_ITEM}]]]"
1813 fi
1814 # connection error already returns '$SID|FAILURE|' in mk_ora_sqplplus
1815 errors=${errors#*FAILURE|}
1816 echo "$(echo "$MK_SID" | tr '[:lower:]' '[:upper:]')|FAILURE|$errors"
1817 else
1818 echo "$output"
1819 fi
1820 }
1821
1822
1823 export -f unset_custom_sqls_vars
1824 export -f custom_sql_section
1825 export -f do_async_custom_sqls
1826 export -f handle_custom_sql_errors
1827
1828
1829 # globals
1830 custom_sqls_sections_arr=($(echo "$SQLS_SECTIONS" | tr ',' '\n'))
1831
1832 # globals and locals
1833 custom_sqls_section_name="oracle_sql" # SQLS_SECTION_NAME
1834 custom_sqls_section_sep="$SQLS_SECTION_SEP"
1835 custom_sqls_sids="$SQLS_SIDS"
1836 custom_sqls_dir="$SQLS_DIR"
1837 custom_sqls_sql="$SQLS_SQL"
1838 custom_sqls_parameters="$SQLS_PARAMETERS"
1839 custom_sqls_max_cache_age="$SQLS_MAX_CACHE_AGE"
1840 unset_custom_sqls_vars
1841
1842
1843 #.
1844 # .--helper--------------------------------------------------------------.
1845 # | _ _ |
1846 # | | |__ ___| |_ __ ___ _ __ |
1847 # | | '_ \ / _ \ | '_ \ / _ \ '__| |
1848 # | | | | | __/ | |_) | __/ | |
1849 # | |_| |_|\___|_| .__/ \___|_| |
1850 # | |_| |
1851 # '----------------------------------------------------------------------'
1852
1853 do_dummy_sections () {
1854 if [ "$MK_ORA_LOGGING" != "1" ]; then
1855 for section in $SYNC_SECTIONS $ASYNC_SECTIONS $SYNC_ASM_SECTIONS $ASYNC_ASM_SECTIONS; do
1856 echo "<<<oracle_${section}>>>"
1857 done
1858 fi
1859 }
1860
1861
1862 do_section () {
1863 local section=$1
1864 if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
1865 for mk_ora_section in "${MK_ORA_SECTIONS[@]}"; do
1866 if [ "$mk_ora_section" = "${section}" ]; then
1867 echo "yes"
1868 return
1869 fi
1870 done
1871 echo "no"
1872 else
1873 echo "yes"
1874 fi
1875 }
1876
1877
1878 skip_sid () {
1879 local sid="$1"
1880 if [ "$ONLY_SIDS" ]; then
1881 if echo "$ONLY_SIDS" | "${GREP}" -q "$sid"; then
1882 echo "no"
1883 return
1884 fi
1885 echo "yes"
1886 return
1887 fi
1888
1889 if [ "$SKIP_SIDS" ]; then
1890 if echo "$SKIP_SIDS" | "${GREP}" -q "$sid"; then
1891 echo "yes"
1892 return
1893 fi
1894 echo "no"
1895 return
1896 fi
1897
1898 EXCLUDE=EXCLUDE_$sid
1899 # Handle explicit exclusion of instances but not for +ASM
1900 if [[ "$EXCLUDE" =~ ^[a-zA-Z][a-zA-Z0-9_]*$ ]]; then
1901 EXCLUDE=${!EXCLUDE}
1902 if [ "$EXCLUDE" = "ALL" ]; then
1903 echo "yes"
1904 return
1905 fi
1906 echo "no"
1907 return
1908 fi
1909 echo "no"
1910 }
1911
1912
1913 remove_excluded_sections () {
1914 # We exclude instance section because we have already executed
1915 # the sql_instance section at the beginning for every SID.
1916 local sections="$1"
1917 local excluded="$2"
1918 local result=
1919 local skip=
1920 for section in $sections; do
1921 skip=
1922 for exclude in $excluded; do
1923 if [ "$exclude" = "$section" ]; then
1924 skip=yes
1925 break
1926 fi
1927 done
1928 if [ "$skip" != yes ]; then
1929 result=${result:+"$result "}"${section}"
1930 fi
1931 done
1932 echo "$result"
1933 }
1934
1935
1936 ora_session_environment () {
1937 echo 'set pages 0 trimspool on feedback off lines 8000'
1938 if [ "$NUMERIC_ORACLE_VERSION" -ge 102 ] && [ ! "$DISABLE_ORA_SESSION_SETTINGS" ]; then
1939 echo 'set echo off'
1940 echo 'alter session set "_optimizer_mjc_enabled"=false;'
1941
1942 # cursor_sharing is not valid for ASM instances
1943 if [ ! "${ORACLE_SID:0:1}" = '+' ]; then
1944 echo 'alter session set cursor_sharing=exact;'
1945 fi
1946
1947 echo 'set echo on'
1948 fi
1949 #TODO Do not exit after first error otherwise
1950 # section specific errors won't be seen any more.
1951 #echo 'whenever sqlerror exit 1'
1952 echo ' '
1953 }
1954
1955 #.
1956 # .--run cached----------------------------------------------------------.
1957 # | _ _ |
1958 # | _ __ _ _ _ __ ___ __ _ ___| |__ ___ __| | |
1959 # | | '__| | | | '_ \ / __/ _` |/ __| '_ \ / _ \/ _` | |
1960 # | | | | |_| | | | | | (_| (_| | (__| | | | __/ (_| | |
1961 # | |_| \__,_|_| |_| \___\__,_|\___|_| |_|\___|\__,_| |
1962 # | |
1963 # '----------------------------------------------------------------------'
1964
1965 run_cached () {
1966 if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
1967 return
1968 fi
1969
1970 local maxage="$1"
1971 local cmd_name="$2"
1972
1973 local cache_file="$MK_VARDIR/cache/oracle_${MK_SID}${3}.cache"
1974 local use_cache_file=
1975 local now=
1976 local cf_atime=
1977 local mtime=
1978
1979 logging "[${MK_SID}] [run_cached]" "Cache file: $cache_file"\
1980 "Cache max age: $maxage" "Command name: $cmd_name"
1981
1982 if [ ! -d "$MK_VARDIR/cache" ]; then
1983 mkdir -p "$MK_VARDIR/cache"
1984 fi
1985
1986 # Check if the creation of the cache takes suspiciously long and return
1987 # nothing if the age (access time) of $cache_file.new is twice the 'maxage'
1988 # perl is needed for Solaris => no date +%s availible
1989 now=$(perl -le "print time()")
1990 if [ -e "${cache_file}.new" ]; then
1991 cf_atime=$(eval "$STATCX" "${cache_file}.new")
1992 if [ $((now - cf_atime)) -ge $((maxage * 2)) ]; then
1993 # Kill the process still accessing that file in case
1994 # it is still running. This avoids overlapping processes!
1995 fuser -k -9 "${cache_file}.new" >/dev/null 2>&1
1996 rm -f "${cache_file}.new"
1997 logging "[${MK_SID}] [run_cached]" "Creation of the cache takes suspiciously long"
1998 return
1999 fi
2000 fi
2001
2002 # Check if cache file exists and is recent enough
2003 if [ -s "$cache_file" ]; then
2004 mtime=$(eval "$STATCY" "$cache_file")
2005 if [ $((now - mtime)) -le "$maxage" ]; then
2006 use_cache_file=1
2007 fi
2008 # Output the file in any case, even if it is
2009 # outdated. The new file will not yet be available
2010 logging "[${MK_SID}] [run_cached]" "Cache file exists and is recent enough"
2011 cat "$cache_file"
2012 fi
2013
2014 # Cache file outdated and new job not yet running? Start it
2015 if [ -z "$use_cache_file" ] && [ ! -e "${cache_file}.new" ]; then
2016 logging "[${MK_SID}] [run_cached]" "Cache file outdated, start it."
2017 if [ "$MK_ORA_DEBUG" ]; then
2018 echo "set -o noclobber; exec > \"${cache_file}.new\" || exit 1; ${cmd_name} && mv \"${cache_file}.new\" \"${cache_file}\" || rm -f \"${cache_file}\" \"${cache_file}.new\"" | /bin/bash
2019 else
2020 # When the command fails, the output is throws away ignored
2021 echo "set -o noclobber; exec > \"${cache_file}.new\" || exit 1; ${cmd_name} && mv \"${cache_file}.new\" \"${cache_file}\" || rm -f \"${cache_file}\" \"${cache_file}.new\"" | nohup /bin/bash >/dev/null 2>&1 &
2022 fi
2023 fi
2024 }
2025
2026 #.
2027 # .--DB connect----------------------------------------------------------.
2028 # | ____ ____ _ |
2029 # | | _ \| __ ) ___ ___ _ __ _ __ ___ ___| |_ |
2030 # | | | | | _ \ / __/ _ \| '_ \| '_ \ / _ \/ __| __| |
2031 # | | |_| | |_) | | (_| (_) | | | | | | | __/ (__| |_ |
2032 # | |____/|____/ \___\___/|_| |_|_| |_|\___|\___|\__| |
2033 # | |
2034 # '----------------------------------------------------------------------'
2035
2036 mk_ora_db_connect () {
2037 local sid="$1"
2038
2039 ORADBUSER=""
2040 DBPASSWORD=""
2041
2042 if [[ "$sid" =~ ^REMOTE_INSTANCE_.* ]]; then
2043 # working on REMOTE_-Mode!
2044 ORACFGLINE=$(eval "echo \${$1}")
2045 ORACLE_SID=$(echo "${ORACFGLINE}" | cut -d":" -f7)
2046 TNSALIAS=$(echo "${REMOTE_VARNAME}" | cut -d"_" -f3-)
2047
2048 else
2049 # working with locally running instances
2050 # mk_oracle_dbusers.conf is for compatibility. Do not use it anymore
2051 ORACLE_USERCONF=${MK_CONFDIR}/mk_oracle_dbuser.conf
2052 TNSALIAS=${ORACLE_SID}
2053
2054 # ASM use '+' as 1st character in SID!
2055 if [ "${ORACLE_SID:0:1}" = '+' ]; then
2056 ORACFGLINE=${ASMUSER}
2057
2058 else
2059 # use an individuel user or the default DBUSER from mk_oracle.cfg
2060 dummy="DBUSER_${ORACLE_SID}"
2061 ORACFGLINE=${!dummy}
2062 if [ "$ORACFGLINE" = '' ]; then
2063 ORACFGLINE=${DBUSER}
2064 fi
2065 fi
2066
2067 if [ -f "${ORACLE_USERCONF}" ] && [ "${ORACFGLINE}" = '' ]; then
2068 # mk_oracle_dbuser.conf
2069 ORACFGLINE=$("${GREP}" "^${ORACLE_SID}:" < "${ORACLE_USERCONF}")
2070 # mk_oracle_dbuser has ORACLE_SID as 1. parameter. we need an offset for all values
2071 offset=1
2072 fi
2073 fi
2074
2075 offset=${offset:-0}
2076 if [ -n "$SQLS_DBUSER" ]; then
2077 ORADBUSER=${SQLS_DBUSER}
2078 DBPASSWORD=${SQLS_DBPASSWORD}
2079 DBSYSCONNECT=${SQLS_DBSYSCONNECT:-}
2080 else
2081 ORADBUSER=$(echo "${ORACFGLINE}" | cut -d":" -f$((1+offset)))
2082 DBPASSWORD=$(echo "${ORACFGLINE}" | cut -d":" -f$((2+offset)))
2083 DBSYSCONNECT=$(echo "${ORACFGLINE}" | cut -d":" -f$((3+offset)))
2084 fi
2085 DBHOST=$(echo "${ORACFGLINE}" | cut -d":" -f$((4+offset)))
2086 DBPORT=$(echo "${ORACFGLINE}" | cut -d":" -f$((5+offset)))
2087
2088 TNSPINGOK=no
2089 if [ -f "${TNS_ADMIN}/tnsnames.ora" ]; then
2090 if [ "${ORADBUSER:0:2}" = '/@' ]; then
2091 P_TNSALIAS_P=$(eval echo "${ORADBUSER:2}")
2092 else
2093 P_TNSALIAS_P=$ORACLE_SID
2094 fi
2095
2096 # PREFIX / POSTFIX makes no sense for ASM
2097 # => only 1 Instance with known connection possible
2098 if [ -n "$P_TNSALIAS_P" -a ! "${ORACLE_SID:0:1}" = '+' ]; then
2099 PREFIX_SID="PREFIX_$sid"
2100 PREFIX_SID=${!PREFIX_SID}
2101 if [ -n "$PREFIX_SID" ]; then
2102 P_TNSALIAS_P="$PREFIX_SID$P_TNSALIAS_P"
2103 elif [ -n "$PREFIX" ]; then
2104 P_TNSALIAS_P="$PREFIX$P_TNSALIAS_P"
2105 else
2106 P_TNSALIAS_P="$P_TNSALIAS_P"
2107 fi
2108
2109 POSTFIX_SID="POSTFIX_$sid"
2110 POSTFIX_SID=${!POSTFIX_SID}
2111 if [ -n "$POSTFIX_SID" ]; then
2112 P_TNSALIAS_P="$P_TNSALIAS_P$POSTFIX_SID"
2113 elif [ -n "$POSTFIX" ]; then
2114 P_TNSALIAS_P="$P_TNSALIAS_P$POSTFIX"
2115 else
2116 P_TNSALIAS_P="$P_TNSALIAS_P"
2117 fi
2118 fi
2119
2120 TNSALIAS=${SQLS_TNSALIAS:-${P_TNSALIAS_P}}
2121 if "${ORACLE_HOME}"/bin/tnsping "$TNSALIAS" >/dev/null 2>&1; then
2122 TNSPINGOK=yes
2123 else
2124 unset TNSALIAS
2125 fi
2126 else
2127 unset TNSALIAS
2128 fi
2129
2130 logging "[${sid}] [mk_ora_db_connect]" "ORA DB user: $ORADBUSER"\
2131 "DB sys connect: $DBSYSCONNECT" "DB host: $DBHOST" "DB port: $DBPORT"\
2132 "TNS alias: $TNSALIAS" "TNS PING: ${TNSPINGOK}"
2133
2134 if [ ! "${ORACFGLINE}" ]; then
2135 # no configuration found
2136 # => use the wallet with tnsnames.ora or EZCONNECT
2137 TNSALIAS=${TNSALIAS:-"localhost:1521/${ORACLE_SID}"}
2138 else
2139 if [ "${DBSYSCONNECT}" ]; then
2140 assysdbaconnect=" as "${DBSYSCONNECT}
2141 fi
2142
2143 TNSALIAS=${TNSALIAS:-"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${DBHOST:-"localhost"})(PORT=${DBPORT:-1521}))(CONNECT_DATA=(SID=${ORACLE_SID})(SERVER=DEDICATED)(UR=A)))"}
2144
2145 # ORADBUSER = '/'? => ignore DBPASSWORD and use the wallet
2146 if [ "${ORADBUSER:0:1}" = '/' ]; then
2147 # connect with / and wallet
2148 ORADBUSER=""
2149 DBPASSWORD=""
2150 if [ "$TNSPINGOK" = 'no' ]; then
2151 # create an EZCONNECT string when no tnsnames.ora is usable
2152 # defaults to localhost:1521/<ORACLE_SID>
2153 TNSALIAS="${DBHOST:-"localhost"}:${DBPORT:-1521}/${ORACLE_SID}"
2154 fi
2155 fi
2156 fi
2157
2158 logging "[${sid}] [mk_ora_db_connect]" "DB connection: ${ORADBUSER}/${DBPASSWORD//?/*}@${TNSALIAS}${assysdbaconnect}"
2159 echo "${ORADBUSER}/${DBPASSWORD}@${TNSALIAS}${assysdbaconnect}"
2160
2161 }
2162
2163 #.
2164 # .--mk ora sqlplus------------------------------------------------------.
2165 # | _ _ _ |
2166 # | _ __ ___ | | __ ___ _ __ __ _ ___ __ _| |_ __ | |_ _ ___ |
2167 # | | '_ ` _ \| |/ / / _ \| '__/ _` | / __|/ _` | | '_ \| | | | / __| |
2168 # | | | | | | | < | (_) | | | (_| | \__ \ (_| | | |_) | | |_| \__ \ |
2169 # | |_| |_| |_|_|\_\ \___/|_| \__,_| |___/\__, |_| .__/|_|\__,_|___/ |
2170 # | |_| |_| |
2171 # '----------------------------------------------------------------------'
2172
2173 mk_ora_sqlplus () {
2174 local from_where="$1"
2175 local print_elapsed_time="$2"
2176 local start_time=
2177 local elapsed_time=
2178 local loc_stdin=
2179 local output=
2180
2181 logging "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "Piggyback host: $MK_PIGGYBACK_HOST"
2182
2183 start_time="$(perl -MTime::HiRes=time -wle 'print time')"
2184 loc_stdin=$(cat)
2185
2186 SQLPLUS=${ORACLE_HOME}/bin/sqlplus
2187 if [ ! -x "${SQLPLUS}" ]; then
2188 logging -w "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "SQLplus '${SQLPLUS}' not found or ORACLE_HOME '${ORACLE_HOME}' wrong."
2189 echo "sqlplus not found or ORACLE_HOME wrong!"
2190 echo "SQLPLUS=${SQLPLUS}"
2191 return 1
2192 fi
2193
2194 if [ -n "$MK_PIGGYBACK_HOST" ]; then
2195 echo "<<<<${MK_PIGGYBACK_HOST}>>>>"
2196 fi
2197
2198 if output=$(echo "$(ora_session_environment)${loc_stdin}" | "$SQLPLUS" -L -s "$MK_DB_CONNECT"); then
2199 echo -e "$output"
2200
2201 else
2202 logging -w "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "OUTPUT '$output'"
2203 output=$(echo -e "$output" | "${GREP}" -v "^ERROR at line" | tr '\n' ' ' |\
2204 sed "s/^/$(echo "$MK_SID" | tr '[:lower:]' '[:upper:]')|FAILURE|/" ; echo)
2205 if [ "$MK_ORA_DEBUG_CONNECT" ]; then
2206 echo "Logindetails: ${DBCONNECT}" >&2
2207 echo -e "$output"
2208 else
2209 echo '<<<oracle_instance:sep(124)>>>'
2210 echo -e "$output"
2211 echo
2212 fi
2213 fi
2214
2215 elapsed_time=$(bc <<<"$(perl -MTime::HiRes=time -wle 'print time')-$start_time")
2216
2217 if [ "$print_elapsed_time" = "yes" ]; then
2218 echo "elapsed:$elapsed_time"
2219 fi
2220
2221 if [ -n "$MK_PIGGYBACK_HOST" ]; then
2222 echo "<<<<>>>>"
2223 fi
2224
2225 logging "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "Elapsed time: $elapsed_time"
2226 }
2227
2228 #.
2229 # .--do checks-----------------------------------------------------------.
2230 # | _ _ _ |
2231 # | __| | ___ ___| |__ ___ ___| | _____ |
2232 # | / _` |/ _ \ / __| '_ \ / _ \/ __| |/ / __| |
2233 # | | (_| | (_) | | (__| | | | __/ (__| <\__ \ |
2234 # | \__,_|\___/ \___|_| |_|\___|\___|_|\_\___/ |
2235 # | |
2236 # '----------------------------------------------------------------------'
2237
2238 # Create one SQL statements for several sections and run
2239 # these with sqlplus. The exitcode is preserved.
2240 do_sync_checks () {
2241 logging "[${MK_SID}] [do_sync_checks]" "Query: $MK_SYNC_SECTIONS_QUERY"
2242 echo "$MK_SYNC_SECTIONS_QUERY" | mk_ora_sqlplus "do_sync_checks"
2243 }
2244
2245
2246 do_async_checks () {
2247 logging "[${MK_SID}] [do_async_checks]" "Query: $MK_ASYNC_SECTIONS_QUERY"
2248 echo "$MK_ASYNC_SECTIONS_QUERY" | mk_ora_sqlplus "do_async_checks"
2249 }
2250
2251
2252 do_testmode () {
2253 local sections=$1
2254 local asections=$2
2255
2256 echo
2257 echo "---login----------------------------------------------------------------"
2258 echo " Operating System: ${OS_TYPE}"
2259 echo " ORACLE_HOME ${ORA_HOME_SOURCE} ${ORACLE_HOME}"
2260 echo " Logincheck to Instance: ${MK_SID}"
2261 echo " Version: ${ORACLE_VERSION}"
2262 echo "select ' Login ok User: ' || user || ' on ' || host_name ||' Instance ' || instance_name
2263 from v\$instance;" | mk_ora_sqlplus "do_testmode"
2264 echo " SYNC_SECTIONS: $sections"
2265 echo " ASYNC_SECTIONS: $asections"
2266 if [ "$IGNORE_DB_NAME" ]; then
2267 echo " IGNORE_DB_NAME found. Ignoring DB_NAME in all SQLs!"
2268 fi
2269
2270 if [ "$DISABLE_ORA_SESSION_SETTINGS" ]; then
2271 echo " Paramter DISABLE_ORA_SESSION_SETTINGS found!"
2272 fi
2273
2274 if [ "$HINT_RMAN" ]; then
2275 echo " Using HINT_RMAN for this Instance!"
2276 fi
2277 echo "------------------------------------------------------------------------"
2278 echo
2279 }
2280
2281 do_testmode_custom_sql() {
2282 echo "---custom SQL ----------------------------------------------------------"
2283 echo " section: ${section}"
2284 echo " SQL: ${sql}"
2285 echo " MAX_CACHE_AGE: ${max_cache_age}"
2286 echo "------------------------------------------------------------------------"
2287 }
2288
2289 do_checks () {
2290 local excluded=
2291 local sections=
2292 local asections=
2293
2294 if [ "${ORACLE_SID:0:1}" = '+' ]; then
2295 # ASM sections
2296 local do_async_sections=${ASYNC_ASM_SECTIONS}
2297 local do_sync_sections=${SYNC_ASM_SECTIONS}
2298 else
2299 local dummy="SYNC_SECTIONS_${ORACLE_SID}"
2300 local SYNC_SECTIONS_SID=${!dummy}
2301 local do_sync_sections=${SYNC_SECTIONS_SID:-${SYNC_SECTIONS}}
2302 unset dummy
2303
2304 local dummy="ASYNC_SECTIONS_${ORACLE_SID}"
2305 local ASYNC_SECTIONS_SID=${!dummy}
2306 local do_async_sections=${ASYNC_SECTIONS_SID:-${ASYNC_SECTIONS}}
2307 fi
2308
2309 if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
2310 sections=$(
2311 for section in $do_sync_sections $do_async_sections; do
2312 if [ "$(do_section "$section")" = "yes" ]; then
2313 sections=${sections:+"$sections "}"${section}"
2314 fi
2315 done; echo "$sections")
2316 logging "[${MK_SID}] [do_checks]" "Do single sections: $sections"
2317
2318 MK_SYNC_SECTIONS_QUERY=$(for section in $sections; do eval "sql_$section" ; done)
2319 MK_ASYNC_SECTIONS_QUERY=
2320
2321 else
2322 excluded=$(eval "echo \$EXCLUDE_$MK_SID")
2323 sections=$(remove_excluded_sections "$do_sync_sections" "$excluded")
2324 asections=$(remove_excluded_sections "$do_async_sections" "$excluded")
2325 logging "[${MK_SID}] [do_checks]" "Excluded: $excluded"\
2326 "Sections: $sections" "Async sections: $asections"
2327
2328 MK_SYNC_SECTIONS_QUERY=$(for section in $sections; do eval "sql_$section" ; done)
2329 MK_ASYNC_SECTIONS_QUERY=$(for section in $asections; do eval "sql_$section" ; done)
2330
2331 fi
2332 export MK_ASYNC_SECTIONS_QUERY
2333
2334 if [ "$MK_ORA_LOGGING" = "1" ]; then
2335 logging "[${MK_SID}] [do_checks]" "Testmode: $(echo \
2336 "select user || ' on ' || host_name || ' instance ' || instance_name from v\$instance;" | mk_ora_sqlplus "do_checks")"
2337 fi
2338
2339 if [ "$MK_ORA_DEBUG_CONNECT" = "1" ]; then
2340 do_testmode "$sections" "$asections" 2>&1
2341 else
2342 do_sync_checks
2343 run_cached "$CACHE_MAXAGE" do_async_checks
2344 fi
2345 }
2346
2347 #.
2348 # .--main----------------------------------------------------------------.
2349 # | _ |
2350 # | _ __ ___ __ _(_)_ __ |
2351 # | | '_ ` _ \ / _` | | '_ \ |
2352 # | | | | | | | (_| | | | | | |
2353 # | |_| |_| |_|\__,_|_|_| |_| |
2354 # | |
2355 # +----------------------------------------------------------------------+
2356 # | Iterate over all instances and execute sync and async sections. |
2357 # '----------------------------------------------------------------------'
2358
2359 # Make sure that the new shell that is being run by run_cached inherits
2360 # our functions
2361 export -f logging
2362 export -f mk_ora_sqlplus
2363 export -f ora_session_environment
2364 export -f do_async_checks
2365 export -f set_ora_env
2366
2367 # ---preliminaries--------------------------------------------------------
2368
2369 logging "--------------------------------------------------------------------"
2370 if [ "$MK_ORA_LOGGING" == "1" ]; then
2371 echo "Start logging to file: $MK_VARDIR/log/mk_oracle.log" >&2
2372 fi
2373
2374 set_os_env
2375 logging "[preliminaries]" "OS: ${OS_TYPE}"\
2376 "GREP: ${GREP}" "AWK: ${AWK}" "STATCX: ${STATCX}" "STATCY: ${STATCY}"
2377
2378
2379 logging "[preliminaries]" "SYNC_SECTIONS: ${SYNC_SECTIONS}"\
2380 "ASYNC_SECTIONS: ${ASYNC_SECTIONS}" "SYNC_ASM_SECTIONS: ${SYNC_ASM_SECTIONS}"\
2381 "ASYNC_ASM_SECTIONS: ${ASYNC_ASM_SECTIONS}" "CACHE_MAXAGE: ${CACHE_MAXAGE}"\
2382 "ONLY_SIDS: ${ONLY_SIDS}" "SKIP_SIDS: ${SKIP_SIDS}"
2383
2384
2385 # Get list of all running databases. Do not work on ASM in this plugin.
2386 # => Ignore a running ASM-Instance!
2387 # shellcheck disable=SC2016
2388 SIDS=$(UNIX95=true ps -ef | "${AWK}" '{print $NF}' | "${GREP}" -E '^asm_pmon_|^ora_pmon_|^xe_pmon_XE' | cut -d"_" -f3-)
2389
2390
2391 # Are there any remote configurations?
2392 for remote_instance in $(compgen -A variable | "${GREP}" -E "^REMOTE_INSTANCE_.*"); do
2393 REMOTE_INSTANCES="${REMOTE_INSTANCES} ${remote_instance}"
2394 # shellcheck disable=SC2005
2395 PIGGYBACK_HOSTS="$(echo "$(eval "echo \${$remote_instance}")" | cut -d":" -f6) ${PIGGYBACK_HOSTS}"
2396 done
2397
2398
2399 logging "[preliminaries]" "SIDs: ${SIDS//\\n/ }"\
2400 "Remote instances: ${REMOTE_INSTANCES}"\
2401 "Piggyback hosts: ${PIGGYBACK_HOSTS}"
2402
2403
2404 if [ "$PIGGYBACK_HOSTS" ]; then
2405 PIGGYBACK_HOSTS=$(echo "$PIGGYBACK_HOSTS" | tr ' ' '\n' | sort | uniq)
2406 logging "[preliminaries]" "Removed duplicate piggyback hosts"\
2407 "Remaining piggyback hosts: ${PIGGYBACK_HOSTS}"
2408
2409 if [ ! -e "$MK_VARDIR/mk_oracle.found" ]; then
2410 touch "$MK_VARDIR/mk_oracle.found"
2411 fi
2412 fi
2413
2414
2415 # If we do not have found any running database instance, then either
2416 # no ORACLE is present on this system or it's just currently not running.
2417 # In the later case we ouput empty agent sections so that Check_MK will be
2418 # happy and execute the actual check functions.
2419 if [ -z "$SIDS" ] && [ ! -e "$MK_VARDIR/mk_oracle.found" ]; then
2420 logging -c "[preliminaries]" "No SIDs and \$MK_VARDIR/mk_oracle.found not found"
2421 echo "No SIDs and \$MK_VARDIR/mk_oracle.found not found" >&2
2422 exit 1
2423 fi
2424
2425
2426 # From now on we expect databases on this system (for ever)
2427 touch "$MK_VARDIR/mk_oracle.found"
2428
2429
2430 # Make sure that always all sections are present, even
2431 # in case of an error. Note: the section <<<oracle_instance>>>
2432 # section shows the general state of a database instance. If
2433 # that section fails for an instance then all other sections
2434 # do not contain valid data anyway.
2435 do_dummy_sections
2436
2437
2438 if [ "$PIGGYBACK_HOSTS" ]; then
2439 for piggyback_host in $PIGGYBACK_HOSTS; do
2440 echo "<<<<${piggyback_host}>>>>"
2441 do_dummy_sections
2442 echo "<<<<>>>>"
2443 done
2444 fi
2445
2446 # ---local----------------------------------------------------------------
2447
2448 for sid in $SIDS; do
2449 skip=$(skip_sid "$sid")
2450 logging "[${sid}] [local]" "Skipping: $skip"
2451 if [ "$skip" == "yes" ]; then
2452 continue
2453 fi
2454
2455 set_ora_env "$sid"
2456 if [ $? -eq 2 ] ; then
2457 # we have to skip this SID due to missing/unknown ORACLE_HOME
2458 continue
2459 fi
2460 set_ora_version "$("${ORACLE_HOME}"/bin/sqlplus -V | "${GREP}" ^SQL | cut -d" " -f3 | cut -d"." -f-2)"
2461 logging "[${sid}] [local]" "ORACLE_HOME: ${ORACLE_HOME}" "TNS_ADMIN: ${TNS_ADMIN}"\
2462 "ORACLE_SID: ${ORACLE_SID}" "LD_LIBRARY_PATH: ${LD_LIBRARY_PATH}"\
2463 "ORACLE_VERSION: ${ORACLE_VERSION} (${NUMERIC_ORACLE_VERSION})"
2464
2465
2466 db_connect=$(mk_ora_db_connect "$sid")
2467 export MK_SID=$sid
2468 export MK_DB_CONNECT=$db_connect
2469
2470 do_checks
2471
2472 # MK_DB_CONNECT could be changed by do_custom_sqls!
2473 do_custom_sqls
2474 done
2475
2476 # ---remote---------------------------------------------------------------
2477
2478 for remote_instance in $REMOTE_INSTANCES; do
2479 remote_instance_line=$(eval "echo \${$remote_instance}")
2480
2481 set_ora_env "$remote_instance"
2482 set_ora_version "$(echo "$remote_instance_line" | cut -d":" -f8)"
2483 logging "[${remote_instance}] [remote]" "ORACLE_HOME: ${ORACLE_HOME}" "TNS_ADMIN: ${TNS_ADMIN}"\
2484 "ORACLE_SID: ${ORACLE_SID}" "LD_LIBRARY_PATH: ${LD_LIBRARY_PATH}"\
2485 "ORACLE_VERSION: ${ORACLE_VERSION} (${NUMERIC_ORACLE_VERSION})"
2486
2487 db_connect=$(mk_ora_db_connect "$remote_instance")
2488 piggyback_host=$(echo "${remote_instance_line}" | cut -d":" -f6)
2489 export MK_SID=$remote_instance
2490 export MK_DB_CONNECT=$db_connect
2491 export MK_PIGGYBACK_HOST=$piggyback_host
2492
2493 do_checks
2494 done
check_mk