?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Refactoring: Changed all check parameters starting with an 'o' to the new rulespec...
[check_mk.git] / agents / plugins / mk_oracle
blob9c7eff35ba73b170e6b089738d3d41d998217acb
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 FROM v\$database d
793 JOIN v\$parameter vp on 1=1
794 JOIN v\$instance i on 1=1
795 left outer join V\$dataguard_stats ds on 1=1
796 WHERE vp.name = 'log_archive_config'
797 AND vp.value is not null
798 ORDER BY 1;"
799 fi
800 }
801
802
803 sql_recovery_status () {
804 echo 'PROMPT <<<oracle_recovery_status:sep(124)>>>'
805 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
806 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}
807 , 0, decode(vp.con_id, null, d.NAME
808 ,d.NAME||'.'||vp.name)
809 , i.instance_name))
810 ||'|'|| d.DB_UNIQUE_NAME
811 ||'|'|| d.DATABASE_ROLE
812 ||'|'|| d.open_mode
813 ||'|'|| dh.file#
814 ||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
815 ||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
816 ||'|'|| dh.STATUS
817 ||'|'|| dh.RECOVER
818 ||'|'|| dh.FUZZY
819 ||'|'|| dh.CHECKPOINT_CHANGE#
820 ||'|'|| vb.STATUS
821 ||'|'|| round((sysdate-vb.TIME)*24*60*60)
822 FROM V\$datafile_header dh
823 JOIN v\$database d on 1=1
824 JOIN v\$instance i on 1=1
825 JOIN v\$backup vb on 1=1
826 LEFT OUTER JOIN V\$PDBS vp on dh.con_id = vp.con_id
827 WHERE vb.file# = dh.file#
828 ORDER BY dh.file#;"
829 elif [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
830 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
831 ||'|'|| d.DB_UNIQUE_NAME
832 ||'|'|| d.DATABASE_ROLE
833 ||'|'|| d.open_mode
834 ||'|'|| dh.file#
835 ||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
836 ||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
837 ||'|'|| dh.STATUS
838 ||'|'|| dh.RECOVER
839 ||'|'|| dh.FUZZY
840 ||'|'|| dh.CHECKPOINT_CHANGE#
841 FROM V\$datafile_header dh, v\$database d, v\$instance i
842 ORDER BY dh.file#;
843 "
844 elif [ "$NUMERIC_ORACLE_VERSION" -ge 92 ]; then
845 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
846 ||'|'|| upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
847 ||'|'|| d.DATABASE_ROLE
848 ||'|'|| d.open_mode
849 ||'|'|| dh.file#
850 ||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
851 ||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
852 ||'|'|| dh.STATUS
853 ||'|'|| dh.RECOVER
854 ||'|'|| dh.FUZZY
855 ||'|'|| dh.CHECKPOINT_CHANGE#
856 FROM V\$datafile_header dh, v\$database d, v\$instance i
857 ORDER BY dh.file#;
858 "
859 fi
860 }
861
862
863 sql_rman () {
864 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
865
866 echo 'PROMPT <<<oracle_rman:sep(124)>>>'
867 echo "select /*"$HINT_RMAN" check_mk rman1 */ upper(name)
868 || '|'|| 'COMPLETED'
869 || '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
870 || '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
871 || '|'|| case when INCREMENTAL_LEVEL IS NULL
872 then 'DB_FULL'
873 else 'DB_INCR'
874 end
875 || '|'|| INCREMENTAL_LEVEL
876 || '|'|| round(((sysdate-COMPLETION_TIME) * 24 * 60), 0)
877 || '|'|| INCREMENTAL_CHANGE#
878 from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name)) name
879 , bd2.INCREMENTAL_LEVEL, bd2.INCREMENTAL_CHANGE#, min(bd2.COMPLETION_TIME) COMPLETION_TIME
880 from (select bd.file#, bd.INCREMENTAL_LEVEL, max(bd.COMPLETION_TIME) COMPLETION_TIME
881 from v\$backup_datafile bd
882 join v\$datafile_header dh on dh.file# = bd.file#
883 where dh.status = 'ONLINE'
884 and dh.con_id <> 2
885 group by bd.file#, bd.INCREMENTAL_LEVEL
886 ) bd
887 join v\$backup_datafile bd2 on bd2.file# = bd.file#
888 and bd2.COMPLETION_TIME = bd.COMPLETION_TIME
889 join v\$database vd on vd.RESETLOGS_CHANGE# = bd2.RESETLOGS_CHANGE#
890 join v\$instance i on 1=1
891 group by upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
892 , bd2.INCREMENTAL_LEVEL
893 , bd2.INCREMENTAL_CHANGE#
894 order by name, bd2.INCREMENTAL_LEVEL);
895
896 select /*"$HINT_RMAN" check_mk rman2 */ name
897 || '|' || 'COMPLETED'
898 || '|'
899 || '|' || to_char(CHECKPOINT_TIME, 'yyyy-mm-dd_hh24:mi:ss')
900 || '|' || 'CONTROLFILE'
901 || '|'
902 || '|' || round((sysdate - CHECKPOINT_TIME) * 24 * 60)
903 || '|' || '0'
904 from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
905 ,max(bcd.CHECKPOINT_TIME) CHECKPOINT_TIME
906 from v\$database d
907 join V\$BACKUP_CONTROLFILE_DETAILS bcd on d.RESETLOGS_CHANGE# = bcd.RESETLOGS_CHANGE#
908 join v\$instance i on 1=1
909 group by upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
910 );
911
912 select /*"$HINT_RMAN" check_mk rman3 */ name
913 || '|COMPLETED'
914 || '|'|| to_char(sysdate, 'YYYY-mm-dd_HH24:MI:SS')
915 || '|'|| to_char(completed, 'YYYY-mm-dd_HH24:MI:SS')
916 || '|ARCHIVELOG||'
917 || round((sysdate - completed)*24*60,0)
918 || '|'
919 from (
920 select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
921 , max(a.completion_time) completed
922 , case when a.backup_count > 0 then 1 else 0 end
923 from v\$archived_log a, v\$database d, v\$instance i
924 where a.backup_count > 0
925 and a.dest_id in
926 (select b.dest_id
927 from v\$archive_dest b
928 where b.target = 'PRIMARY'
929 and b.SCHEDULE = 'ACTIVE'
930 )
931 group by d.NAME, i.instance_name
932 , case when a.backup_count > 0 then 1 else 0 end);"
933
934 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
935 echo 'PROMPT <<<oracle_rman:sep(124)>>>'
936 echo "select /*${HINT_RMAN} check_mk rman1 */ upper(name)
937 || '|'|| 'COMPLETED'
938 || '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
939 || '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
940 || '|'|| case when INCREMENTAL_LEVEL IS NULL
941 then 'DB_FULL'
942 else 'DB_INCR'
943 end
944 || '|'|| INCREMENTAL_LEVEL
945 || '|'|| round(((sysdate-COMPLETION_TIME) * 24 * 60), 0)
946 || '|'|| INCREMENTAL_CHANGE#
947 from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name)) name
948 , bd2.INCREMENTAL_LEVEL, bd2.INCREMENTAL_CHANGE#, min(bd2.COMPLETION_TIME) COMPLETION_TIME
949 from (select bd.file#, bd.INCREMENTAL_LEVEL, max(bd.COMPLETION_TIME) COMPLETION_TIME
950 from v\$backup_datafile bd
951 join v\$datafile_header dh on dh.file# = bd.file#
952 where dh.status = 'ONLINE'
953 group by bd.file#, bd.INCREMENTAL_LEVEL
954 ) bd
955 join v\$backup_datafile bd2 on bd2.file# = bd.file#
956 and bd2.COMPLETION_TIME = bd.COMPLETION_TIME
957 join v\$database vd on vd.RESETLOGS_CHANGE# = bd2.RESETLOGS_CHANGE#
958 join v\$instance i on 1=1
959 group by upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
960 , bd2.INCREMENTAL_LEVEL
961 , bd2.INCREMENTAL_CHANGE#
962 order by name, bd2.INCREMENTAL_LEVEL);
963
964 select /*${HINT_RMAN} check_mk rman2 */ name
965 || '|' || 'COMPLETED'
966 || '|'
967 || '|' || to_char(CHECKPOINT_TIME, 'yyyy-mm-dd_hh24:mi:ss')
968 || '|' || 'CONTROLFILE'
969 || '|'
970 || '|' || round((sysdate - CHECKPOINT_TIME) * 24 * 60)
971 || '|' || '0'
972 from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
973 ,max(bcd.CHECKPOINT_TIME) CHECKPOINT_TIME
974 from v\$database d
975 join V\$BACKUP_CONTROLFILE_DETAILS bcd on d.RESETLOGS_CHANGE# = bcd.RESETLOGS_CHANGE#
976 join v\$instance i on 1=1
977 group by upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
978 );
979
980 select /*${HINT_RMAN} check_mk rman3 */ name
981 || '|COMPLETED'
982 || '|'|| to_char(sysdate, 'YYYY-mm-dd_HH24:MI:SS')
983 || '|'|| to_char(completed, 'YYYY-mm-dd_HH24:MI:SS')
984 || '|ARCHIVELOG||'
985 || round((sysdate - completed)*24*60,0)
986 || '|'
987 from (
988 select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
989 , max(a.completion_time) completed
990 , case when a.backup_count > 0 then 1 else 0 end
991 from v\$archived_log a, v\$database d, v\$instance i
992 where a.backup_count > 0
993 and a.dest_id in
994 (select b.dest_id
995 from v\$archive_dest b
996 where b.target = 'PRIMARY'
997 and b.SCHEDULE = 'ACTIVE'
998 )
999 group by d.NAME, i.instance_name
1000 , case when a.backup_count > 0 then 1 else 0 end);"
1001 fi
1002 }
1003
1004
1005 sql_recovery_area () {
1006 if [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1007 echo 'PROMPT <<<oracle_recovery_area:sep(124)>>>'
1008 echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
1009 ||'|'|| round((SPACE_USED-SPACE_RECLAIMABLE)/
1010 (CASE NVL(SPACE_LIMIT,1) WHEN 0 THEN 1 ELSE SPACE_LIMIT END)*100)
1011 ||'|'|| round(SPACE_LIMIT/1024/1024)
1012 ||'|'|| round(SPACE_USED/1024/1024)
1013 ||'|'|| round(SPACE_RECLAIMABLE/1024/1024)
1014 ||'|'|| d.FLASHBACK_ON
1015 from V\$RECOVERY_FILE_DEST, v\$database d, v\$instance i;"
1016 fi
1017 }
1018
1019
1020 sql_undostat () {
1021 echo 'PROMPT <<<oracle_undostat:sep(124)>>>'
1022 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1023 echo "select decode(vp.con_id, null, upper(i.INSTANCE_NAME)
1024 ,upper(i.INSTANCE_NAME || '.' || vp.name))
1025 ||'|'|| ACTIVEBLKS
1026 ||'|'|| MAXCONCURRENCY
1027 ||'|'|| TUNED_UNDORETENTION
1028 ||'|'|| maxquerylen
1029 ||'|'|| NOSPACEERRCNT
1030 from v\$instance i
1031 join
1032 (select * from v\$undostat
1033 where TUNED_UNDORETENTION > 0
1034 order by end_time desc
1035 fetch next 1 rows only
1036 ) u on 1=1
1037 left outer join v\$pdbs vp on vp.con_id = u.con_id;
1038 "
1039
1040 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1041 echo "select upper(i.INSTANCE_NAME)
1042 ||'|'|| ACTIVEBLKS
1043 ||'|'|| MAXCONCURRENCY
1044 ||'|'|| TUNED_UNDORETENTION
1045 ||'|'|| maxquerylen
1046 ||'|'|| NOSPACEERRCNT
1047 from v\$instance i,
1048 (select * from (select *
1049 from v\$undostat order by end_time desc
1050 )
1051 where rownum = 1
1052 and TUNED_UNDORETENTION > 0
1053 );"
1054
1055 elif [ "$NUMERIC_ORACLE_VERSION" -ge 92 ]; then
1056 # TUNED_UNDORETENTION and ACTIVEBLKS are not availibe in Oracle <=9.2!
1057 # we sent a -1 for filtering in check_undostat
1058 echo "select upper(i.INSTANCE_NAME)
1059 ||'|-1'
1060 ||'|'|| MAXCONCURRENCY
1061 ||'|-1'
1062 ||'|'|| maxquerylen
1063 ||'|'|| NOSPACEERRCNT
1064 from v\$instance i,
1065 (select * from (select *
1066 from v\$undostat order by end_time desc
1067 )
1068 where rownum = 1
1069 );"
1070 fi
1071 }
1072
1073
1074 sql_resumable () {
1075 echo 'PROMPT <<<oracle_resumable:sep(124)>>>'
1076 echo "select upper(i.INSTANCE_NAME)
1077 ||'|'|| u.username
1078 ||'|'|| a.SESSION_ID
1079 ||'|'|| a.status
1080 ||'|'|| a.TIMEOUT
1081 ||'|'|| round((sysdate-to_date(a.SUSPEND_TIME,'mm/dd/yy hh24:mi:ss'))*24*60*60)
1082 ||'|'|| a.ERROR_NUMBER
1083 ||'|'|| to_char(to_date(a.SUSPEND_TIME, 'mm/dd/yy hh24:mi:ss'),'mm/dd/yy_hh24:mi:ss')
1084 ||'|'|| a.RESUME_TIME
1085 ||'|'|| a.ERROR_MSG
1086 from dba_resumable a, v\$instance i, dba_users u
1087 where a.INSTANCE_ID = i.INSTANCE_NUMBER
1088 and u.user_id = a.user_id
1089 and a.SUSPEND_TIME is not null
1090 union all
1091 select upper(i.INSTANCE_NAME)
1092 || '|||||||||'
1093 from v\$instance i;"
1094 }
1095
1096
1097 sql_jobs () {
1098 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1099
1100 echo 'PROMPT <<<oracle_jobs:sep(124)>>>'
1101 echo "SELECT upper(vp.name)
1102 ||'|'|| j.OWNER
1103 ||'|'|| j.JOB_NAME
1104 ||'|'|| j.STATE
1105 ||'|'|| ROUND((TRUNC(sysdate) + j.LAST_RUN_DURATION - TRUNC(sysdate)) * 86400)
1106 ||'|'|| j.RUN_COUNT
1107 ||'|'|| j.ENABLED
1108 ||'|'|| NVL(j.NEXT_RUN_DATE, to_date('1970-01-01', 'YYYY-mm-dd'))
1109 ||'|'|| NVL(j.SCHEDULE_NAME, '-')
1110 ||'|'|| jd.STATUS
1111 FROM cdb_scheduler_jobs j
1112 JOIN ( SELECT vp.con_id
1113 ,d.name || '|' || vp.name name
1114 FROM v\$containers vp
1115 JOIN v\$database d on 1=1
1116 WHERE d.cdb = 'YES' and vp.con_id <> 2
1117 UNION ALL
1118 SELECT 0, name
1119 FROM v\$database
1120 ) vp on j.con_id = vp.con_id
1121 left outer join (SELECT con_id, owner, job_name, max(LOG_ID) log_id
1122 FROM cdb_scheduler_job_run_details dd
1123 group by con_id, owner, job_name
1124 ) jm on jm.JOB_NAME = j.JOB_NAME
1125 and jm.owner=j.OWNER
1126 and jm.con_id = j.con_id
1127 left outer join cdb_scheduler_job_run_details jd
1128 on jd.con_id = jm.con_id
1129 AND jd.owner = jm.OWNER
1130 AND jd.JOB_NAME = jm.JOB_NAME
1131 AND jd.LOG_ID = jm.LOG_ID;
1132 "
1133
1134 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1135 echo 'PROMPT <<<oracle_jobs:sep(124)>>>'
1136 echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
1137 ||'|'|| j.OWNER
1138 ||'|'|| j.JOB_NAME
1139 ||'|'|| j.STATE
1140 ||'|'|| ROUND((TRUNC(sysdate) + j.LAST_RUN_DURATION - TRUNC(sysdate)) * 86400)
1141 ||'|'|| j.RUN_COUNT
1142 ||'|'|| j.ENABLED
1143 ||'|'|| NVL(j.NEXT_RUN_DATE, to_date('1970-01-01', 'YYYY-mm-dd'))
1144 ||'|'|| NVL(j.SCHEDULE_NAME, '-')
1145 ||'|'|| jd.STATUS
1146 FROM dba_scheduler_jobs j
1147 join v\$database vd on 1 = 1
1148 join v\$instance i on 1 = 1
1149 left outer join (SELECT owner, job_name, max(LOG_ID) log_id
1150 FROM dba_scheduler_job_run_details dd
1151 group by owner, job_name
1152 ) jm on jm.JOB_NAME = j.JOB_NAME
1153 and jm.owner=j.OWNER
1154 left outer join dba_scheduler_job_run_details jd
1155 on jd.owner = jm.OWNER
1156 AND jd.JOB_NAME = jm.JOB_NAME
1157 AND jd.LOG_ID = jm.LOG_ID
1158 WHERE j.auto_drop = 'FALSE';"
1159 fi
1160 }
1161
1162
1163 sql_ts_quotas () {
1164 echo 'PROMPT <<<oracle_ts_quotas:sep(124)>>>'
1165 echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
1166 ||'|'|| Q.USERNAME
1167 ||'|'|| Q.TABLESPACE_NAME
1168 ||'|'|| Q.BYTES
1169 ||'|'|| Q.MAX_BYTES
1170 from dba_ts_quotas Q, v\$database d, v\$instance i
1171 where max_bytes > 0
1172 union all
1173 select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
1174 ||'|||'
1175 from v\$database d, v\$instance i
1176 order by 1;"
1177 }
1178
1179
1180 sql_version () {
1181 echo 'PROMPT <<<oracle_version>>>'
1182 echo "select upper(i.INSTANCE_NAME)
1183 || ' ' || banner
1184 from v\$version, v\$instance i
1185 where banner like 'Oracle%';"
1186 }
1187
1188
1189 sql_instance () {
1190 echo 'prompt <<<oracle_instance:sep(124)>>>'
1191 if [ "${ORACLE_SID:0:1}" = '+' ]; then
1192 # ASM
1193 echo "select upper(i.instance_name)
1194 || '|' || i.VERSION
1195 || '|' || i.STATUS
1196 || '|' || i.LOGINS
1197 || '|' || i.ARCHIVER
1198 || '|' || round((sysdate - i.startup_time) * 24*60*60)
1199 || '|' || '0'
1200 || '|' || 'NO'
1201 || '|' || 'ASM'
1202 || '|' || 'NO'
1203 || '|' || i.instance_name
1204 from v\$instance i;"
1205
1206 elif [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1207 echo "select upper(instance_name)
1208 || '|' || version
1209 || '|' || status
1210 || '|' || logins
1211 || '|' || archiver
1212 || '|' || round((sysdate - startup_time) * 24*60*60)
1213 || '|' || dbid
1214 || '|' || log_mode
1215 || '|' || database_role
1216 || '|' || force_logging
1217 || '|' || name
1218 || '|' || to_char(created, 'ddmmyyyyhh24mi')
1219 || '|' || upper(value)
1220 || '|' || con_id
1221 || '|' || pname
1222 || '|' || pdbid
1223 || '|' || popen_mode
1224 || '|' || prestricted
1225 || '|' || ptotal_time
1226 || '|' || precovery_status
1227 || '|' || round(nvl(popen_time, -1))
1228 || '|' || pblock_size
1229 from(
1230 select i.instance_name, i.version, i.status, i.logins, i.archiver
1231 ,i.startup_time, d.dbid, d.log_mode, d.database_role, d.force_logging
1232 ,d.name, d.created, p.value, vp.con_id, vp.name pname
1233 ,vp.dbid pdbid, vp.open_mode popen_mode, vp.restricted prestricted, vp.total_size ptotal_time
1234 ,vp.block_size pblock_size, vp.recovery_status precovery_status
1235 ,(cast(systimestamp as date) - cast(open_time as date)) * 24*60*60 popen_time
1236 from v\$instance i
1237 join v\$database d on 1=1
1238 join v\$parameter p on 1=1
1239 join v\$pdbs vp on 1=1
1240 where p.name = 'enable_pluggable_database'
1241 union all
1242 select
1243 i.instance_name, i.version, i.status, i.logins, i.archiver
1244 ,i.startup_time, d.dbid, d.log_mode, d.database_role, d.force_logging
1245 ,d.name, d.created, p.value, 0 con_id, null pname
1246 ,0 pdbis, null popen_mode, null prestricted, null ptotal_time
1247 ,0 pblock_size, null precovery_status, null popen_time
1248 from v\$instance i
1249 join v\$database d on 1=1
1250 join v\$parameter p on 1=1
1251 where p.name = 'enable_pluggable_database'
1252 order by con_id
1253 );
1254 "
1255 else
1256 # normal Instance
1257 echo "select upper(i.instance_name)
1258 || '|' || i.VERSION
1259 || '|' || i.STATUS
1260 || '|' || i.LOGINS
1261 || '|' || i.ARCHIVER
1262 || '|' || round((sysdate - i.startup_time) * 24*60*60)
1263 || '|' || DBID
1264 || '|' || LOG_MODE
1265 || '|' || DATABASE_ROLE
1266 || '|' || FORCE_LOGGING
1267 || '|' || d.name
1268 || '|' || to_char(d.created, 'ddmmyyyyhh24mi')
1269 from v\$instance i, v\$database d;"
1270 fi
1271 }
1272
1273
1274 sql_sessions () {
1275 echo 'prompt <<<oracle_sessions:sep(124)>>>'
1276
1277 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1278 echo "SELECT upper(vp.name)
1279 || '|' || ltrim(COUNT(1))
1280 || decode(vp.con_id
1281 , 0, '|'||ltrim(rtrim(LIMIT_VALUE))||'|-1')
1282 FROM ( SELECT vp.con_id
1283 ,i.instance_name || '.' || vp.name name
1284 FROM v\$containers vp
1285 JOIN v\$instance i ON 1 = 1
1286 JOIN v\$database d on 1=1
1287 WHERE d.cdb = 'YES' and vp.con_id <> 2
1288 UNION ALL
1289 SELECT 0, instance_name
1290 FROM v\$instance
1291 ) vp
1292 JOIN v\$resource_limit rl on RESOURCE_NAME = 'sessions'
1293 LEFT OUTER JOIN v\$session vs ON vp.con_id = vs.con_id
1294 GROUP BY vp.name, vp.con_id, rl.LIMIT_VALUE
1295 ORDER BY 1;"
1296
1297 else
1298 echo "select upper(i.instance_name)
1299 || '|' || CURRENT_UTILIZATION
1300 || '|' || ltrim(LIMIT_VALUE)
1301 || '|' || MAX_UTILIZATION
1302 from v\$resource_limit, v\$instance i
1303 where RESOURCE_NAME = 'sessions';"
1304 fi
1305 }
1306
1307
1308 sql_processes () {
1309 echo 'prompt <<<oracle_processes:sep(124)>>>'
1310 echo "select upper(i.instance_name)
1311 || '|' || CURRENT_UTILIZATION
1312 || '|' || ltrim(rtrim(LIMIT_VALUE))
1313 from v\$resource_limit, v\$instance i
1314 where RESOURCE_NAME = 'processes';"
1315 }
1316
1317
1318 sql_logswitches () {
1319 echo 'prompt <<<oracle_logswitches:sep(124)>>>'
1320 echo "select upper(i.instance_name)
1321 || '|' || logswitches
1322 from v\$instance i ,
1323 (select count(1) logswitches
1324 from v\$loghist h , v\$instance i
1325 where h.first_time > sysdate - 1/24
1326 and h.thread# = i.instance_number
1327 );"
1328 }
1329
1330
1331 sql_locks () {
1332 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1333 echo 'prompt <<<oracle_locks:sep(124)>>>'
1334 echo "select upper(vp.name)
1335 || '|' || b.sid
1336 || '|' || b.serial#
1337 || '|' || b.machine
1338 || '|' || b.program
1339 || '|' || b.process
1340 || '|' || b.osuser
1341 || '|' || b.username
1342 || '|' || b.SECONDS_IN_WAIT
1343 || '|' || b.BLOCKING_SESSION_STATUS
1344 || '|' || bs.inst_id
1345 || '|' || bs.sid
1346 || '|' || bs.serial#
1347 || '|' || bs.machine
1348 || '|' || bs.program
1349 || '|' || bs.process
1350 || '|' || bs.osuser
1351 || '|' || bs.username
1352 from v\$session b
1353 join gv\$session bs on bs.inst_id = b.BLOCKING_INSTANCE
1354 and bs.sid = b.BLOCKING_SESSION
1355 and bs.con_id = b.con_id
1356 join ( SELECT vp.con_id
1357 ,i.instance_name || '.' || vp.name name
1358 FROM v\$containers vp
1359 JOIN v\$instance i ON 1 = 1
1360 JOIN v\$database d on 1=1
1361 WHERE d.cdb = 'YES' and vp.con_id <> 2
1362 UNION ALL
1363 SELECT 0, instance_name
1364 FROM v\$instance
1365 ) vp on b.con_id = vp.con_id
1366 where b.BLOCKING_SESSION is not null;
1367
1368 SELECT upper(i.instance_name || '.' || vp.name)
1369 || '|||||||||||||||||'
1370 FROM v\$containers vp
1371 JOIN v\$instance i ON 1 = 1
1372 JOIN v\$database d on 1=1
1373 WHERE d.cdb = 'YES' and vp.con_id <> 2
1374 UNION ALL
1375 SELECT upper(i.instance_name)
1376 || '|||||||||||||||||'
1377 FROM v\$instance i;
1378 "
1379
1380 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1381 echo 'prompt <<<oracle_locks:sep(124)>>>'
1382 echo "select upper(i.instance_name)
1383 || '|' || b.sid
1384 || '|' || b.serial#
1385 || '|' || b.machine
1386 || '|' || b.program
1387 || '|' || b.process
1388 || '|' || b.osuser
1389 || '|' || b.username
1390 || '|' || b.SECONDS_IN_WAIT
1391 || '|' || b.BLOCKING_SESSION_STATUS
1392 || '|' || bs.inst_id
1393 || '|' || bs.sid
1394 || '|' || bs.serial#
1395 || '|' || bs.machine
1396 || '|' || bs.program
1397 || '|' || bs.process
1398 || '|' || bs.osuser
1399 || '|' || bs.username
1400 from v\$session b
1401 join v\$instance i on 1=1
1402 join gv\$session bs on bs.inst_id = b.BLOCKING_INSTANCE
1403 and bs.sid = b.BLOCKING_SESSION
1404 where b.BLOCKING_SESSION is not null;
1405 select upper(i.instance_name)
1406 || '|||||||||||||||||'
1407 from v\$instance i;"
1408 fi
1409 }
1410
1411
1412 sql_locks_old () {
1413 if [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
1414 echo 'prompt <<<oracle_locks:sep(124)>>>'
1415 echo "SET SERVEROUTPUT ON feedback off
1416 DECLARE
1417 type x is table of varchar2(20000) index by pls_integer;
1418 xx x;
1419 begin
1420 begin
1421 execute immediate 'select upper(i.instance_name)
1422 || ''|'' || a.sid
1423 || ''|'' || b.serial#
1424 || ''|'' || b.machine
1425 || ''|'' || b.program
1426 || ''|'' || b.process
1427 || ''|'' || b.osuser
1428 || ''|'' || a.ctime
1429 || ''|'' || decode(c.owner,NULL,''NULL'',c.owner)
1430 || ''|'' || decode(c.object_name,NULL,''NULL'',c.object_name)
1431 from V\$LOCK a, v\$session b, dba_objects c, v\$instance i
1432 where (a.id1, a.id2, a.type)
1433 IN (SELECT id1, id2, type
1434 FROM GV\$LOCK
1435 WHERE request>0
1436 )
1437 and request=0
1438 and a.sid = b.sid
1439 and a.id1 = c.object_id (+)
1440 union all
1441 select upper(i.instance_name) || ''|||||||||''
1442 from v\$instance i'
1443 bulk collect into xx;
1444 if xx.count >= 1 then
1445 for i in 1 .. xx.count loop
1446 dbms_output.put_line(xx(i));
1447 end loop;
1448 end if;
1449 exception
1450 when others then
1451 for cur1 in (select upper(i.instance_name) instance_name from v\$instance i) loop
1452 dbms_output.put_line(cur1.instance_name || '|||||||||'||sqlerrm);
1453 end loop;
1454 end;
1455 END;
1456 /
1457 set serverout off"
1458 fi
1459 }
1460
1461
1462 sql_longactivesessions () {
1463 if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
1464
1465 echo 'prompt <<<oracle_longactivesessions:sep(124)>>>'
1466 echo "select upper(vp.name)
1467 || '|' || s.sid
1468 || '|' || s.serial#
1469 || '|' || s.machine
1470 || '|' || s.process
1471 || '|' || s.osuser
1472 || '|' || s.program
1473 || '|' || s.last_call_et
1474 || '|' || s.sql_id
1475 from v\$session s
1476 join ( SELECT vp.con_id
1477 ,i.instance_name || '.' || vp.name name
1478 FROM v\$containers vp
1479 JOIN v\$instance i ON 1 = 1
1480 JOIN v\$database d on 1=1
1481 WHERE d.cdb = 'YES' and vp.con_id <> 2
1482 UNION ALL
1483 SELECT 0, instance_name
1484 FROM v\$instance
1485 ) vp on 1=1
1486 where s.status = 'ACTIVE'
1487 and s.type != 'BACKGROUND'
1488 and s.username is not null
1489 and s.username not in('PUBLIC')
1490 and s.last_call_et > 60*60;
1491
1492 SELECT upper(i.instance_name || '.' || vp.name)
1493 || '||||||||'
1494 FROM v\$containers vp
1495 JOIN v\$instance i ON 1 = 1
1496 JOIN v\$database d on 1=1
1497 WHERE d.cdb = 'YES' and vp.con_id <> 2
1498 UNION ALL
1499 SELECT upper(i.instance_name)
1500 || '||||||||'
1501 FROM v\$instance i;
1502 "
1503
1504 elif [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
1505 echo 'prompt <<<oracle_longactivesessions:sep(124)>>>'
1506 echo "select upper(i.instance_name)
1507 || '|' || s.sid
1508 || '|' || s.serial#
1509 || '|' || s.machine
1510 || '|' || s.process
1511 || '|' || s.osuser
1512 || '|' || s.program
1513 || '|' || s.last_call_et
1514 || '|' || s.sql_id
1515 from v\$session s, v\$instance i
1516 where s.status = 'ACTIVE'
1517 and type != 'BACKGROUND'
1518 and s.username is not null
1519 and s.username not in('PUBLIC')
1520 and s.last_call_et > 60*60
1521 union all
1522 select upper(i.instance_name)
1523 || '||||||||'
1524 from v\$instance i;"
1525 fi
1526 }
1527
1528
1529 sql_asm_diskgroup () {
1530 echo 'prompt <<<oracle_asm_diskgroup:sep(124)>>>'
1531 if [ "$NUMERIC_ORACLE_VERSION" -ge 112 ]; then
1532
1533 echo "SELECT g.state
1534 || '|' || g.type
1535 || '|' || g.name
1536 || '|' || g.BLOCK_SIZE
1537 || '|' || g.ALLOCATION_UNIT_SIZE
1538 || '|' || g.REQUIRED_MIRROR_FREE_MB
1539 || '|' || sum(d.total_mb)
1540 || '|' || sum(d.free_mb)
1541 || '|' || d.failgroup
1542 || '|' || d.VOTING_FILE
1543 || '|' || d.FAILGROUP_TYPE
1544 || '|' || g.offline_disks
1545 || '|' || min(decode(d.REPAIR_TIMER, 0, 8640000, d.REPAIR_TIMER))
1546 || '|' || count(*)
1547 FROM v\$asm_diskgroup g
1548 LEFT OUTER JOIN v\$asm_disk d on d.group_number = g.group_number
1549 and d.group_number = g.group_number
1550 and d.group_number <> 0
1551 GROUP BY g.name
1552 , g.state
1553 , g.type
1554 , d.failgroup
1555 , d.VOTING_FILE
1556 , g.BLOCK_SIZE
1557 , g.ALLOCATION_UNIT_SIZE
1558 , g.REQUIRED_MIRROR_FREE_MB
1559 , g.offline_disks
1560 , d.FAILGROUP_TYPE
1561 , d.REPAIR_TIMER
1562 ORDER BY g.name, d.failgroup;"
1563
1564 elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
1565 echo "select STATE
1566 || '|' || TYPE
1567 || '|' || 'N'
1568 || '|' || sector_size
1569 || '|' || block_size
1570 || '|' || allocation_unit_size
1571 || '|' || total_mb
1572 || '|' || free_mb
1573 || '|' || required_mirror_free_mb
1574 || '|' || usable_file_mb
1575 || '|' || offline_disks
1576 || '|' || 'N'
1577 || '|' || name || '/'
1578 from v\$asm_diskgroup;"
1579 fi
1580 }
1581
1582 #.
1583 # .--helper--------------------------------------------------------------.
1584 # | _ _ |
1585 # | | |__ ___| |_ __ ___ _ __ |
1586 # | | '_ \ / _ \ | '_ \ / _ \ '__| |
1587 # | | | | | __/ | |_) | __/ | |
1588 # | |_| |_|\___|_| .__/ \___|_| |
1589 # | |_| |
1590 # '----------------------------------------------------------------------'
1591
1592 do_dummy_sections () {
1593 if [ "$MK_ORA_LOGGING" != "1" ]; then
1594 for section in $SYNC_SECTIONS $ASYNC_SECTIONS $SYNC_ASM_SECTIONS $ASYNC_ASM_SECTIONS; do
1595 echo "<<<oracle_${section}>>>"
1596 done
1597 fi
1598 }
1599
1600
1601 do_section () {
1602 local section=$1
1603 if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
1604 for mk_ora_section in "${MK_ORA_SECTIONS[@]}"; do
1605 if [ "$mk_ora_section" = "${section}" ]; then
1606 echo "yes"
1607 return
1608 fi
1609 done
1610 echo "no"
1611 else
1612 echo "yes"
1613 fi
1614 }
1615
1616
1617 skip_sid () {
1618 local sid="$1"
1619 if [ "$ONLY_SIDS" ]; then
1620 if echo "$ONLY_SIDS" | "${GREP}" -q "$sid"; then
1621 echo "no"
1622 return
1623 fi
1624 echo "yes"
1625 return
1626 fi
1627
1628 if [ "$SKIP_SIDS" ]; then
1629 if echo "$SKIP_SIDS" | "${GREP}" -q "$sid"; then
1630 echo "yes"
1631 return
1632 fi
1633 echo "no"
1634 return
1635 fi
1636
1637 EXCLUDE=EXCLUDE_$sid
1638 # Handle explicit exclusion of instances but not for +ASM
1639 if [[ "$EXCLUDE" =~ ^[a-zA-Z][a-zA-Z0-9_]*$ ]]; then
1640 EXCLUDE=${!EXCLUDE}
1641 if [ "$EXCLUDE" = "ALL" ]; then
1642 echo "yes"
1643 return
1644 fi
1645 echo "no"
1646 return
1647 fi
1648 echo "no"
1649 }
1650
1651
1652 remove_excluded_sections () {
1653 # We exclude instance section because we have already executed
1654 # the sql_instance section at the beginning for every SID.
1655 local sections="$1"
1656 local excluded="$2"
1657 local result=
1658 local skip=
1659 for section in $sections; do
1660 skip=
1661 for exclude in $excluded; do
1662 if [ "$exclude" = "$section" ]; then
1663 skip=yes
1664 break
1665 fi
1666 done
1667 if [ "$skip" != yes ]; then
1668 result=${result:+"$result "}"${section}"
1669 fi
1670 done
1671 echo "$result"
1672 }
1673
1674
1675 ora_session_environment () {
1676 echo 'set pages 0 trimspool on feedback off lines 8000'
1677 if [ "$NUMERIC_ORACLE_VERSION" -ge 102 ] && [ ! "$DISABLE_ORA_SESSION_SETTINGS" ]; then
1678 echo 'set echo off'
1679 echo 'alter session set "_optimizer_mjc_enabled"=false;'
1680
1681 # cursor_sharing is not valid for ASM instances
1682 if [ ! "${ORACLE_SID:0:1}" = '+' ]; then
1683 echo 'alter session set cursor_sharing=exact;'
1684 fi
1685
1686 echo 'set echo on'
1687 fi
1688 #TODO Do not exit after first error otherwise
1689 # section specific errors won't be seen any more.
1690 #echo 'whenever sqlerror exit 1'
1691 echo ' '
1692 }
1693
1694 #.
1695 # .--run cached----------------------------------------------------------.
1696 # | _ _ |
1697 # | _ __ _ _ _ __ ___ __ _ ___| |__ ___ __| | |
1698 # | | '__| | | | '_ \ / __/ _` |/ __| '_ \ / _ \/ _` | |
1699 # | | | | |_| | | | | | (_| (_| | (__| | | | __/ (_| | |
1700 # | |_| \__,_|_| |_| \___\__,_|\___|_| |_|\___|\__,_| |
1701 # | |
1702 # '----------------------------------------------------------------------'
1703
1704 run_cached () {
1705 if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
1706 return
1707 fi
1708
1709 local maxage="$1"
1710 local cmd_name="$2"
1711
1712 local cache_file="$MK_VARDIR/cache/oracle_${MK_SID}.cache"
1713 local use_cache_file=
1714 local now=
1715 local cf_atime=
1716 local mtime=
1717
1718 logging "[${MK_SID}] [run_cached]" "Cache file: $cache_file"\
1719 "Cache max age: $maxage" "Command name: $cmd_name"
1720
1721 if [ ! -d "$MK_VARDIR/cache" ]; then
1722 mkdir -p "$MK_VARDIR/cache"
1723 fi
1724
1725 # Check if the creation of the cache takes suspiciously long and return
1726 # nothing if the age (access time) of $cache_file.new is twice the 'maxage'
1727 # perl is needed for Solaris => no date +%s availible
1728 now=$(perl -le "print time()")
1729 if [ -e "${cache_file}.new" ]; then
1730 cf_atime=$(eval "$STATCX" "${cache_file}.new")
1731 if [ $((now - cf_atime)) -ge $((maxage * 2)) ]; then
1732 # Kill the process still accessing that file in case
1733 # it is still running. This avoids overlapping processes!
1734 fuser -k -9 "${cache_file}.new" >/dev/null 2>&1
1735 rm -f "${cache_file}.new"
1736 logging "[${MK_SID}] [run_cached]" "Creation of the cache takes suspiciously long"
1737 return
1738 fi
1739 fi
1740
1741 # Check if cache file exists and is recent enough
1742 if [ -s "$cache_file" ]; then
1743 mtime=$(eval "$STATCY" "$cache_file")
1744 if [ $((now - mtime)) -le "$maxage" ]; then
1745 use_cache_file=1
1746 fi
1747 # Output the file in any case, even if it is
1748 # outdated. The new file will not yet be available
1749 logging "[${MK_SID}] [run_cached]" "Cache file exists and is recent enough"
1750 cat "$cache_file"
1751 fi
1752
1753 # Cache file outdated and new job not yet running? Start it
1754 if [ -z "$use_cache_file" ] && [ ! -e "${cache_file}.new" ]; then
1755 logging "[${MK_SID}] [run_cached]" "Cache file outdated, start it."
1756 if [ "$MK_ORA_DEBUG" ]; then
1757 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
1758 else
1759 # When the command fails, the output is throws away ignored
1760 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 &
1761 fi
1762 fi
1763 }
1764
1765 #.
1766 # .--DB connect----------------------------------------------------------.
1767 # | ____ ____ _ |
1768 # | | _ \| __ ) ___ ___ _ __ _ __ ___ ___| |_ |
1769 # | | | | | _ \ / __/ _ \| '_ \| '_ \ / _ \/ __| __| |
1770 # | | |_| | |_) | | (_| (_) | | | | | | | __/ (__| |_ |
1771 # | |____/|____/ \___\___/|_| |_|_| |_|\___|\___|\__| |
1772 # | |
1773 # '----------------------------------------------------------------------'
1774
1775 mk_ora_db_connect () {
1776 local sid="$1"
1777
1778 ORADBUSER=""
1779 DBPASSWORD=""
1780
1781 if [[ "$sid" =~ ^REMOTE_INSTANCE_.* ]]; then
1782 # working on REMOTE_-Mode!
1783 ORACFGLINE=$(eval "echo \${$1}")
1784 ORACLE_SID=$(echo "${ORACFGLINE}" | cut -d":" -f7)
1785 TNSALIAS=$(echo "${REMOTE_VARNAME}" | cut -d"_" -f3-)
1786
1787 else
1788 # working with locally running instances
1789 # mk_oracle_dbusers.conf is for compatibility. Do not use it anymore
1790 ORACLE_USERCONF=${MK_CONFDIR}/mk_oracle_dbuser.conf
1791 TNSALIAS=${ORACLE_SID}
1792
1793 # ASM use '+' as 1st character in SID!
1794 if [ "${ORACLE_SID:0:1}" = '+' ]; then
1795 ORACFGLINE=${ASMUSER}
1796
1797 else
1798 # use an individuel user or the default DBUSER from mk_oracle.cfg
1799 dummy="DBUSER_${ORACLE_SID}"
1800 ORACFGLINE=${!dummy}
1801 if [ "$ORACFGLINE" = '' ]; then
1802 ORACFGLINE=${DBUSER}
1803 fi
1804 fi
1805
1806 if [ -f "${ORACLE_USERCONF}" ] && [ "${ORACFGLINE}" = '' ]; then
1807 # mk_oracle_dbuser.conf
1808 ORACFGLINE=$("${GREP}" "^${ORACLE_SID}:" < "${ORACLE_USERCONF}")
1809 # mk_oracle_dbuser has ORACLE_SID as 1. parameter. we need an offset for all values
1810 offset=1
1811 fi
1812 fi
1813
1814 offset=${offset:-0}
1815 ORADBUSER=$(echo "${ORACFGLINE}" | cut -d":" -f$((1+offset)))
1816 DBPASSWORD=$(echo "${ORACFGLINE}" | cut -d":" -f$((2+offset)))
1817 DBSYSCONNECT=$(echo "${ORACFGLINE}" | cut -d":" -f$((3+offset)))
1818 DBHOST=$(echo "${ORACFGLINE}" | cut -d":" -f$((4+offset)))
1819 DBPORT=$(echo "${ORACFGLINE}" | cut -d":" -f$((5+offset)))
1820
1821 TNSPINGOK=no
1822 if [ -f "${TNS_ADMIN}/tnsnames.ora" ]; then
1823 if [ "${ORADBUSER:0:2}" = '/@' ]; then
1824 P_TNSALIAS_P=$(eval echo "${ORADBUSER:2}")
1825 else
1826 P_TNSALIAS_P=$ORACLE_SID
1827 fi
1828
1829 if [ -n "$P_TNSALIAS_P" ]; then
1830 PREFIX_SID="PREFIX_$sid"
1831 PREFIX_SID=${!PREFIX_SID}
1832 if [ -n "$PREFIX_SID" ]; then
1833 P_TNSALIAS_P="$PREFIX_SID$P_TNSALIAS_P"
1834 elif [ -n "$PREFIX" ]; then
1835 P_TNSALIAS_P="$PREFIX$P_TNSALIAS_P"
1836 else
1837 P_TNSALIAS_P="$P_TNSALIAS_P"
1838 fi
1839
1840 POSTFIX_SID="POSTFIX_$sid"
1841 POSTFIX_SID=${!POSTFIX_SID}
1842 if [ -n "$POSTFIX_SID" ]; then
1843 P_TNSALIAS_P="$P_TNSALIAS_P$POSTFIX_SID"
1844 elif [ -n "$POSTFIX" ]; then
1845 P_TNSALIAS_P="$P_TNSALIAS_P$POSTFIX"
1846 else
1847 P_TNSALIAS_P="$P_TNSALIAS_P"
1848 fi
1849 fi
1850
1851 if "${ORACLE_HOME}"/bin/tnsping "${P_TNSALIAS_P}" >/dev/null 2>&1; then
1852 TNSALIAS="$P_TNSALIAS_P"
1853 TNSPINGOK=yes
1854 else
1855 unset TNSALIAS
1856 fi
1857 else
1858 unset TNSALIAS
1859 fi
1860
1861 logging "[${sid}] [mk_ora_db_connect]" "ORA DB user: $ORADBUSER"\
1862 "DB sys connect: $DBSYSCONNECT" "DB host: $DBHOST" "DB port: $DBPORT"\
1863 "TNS alias: $TNSALIAS" "TNS PING: ${TNSPINGOK}"
1864
1865 if [ ! "${ORACFGLINE}" ]; then
1866 # no configuration found
1867 # => use the wallet with tnsnames.ora or EZCONNECT
1868 TNSALIAS=${TNSALIAS:-"localhost:1521/${ORACLE_SID}"}
1869 else
1870 if [ "${DBSYSCONNECT}" ]; then
1871 assysdbaconnect=" as "${DBSYSCONNECT}
1872 fi
1873
1874 TNSALIAS=${TNSALIAS:-"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${DBHOST:-"localhost"})(PORT=${DBPORT:-1521}))(CONNECT_DATA=(SID=${ORACLE_SID})(SERVER=DEDICATED)(UR=A)))"}
1875
1876 # ORADBUSER = '/'? => ignore DBPASSWORD and use the wallet
1877 if [ "${ORADBUSER:0:1}" = '/' ]; then
1878 # connect with / and wallet
1879 ORADBUSER=""
1880 DBPASSWORD=""
1881 if [ "$TNSPINGOK" = 'no' ]; then
1882 # create an EZCONNECT string when no tnsnames.ora is usable
1883 # defaults to localhost:1521/<ORACLE_SID>
1884 TNSALIAS="${DBHOST:-"localhost"}:${DBPORT:-1521}/${ORACLE_SID}"
1885 fi
1886 fi
1887 fi
1888
1889 logging "[${sid}] [mk_ora_db_connect]" "DB connection: ${ORADBUSER}/${DBPASSWORD//?/*}@${TNSALIAS}${assysdbaconnect}"
1890 echo "${ORADBUSER}/${DBPASSWORD}@${TNSALIAS}${assysdbaconnect}"
1891
1892 }
1893
1894 #.
1895 # .--mk ora sqlplus------------------------------------------------------.
1896 # | _ _ _ |
1897 # | _ __ ___ | | __ ___ _ __ __ _ ___ __ _| |_ __ | |_ _ ___ |
1898 # | | '_ ` _ \| |/ / / _ \| '__/ _` | / __|/ _` | | '_ \| | | | / __| |
1899 # | | | | | | | < | (_) | | | (_| | \__ \ (_| | | |_) | | |_| \__ \ |
1900 # | |_| |_| |_|_|\_\ \___/|_| \__,_| |___/\__, |_| .__/|_|\__,_|___/ |
1901 # | |_| |_| |
1902 # '----------------------------------------------------------------------'
1903
1904 mk_ora_sqlplus () {
1905 local from_where="$1"
1906 local start_time=
1907 local elapsed_time=
1908 local loc_stdin=
1909 local output=
1910
1911 logging "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "Piggyback host: $MK_PIGGYBACK_HOST"
1912
1913 start_time="$(perl -MTime::HiRes=time -wle 'print time')"
1914 loc_stdin=$(cat)
1915
1916 SQLPLUS=${ORACLE_HOME}/bin/sqlplus
1917 if [ ! -x "${SQLPLUS}" ]; then
1918 logging -w "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "SQLplus '${SQLPLUS}' not found or ORACLE_HOME '${ORACLE_HOME}' wrong."
1919 echo "sqlplus not found or ORACLE_HOME wrong!"
1920 echo "SQLPLUS=${SQLPLUS}"
1921 return 1
1922 fi
1923
1924 if [ -n "$MK_PIGGYBACK_HOST" ]; then
1925 echo "<<<<${MK_PIGGYBACK_HOST}>>>>"
1926 fi
1927
1928 if output=$(echo "$(ora_session_environment)${loc_stdin}" | "$SQLPLUS" -L -s "$MK_DB_CONNECT"); then
1929 echo -e "$output"
1930
1931 else
1932 logging -w "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "OUTPUT '$output'"
1933 output=$(echo -e "$output" | "${GREP}" -v "^ERROR at line" | tr '\n' ' ' |\
1934 sed "s/^/$(echo "$MK_SID" | tr '[:lower:]' '[:upper:]')|FAILURE|/" ; echo)
1935 if [ "$MK_ORA_DEBUG_CONNECT" ]; then
1936 echo "Logindetails: ${DBCONNECT}" >&2
1937 echo -e "$output"
1938 else
1939 echo '<<<oracle_instance:sep(124)>>>'
1940 echo -e "$output"
1941 echo
1942 fi
1943 fi
1944
1945 elapsed_time=$(bc <<<"$(perl -MTime::HiRes=time -wle 'print time')-$start_time")
1946
1947 if [ -n "$MK_PIGGYBACK_HOST" ]; then
1948 echo "<<<<>>>>"
1949 fi
1950
1951 logging "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "Elapsed time: $elapsed_time"
1952 }
1953
1954 #.
1955 # .--do checks-----------------------------------------------------------.
1956 # | _ _ _ |
1957 # | __| | ___ ___| |__ ___ ___| | _____ |
1958 # | / _` |/ _ \ / __| '_ \ / _ \/ __| |/ / __| |
1959 # | | (_| | (_) | | (__| | | | __/ (__| <\__ \ |
1960 # | \__,_|\___/ \___|_| |_|\___|\___|_|\_\___/ |
1961 # | |
1962 # '----------------------------------------------------------------------'
1963
1964 # Create one SQL statements for several sections and run
1965 # these with sqlplus. The exitcode is preserved.
1966 do_sync_checks () {
1967 logging "[${MK_SID}] [do_sync_checks]" "Query: $MK_SYNC_SECTIONS_QUERY"
1968 echo "$MK_SYNC_SECTIONS_QUERY" | mk_ora_sqlplus "do_sync_checks"
1969 }
1970
1971
1972 do_async_checks () {
1973 logging "[${MK_SID}] [do_async_checks]" "Query: $MK_ASYNC_SECTIONS_QUERY"
1974 echo "$MK_ASYNC_SECTIONS_QUERY" | mk_ora_sqlplus "do_async_checks"
1975 }
1976
1977
1978 do_testmode () {
1979 local sections=$1
1980 local asections=$2
1981
1982 echo
1983 echo "---login----------------------------------------------------------------"
1984 echo " Operating System: ${OS_TYPE}"
1985 echo " ORACLE_HOME ${ORA_HOME_SOURCE} ${ORACLE_HOME}"
1986 echo " Logincheck to Instance: ${MK_SID}"
1987 echo " Version: ${ORACLE_VERSION}"
1988 echo "select ' Login ok User: ' || user || ' on ' || host_name ||' Instance ' || instance_name
1989 from v\$instance;" | mk_ora_sqlplus "do_testmode"
1990 echo " SYNC_SECTIONS: $sections"
1991 echo " ASYNC_SECTIONS: $asections"
1992 if [ "$IGNORE_DB_NAME" ]; then
1993 echo " IGNORE_DB_NAME found. Ignoring DB_NAME in all SQLs!"
1994 fi
1995
1996 if [ "$DISABLE_ORA_SESSION_SETTINGS" ]; then
1997 echo " Paramter DISABLE_ORA_SESSION_SETTINGS found!"
1998 fi
1999
2000 if [ "$HINT_RMAN" ]; then
2001 echo " Using HINT_RMAN for this Instance!"
2002 fi
2003 echo "------------------------------------------------------------------------"
2004 echo
2005 }
2006
2007
2008 do_checks () {
2009 local excluded=
2010 local sections=
2011 local asections=
2012
2013 if [ "${ORACLE_SID:0:1}" = '+' ]; then
2014 # ASM sections
2015 local do_async_sections=${ASYNC_ASM_SECTIONS}
2016 local do_sync_sections=${SYNC_ASM_SECTIONS}
2017 else
2018 local do_sync_sections=${SYNC_SECTIONS}
2019 local do_async_sections=${ASYNC_SECTIONS}
2020 fi
2021
2022 if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
2023 sections=$(
2024 for section in $do_sync_sections $do_async_sections; do
2025 if [ "$(do_section "$section")" = "yes" ]; then
2026 sections=${sections:+"$sections "}"${section}"
2027 fi
2028 done; echo "$sections")
2029 logging "[${MK_SID}] [do_checks]" "Do single sections: $sections"
2030
2031 MK_SYNC_SECTIONS_QUERY=$(for section in $sections; do eval "sql_$section" ; done)
2032 MK_ASYNC_SECTIONS_QUERY=
2033
2034 else
2035 excluded=$(eval "echo \$EXCLUDE_$MK_SID")
2036 sections=$(remove_excluded_sections "$do_sync_sections" "$excluded")
2037 asections=$(remove_excluded_sections "$do_async_sections" "$excluded")
2038 logging "[${MK_SID}] [do_checks]" "Excluded: $excluded"\
2039 "Sections: $sections" "Async sections: $asections"
2040
2041 MK_SYNC_SECTIONS_QUERY=$(for section in $sections; do eval "sql_$section" ; done)
2042 MK_ASYNC_SECTIONS_QUERY=$(for section in $asections; do eval "sql_$section" ; done)
2043
2044 fi
2045 export MK_ASYNC_SECTIONS_QUERY
2046
2047 if [ "$MK_ORA_LOGGING" = "1" ]; then
2048 logging "[${MK_SID}] [do_checks]" "Testmode: $(echo \
2049 "select user || ' on ' || host_name || ' instance ' || instance_name from v\$instance;" | mk_ora_sqlplus "do_checks")"
2050 fi
2051
2052 if [ "$MK_ORA_DEBUG_CONNECT" = "1" ]; then
2053 do_testmode "$sections" "$asections" 2>&1
2054 else
2055 do_sync_checks
2056 run_cached "$CACHE_MAXAGE" do_async_checks
2057 fi
2058 }
2059
2060 #.
2061 # .--main----------------------------------------------------------------.
2062 # | _ |
2063 # | _ __ ___ __ _(_)_ __ |
2064 # | | '_ ` _ \ / _` | | '_ \ |
2065 # | | | | | | | (_| | | | | | |
2066 # | |_| |_| |_|\__,_|_|_| |_| |
2067 # | |
2068 # +----------------------------------------------------------------------+
2069 # | Iterate over all instances and execute sync and async sections. |
2070 # '----------------------------------------------------------------------'
2071
2072 # Make sure that the new shell that is being run by run_cached inherits
2073 # our functions
2074 export -f logging
2075 export -f mk_ora_sqlplus
2076 export -f ora_session_environment
2077 export -f do_async_checks
2078 export -f set_ora_env
2079
2080 # ---preliminaries--------------------------------------------------------
2081
2082 logging "--------------------------------------------------------------------"
2083 if [ "$MK_ORA_LOGGING" == "1" ]; then
2084 echo "Start logging to file: $MK_VARDIR/log/mk_oracle.log" >&2
2085 fi
2086
2087 set_os_env
2088 logging "[preliminaries]" "OS: ${OS_TYPE}"\
2089 "GREP: ${GREP}" "AWK: ${AWK}" "STATCX: ${STATCX}" "STATCY: ${STATCY}"
2090
2091
2092 logging "[preliminaries]" "SYNC_SECTIONS: ${SYNC_SECTIONS}"\
2093 "ASYNC_SECTIONS: ${ASYNC_SECTIONS}" "SYNC_ASM_SECTIONS: ${SYNC_ASM_SECTIONS}"\
2094 "ASYNC_ASM_SECTIONS: ${ASYNC_ASM_SECTIONS}" "CACHE_MAXAGE: ${CACHE_MAXAGE}"\
2095 "ONLY_SIDS: ${ONLY_SIDS}" "SKIP_SIDS: ${SKIP_SIDS}"
2096
2097
2098 # Get list of all running databases. Do not work on ASM in this plugin.
2099 # => Ignore a running ASM-Instance!
2100 # shellcheck disable=SC2016
2101 SIDS=$(UNIX95=true ps -ef | "${AWK}" '{print $NF}' | "${GREP}" -E '^asm_pmon_|^ora_pmon_|^xe_pmon_XE' | cut -d"_" -f3-)
2102
2103
2104 # Are there any remote configurations?
2105 for remote_instance in $(compgen -A variable | "${GREP}" -E "^REMOTE_INSTANCE_.*"); do
2106 REMOTE_INSTANCES="${REMOTE_INSTANCES} ${remote_instance}"
2107 # shellcheck disable=SC2005
2108 PIGGYBACK_HOSTS="$(echo "$(eval "echo \${$remote_instance}")" | cut -d":" -f6) ${PIGGYBACK_HOSTS}"
2109 done
2110
2111
2112 logging "[preliminaries]" "SIDs: ${SIDS//\\n/ }"\
2113 "Remote instances: ${REMOTE_INSTANCES}"\
2114 "Piggyback hosts: ${PIGGYBACK_HOSTS}"
2115
2116
2117 if [ "$PIGGYBACK_HOSTS" ]; then
2118 PIGGYBACK_HOSTS=$(echo "$PIGGYBACK_HOSTS" | tr ' ' '\n' | sort | uniq)
2119 logging "[preliminaries]" "Removed duplicate piggyback hosts"\
2120 "Remaining piggyback hosts: ${PIGGYBACK_HOSTS}"
2121
2122 if [ ! -e "$MK_VARDIR/mk_oracle.found" ]; then
2123 touch "$MK_VARDIR/mk_oracle.found"
2124 fi
2125 fi
2126
2127
2128 # If we do not have found any running database instance, then either
2129 # no ORACLE is present on this system or it's just currently not running.
2130 # In the later case we ouput empty agent sections so that Check_MK will be
2131 # happy and execute the actual check functions.
2132 if [ -z "$SIDS" ] && [ ! -e "$MK_VARDIR/mk_oracle.found" ]; then
2133 logging -c "[preliminaries]" "No SIDs and \$MK_VARDIR/mk_oracle.found not found"
2134 echo "No SIDs and \$MK_VARDIR/mk_oracle.found not found" >&2
2135 exit 1
2136 fi
2137
2138
2139 # From now on we expect databases on this system (for ever)
2140 touch "$MK_VARDIR/mk_oracle.found"
2141
2142
2143 # Make sure that always all sections are present, even
2144 # in case of an error. Note: the section <<<oracle_instance>>>
2145 # section shows the general state of a database instance. If
2146 # that section fails for an instance then all other sections
2147 # do not contain valid data anyway.
2148 do_dummy_sections
2149
2150
2151 if [ "$PIGGYBACK_HOSTS" ]; then
2152 for piggyback_host in $PIGGYBACK_HOSTS; do
2153 echo "<<<<${piggyback_host}>>>>"
2154 do_dummy_sections
2155 echo "<<<<>>>>"
2156 done
2157 fi
2158
2159 # ---local----------------------------------------------------------------
2160
2161 for sid in $SIDS; do
2162 skip=$(skip_sid "$sid")
2163 logging "[${sid}] [local]" "Skipping: $skip"
2164 if [ "$skip" == "yes" ]; then
2165 continue
2166 fi
2167
2168 set_ora_env "$sid"
2169 if [ $? -eq 2 ] ; then
2170 # we have to skip this SID due to missing/unknown ORACLE_HOME
2171 continue
2172 fi
2173 set_ora_version "$("${ORACLE_HOME}"/bin/sqlplus -V | "${GREP}" ^SQL | cut -d" " -f3 | cut -d"." -f-2)"
2174 logging "[${sid}] [local]" "ORACLE_HOME: ${ORACLE_HOME}" "TNS_ADMIN: ${TNS_ADMIN}"\
2175 "ORACLE_SID: ${ORACLE_SID}" "LD_LIBRARY_PATH: ${LD_LIBRARY_PATH}"\
2176 "ORACLE_VERSION: ${ORACLE_VERSION} (${NUMERIC_ORACLE_VERSION})"
2177
2178
2179 db_connect=$(mk_ora_db_connect "$sid")
2180 export MK_SID=$sid
2181 export MK_DB_CONNECT=$db_connect
2182
2183 do_checks
2184 done
2185
2186 # ---remote---------------------------------------------------------------
2187
2188 for remote_instance in $REMOTE_INSTANCES; do
2189 remote_instance_line=$(eval "echo \${$remote_instance}")
2190
2191 set_ora_env "$remote_instance"
2192 set_ora_version "$(echo "$remote_instance_line" | cut -d":" -f8)"
2193 logging "[${remote_instance}] [remote]" "ORACLE_HOME: ${ORACLE_HOME}" "TNS_ADMIN: ${TNS_ADMIN}"\
2194 "ORACLE_SID: ${ORACLE_SID}" "LD_LIBRARY_PATH: ${LD_LIBRARY_PATH}"\
2195 "ORACLE_VERSION: ${ORACLE_VERSION} (${NUMERIC_ORACLE_VERSION})"
2196
2197 db_connect=$(mk_ora_db_connect "$remote_instance")
2198 piggyback_host=$(echo "${remote_instance_line}" | cut -d":" -f6)
2199 export MK_SID=$remote_instance
2200 export MK_DB_CONNECT=$db_connect
2201 export MK_PIGGYBACK_HOST=$piggyback_host
2202
2203 do_checks
2204 done
check_mk