File: //lib/check_mk_agent/plugins/mk_oracle
#!/bin/bash
# Copyright (C) 2019 Checkmk GmbH - License: GNU General Public License v2
# This file is part of Checkmk (https://checkmk.com). It is subject to the terms and
# conditions defined in the file COPYING, which is part of this source code package.
# Reason for this no-op: shellcheck disable=... before the first command disables the error for the
# entire script.
:
# Disable unused variable error (needed to keep track of version)
# shellcheck disable=SC2034
CMK_VERSION="2.3.0p38"
# Checkmk agent plugin for monitoring ORACLE databases
# This plugin is a result of the common work of Thorsten Bruhns
# and Mathias Kettner. Thorsten is responsible for the ORACLE
# stuff, Mathias for the shell hacking...
# This plugin works for Linux, Solaris and AIX.
# See http://docs.checkmk.com/master/en/monitoring_oracle.html
# for more details regarding configuration.
# TODO
# - cleanup ORACLE_SID, MK_SID, local sid
# - separate remote instance, local, ASM
# .--default config------------------------------------------------------.
# | _ __ _ _ __ _ |
# | __| | ___ / _| __ _ _ _| | |_ ___ ___ _ __ / _(_) __ _ |
# | / _` |/ _ \ |_ / _` | | | | | __| / __/ _ \| '_ \| |_| |/ _` | |
# | | (_| | __/ _| (_| | |_| | | |_ | (_| (_) | | | | _| | (_| | |
# | \__,_|\___|_| \__,_|\__,_|_|\__| \___\___/|_| |_|_| |_|\__, | |
# | |___/ |
# '----------------------------------------------------------------------'
load_default_config() {
SYNC_SECTIONS="instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance locks systemparameter"
ASYNC_SECTIONS="tablespaces rman jobs resumable iostats"
SYNC_ASM_SECTIONS="instance processes"
ASYNC_ASM_SECTIONS="asm_diskgroup"
CACHE_MAXAGE=600
OLRLOC=/etc/oracle/olr.loc
MAX_TASKS=1
}
# .
# .--usage---------------------------------------------------------------.
# | |
# | _ _ ___ __ _ __ _ ___ |
# | | | | / __|/ _` |/ _` |/ _ \ |
# | | |_| \__ \ (_| | (_| | __/ |
# | \__,_|___/\__,_|\__, |\___| |
# | |___/ |
# '----------------------------------------------------------------------'
display_usage() {
load_default_config
cat <<MK-ORA-USAGE
USAGE:
mk_oracle [OPTIONS]
DESCRIPTION:
Check_MK agent plugin for monitoring ORACLE databases. This plugin works for
Linux, Solaris and AIX. See https://docs.checkmk.com/master/en/monitoring_oracle.html for
more details regarding configuration.
OPTIONS:
-h, --help Shows this help message and exit
-d Enable full bash debugging
-t Just check the connection
-l, --log Logs certain steps while execution.
Note: These sections are executed as synchronous sections
The logfile is in the folder \$MK_VARDIR/log.
--no-spool Writes output to stdout instead of the spool file if systemd is used.
Note: The option doesn't influence the behavior of the script
if xinetd is used to run the check_mk_agent.
-s, --sections <section>,...
Execute only these sections.
Note: These sections are execute as synchronous sections
--oratestversion Set a custom Oracleversion during execution.
This parameter is only for development. Example: 12.1
CONFIGURATION:
The following variables can be used in the configuration file mk_oracle.cfg;
mk_oracle.cfg is in the folder \$MK_CONFDIR or \$MK_VARDIR.
SYNC_SECTIONS="<section> ..."
Specify which sections will be executed sychronously.
Default is "$SYNC_SECTIONS"
ASYNC_SECTIONS="<section> ..."
Specify which sections will be executed asychronously.
Default is "$ASYNC_SECTIONS"
SYNC_ASM_SECTIONS="<section> ..."
Specify which ASM sections will be executed sychronously.
Default is "$SYNC_ASM_SECTIONS"
ASYNC_ASM_SECTIONS="<section> ..."
Specify which ASM sections will be executed asychronously.
Default is "$ASYNC_ASM_SECTIONS"
CACHE_MAXAGE=<age> Interval for running async checks (in seconds).
Default is "$CACHE_MAXAGE"
OLRLOC=/PATH/TO/olr.loc
Disable the discovery from GI/Restart. A fully configured
oratab is mandatory, when this has been disabled. Be aware!
Please disable OLRLOC for failover cluster with custom
clusterware resources.
Default is "$OLRLOC"
ONLY_SIDS="<sid> ..." Specify which SIDs will be checked.
This variable has priority 1.
Default is empty.
SKIP_SIDS="<sid> ..." Specify which SIDs will not be checked.
This variable has priority 2.
Default is empty.
EXCLUDE_<sid>="<section> ..."
Exclude sections for a specific SID. Valid values
are "ALL" or specific section names.
For ASM SIDs you cannot use this concept but you
can use SKIP_SIDS="+ASM1 ..." instead.
This variable has priority 3.
Default is empty.
MAX_TASKS=<Number> Specify how many SIDs to check in parallel
Default is "$MAX_TASKS"
CONFIGURATION REMOTE INSTANCES:
REMOTE_INSTANCE_<id>="<user>:<password>:<role>:<host>:<port>:<piggybackhost>:<sid>:<version>:<tnsalias>"
<piggybackhost> and <tnsalias> are optional.
CONFIGURATION CUSTOM SQLS:
The following variables for custom SQLs can be used in the configuration file
mk_oracle.cfg. Global variables can be set in mk_oracle.cfg; local variables
can be set within a section definition. Sections are defined in mk_oracle.cfg.
There are global and local variables. The local variables have precedence and
overwrite the global ones.
The variable "\$ORACLE_SID" can be used within every section.
SQLS_DBUSER="<username>"
SQLS_DBPASSWORD="<password>"
SQLS_DBSYSCONNECT="SYSDBA|SYSOPER"
SQLS_TNSALIAS="<tnsalias>"
Dedicated credentials that can be configured per
section for custom_sqls.
SQLS_DBSYSCONNECT needs to be set if the user is
sysdba or sysoper. Otherwise just skip this option.
SQLS_SECTIONS="<section>,..."
Registry of custom SQL sections. If this configuration
variable is not set or empty then no custom SQL sections
will be executed. A custom SQL section looks like:
section () {
VAR=VALUE
...
}
Can only be set globally.
Default is empty.
SQLS_SIDS="<sid>,..." List of SIDs for which the custom SQLs will be executed.
If not set then the section will be ignored. You can use
"\$SIDS" in order to execute the custom SQLs for all SIDs.
Can be set globally or within a section definition.
SQLS_DIR=/PATH/TO/SQLDIR
Path of the directory where the SQLs are stored.
Can be set globally or within a section definition.
SQLS_SQL=<name.sql> File containing a SQL statement.
Can be set globally or within a section definition.
SQLS_PARAMETERS=<params>
Parameters as a string, double quotes will be removed.
Can be set globally or within a section definition.
SQLS_SECTION_NAME="<name>"
By default the section header is 'oracle_sql' is used and
the check plugin "oracle_sql" processes this data. The data
must have a fixed form:
<<<oracle_sql:sep(58)>>>
[[[<item>]]]
details:DETAILS
perfdata:NAME=VAL;WARN;CRIT;MIN;MAX ...
long:LONG
...
exit:CODE
By default the <item> is "<sid>|<sql-name>".
Can be set globally or within a section definition.
If \$SQLS_MAX_CACHE_AGE is set and the section name is 'oracle_sql'
then the cache information is appended to the instance header, ie.:
<<<oracle_sql:sep(58)>>>
[[[<item-1>|cached(<MTIME-1>,<MAX_AGE-1>)]]]
...
[[[<item-2>|cached(<MTIME-2>,<MAX_AGE-2>)]]]
...
SQLS_SECTION_SEP="<sep>"
Only useful if SQLS_SECTION_NAME is not 'oracle_sql'.
ASCII code, eg. '58' means ':'.
Can be set globally or within a section definition.
If an error occurs, the error message will have the
following format, independent of the separator chosen.
<sid>|FAILURE|<error description>
SQLS_ITEM_NAME=item_name
Only useful if SQLS_SECTION_NAME is 'oracle_sql'. In
this case the <item> is "<sid>|\$SQLS_ITEM_NAME".
Can only be set within a section definition.
SQLS_MAX_CACHE_AGE=<age>
See CACHE_MAX_AGE.
Can be set globally or within a section definition.
MK-ORA-USAGE
}
# .
# .--args----------------------------------------------------------------.
# | |
# | __ _ _ __ __ _ ___ |
# | / _` | '__/ _` / __| |
# | | (_| | | | (_| \__ \ |
# | \__,_|_| \__, |___/ |
# | |___/ |
# '----------------------------------------------------------------------'
MK_ORA_NOSPOOL=false
MK_ORA_DEBUG=false
MK_ORA_DEBUG_CONNECT=false
MK_ORA_LOGGING=false
export MK_ORA_LOGGING
while test -n "$1"; do
case "$1" in
-h | --help)
display_usage
exit 0
;;
-d)
set -xv
MK_ORA_DEBUG=true
shift
;;
-t)
MK_ORA_DEBUG_CONNECT=true
shift
;;
-l | --log)
MK_ORA_LOGGING=true
shift
;;
--no-spool)
MK_ORA_NOSPOOL=true
shift
;;
-s | --sections)
shift
MK_ORA_SECTIONS=$(echo "$1" | tr ',' ' ')
shift
;;
--oratestversion)
shift
MK_ORA_TESTVERSION="$1"
shift
;;
*)
shift
;;
esac
done
# .
# .--config--------------------------------------------------------------.
# | __ _ |
# | ___ ___ _ __ / _(_) __ _ |
# | / __/ _ \| '_ \| |_| |/ _` | |
# | | (_| (_) | | | | _| | (_| | |
# | \___\___/|_| |_|_| |_|\__, | |
# | |___/ |
# '----------------------------------------------------------------------'
filter_sections() {
local opt_sync_sections=
local opt_async_sections=
local opt_sync_asm_sections=
local opt_async_asm_sections=
local opt_sqls_sections=
for section in $MK_ORA_SECTIONS; do
if [ -n "$SYNC_SECTIONS" ] && [[ "$SYNC_SECTIONS" == *"$section"* ]]; then
opt_sync_sections="$opt_sync_sections $section"
elif [ -n "$ASYNC_SECTIONS" ] && [[ "$ASYNC_SECTIONS" == *"$section"* ]]; then
opt_async_sections="$opt_async_sections $section"
fi
if [ -n "$SYNC_ASM_SECTIONS" ] && [[ "$SYNC_ASM_SECTIONS" == *"$section"* ]]; then
opt_sync_asm_sections="$opt_sync_asm_sections $section"
elif [ -n "$ASYNC_ASM_SECTIONS" ] && [[ "$ASYNC_ASM_SECTIONS" == *"$section"* ]]; then
opt_async_asm_sections="$opt_async_asm_sections $section"
fi
if [ -n "$SQLS_SECTIONS" ] && [[ "$SQLS_SECTIONS" == *"$section"* ]]; then
opt_sqls_sections="$opt_sqls_sections $section"
fi
done
SYNC_SECTIONS=$opt_sync_sections
ASYNC_SECTIONS=$opt_async_sections
SYNC_ASM_SECTIONS=$opt_sync_asm_sections
ASYNC_ASM_SECTIONS=$opt_async_asm_sections
SQLS_SECTIONS=$opt_sqls_sections
}
load_config() {
if [ ! "$MK_CONFDIR" ]; then
echo "MK_CONFDIR not set!" >&2
exit 1
fi
if [ ! "$MK_VARDIR" ]; then
#TODO Which default? (run_cached/ mk_oracle.found, log file)
export MK_VARDIR=$MK_CONFDIR
fi
if [ ! -d "$MK_VARDIR/log" ]; then
mkdir "$MK_VARDIR/log"
fi
load_default_config
# Source the optional configuration file for this agent plugin
if [ -e "$MK_CONFDIR/mk_oracle.cfg" ]; then
# shellcheck source=../cfg_examples/mk_oracle.cfg
. "$MK_CONFDIR/mk_oracle.cfg"
fi
if [ -d "$MK_CONFDIR/mk_oracle.d" ] && [ "$(ls -A "$MK_CONFDIR/mk_oracle.d")" ]; then
for cfg in "$MK_CONFDIR"/mk_oracle.d/*.cfg; do
# shellcheck disable=SC1090
. "$cfg"
done
fi
if [ -n "$MK_ORA_SECTIONS" ]; then
filter_sections
fi
# globals
custom_sqls_sections=$(echo "$SQLS_SECTIONS" | tr ',' ' ')
# globals and locals
custom_sqls_section_name="oracle_sql" # SQLS_SECTION_NAME
custom_sqls_section_sep="$SQLS_SECTION_SEP"
custom_sqls_sids="$SQLS_SIDS"
custom_sqls_dir="$SQLS_DIR"
custom_sqls_sql="$SQLS_SQL"
custom_sqls_parameters="$SQLS_PARAMETERS"
custom_sqls_max_cache_age="$SQLS_MAX_CACHE_AGE"
unset_custom_sqls_vars
if $MK_ORA_LOGGING || [ -n "$MK_ORA_SECTIONS" ]; then
MK_DEBUG_MODE=true
else
MK_DEBUG_MODE=false
fi
}
# .
# .--OS env--------------------------------------------------------------.
# | ___ ____ |
# | / _ \/ ___| ___ _ ____ __ |
# | | | | \___ \ / _ \ '_ \ \ / / |
# | | |_| |___) | | __/ | | \ V / |
# | \___/|____/ \___|_| |_|\_/ |
# | |
# '----------------------------------------------------------------------'
OS_TYPE=$(uname -s)
unsupported_os() {
logging -c -e "[set_os_env]" "Unsupported OS: ${OS_TYPE}"
exit 1
}
set_os_env() {
if [ "$OS_TYPE" = 'Linux' ]; then
GREP="grep"
AWK="awk"
elif [ "$OS_TYPE" = 'SunOS' ]; then
# expand the PATH for inetd. Otherwise some stuff in /opt/sfw/bin is not found!
export PATH=$PATH:/usr/ucb:/usr/proc/bin:opt/sfw/bin:/opt/sfw/sbin:/usr/sfw/bin:/usr/sfw/sbin:/opt/csw/bin
GREP="/usr/xpg4/bin/grep"
if [ ! -x "$GREP" ]; then
logging -c -e "[set_os_env]" "Please make sure that ${GREP} is existing on Solaris. Aborting mk_oracle plugin."
exit 127
fi
AWK="nawk"
elif [ "$OS_TYPE" = 'AIX' ]; then
GREP="grep"
AWK="awk"
elif [ "$OS_TYPE" = 'HP-UX' ]; then
GREP="grep"
AWK="awk"
else
unsupported_os
fi
export GREP AWK
}
set_up_get_epoch() {
# On some systems date +%s returns a literal %s
if date +%s | grep "^[0-9].*$" >/dev/null 2>&1; then
get_epoch() { date +%s; }
else
# do not check whether perl is even present.
# in weird cases we may be fine without get_epoch.
get_epoch() { perl -e 'print($^T."\n");'; }
fi
}
# Please keep this in sync with the agents!
get_file_atime() {
stat -c %X "${1}" 2>/dev/null ||
stat -f %a "${1}" 2>/dev/null ||
perl -e 'if (! -f $ARGV[0]){die "0000000"};$atime=(stat($ARGV[0]))[8];print $atime."\n";' "${1}"
}
# Please keep this in sync with the agents!
get_file_mtime() {
stat -c %Y "${1}" 2>/dev/null ||
stat -f %m "${1}" 2>/dev/null ||
perl -e 'if (! -f $ARGV[0]){die "0000000"};$mtime=(stat($ARGV[0]))[9];print $mtime."\n";' "${1}"
}
# .
# .--logging-------------------------------------------------------------.
# | _ _ |
# | | | ___ __ _ __ _(_)_ __ __ _ |
# | | |/ _ \ / _` |/ _` | | '_ \ / _` | |
# | | | (_) | (_| | (_| | | | | | (_| | |
# | |_|\___/ \__, |\__, |_|_| |_|\__, | |
# | |___/ |___/ |___/ |
# '----------------------------------------------------------------------'
# How to use logging function:
# Basic form:
# logging "[WHEREIAM]"
#
# Logging without ID ELSEWHERE:
# logging "[ELSEWHERE]"
#
# Logging with ID:
# logging "[ID]"
#
# Logging with ID in function:
# logging "[ID] [FUNC-NAME]"
#
# Logging with ID elsewhere:
# logging "[ID] [ELSEWHERE]"
#
# Add some useful messages:
# logging "" "Set ORACLE_HOME=${ORACLE_HOME}"
#
# There are some optional flags for prefix in log lines:
# Flag Criticality Meaning
# --------------------------------------------------------------
# '-o' 0 (OK) clear
# '-w' 1 (WARNING) error but plugin goes on
# '-c' 2 (CRITICAL) error and plugin exits
# '-u' 3 (UNKNOWN) ?
# '-e' Writes message to stderr, too
#
# Add some useful messages:
# logging "" "Set OS environment" "OS: ${OS_TYPE}" "GREP: ${GREP}"
#
# Examples:
# logging "[${SID}] [set_ora_env]" "Found local ORACLE_HOME: ${ORACLE_HOME}"
# >>> 2018-05-15 16:27:43 [0] [352] [this-sid] [set_ora_env] Found local ORACLE_HOME: /path/to/ora/home
#
# logging "[preliminaries]" "Set OS environment" "OS: ${OS_TYPE}" "GREP: ${GREP}" "AWK: ${AWK}"
# >>> 2018-05-15 16:27:43 [0] [1748] [preliminaries] Set OS environment
# >>> 2018-05-15 16:27:44 [0] [1748] [preliminaries] OS: Linux
# >>> 2018-05-15 16:27:45 [0] [1748] [preliminaries] GREP: /bin/grep
# >>> 2018-05-15 16:27:46 [0] [1748] [preliminaries] AWK: /usr/bin/awk
#
# logging -c "[${SID}] [set_ora_env]" "ORA-99999 oratab not found in local mode"
# >>> 2018-05-15 16:27:43 [2] [362] [this-sid] [set_ora_env] ORA-99999 oratab not found in local mode
#
# If you need to declare some headers:
# logging "" "-- Set OS environment --" "OS: ${OS_TYPE}" "GREP: ${GREP}"
# >>> 2018-05-15 16:27:43 [0] [1748] [preliminaries] -- Set OS environment --
# >>> 2018-05-15 16:27:44 [0] [1748] [preliminaries] OS: Linux
# >>> 2018-05-15 16:27:45 [0] [1748] [preliminaries] GREP: /bin/grep
logging() {
if $MK_ORA_LOGGING; then
local log_file=${MK_VARDIR}/log/mk_oracle_task_${TASK_NR:-0}.log
local criticality=
local args=
local header=
local to_stderr=false
i=0
while test -n "$1"; do
case "$1" in
-o)
criticality="0" # OK, default
shift
;;
-w)
criticality="1" # WARNING
shift
;;
-c)
criticality="2" # CRITICAL
shift
;;
-u)
criticality="3" # UNKNOWN
shift
;;
-e)
to_stderr=true
shift
;;
*)
args[i]="$1"
i=$((i + 1))
shift
;;
esac
done
if [ -z "${criticality}" ]; then
criticality="0"
fi
header="$(perl -MPOSIX -le 'print strftime "%F %T", localtime $^T') [${criticality}] ${args[0]}"
if [ "${#args[@]}" -le 1 ]; then
echo "$header" >>"$log_file"
if [ $to_stderr = true ]; then
echo "$header" >&2
fi
else
for arg in "${args[@]:1}"; do
echo "${header} $arg" >>"$log_file"
if [ $to_stderr = true ]; then
echo "${header} $arg" >&2
fi
done
fi
fi
}
# .
# .--ORA env-------------------------------------------------------------.
# | ___ ____ _ |
# | / _ \| _ \ / \ ___ _ ____ __ |
# | | | | | |_) | / _ \ / _ \ '_ \ \ / / |
# | | |_| | _ < / ___ \ | __/ | | \ V / |
# | \___/|_| \_\/_/ \_\ \___|_| |_|\_/ |
# | |
# '----------------------------------------------------------------------'
set_ora_env() {
local sid=${1}
ORACLE_SID="$sid"
unset ORA_HOME_SOURCE
if [[ "$sid" =~ ^REMOTE_INSTANCE_.* ]]; then
# we get the ORACLE_HOME from mk_oracle.cfg for REMOTE execution
ORACLE_HOME=${ORACLE_HOME:-${REMOTE_ORACLE_HOME}}
export ORA_HOME_SOURCE="(remote):"
else
# we work in local mode
# GI/Restart installed?
if [ -f ${OLRLOC} ]; then
# oratab is not supported in Grid-Infrastructure 12.2+
# => fetch ORACLE_HOME from cluster repository for all GI/Restart Environments!
# OLRLOC holds crs_home
crs_home=$(get_crs_home_from_olrloc "${OLRLOC}")
export ORA_HOME_SOURCE="(GI): "
# set ORACLE_HOME = crs_home for ASM
if [ "${ORACLE_SID:0:1}" = '+' ]; then
# shellcheck disable=SC2154
ORACLE_HOME=${crs_home}
else
crsctl_path="${crs_home}"/bin/crsctl
EXECUTION_MODE="$(get_binary_execution_mode "$crsctl_path" "$(get_binary_owner "$crsctl_path")")"
# get ORACLE_HOME with crsctl from Oracle Grid Infrastructure / Restart
ORACLE_HOME=$($EXECUTION_MODE "\"${crsctl_path}\" stat res -p -w \"((TYPE = ora.database.type) and (GEN_USR_ORA_INST_NAME = ${ORACLE_SID}))\" | ${GREP} -m1 ^ORACLE_HOME= | cut -d= -f2")
fi
else
# Single Instance with oratab
test -f /etc/oratab && ORATAB=/etc/oratab
# /var/opt/oracle/oratab is needed for Oracle Solaris
test -f /var/opt/oracle/oratab && ORATAB=/var/opt/oracle/oratab
if ! test -f "${ORATAB:-""}"; then
logging -c -e "[${sid}] [set_ora_env]" "ORA-99999 oratab not found in local mode"
exit 1
fi
ORACLE_HOME=$("${GREP}" "^${ORACLE_SID}:" <"${ORATAB}" | cut -d":" -f2)
export ORA_HOME_SOURCE="(oratab):"
fi
if [ -z "${ORACLE_HOME}" ]; then
# cut last number from SID for Oracle RAC to find entry in oratab
ORACLE_HOME=$("${GREP}" "^${ORACLE_SID/%[0-9]/}:" <"${ORATAB}" | cut -d":" -f2)
fi
fi
if [ ! -d "${ORACLE_HOME:-'not_found'}" ]; then
logging -c -e "[${sid}] [set_ora_env]" "ORA-99999 ORACLE_HOME for SID '${ORACLE_SID}' not found or not existing!"
return 2
fi
TNS_ADMIN=${TNS_ADMIN:-$MK_CONFDIR}
if ! test -f "${TNS_ADMIN}/sqlnet.ora"; then
logging -c -e "[${sid}] [set_ora_env]" "TNS_ADMIN/sqlnet.ora: ${TNS_ADMIN}/sqlnet.ora"
exit 1
fi
export ORACLE_HOME TNS_ADMIN ORACLE_SID
}
set_ora_version() {
if [ "$MK_ORA_TESTVERSION" ]; then
# TODO: How is MK_ORA_TESTVERSION used? How many digits does it have?
ORACLE_VERSION_FOUR_PARTS="$MK_ORA_TESTVERSION"
logging -o -e "[${sid}] [set_ora_version]" "Custom ORACLE_VERSION: ${ORACLE_VERSION_SHORT}"
elif [ "$1" ]; then
# Used for REMOTE_INSTANCES
ORACLE_VERSION_FOUR_PARTS="$1"
else
# Get the sql version by call sqlplus
ORACLE_VERSION_FOUR_PARTS="$(get_sqlplus_version_with_precision 4)"
fi
ORACLE_VERSION_SHORT=$(echo "${ORACLE_VERSION_FOUR_PARTS}" | cut -d"." -f-2)
NUMERIC_ORACLE_VERSION=${ORACLE_VERSION_SHORT//./}
NUMERIC_ORACLE_VERSION_FOUR_PARTS=${ORACLE_VERSION_FOUR_PARTS//./}
export NUMERIC_ORACLE_VERSION
export NUMERIC_ORACLE_VERSION_FOUR_PARTS
}
# .
# .--SQL Queries---------------------------------------------------------.
# | ____ ___ _ ___ _ |
# | / ___| / _ \| | / _ \ _ _ ___ _ __(_) ___ ___ |
# | \___ \| | | | | | | | | | | |/ _ \ '__| |/ _ \/ __| |
# | ___) | |_| | |___ | |_| | |_| | __/ | | | __/\__ \ |
# | |____/ \__\_\_____| \__\_\\__,_|\___|_| |_|\___||___/ |
# | |
# +----------------------------------------------------------------------+
# | The following functions create SQL queries for ORACLE and output |
# | them to stdout. All queries output the database name or the instane |
# | name as first column. |
# | V$BUFFER_POOL_STATISTICS: Convert Instancedata to cdb$root in |
# | cdb-environments (only con_id = 0!) |
# | V$SGAINFO: Convert Instancedata to cdb$root in cdb-environments |
# '----------------------------------------------------------------------'
#TODO Create subsections in query and parse them in related check plugin.
sql_iostats() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo 'PROMPT <<<oracle_performance:sep(124)>>>'
echo "WITH iostat_file AS (
SELECT con_id,
filetype_name,
SUM(large_read_reqs) large_read_reqs,
SUM(large_read_servicetime) large_read_servicetime,
SUM(large_write_reqs) large_write_reqs,
SUM(large_write_servicetime) large_write_servicetime,
SUM(small_read_reqs) small_read_reqs,
SUM(small_read_servicetime) small_read_servicetime,
SUM(small_sync_read_reqs) small_sync_read_reqs,
SUM(small_write_reqs) small_write_reqs,
SUM(small_write_servicetime) small_write_servicetime,
SUM(small_read_megabytes * 1024 * 1024) small_read_bytes,
SUM(large_read_megabytes * 1024 * 1024) large_read_bytes,
SUM(small_write_megabytes * 1024 * 1024) small_write_bytes,
SUM(large_write_megabytes * 1024 * 1024) large_write_bytes
FROM v\$iostat_file
GROUP BY con_id,
filetype_name
)
SELECT upper(
DECODE(
d.cdb,
'NO',
i.instance_name,
i.instance_name || '.' || vd.name
)
)
|| '|iostat_file'
|| '|' || filetype_name
|| '|' || small_read_reqs
|| '|' || large_read_reqs
|| '|' || small_write_reqs
|| '|' || large_write_reqs
|| '|' || small_read_servicetime
|| '|' || large_read_servicetime
|| '|' || small_write_servicetime
|| '|' || large_write_servicetime
|| '|' || small_read_bytes
|| '|' || large_read_bytes
|| '|' || small_write_bytes
|| '|' || large_write_bytes
FROM iostat_file io
JOIN v\$containers vd ON io.con_id = vd.con_id
JOIN v\$instance i ON 1 = 1
JOIN v\$database d ON 1 = 1
ORDER BY vd.con_id,
io.filetype_name;"
fi
}
sql_performance() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo 'PROMPT <<<oracle_performance:sep(124)>>>'
echo " select upper(DECODE(cdb,'NO',instance_name
,instance_name || '.' || con_name) )
||'|'|| 'sys_time_model'
||'|'|| STAT_NAME
||'|'|| Round(value/1000000)
from (
select d.cdb, i.instance_name, s.stat_name, s.value, vd.name con_name
from v\$instance i
join v\$con_sys_time_model s on s.stat_name in('DB time', 'DB CPU')
join v\$containers vd on vd.con_id = s.con_id
join v\$database d on d.cdb = 'YES'
where vd.con_id <> 2
union all
select d.cdb, i.instance_name, s.stat_name, s.value, null
from v\$instance i
join v\$sys_time_model s on s.stat_name in('DB time', 'DB CPU')
join v\$database d on d.cdb = 'NO'
order by stat_name);
select upper(decode(cdb, 'NO', instance_name
,instance_name || '.'||con_name))
||'|'|| 'sys_wait_class'
||'|'|| WAIT_CLASS
||'|'|| Round(total_waits)
||'|'|| Round(time_waited)
||'|'|| Round(total_waits_fg)
||'|'|| Round(time_waited_fg)
from (
select i.instance_name, vd.con_id, S.WAIT_CLASS
, s.total_waits, s.time_waited, s.total_waits_fg
, s.time_waited_fg, vd.name con_name, d.cdb
from v\$instance i
join v\$database d on d.cdb = 'YES'
join v\$containers vd on 1=1
join v\$con_system_wait_class s on vd.con_id = s.con_id
where s.WAIT_CLASS <> 'Idle'
union all
select i.instance_name, 0, S.WAIT_CLASS
, s.total_waits, s.time_waited, s.total_waits_fg
, s.time_waited_fg, null, d.cdb
from v\$instance i
join v\$database d on d.cdb = 'NO'
join v\$system_wait_class s on s.WAIT_CLASS <> 'Idle'
)
order by con_name, wait_class;
select upper(DECODE(d.cdb,'NO',i.instance_name
,i.instance_name || '.CDB\$ROOT') )
||'|'|| 'buffer_pool_statistics'
||'|'|| b.name
||'|'|| b.db_block_gets
||'|'|| b.db_block_change
||'|'|| b.consistent_gets
||'|'|| b.physical_reads
||'|'|| b.physical_writes
||'|'|| b.FREE_BUFFER_WAIT
||'|'|| b.BUFFER_BUSY_WAIT
from v\$instance i
join V\$BUFFER_POOL_STATISTICS b on b.con_id = 0
join v\$database d on 1=1;
select upper(DECODE(d.cdb,'NO',i.instance_name
,i.instance_name || '.CDB\$ROOT') )
||'|'|| 'SGA_info'
||'|'|| s.name
||'|'|| s.bytes
from v\$instance i
join V\$sgainfo s on s.con_id = 0
join v\$database d on 1=1;
select upper(DECODE(d.cdb,'NO',i.instance_name
,i.instance_name || '.CDB\$ROOT') )
||'|'|| 'librarycache'
||'|'|| b.namespace
||'|'|| b.gets
||'|'|| b.gethits
||'|'|| b.pins
||'|'|| b.pinhits
||'|'|| b.reloads
||'|'|| b.invalidations
from v\$instance i
join V\$librarycache b on b.con_id = 0
join v\$database d on 1=1;
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
l_i_cursor_id INTEGER;
l_n_rowcount NUMBER;
l_vc_name VARCHAR2(200);
l_vc_value VARCHAR2(200);
l_vc_unit VARCHAR2(20);
l_vc_sql VARCHAR2(200);
l_vc_step VARCHAR2(137);
BEGIN
dbms_output.enable(200000);
l_i_cursor_id := dbms_sql.open_cursor(security_level => 2);
l_vc_step := 'before con';
FOR con IN (
SELECT
upper(DECODE(d.cdb,'NO',i.instance_name
,i.instance_name || '.' || c.name
)
) iname, c.name
FROM
v\$containers c,
v\$database d,
v\$instance i
WHERE
c.open_mode LIKE 'READ %'
AND c.name <> 'PDB\$SEED'
ORDER BY c.con_id) LOOP
l_vc_sql := 'select name, value, unit from v\$pgastat order by name';
l_vc_step := 'parse: ' || con.name;
dbms_sql.parse(c => l_i_cursor_id, statement => l_vc_sql, language_flag => dbms_sql
.native, container => con.name);
dbms_sql.define_column(l_i_cursor_id, 1, l_vc_name, 200);
dbms_sql.define_column(l_i_cursor_id, 2, l_vc_value, 200);
dbms_sql.define_column(l_i_cursor_id, 3, l_vc_unit, 200);
l_vc_step := 'execute: ' || con.name;
l_n_rowcount := dbms_sql.execute_and_fetch(l_i_cursor_id);
LOOP
EXIT WHEN dbms_sql.fetch_rows(l_i_cursor_id) = 0;
dbms_sql.column_value(l_i_cursor_id, 1, l_vc_name);
dbms_sql.column_value(l_i_cursor_id, 2, l_vc_value);
dbms_sql.column_value(l_i_cursor_id, 3, l_vc_unit);
dbms_output.put_line(con.iname ||'|PGA_info|'||l_vc_name||'|'||l_vc_value||'|'||l_vc_unit);
END LOOP;
END LOOP;
dbms_sql.close_cursor(l_i_cursor_id);
EXCEPTION
WHEN OTHERS THEN
FOR cur1 in (select upper(i.instance_name) instance_name from v\$instance i) LOOP
dbms_output.put_line(cur1.instance_name || '| Debug: '|| l_vc_step || ': ' || sqlerrm);
END LOOP;
END;
/
"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
echo "PROMPT <<<oracle_performance:sep(124)>>>"
echo "select upper(i.INSTANCE_NAME)
||'|'|| 'sys_time_model'
||'|'|| S.STAT_NAME
||'|'|| Round(s.value/1000000)
from v\$instance i,
v\$sys_time_model s
where s.stat_name in('DB time', 'DB CPU')
order by s.stat_name;
select upper(i.INSTANCE_NAME)
||'|'|| 'buffer_pool_statistics'
||'|'|| b.name
||'|'|| b.db_block_gets
||'|'|| b.db_block_change
||'|'|| b.consistent_gets
||'|'|| b.physical_reads
||'|'|| b.physical_writes
||'|'|| b.FREE_BUFFER_WAIT
||'|'|| b.BUFFER_BUSY_WAIT
from v\$instance i, V\$BUFFER_POOL_STATISTICS b;
select upper(i.INSTANCE_NAME)
||'|'|| 'SGA_info'
||'|'|| s.name
||'|'|| s.bytes
from v\$sgainfo s, v\$instance i;
select upper(i.INSTANCE_NAME)
||'|'|| 'librarycache'
||'|'|| b.namespace
||'|'|| b.gets
||'|'|| b.gethits
||'|'|| b.pins
||'|'|| b.pinhits
||'|'|| b.reloads
||'|'|| b.invalidations
from v\$instance i, V\$librarycache b;"
fi
}
sql_tablespaces() {
echo "PROMPT <<<oracle_tablespaces:sep(124)>>>"
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo "SET SERVEROUTPUT ON feedback off
DECLARE
type x is table of varchar2(20000) index by pls_integer;
xx x;
begin
begin
execute immediate 'select upper(decode(${IGNORE_DB_NAME:-0}
, 0, decode(vp.con_id, null, d.NAME
,d.NAME||''.''||vp.name)
, i.instance_name))
|| ''|'' || dbf.file_name
|| ''|'' || dbf.tablespace_name
|| ''|'' || dbf.fstatus
|| ''|'' || dbf.AUTOEXTENSIBLE
|| ''|'' || dbf.blocks
|| ''|'' || dbf.maxblocks
|| ''|'' || dbf.USER_BLOCKS
|| ''|'' || dbf.INCREMENT_BY
|| ''|'' || dbf.ONLINE_STATUS
|| ''|'' || dbf.BLOCK_SIZE
|| ''|'' || decode(tstatus,''READ ONLY'', ''READONLY'', tstatus)
|| ''|'' || dbf.free_blocks
|| ''|'' || dbf.contents
|| ''|'' || i.version
from v\$database d
join v\$instance i on 1=1
join (
select f.con_id, f.file_name, f.tablespace_name, f.status fstatus, f.AUTOEXTENSIBLE,
f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY,
f.ONLINE_STATUS, t.BLOCK_SIZE, t.status tstatus, nvl(sum(fs.blocks),0) free_blocks, t.contents
from cdb_data_files f
join cdb_tablespaces t on f.tablespace_name = t.tablespace_name
and f.con_id = t.con_id
left outer join cdb_free_space fs on f.file_id = fs.file_id
and f.con_id = fs.con_id
group by f.con_id, f.file_name, f.tablespace_name, f.status, f.autoextensible,
f.blocks, f.maxblocks, f.user_blocks, f.increment_by, f.online_status,
t.block_size, t.status, t.contents
) dbf on 1=1
left outer join v\$pdbs vp on dbf.con_id = vp.con_id
where d.database_role = ''PRIMARY'''
bulk collect into xx;
if xx.count >= 1 then
for i in 1 .. xx.count loop
dbms_output.put_line(xx(i));
end loop;
end if;
exception
when others then
for cur1 in (select upper(name) name from v\$database) loop
dbms_output.put_line(cur1.name || '| Debug (121) 1: ' ||sqlerrm);
end loop;
end;
END;
/
set serverout off"
echo "SET SERVEROUTPUT ON feedback off
DECLARE
type x is table of varchar2(20000) index by pls_integer;
xx x;
begin
begin
execute immediate 'select upper(decode(${IGNORE_DB_NAME:-0}
, 0, decode(dbf.con_id, null, d.NAME
,dbf.name)
, i.instance_name))
|| ''|'' || dbf.file_name
|| ''|'' || dbf.tablespace_name
|| ''|'' || dbf.fstatus
|| ''|'' || dbf.AUTOEXTENSIBLE
|| ''|'' || dbf.blocks
|| ''|'' || dbf.maxblocks
|| ''|'' || dbf.USER_BLOCKS
|| ''|'' || dbf.INCREMENT_BY
|| ''|'' || dbf.ONLINE_STATUS
|| ''|'' || dbf.BLOCK_SIZE
|| ''|'' || decode(tstatus,''READ ONLY'', ''READONLY'', tstatus)
|| ''|'' || dbf.free_blocks
|| ''|'' || ''TEMPORARY''
|| ''|'' || i.version
FROM v\$database d
JOIN v\$instance i ON 1 = 1
JOIN (
SELECT vp.name,
vp.con_id,
f.file_name,
t.tablespace_name,
f.status fstatus,
f.autoextensible,
f.blocks,
f.maxblocks,
f.user_blocks,
f.increment_by,
''ONLINE'' online_status,
t.block_size,
t.status tstatus,
f.blocks - nvl(SUM(tu.blocks),0) free_blocks,
t.contents
FROM cdb_tablespaces t
JOIN (
SELECT vp.con_id
,d.name || ''.''|| vp.name name
FROM v\$containers vp
JOIN v\$database d ON 1 = 1
WHERE d.cdb = ''YES''
AND vp.con_id <> 2
UNION ALL
SELECT 0
,name
FROM v\$database
) vp ON t.con_id = vp.con_id
LEFT OUTER JOIN cdb_temp_files f ON t.con_id = f.con_id
AND t.tablespace_name = f.tablespace_name
LEFT OUTER JOIN gv\$tempseg_usage tu ON f.con_id = tu.con_id
AND f.tablespace_name = tu.tablespace
AND f.RELATIVE_FNO = tu.SEGRFNO#
WHERE t.contents = ''TEMPORARY''
GROUP BY vp.name,
vp.con_id,
f.file_name,
t.tablespace_name,
f.status,
f.autoextensible,
f.blocks,
f.maxblocks,
f.user_blocks,
f.increment_by,
t.block_size,
t.status,
t.contents
) dbf ON 1 = 1
where d.database_role = ''PRIMARY'''
bulk collect into xx;
if xx.count >= 1 then
for i in 1 .. xx.count loop
dbms_output.put_line(xx(i));
end loop;
end if;
exception
when others then
for cur1 in (select upper(name) name from v\$database) loop
dbms_output.put_line(cur1.name || '| Debug (121) 2: ' ||sqlerrm);
end loop;
end;
END;
/
set serverout off"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
echo "SET SERVEROUTPUT ON feedback off
DECLARE
type x is table of varchar2(20000) index by pls_integer;
xx x;
begin
begin
execute immediate 'select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
|| ''|'' || file_name ||''|''|| tablespace_name ||''|''|| fstatus ||''|''|| AUTOEXTENSIBLE
||''|''|| blocks ||''|''|| maxblocks ||''|''|| USER_BLOCKS ||''|''|| INCREMENT_BY
||''|''|| ONLINE_STATUS ||''|''|| BLOCK_SIZE
||''|''|| decode(tstatus,''READ ONLY'', ''READONLY'', tstatus) || ''|'' || free_blocks
||''|''|| contents
||''|''|| iversion
from v\$database d , v\$instance i, (
select f.file_name, f.tablespace_name, f.status fstatus, f.AUTOEXTENSIBLE,
f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY,
f.ONLINE_STATUS, t.BLOCK_SIZE, t.status tstatus, nvl(sum(fs.blocks),0) free_blocks, t.contents,
(select version from v\$instance) iversion
from dba_data_files f, dba_tablespaces t, dba_free_space fs
where f.tablespace_name = t.tablespace_name
and f.file_id = fs.file_id(+)
group by f.file_name, f.tablespace_name, f.status, f.autoextensible,
f.blocks, f.maxblocks, f.user_blocks, f.increment_by, f.online_status,
t.block_size, t.status, t.contents)
where d.database_role = ''PRIMARY'''
bulk collect into xx;
if xx.count >= 1 then
for i in 1 .. xx.count loop
dbms_output.put_line(xx(i));
end loop;
end if;
exception
when others then
for cur1 in (select upper(name) name from v\$database) loop
dbms_output.put_line(cur1.name || '| Debug (102) 1: ' ||sqlerrm);
end loop;
end;
END;
/
set serverout off"
echo "SET SERVEROUTPUT ON feedback off
DECLARE
type x is table of varchar2(20000) index by pls_integer;
xx x;
begin
begin
execute immediate 'select upper(decode(${IGNORE_DB_NAME:-0}
, 0, dbf.name
, i.instance_name))
|| ''|'' || dbf.file_name
|| ''|'' || dbf.tablespace_name
|| ''|'' || dbf.fstatus
|| ''|'' || dbf.AUTOEXTENSIBLE
|| ''|'' || dbf.blocks
|| ''|'' || dbf.maxblocks
|| ''|'' || dbf.USER_BLOCKS
|| ''|'' || dbf.INCREMENT_BY
|| ''|'' || dbf.ONLINE_STATUS
|| ''|'' || dbf.BLOCK_SIZE
|| ''|'' || decode(tstatus,''READ ONLY'', ''READONLY'', tstatus)
|| ''|'' || dbf.free_blocks
|| ''|'' || ''TEMPORARY''
|| ''|'' || i.version
FROM v\$database d
JOIN v\$instance i ON 1 = 1
JOIN (
SELECT vp.name,
f.file_name,
t.tablespace_name,
f.status fstatus,
f.autoextensible,
f.blocks,
f.maxblocks,
f.user_blocks,
f.increment_by,
''ONLINE'' online_status,
t.block_size,
t.status tstatus,
f.blocks - nvl(SUM(tu.blocks),0) free_blocks,
t.contents
FROM dba_tablespaces t
JOIN ( SELECT 0
,name
FROM v\$database
) vp ON 1=1
LEFT OUTER JOIN dba_temp_files f ON t.tablespace_name = f.tablespace_name
LEFT OUTER JOIN gv\$tempseg_usage tu ON f.tablespace_name = tu.tablespace
AND f.RELATIVE_FNO = tu.SEGRFNO#
WHERE t.contents = ''TEMPORARY''
GROUP BY vp.name,
f.file_name,
t.tablespace_name,
f.status,
f.autoextensible,
f.blocks,
f.maxblocks,
f.user_blocks,
f.increment_by,
t.block_size,
t.status,
t.contents
) dbf ON 1 = 1'
bulk collect into xx;
if xx.count >= 1 then
for i in 1 .. xx.count loop
dbms_output.put_line(xx(i));
end loop;
end if;
exception
when others then
for cur1 in (select upper(name) name from v\$database) loop
dbms_output.put_line(cur1.name || '| Debug (102) 2: ' ||sqlerrm);
end loop;
end;
END;
/
set serverout off"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 92 ]; then
echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
|| '|' || file_name ||'|'|| tablespace_name ||'|'|| fstatus ||'|'|| AUTOEXTENSIBLE
||'|'|| blocks ||'|'|| maxblocks ||'|'|| USER_BLOCKS ||'|'|| INCREMENT_BY
||'|'|| ONLINE_STATUS ||'|'|| BLOCK_SIZE
||'|'|| decode(tstatus,'READ ONLY', 'READONLY', tstatus) || '|' || free_blocks
||'|'|| contents
from v\$database d , v\$instance i, (
select f.file_name, f.tablespace_name, f.status fstatus, f.AUTOEXTENSIBLE,
f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY,
'ONLINE' ONLINE_STATUS, t.BLOCK_SIZE, t.status tstatus, nvl(sum(fs.blocks),0) free_blocks, t.contents
from dba_data_files f, dba_tablespaces t, dba_free_space fs
where f.tablespace_name = t.tablespace_name
and f.file_id = fs.file_id(+)
group by f.file_name, f.tablespace_name, f.status, f.autoextensible,
f.blocks, f.maxblocks, f.user_blocks, f.increment_by, 'ONLINE',
t.block_size, t.status, t.contents
UNION
select f.file_name, f.tablespace_name, 'ONLINE' status, f.AUTOEXTENSIBLE,
f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY, 'TEMP',
t.BLOCK_SIZE, 'TEMP' status, sum(sh.blocks_free) free_blocks, 'TEMPORARY'
from v\$thread th, dba_temp_files f, dba_tablespaces t, v\$temp_space_header sh
WHERE f.tablespace_name = t.tablespace_name and f.file_id = sh.file_id
GROUP BY th.instance, f.file_name, f.tablespace_name, 'ONLINE',
f.autoextensible, f.blocks, f.maxblocks, f.user_blocks, f.increment_by,
'TEMP', t.block_size, t.status);"
fi
}
sql_dataguard_stats() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
echo 'PROMPT <<<oracle_dataguard_stats:sep(124)>>>'
echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
||'|'|| upper(d.DB_UNIQUE_NAME)
||'|'|| d.DATABASE_ROLE
||'|'|| ds.name
||'|'|| ds.value
||'|'|| d.SWITCHOVER_STATUS
||'|'|| d.DATAGUARD_BROKER
||'|'|| d.PROTECTION_MODE
||'|'|| d.FS_FAILOVER_STATUS
||'|'|| d.FS_FAILOVER_OBSERVER_PRESENT
||'|'|| d.FS_FAILOVER_OBSERVER_HOST
||'|'|| d.FS_FAILOVER_CURRENT_TARGET
||'|'|| ms.status
||'|'|| d.open_mode
FROM v\$database d
JOIN v\$parameter vp on 1=1
JOIN v\$instance i on 1=1
left outer join V\$dataguard_stats ds on 1=1
left outer join (select listagg(to_char(inst_id) || '.' || status, ', ') WITHIN GROUP (ORDER BY to_char(inst_id) || '.' || status) status
from gv\$managed_standby
where process = 'MRP0') ms on 1=1
WHERE vp.name = 'log_archive_config'
AND vp.value is not null
ORDER BY 1;"
fi
}
sql_recovery_status() {
echo 'PROMPT <<<oracle_recovery_status:sep(124)>>>'
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}
, 0, decode(vp.con_id, null, d.NAME
,d.NAME||'.'||vp.name)
, i.instance_name))
||'|'|| d.DB_UNIQUE_NAME
||'|'|| d.DATABASE_ROLE
||'|'|| d.open_mode
||'|'|| dh.file#
||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
||'|'|| dh.STATUS
||'|'|| dh.RECOVER
||'|'|| dh.FUZZY
||'|'|| dh.CHECKPOINT_CHANGE#
||'|'|| nvl(vb.STATUS, 'unknown')
||'|'|| nvl2(vb.TIME, round((sysdate-vb.TIME)*24*60*60), 0)
FROM V\$datafile_header dh
JOIN v\$database d on 1=1
JOIN v\$instance i on 1=1
LEFT OUTER JOIN v\$backup vb on vb.file# = dh.file#
LEFT OUTER JOIN V\$PDBS vp on dh.con_id = vp.con_id
ORDER BY dh.file#;"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
||'|'|| d.DB_UNIQUE_NAME
||'|'|| d.DATABASE_ROLE
||'|'|| d.open_mode
||'|'|| dh.file#
||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
||'|'|| dh.STATUS
||'|'|| dh.RECOVER
||'|'|| dh.FUZZY
||'|'|| dh.CHECKPOINT_CHANGE#
FROM V\$datafile_header dh, v\$database d, v\$instance i
ORDER BY dh.file#;
"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 92 ]; then
echo "SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
||'|'|| upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
||'|'|| d.DATABASE_ROLE
||'|'|| d.open_mode
||'|'|| dh.file#
||'|'|| round((dh.CHECKPOINT_TIME-to_date('01.01.1970','dd.mm.yyyy'))*24*60*60)
||'|'|| round((sysdate-dh.CHECKPOINT_TIME)*24*60*60)
||'|'|| dh.STATUS
||'|'|| dh.RECOVER
||'|'|| dh.FUZZY
||'|'|| dh.CHECKPOINT_CHANGE#
FROM V\$datafile_header dh, v\$database d, v\$instance i
ORDER BY dh.file#;
"
fi
}
sql_rman() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
local archive_target="'PRIMARY'"
if [ "$NUMERIC_ORACLE_VERSION" -ge 122 ]; then
archive_target="'PRIMARY', 'LOCAL'"
fi
echo 'PROMPT <<<oracle_rman:sep(124)>>>'
echo "select /*$HINT_RMAN check_mk rman1 */ upper(name)
|| '|'|| 'COMPLETED'
|| '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
|| '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
|| '|'|| case when INCREMENTAL_LEVEL IS NULL
then 'DB_FULL'
else 'DB_INCR'
end
|| '|'|| INCREMENTAL_LEVEL
|| '|'|| round(((sysdate-COMPLETION_TIME) * 24 * 60), 0)
|| '|'|| INCREMENTAL_CHANGE#
from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name)) name
, bd2.INCREMENTAL_LEVEL, bd2.INCREMENTAL_CHANGE#, min(bd2.COMPLETION_TIME) COMPLETION_TIME
from (select bd.file#, bd.INCREMENTAL_LEVEL, max(bd.COMPLETION_TIME) COMPLETION_TIME
from v\$backup_datafile bd
join v\$datafile_header dh on dh.file# = bd.file#
where dh.status = 'ONLINE'
and dh.con_id <> 2
group by bd.file#, bd.INCREMENTAL_LEVEL
) bd
join v\$backup_datafile bd2 on bd2.file# = bd.file#
and bd2.COMPLETION_TIME = bd.COMPLETION_TIME
join v\$database vd on vd.RESETLOGS_CHANGE# = bd2.RESETLOGS_CHANGE#
join v\$instance i on 1=1
group by upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
, bd2.INCREMENTAL_LEVEL
, bd2.INCREMENTAL_CHANGE#
order by name, bd2.INCREMENTAL_LEVEL);
select /*$HINT_RMAN check_mk rman2 */ name
|| '|' || 'COMPLETED'
|| '|'
|| '|' || to_char(CHECKPOINT_TIME, 'yyyy-mm-dd_hh24:mi:ss')
|| '|' || 'CONTROLFILE'
|| '|'
|| '|' || round((sysdate - CHECKPOINT_TIME) * 24 * 60)
|| '|' || '0'
from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
,max(bcd.CHECKPOINT_TIME) CHECKPOINT_TIME
from v\$database d
join V\$BACKUP_CONTROLFILE_DETAILS bcd on d.RESETLOGS_CHANGE# = bcd.RESETLOGS_CHANGE#
join v\$instance i on 1=1
group by upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
);
select /*$HINT_RMAN check_mk rman3 */ name
|| '|COMPLETED'
|| '|'|| to_char(sysdate, 'YYYY-mm-dd_HH24:MI:SS')
|| '|'|| to_char(completed, 'YYYY-mm-dd_HH24:MI:SS')
|| '|ARCHIVELOG||'
|| round((sysdate - completed)*24*60,0)
|| '|'
from (
select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
, max(a.completion_time) completed
, case when a.backup_count > 0 then 1 else 0 end
from v\$archived_log a, v\$database d, v\$instance i
where a.backup_count > 0
and a.dest_id in
(select b.dest_id
from v\$archive_dest b
where b.target IN (${archive_target})
and b.SCHEDULE = 'ACTIVE'
)
group by d.NAME, i.instance_name
, case when a.backup_count > 0 then 1 else 0 end);"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
echo 'PROMPT <<<oracle_rman:sep(124)>>>'
echo "select /*${HINT_RMAN} check_mk rman1 */ upper(name)
|| '|'|| 'COMPLETED'
|| '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
|| '|'|| to_char(COMPLETION_TIME, 'YYYY-mm-dd_HH24:MI:SS')
|| '|'|| case when INCREMENTAL_LEVEL IS NULL
then 'DB_FULL'
else 'DB_INCR'
end
|| '|'|| INCREMENTAL_LEVEL
|| '|'|| round(((sysdate-COMPLETION_TIME) * 24 * 60), 0)
|| '|'|| INCREMENTAL_CHANGE#
from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name)) name
, bd2.INCREMENTAL_LEVEL, bd2.INCREMENTAL_CHANGE#, min(bd2.COMPLETION_TIME) COMPLETION_TIME
from (select bd.file#, bd.INCREMENTAL_LEVEL, max(bd.COMPLETION_TIME) COMPLETION_TIME
from v\$backup_datafile bd
join v\$datafile_header dh on dh.file# = bd.file#
where dh.status = 'ONLINE'
group by bd.file#, bd.INCREMENTAL_LEVEL
) bd
join v\$backup_datafile bd2 on bd2.file# = bd.file#
and bd2.COMPLETION_TIME = bd.COMPLETION_TIME
join v\$database vd on vd.RESETLOGS_CHANGE# = bd2.RESETLOGS_CHANGE#
join v\$instance i on 1=1
group by upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
, bd2.INCREMENTAL_LEVEL
, bd2.INCREMENTAL_CHANGE#
order by name, bd2.INCREMENTAL_LEVEL);
select /*${HINT_RMAN} check_mk rman2 */ name
|| '|' || 'COMPLETED'
|| '|'
|| '|' || to_char(CHECKPOINT_TIME, 'yyyy-mm-dd_hh24:mi:ss')
|| '|' || 'CONTROLFILE'
|| '|'
|| '|' || round((sysdate - CHECKPOINT_TIME) * 24 * 60)
|| '|' || '0'
from (select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
,max(bcd.CHECKPOINT_TIME) CHECKPOINT_TIME
from v\$database d
join V\$BACKUP_CONTROLFILE_DETAILS bcd on d.RESETLOGS_CHANGE# = bcd.RESETLOGS_CHANGE#
join v\$instance i on 1=1
group by upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
);
select /*${HINT_RMAN} check_mk rman3 */ name
|| '|COMPLETED'
|| '|'|| to_char(sysdate, 'YYYY-mm-dd_HH24:MI:SS')
|| '|'|| to_char(completed, 'YYYY-mm-dd_HH24:MI:SS')
|| '|ARCHIVELOG||'
|| round((sysdate - completed)*24*60,0)
|| '|'
from (
select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name)) name
, max(a.completion_time) completed
, case when a.backup_count > 0 then 1 else 0 end
from v\$archived_log a, v\$database d, v\$instance i
where a.backup_count > 0
and a.dest_id in
(select b.dest_id
from v\$archive_dest b
where b.target = 'PRIMARY'
and b.SCHEDULE = 'ACTIVE'
)
group by d.NAME, i.instance_name
, case when a.backup_count > 0 then 1 else 0 end);"
fi
}
sql_recovery_area() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
echo 'PROMPT <<<oracle_recovery_area:sep(124)>>>'
echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
||'|'|| round((SPACE_USED-SPACE_RECLAIMABLE)/
(CASE NVL(SPACE_LIMIT,1) WHEN 0 THEN 1 ELSE SPACE_LIMIT END)*100)
||'|'|| round(SPACE_LIMIT/1024/1024)
||'|'|| round(SPACE_USED/1024/1024)
||'|'|| round(SPACE_RECLAIMABLE/1024/1024)
||'|'|| d.FLASHBACK_ON
from V\$RECOVERY_FILE_DEST, v\$database d, v\$instance i;"
fi
}
sql_undostat() {
echo 'PROMPT <<<oracle_undostat:sep(124)>>>'
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo "select decode(vp.con_id, null, upper(i.INSTANCE_NAME)
,upper(i.INSTANCE_NAME || '.' || vp.name))
||'|'|| ACTIVEBLKS
||'|'|| MAXCONCURRENCY
||'|'|| TUNED_UNDORETENTION
||'|'|| maxquerylen
||'|'|| NOSPACEERRCNT
from v\$instance i
join
(select * from v\$undostat
where TUNED_UNDORETENTION > 0
order by end_time desc
fetch next 1 rows only
) u on 1=1
left outer join v\$pdbs vp on vp.con_id = u.con_id;
"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
echo "select upper(i.INSTANCE_NAME)
||'|'|| ACTIVEBLKS
||'|'|| MAXCONCURRENCY
||'|'|| TUNED_UNDORETENTION
||'|'|| maxquerylen
||'|'|| NOSPACEERRCNT
from v\$instance i,
(select * from (select *
from v\$undostat order by end_time desc
)
where rownum = 1
and TUNED_UNDORETENTION > 0
);"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 92 ]; then
# TUNED_UNDORETENTION and ACTIVEBLKS are not availibe in Oracle <=9.2!
# we sent a -1 for filtering in check_undostat
echo "select upper(i.INSTANCE_NAME)
||'|-1'
||'|'|| MAXCONCURRENCY
||'|-1'
||'|'|| maxquerylen
||'|'|| NOSPACEERRCNT
from v\$instance i,
(select * from (select *
from v\$undostat order by end_time desc
)
where rownum = 1
);"
fi
}
sql_resumable() {
echo 'PROMPT <<<oracle_resumable:sep(124)>>>'
echo "SET SERVEROUTPUT ON feedback off
DECLARE
type x is table of varchar2(20000) index by pls_integer;
xx x;
begin
begin
execute immediate 'select upper(i.INSTANCE_NAME)
||''|''|| u.username
||''|''|| a.SESSION_ID
||''|''|| a.status
||''|''|| a.TIMEOUT
||''|''|| round((sysdate-to_date(a.SUSPEND_TIME,''mm/dd/yy hh24:mi:ss''))*24*60*60)
||''|''|| a.ERROR_NUMBER
||''|''|| to_char(to_date(a.SUSPEND_TIME, ''mm/dd/yy hh24:mi:ss''),''mm/dd/yy_hh24:mi:ss'')
||''|''|| a.RESUME_TIME
||''|''|| a.ERROR_MSG
from dba_resumable a, v\$instance i, dba_users u
where a.INSTANCE_ID = i.INSTANCE_NUMBER
and u.user_id = a.user_id
and a.SUSPEND_TIME is not null
union all
select upper(i.INSTANCE_NAME)
|| ''|||||||||''
from v\$instance i'
bulk collect into xx;
if xx.count >= 1 then
for i in 1 .. xx.count loop
dbms_output.put_line(xx(i));
end loop;
end if;
exception
when others then
for cur1 in (select upper(i.instance_name) instance_name from v\$instance i) loop
dbms_output.put_line(cur1.instance_name || '| Debug: '||sqlerrm);
end loop;
end;
END;
/
set serverout off"
}
sql_jobs() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo 'PROMPT <<<oracle_jobs:sep(124)>>>'
echo "SET SERVEROUTPUT ON feedback off
DECLARE
type x is table of varchar2(20000) index by pls_integer;
xx x;
begin
begin
execute immediate 'SELECT upper(vp.name)
||''|''|| j.OWNER
||''|''|| j.JOB_NAME
||''|''|| j.STATE
||''|''|| ROUND((TRUNC(sysdate) + j.LAST_RUN_DURATION - TRUNC(sysdate)) * 86400)
||''|''|| j.RUN_COUNT
||''|''|| j.ENABLED
||''|''|| NVL(j.NEXT_RUN_DATE, to_date(''1970-01-01'', ''YYYY-mm-dd''))
||''|''|| NVL(j.SCHEDULE_NAME, ''-'')
||''|''|| jd.STATUS
FROM cdb_scheduler_jobs j
JOIN ( SELECT vp.con_id
,d.name || ''|'' || vp.name name
FROM v\$containers vp
JOIN v\$database d on 1=1
WHERE d.cdb = ''YES'' and vp.con_id <> 2
AND d.database_role = ''PRIMARY''
AND d.open_mode = ''READ WRITE''
UNION ALL
SELECT 0, name
FROM v\$database d
WHERE d.database_role = ''PRIMARY''
AND d.open_mode = ''READ WRITE''
) vp on j.con_id = vp.con_id
left outer join (SELECT con_id, owner, job_name, max(LOG_ID) log_id
FROM cdb_scheduler_job_run_details dd
group by con_id, owner, job_name
) jm on jm.JOB_NAME = j.JOB_NAME
and jm.owner=j.OWNER
and jm.con_id = j.con_id
left outer join cdb_scheduler_job_run_details jd
on jd.con_id = jm.con_id
AND jd.owner = jm.OWNER
AND jd.JOB_NAME = jm.JOB_NAME
AND jd.LOG_ID = jm.LOG_ID
WHERE not (j.auto_drop = ''TRUE'' and REPEAT_INTERVAL is null)'
bulk collect into xx;
if xx.count >= 1 then
for i in 1 .. xx.count loop
dbms_output.put_line(xx(i));
end loop;
end if;
exception
when others then
for cur1 in (select upper(name) name from v\$database) loop
dbms_output.put_line(cur1.name || '| Debug (121): ' ||sqlerrm);
end loop;
end;
END;
/
set serverout off"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
echo 'PROMPT <<<oracle_jobs:sep(124)>>>'
echo "SET SERVEROUTPUT ON feedback off
DECLARE
type x is table of varchar2(20000) index by pls_integer;
xx x;
begin
begin
execute immediate 'SELECT upper(decode(${IGNORE_DB_NAME:-0}, 0, vd.NAME, i.instance_name))
||''|''|| j.OWNER
||''|''|| j.JOB_NAME
||''|''|| j.STATE
||''|''|| ROUND((TRUNC(sysdate) + j.LAST_RUN_DURATION - TRUNC(sysdate)) * 86400)
||''|''|| j.RUN_COUNT
||''|''|| j.ENABLED
||''|''|| NVL(j.NEXT_RUN_DATE, to_date(''1970-01-01'', ''YYYY-mm-dd''))
||''|''|| NVL(j.SCHEDULE_NAME, ''-'')
||''|''|| jd.STATUS
FROM dba_scheduler_jobs j
join v\$database vd on 1 = 1
join v\$instance i on 1 = 1
left outer join (SELECT owner, job_name, max(LOG_ID) log_id
FROM dba_scheduler_job_run_details dd
group by owner, job_name
) jm on jm.JOB_NAME = j.JOB_NAME
and jm.owner=j.OWNER
left outer join dba_scheduler_job_run_details jd
on jd.owner = jm.OWNER
AND jd.JOB_NAME = jm.JOB_NAME
AND jd.LOG_ID = jm.LOG_ID
WHERE vd.database_role = ''PRIMARY''
AND vd.open_mode = ''READ WRITE''
AND not (j.auto_drop = ''TRUE'' and REPEAT_INTERVAL is null)'
bulk collect into xx;
if xx.count >= 1 then
for i in 1 .. xx.count loop
dbms_output.put_line(xx(i));
end loop;
end if;
exception
when others then
for cur1 in (select upper(name) name from v\$database) loop
dbms_output.put_line(cur1.name || '| Debug (102): ' ||sqlerrm);
end loop;
end;
END;
/
set serverout off"
fi
}
sql_ts_quotas() {
echo 'PROMPT <<<oracle_ts_quotas:sep(124)>>>'
echo "select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
||'|'|| Q.USERNAME
||'|'|| Q.TABLESPACE_NAME
||'|'|| Q.BYTES
||'|'|| Q.MAX_BYTES
from dba_ts_quotas Q, v\$database d, v\$instance i
where max_bytes > 0
union all
select upper(decode(${IGNORE_DB_NAME:-0}, 0, d.NAME, i.instance_name))
||'|||'
from v\$database d, v\$instance i
order by 1;"
}
sql_version() {
echo 'PROMPT <<<oracle_version>>>'
echo "select upper(i.INSTANCE_NAME)
|| ' ' || banner
from v\$version, v\$instance i
where banner like 'Oracle%';"
}
sql_systemparameter() {
# TODO In the future, all data needed for HW/SW inventory, should
# implement a "persist:$UNTIL" to reduce amount of data transmission
echo "PROMPT <<<oracle_systemparameter:sep(124)>>>"
echo "select upper(i.instance_name)
|| '|' || NAME
|| '|' || DISPLAY_VALUE
|| '|' || ISDEFAULT
from v\$system_parameter, v\$instance i
where name not like '!_%' ESCAPE '!';"
}
sql_instance() {
echo 'prompt <<<oracle_instance:sep(124)>>>'
if [ "$NUMERIC_ORACLE_VERSION" -ge 180 ]; then
# Oracle 18c introduced another version column with Release Update information
# version version_full
# 18.0.0.0.0 18.5.0.0.0
# 19.0.0.0.0 19.5.0.0.0
local version_column="version_full"
else
local version_column="version"
fi
if [ "${ORACLE_SID:0:1}" = '+' ]; then
# ASM
echo "select upper(i.instance_name)
|| '|' || i.${version_column}
|| '|' || i.STATUS
|| '|' || i.LOGINS
|| '|' || i.ARCHIVER
|| '|' || round((sysdate - i.startup_time) * 24*60*60)
|| '|' || '0'
|| '|' || 'NO'
|| '|' || 'ASM'
|| '|' || 'NO'
|| '|' || i.instance_name
|| '|' || i.host_name
from v\$instance i;"
# The next query is special: recovery_status is only available from 12.1.0.2 on, that's why we need
# a higher precision oracle version
# https://docs.oracle.com/database/121/REFRN/GUID-A399F608-36C8-4DF0-9A13-CEE25637653E.htm#REFRN30652
elif [ "$NUMERIC_ORACLE_VERSION_FOUR_PARTS" -ge 12102 ]; then
echo "select upper(instance_name)
|| '|' || version
|| '|' || status
|| '|' || logins
|| '|' || archiver
|| '|' || round((sysdate - startup_time) * 24*60*60)
|| '|' || dbid
|| '|' || log_mode
|| '|' || database_role
|| '|' || force_logging
|| '|' || name
|| '|' || to_char(created, 'ddmmyyyyhh24mi')
|| '|' || upper(value)
|| '|' || con_id
|| '|' || pname
|| '|' || pdbid
|| '|' || popen_mode
|| '|' || prestricted
|| '|' || ptotal_time
|| '|' || precovery_status
|| '|' || round(nvl(popen_time, -1))
|| '|' || pblock_size
|| '|' || host_name
from(
select i.instance_name, i.host_name, i.${version_column} version, i.status, i.logins, i.archiver
,i.startup_time, d.dbid, d.log_mode, d.database_role, d.force_logging
,d.name, d.created, p.value, vp.con_id, vp.name pname
,vp.dbid pdbid, vp.open_mode popen_mode, vp.restricted prestricted, vp.total_size ptotal_time
,vp.block_size pblock_size, vp.recovery_status precovery_status
,(cast(systimestamp as date) - cast(open_time as date)) * 24*60*60 popen_time
from v\$instance i
join v\$database d on 1=1
join v\$parameter p on 1=1
join v\$pdbs vp on 1=1
where p.name = 'enable_pluggable_database'
union all
select
i.instance_name, i.host_name, i.${version_column} version, i.status, i.logins, i.archiver
,i.startup_time, d.dbid, d.log_mode, d.database_role, d.force_logging
,d.name, d.created, p.value, 0 con_id, null pname
,0 pdbis, null popen_mode, null prestricted, null ptotal_time
,0 pblock_size, null precovery_status, null popen_time
from v\$instance i
join v\$database d on 1=1
join v\$parameter p on 1=1
where p.name = 'enable_pluggable_database'
order by con_id
);
"
else
# normal Instance
echo "select upper(i.instance_name)
|| '|' || i.VERSION
|| '|' || i.STATUS
|| '|' || i.LOGINS
|| '|' || i.ARCHIVER
|| '|' || round((sysdate - i.startup_time) * 24*60*60)
|| '|' || DBID
|| '|' || LOG_MODE
|| '|' || DATABASE_ROLE
|| '|' || FORCE_LOGGING
|| '|' || d.name
|| '|' || to_char(d.created, 'ddmmyyyyhh24mi')
|| '|' || i.host_name
from v\$instance i, v\$database d;"
fi
}
sql_sessions() {
echo 'prompt <<<oracle_sessions:sep(124)>>>'
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo "SELECT upper(vp.name)
|| '|' || ltrim(COUNT(1))
|| decode(vp.con_id
, 0, '|'||ltrim(rtrim(LIMIT_VALUE))||'|-1')
FROM ( SELECT vp.con_id
,i.instance_name || '.' || vp.name name
FROM v\$containers vp
JOIN v\$instance i ON 1 = 1
JOIN v\$database d on 1=1
WHERE d.cdb = 'YES' and vp.con_id <> 2
UNION ALL
SELECT 0, instance_name
FROM v\$instance
) vp
JOIN v\$resource_limit rl on RESOURCE_NAME = 'sessions'
LEFT OUTER JOIN v\$session vs ON vp.con_id = vs.con_id
GROUP BY vp.name, vp.con_id, rl.LIMIT_VALUE
ORDER BY 1;"
else
echo "select upper(i.instance_name)
|| '|' || CURRENT_UTILIZATION
|| '|' || ltrim(LIMIT_VALUE)
|| '|' || MAX_UTILIZATION
from v\$resource_limit, v\$instance i
where RESOURCE_NAME = 'sessions';"
fi
}
sql_processes() {
echo 'prompt <<<oracle_processes:sep(124)>>>'
echo "select upper(i.instance_name)
|| '|' || CURRENT_UTILIZATION
|| '|' || ltrim(rtrim(LIMIT_VALUE))
from v\$resource_limit, v\$instance i
where RESOURCE_NAME = 'processes';"
}
sql_logswitches() {
echo 'prompt <<<oracle_logswitches:sep(124)>>>'
echo "select upper(i.instance_name)
|| '|' || logswitches
from v\$instance i ,
(select count(1) logswitches
from v\$loghist h , v\$instance i
where h.first_time > sysdate - 1/24
and h.thread# = i.instance_number
);"
}
sql_locks() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo 'prompt <<<oracle_locks:sep(124)>>>'
echo "select upper(vp.name)
|| '|' || b.sid
|| '|' || b.serial#
|| '|' || b.machine
|| '|' || b.program
|| '|' || b.process
|| '|' || b.osuser
|| '|' || b.username
|| '|' || b.SECONDS_IN_WAIT
|| '|' || b.BLOCKING_SESSION_STATUS
|| '|' || bs.inst_id
|| '|' || bs.sid
|| '|' || bs.serial#
|| '|' || bs.machine
|| '|' || bs.program
|| '|' || bs.process
|| '|' || bs.osuser
|| '|' || bs.username
from v\$session b
join gv\$session bs on bs.inst_id = b.BLOCKING_INSTANCE
and bs.sid = b.BLOCKING_SESSION
and bs.con_id = b.con_id
join ( SELECT vp.con_id
,i.instance_name || '.' || vp.name name
FROM v\$containers vp
JOIN v\$instance i ON 1 = 1
JOIN v\$database d on 1=1
WHERE d.cdb = 'YES' and vp.con_id <> 2
UNION ALL
SELECT 0, instance_name
FROM v\$instance
) vp on b.con_id = vp.con_id
where b.BLOCKING_SESSION is not null;
SELECT upper(i.instance_name || '.' || vp.name)
|| '|||||||||||||||||'
FROM v\$containers vp
JOIN v\$instance i ON 1 = 1
JOIN v\$database d on 1=1
WHERE d.cdb = 'YES' and vp.con_id <> 2
UNION ALL
SELECT upper(i.instance_name)
|| '|||||||||||||||||'
FROM v\$instance i;
"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
echo 'prompt <<<oracle_locks:sep(124)>>>'
echo "select upper(i.instance_name)
|| '|' || b.sid
|| '|' || b.serial#
|| '|' || b.machine
|| '|' || b.program
|| '|' || b.process
|| '|' || b.osuser
|| '|' || b.username
|| '|' || b.SECONDS_IN_WAIT
|| '|' || b.BLOCKING_SESSION_STATUS
|| '|' || bs.inst_id
|| '|' || bs.sid
|| '|' || bs.serial#
|| '|' || bs.machine
|| '|' || bs.program
|| '|' || bs.process
|| '|' || bs.osuser
|| '|' || bs.username
from v\$session b
join v\$instance i on 1=1
join gv\$session bs on bs.inst_id = b.BLOCKING_INSTANCE
and bs.sid = b.BLOCKING_SESSION
where b.BLOCKING_SESSION is not null;
select upper(i.instance_name)
|| '|||||||||||||||||'
from v\$instance i;"
fi
}
sql_locks_old() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
echo 'prompt <<<oracle_locks:sep(124)>>>'
echo "SET SERVEROUTPUT ON feedback off
DECLARE
type x is table of varchar2(20000) index by pls_integer;
xx x;
begin
begin
execute immediate 'select upper(i.instance_name)
|| ''|'' || a.sid
|| ''|'' || b.serial#
|| ''|'' || b.machine
|| ''|'' || b.program
|| ''|'' || b.process
|| ''|'' || b.osuser
|| ''|'' || a.ctime
|| ''|'' || decode(c.owner,NULL,''NULL'',c.owner)
|| ''|'' || decode(c.object_name,NULL,''NULL'',c.object_name)
from V\$LOCK a, v\$session b, dba_objects c, v\$instance i
where (a.id1, a.id2, a.type)
IN (SELECT id1, id2, type
FROM GV\$LOCK
WHERE request>0
)
and request=0
and a.sid = b.sid
and a.id1 = c.object_id (+)
union all
select upper(i.instance_name) || ''|||||||||''
from v\$instance i'
bulk collect into xx;
if xx.count >= 1 then
for i in 1 .. xx.count loop
dbms_output.put_line(xx(i));
end loop;
end if;
exception
when others then
for cur1 in (select upper(i.instance_name) instance_name from v\$instance i) loop
dbms_output.put_line(cur1.instance_name || '| Debug (101): '||sqlerrm);
end loop;
end;
END;
/
set serverout off"
fi
}
sql_longactivesessions() {
if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; then
echo 'prompt <<<oracle_longactivesessions:sep(124)>>>'
echo "select upper(vp.name)
|| '|' || s.sid
|| '|' || s.serial#
|| '|' || s.machine
|| '|' || s.process
|| '|' || s.osuser
|| '|' || s.program
|| '|' || s.last_call_et
|| '|' || s.sql_id
from v\$session s
join ( SELECT vp.con_id
,i.instance_name || '.' || vp.name name
FROM v\$containers vp
JOIN v\$instance i ON 1 = 1
JOIN v\$database d on 1=1
WHERE d.cdb = 'YES' and vp.con_id <> 2
UNION ALL
SELECT 0, instance_name
FROM v\$instance
) vp on 1=1
where s.status = 'ACTIVE'
and s.type != 'BACKGROUND'
and s.username is not null
and s.username not in('PUBLIC')
and s.last_call_et > 60*60;
SELECT upper(i.instance_name || '.' || vp.name)
|| '||||||||'
FROM v\$containers vp
JOIN v\$instance i ON 1 = 1
JOIN v\$database d on 1=1
WHERE d.cdb = 'YES' and vp.con_id <> 2
UNION ALL
SELECT upper(i.instance_name)
|| '||||||||'
FROM v\$instance i;
"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 101 ]; then
echo 'prompt <<<oracle_longactivesessions:sep(124)>>>'
echo "select upper(i.instance_name)
|| '|' || s.sid
|| '|' || s.serial#
|| '|' || s.machine
|| '|' || s.process
|| '|' || s.osuser
|| '|' || s.program
|| '|' || s.last_call_et
|| '|' || s.sql_id
from v\$session s, v\$instance i
where s.status = 'ACTIVE'
and type != 'BACKGROUND'
and s.username is not null
and s.username not in('PUBLIC')
and s.last_call_et > 60*60
union all
select upper(i.instance_name)
|| '||||||||'
from v\$instance i;"
fi
}
sql_asm_diskgroup() {
echo 'prompt <<<oracle_asm_diskgroup:sep(124)>>>'
if [ "$NUMERIC_ORACLE_VERSION" -ge 112 ]; then
echo "SELECT g.state
|| '|' || g.type
|| '|' || g.name
|| '|' || g.BLOCK_SIZE
|| '|' || g.ALLOCATION_UNIT_SIZE
|| '|' || g.REQUIRED_MIRROR_FREE_MB
|| '|' || sum(d.total_mb)
|| '|' || sum(d.free_mb)
|| '|' || d.failgroup
|| '|' || max(d.VOTING_FILE)
|| '|' || d.FAILGROUP_TYPE
|| '|' || g.offline_disks
|| '|' || min(decode(d.REPAIR_TIMER, 0, 8640000, d.REPAIR_TIMER))
|| '|' || count(*)
FROM v\$asm_diskgroup g
LEFT OUTER JOIN v\$asm_disk d on d.group_number = g.group_number
and d.group_number = g.group_number
and d.group_number <> 0
GROUP BY g.name
, g.state
, g.type
, d.failgroup
, d.VOTING_FILE
, g.BLOCK_SIZE
, g.ALLOCATION_UNIT_SIZE
, g.REQUIRED_MIRROR_FREE_MB
, g.offline_disks
, d.FAILGROUP_TYPE
, d.REPAIR_TIMER
ORDER BY g.name, d.failgroup;"
elif [ "$NUMERIC_ORACLE_VERSION" -ge 102 ]; then
echo "select STATE
|| '|' || TYPE
|| '|' || 'N'
|| '|' || sector_size
|| '|' || block_size
|| '|' || allocation_unit_size
|| '|' || total_mb
|| '|' || free_mb
|| '|' || required_mirror_free_mb
|| '|' || usable_file_mb
|| '|' || offline_disks
|| '|' || 'N'
|| '|' || name || '/'
from v\$asm_diskgroup;"
fi
}
# .
# .--custom SQL----------------------------------------------------------.
# | _ ____ ___ _ |
# | ___ _ _ ___| |_ ___ _ __ ___ / ___| / _ \| | |
# | / __| | | / __| __/ _ \| '_ ` _ \ \___ \| | | | | |
# | | (__| |_| \__ \ || (_) | | | | | | ___) | |_| | |___ |
# | \___|\__,_|___/\__\___/|_| |_| |_| |____/ \__\_\_____| |
# | |
# '----------------------------------------------------------------------'
unset_custom_sqls_vars() {
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
unset SQLS_DBUSER SQLS_DBPASSWORD SQLS_DBSYSCONNECT SQLS_TNSALIAS SQLS_ITEM_SID
}
sid_matches_defined_sids() {
# first parameter is a list of coma separated values (SQLS_SIDS)
# second parameter is a single value (current sid)
# split $1 into array on , or newline
IFS=$',\n' read -d '' -ra sids_array <<<"$1"
# check if array contains $2
printf '%s\n' "${sids_array[@]}" | "${GREP}" -F -x -- "$2" >/dev/null
}
do_custom_sqls() {
for section in $custom_sqls_sections; do
if ! type "$section" >/dev/null 2>&1; then
logging -w "[${MK_SID}] [custom_sql] [${section}]" \
"Definition of '${section}' not found in configuration"
continue
fi
$section
local sids="${SQLS_SIDS:-$custom_sqls_sids}"
# If SID is not part of sids we can skip the rest
if ! sid_matches_defined_sids "$sids" "$MK_SID"; then
logging -w "[${MK_SID}] [custom_sql] [${section}]" \
"Skipping this section, runs only on SIDs '$sids'"
unset_custom_sqls_vars
continue
fi
local section_name=${SQLS_SECTION_NAME:-$custom_sqls_section_name}
local sql_dir=${SQLS_DIR:-$custom_sqls_dir}
local sql=${SQLS_SQL:-$custom_sqls_sql}
# If no section name, SQL dir or file is stated or SQL file does not exist we skip the rest
if [ -z "$section_name" ]; then
logging -w "[${MK_SID}] [custom_sql] [${section}]" \
"Empty section name"
unset_custom_sqls_vars
continue
fi
if [ ! -d "$sql_dir" ] || [ ! -r "$sql_dir" ]; then
logging -w "[${MK_SID}] [custom_sql] [${section}]" \
"SQL folder '${sql_dir}' not found or not readable"
unset_custom_sqls_vars
continue
fi
if [ ! -f "${sql_dir}/$sql" ] || [ ! -r "${sql_dir}/$sql" ]; then
logging -w "[${MK_SID}] [custom_sql] [${section}]" \
"SQL file '${sql_dir}/$sql' not found or not readable"
unset_custom_sqls_vars
continue
fi
if [ -n "$SQLS_SECTION_SEP" ]; then
local section_sep="$SQLS_SECTION_SEP"
elif [ -n "$custom_sqls_section_sep" ]; then
local section_sep="$custom_sqls_section_sep"
else
local section_sep=
fi
if [ "$section_name" == "oracle_sql" ]; then
local section_header="${section_name}:sep(58)"
elif [ -n "${section_sep}" ]; then
local section_header="${section_name}:sep(${section_sep})"
else
local section_header="$section_name"
fi
if [ "$section_name" = "oracle_sql" ]; then
if [[ "$MK_SID" =~ ^REMOTE_INSTANCE_.* ]]; then
if [ -z "$SQLS_ITEM_SID" ]; then
# we can not extract MK_SID from REMOTE_INSTANCE_ variable name
# get MK_SID from mk_oracle.cfg when SQLS_ITEM_SID is not defined
# => needed when tnsnames.ora is used without SID in CFGLINE...
local SQLS_ITEM_SID
SQLS_ITEM_SID=$(eval "echo \${$MK_SID}" | cut -d":" -f7)
fi
else
# local connection mode
local SQLS_ITEM_SID="$MK_SID"
fi
logging "[${MK_SID}] [custom_sql] [${section}]" \
"SQLS_ITEM_SID: $SQLS_ITEM_SID"
if [ -n "$SQLS_ITEM_NAME" ]; then
local item="${SQLS_ITEM_SID}|${SQLS_ITEM_NAME}"
else
local item="${SQLS_ITEM_SID}|${sql}"
fi
else
local item=
fi
local parameters=${SQLS_PARAMETERS:-$custom_sqls_parameters}
if $MK_DEBUG_MODE; then
local max_cache_age=
elif [ -n "$SQLS_MAX_CACHE_AGE" ]; then
local max_cache_age="$SQLS_MAX_CACHE_AGE"
else
local max_cache_age="$custom_sqls_max_cache_age"
fi
if [ -n "$SQLS_DBUSER" ]; then
# set custom credentials from section
db_connect=$(mk_ora_db_connect "$MK_SID")
export MK_DB_CONNECT=$db_connect
fi
unset_custom_sqls_vars
MK_CUSTOM_SQLS_SECTION="$section"
export MK_CUSTOM_SQLS_SECTION_HEADER="$section_header"
export MK_CUSTOM_SQLS_ITEM="$item"
MK_CUSTOM_SQLS_SECTION_QUERY=$(custom_sql_section "$sql_dir" "$sql" "$parameters")
export MK_CUSTOM_SQLS_SECTION_QUERY
logging "[${MK_SID}] [custom_sql] [${section}]" \
"Section name : $MK_CUSTOM_SQLS_SECTION" \
"Section header : $MK_CUSTOM_SQLS_SECTION_HEADER" \
"Section item : $MK_CUSTOM_SQLS_ITEM" \
"Max cache age : $max_cache_age" \
"Custom query : $MK_CUSTOM_SQLS_SECTION_QUERY"
if $MK_ORA_DEBUG_CONNECT; then
do_testmode_custom_sql
else
if [ -z "$max_cache_age" ]; then
local output=
output=$(echo -e "$MK_CUSTOM_SQLS_SECTION_QUERY" | mk_ora_sqlplus "do_custom_sqls" "yes")
handle_custom_sql_errors "$output"
else
run_cached "$max_cache_age" do_async_custom_sqls "_custom_sql_${MK_CUSTOM_SQLS_SECTION}" "$section_name"
fi
fi
done
}
custom_sql_section() {
local sql_dir="$1"
local sql="$2"
local params="$3"
local sql_content=
echo "PROMPT <<<${MK_CUSTOM_SQLS_SECTION_HEADER}>>>"
if [ -n "$MK_CUSTOM_SQLS_ITEM" ]; then
echo "PROMPT [[[${MK_CUSTOM_SQLS_ITEM}]]]"
fi
echo -e "SET VERIFY OFF;"
echo -e "SET TERMOUT ON;"
echo -e "SET serveroutput ON;"
if [ -n "$params" ]; then
echo -e "$params"
fi
sql_content=$(cat "$sql_dir/$sql")
echo -e "$sql_content"
}
do_async_custom_sqls() {
local output=
output=$(echo -e "$MK_CUSTOM_SQLS_SECTION_QUERY" | mk_ora_sqlplus "do_async_custom_sqls" "yes")
logging "[${MK_SID}] [${MK_CUSTOM_SQLS_SECTION_QUERY}] [do_async_custom_sql]" "Output: $output"
handle_custom_sql_errors "$output"
}
handle_custom_sql_errors() {
local output="$1"
local errors=
errors=$(echo -e "$output" | ${GREP} -e "ERROR at line" -e "ORA-" -e "SP2-" | tr '\n' ' ')
if [ -n "$errors" ]; then
echo "<<<${MK_CUSTOM_SQLS_SECTION_HEADER}>>>"
if [ -n "$MK_CUSTOM_SQLS_ITEM" ]; then
echo "[[[${MK_CUSTOM_SQLS_ITEM}]]]"
fi
# connection error already returns '$SID|FAILURE|' in mk_ora_sqplplus
errors=${errors#*FAILURE|}
echo "$(echo "$MK_SID" | tr '[:lower:]' '[:upper:]')|FAILURE|$errors"
else
echo "$output"
fi
}
# .
# .--helper--------------------------------------------------------------.
# | _ _ |
# | | |__ ___| |_ __ ___ _ __ |
# | | '_ \ / _ \ | '_ \ / _ \ '__| |
# | | | | | __/ | |_) | __/ | |
# | |_| |_|\___|_| .__/ \___|_| |
# | |_| |
# '----------------------------------------------------------------------'
# BEGIN COMMON PLUGIN CODE
# check that no users other than root can change the file
only_root_can_modify() {
permissions=$1
owner=$2
group=$3
group_write_perm=$(echo "$permissions" | cut -c 6)
other_write_perm=$(echo "$permissions" | cut -c 9)
if [ "$owner" != "root" ] || [ "$other_write_perm" != "-" ]; then
return 1
fi
[ "$group" = "root" ] || [ "$group_write_perm" = "-" ]
}
get_binary_owner() {
BINARY_PATH=$1
stat -c '%U' "${BINARY_PATH}"
}
get_binary_execution_mode() {
BINARY_PATH=$1
BINARY_USER=$2
# if the executable belongs to someone besides root, do not execute it as root
if needs_user_switch_before_executing "$BINARY_PATH"; then
echo "su ${BINARY_USER} -c"
return
fi
echo "bash -c"
}
needs_user_switch_before_executing() {
BINARY_PATH=$1
[ "$(whoami)" = "root" ] && ! only_root_can_modify "$(stat -c '%A' "$BINARY_PATH")" "$(stat -c '%U' "$BINARY_PATH")" "$(stat -c '%G' "$BINARY_PATH")"
}
# END COMMON PLUGIN CODE
get_crs_home_from_olrloc() {
"${GREP}" "crs_home" "${1}" | cut -d"=" -f2
}
get_sqlplus_version_with_precision() {
precision="$1"
sqlplus_path="${ORACLE_HOME}"/bin/sqlplus
EXECUTION_MODE="$(get_binary_execution_mode "$sqlplus_path" "$(get_binary_owner "$sqlplus_path")")"
$EXECUTION_MODE "\"${sqlplus_path}\" -V" | ${GREP} ^SQL | cut -d" " -f3 | cut -d"." -f-"${precision}"
}
print_dummy_sections() {
for section in $SYNC_SECTIONS $ASYNC_SECTIONS $SYNC_ASM_SECTIONS $ASYNC_ASM_SECTIONS; do
echo "<<<oracle_${section}>>>"
done
}
do_dummy_sections() {
if [ -n "$MK_ORA_SECTIONS" ]; then
return
fi
print_dummy_sections
for piggyback_host in $PIGGYBACK_HOSTS; do
echo "<<<<${piggyback_host}>>>>"
print_dummy_sections
echo "<<<<>>>>"
done
}
skip_sid() {
local sid="$1"
if [ "$ONLY_SIDS" ]; then
[[ " $ONLY_SIDS " != *" $sid "* ]]
return
fi
if [ "$SKIP_SIDS" ]; then
[[ " $SKIP_SIDS " == *" $sid "* ]]
return
fi
EXCLUDE=EXCLUDE_$sid
# Handle explicit exclusion of instances but not for +ASM
if [[ "$EXCLUDE" =~ ^[a-zA-Z][a-zA-Z0-9_]*$ ]]; then
EXCLUDE=${!EXCLUDE}
[ "$EXCLUDE" = "ALL" ]
return
fi
false
}
remove_excluded_sections() {
# We exclude instance section because we have already executed
# the sql_instance section at the beginning for every SID.
local sections="$1"
local excluded="$2"
for section in $sections; do
if [[ "$excluded" != *"$section"* ]]; then
echo "$section"
fi
done
}
ora_session_environment() {
echo 'set pages 0 trimspool on feedback off lines 8000'
if [ "$NUMERIC_ORACLE_VERSION" -ge 102 ] && [ ! "$DISABLE_ORA_SESSION_SETTINGS" ]; then
echo 'set echo off'
echo 'alter session set "_optimizer_mjc_enabled"=false;'
# cursor_sharing is not valid for ASM instances
if [ ! "${ORACLE_SID:0:1}" = '+' ]; then
echo 'alter session set cursor_sharing=exact;'
fi
echo 'set echo on'
fi
#TODO Do not exit after first error otherwise
# section specific errors won't be seen any more.
#echo 'whenever sqlerror exit 1'
echo ' '
}
# .
# .--run cached----------------------------------------------------------.
# | _ _ |
# | _ __ _ _ _ __ ___ __ _ ___| |__ ___ __| | |
# | | '__| | | | '_ \ / __/ _` |/ __| '_ \ / _ \/ _` | |
# | | | | |_| | | | | | (_| (_| | (__| | | | __/ (_| | |
# | |_| \__,_|_| |_| \___\__,_|\___|_| |_|\___|\__,_| |
# | |
# '----------------------------------------------------------------------'
run_cached() {
if $MK_DEBUG_MODE; then
# Just to be sure
return
fi
MAXAGE="${1}"
# Attention: CMD will contain the shell function to be executed. Make sure to export all needed functions during
# that execution (see e.g. export -f logging)
CMD="${2}"
IDENTIFIER="${3}"
SECTION_NAME="${4}"
if [ "${SECTION_NAME}" = "oracle_sql" ]; then
NAME="oracle_${MK_SID}${IDENTIFIER}_${MAXAGE}"
else
NAME="oracle_${MK_SID}${IDENTIFIER}"
fi
CREATION_TIMEOUT=$((MAXAGE * 2))
OUTPUT_TIMEOUT=$((MAXAGE * 3))
REFRESH_INTERVAL="${MAXAGE}"
[ -d "${MK_VARDIR}/cache" ] || mkdir -p "${MK_VARDIR}/cache"
CACHEFILE="${MK_VARDIR}/cache/${NAME}.cache"
NOW="$(get_epoch)"
MTIME=$(get_file_mtime "${CACHEFILE}" 2>/dev/null) || MTIME=0
if [ -s "${CACHEFILE}" ] && [ $((NOW - MTIME)) -le "${OUTPUT_TIMEOUT}" ]; then
# Output the file (if it is not too outdated)
CACHE_INFO="cached(${MTIME},${MAXAGE})"
# prefix or insert cache info, unless already present.
# WATCH OUT: AIX does not allow us to pass this as a single '-e' option!
if [ "${SECTION_NAME}" = "oracle_sql" ]; then
sed -e "s/^\[\[\[\(.*\)\]\]\]$/[[[\1|${CACHE_INFO}]]]/g" "${CACHEFILE}"
elif [ "${NAME%%_*}" = "local" ] || [ "${NAME%%_*}" = "mrpe" ]; then
sed -e '/^<<<.*>>>/{p;d;}' -e '/^cached([0-9]*,[0-9]*) /{p;d;}' -e "s/^/${CACHE_INFO} /" "${CACHEFILE}"
else
sed -e '/^<<<.*\(:cached(\).*>>>/{p;d;}' -e 's/^<<<\([^>]*\)>>>$/<<<\1:'"${CACHE_INFO}"'>>>/' "${CACHEFILE}"
fi
fi
# Error information about plugin, if available.
# Output independent of cachefile existance
#
# Watch out!
# You cannot consolidate the error reporting with the agents.
# *This* way of error reporting is not suitable for the regular agents, because
# it might interrupt a 'local' or 'mrpe' section -- the agents use the SPOOL DIR
# for that. However, the SPOOL DIR is not known to this plugin.
[ -s "${CACHEFILE}.fail" ] && cat "${CACHEFILE}.fail"
# Kill the process if it is running too long (cache file not accessed for more than CREATION_TIMEOUT seconds).
# If killing succeeds, remove CACHFILE.new.PID.
# Write info about the timed out process and the kill attempt to CACHEFILE.fail.
# It will be reported to the Check_MK agent service later on, by the sync part.
# CACHEFILE.fail will be deleted as soon as the plugin/local check is functional again.
for cfile in "${CACHEFILE}.new."*; do
[ -e "${cfile}" ] || break # no match
TRYING_SINCE="$(get_file_atime "${cfile}")"
[ -n "${TRYING_SINCE}" ] || break # race condition: file vanished
if [ $((NOW - TRYING_SINCE)) -ge "${CREATION_TIMEOUT}" ]; then
{
echo "<<<checkmk_cached_plugins:sep(124)>>>"
pid="${cfile##*.new.}"
printf "timeout|%s|%s|%s\n" "${NAME}" "${CREATION_TIMEOUT}" "${pid}"
# Workaround for AIX to kill child processes
if [ "$OS_TYPE" = 'AIX' ] && [ -x "$(command -v proctree 2>/dev/null)" ]; then
# shellcheck disable=SC2016
pidlist="$(proctree "$pid" 2>/dev/null | $AWK '{ printf $1" "}')"
fi
pidlist="${pidlist:-"${pid}"}"
# shellcheck disable=SC2086
kill -9 ${pidlist} >/dev/null 2>&1 && sleep 2 # TODO: what about child processes under non-AIX systems?
if [ -n "$(ps -o args= -p "${pid}")" ]; then
printf "killfailed|%s|%s|%s\n" "${NAME}" "${CREATION_TIMEOUT}" "${pid}"
else
rm -f "${cfile}"
fi
} >"${CACHEFILE}".fail 2>&1
fi
done
# This does the right thing, regardless whether the pattern matches!
_cfile_in_use() {
for cfile in "${CACHEFILE}.new."*; do
printf "%s\n" "${cfile}"
break
done
}
# Time to refresh cache file and new job not yet running?
if [ $((NOW - MTIME)) -gt "${REFRESH_INTERVAL}" ] && [ ! -e "$(_cfile_in_use)" ]; then
THIS_SHELL="$(ps -o args= -p $$ | sed -e 's/^-//' -e 's/\ .*//')"
# Start it. If the command fails the output is thrown away
if $MK_ORA_DEBUG; then
cat <<HERE | "${THIS_SHELL}"
exec > "${CACHEFILE}.new.\$\$" || exit 1
${CMD} && mv "${CACHEFILE}.new.\$\$" "${CACHEFILE}" && rm -f "${CACHEFILE}".fail || rm -f "${CACHEFILE}.new.\$\$"
HERE
else
cat <<HERE | nohup "${THIS_SHELL}" >/dev/null 2>&1 &
exec > "${CACHEFILE}.new.\$\$" || exit 1
${CMD} && mv "${CACHEFILE}.new.\$\$" "${CACHEFILE}" && rm -f "${CACHEFILE}".fail || rm -f "${CACHEFILE}.new.\$\$"
HERE
fi
fi
unset NAME MAXAGE CREATION_TIMEOUT REFRESH_INTERVAL CACHEFILE NOW MTIME CACHE_INFO TRYING_SINCE OUTPUT_TIMEOUT
}
# .
# .--DB connect----------------------------------------------------------.
# | ____ ____ _ |
# | | _ \| __ ) ___ ___ _ __ _ __ ___ ___| |_ |
# | | | | | _ \ / __/ _ \| '_ \| '_ \ / _ \/ __| __| |
# | | |_| | |_) | | (_| (_) | | | | | | | __/ (__| |_ |
# | |____/|____/ \___\___/|_| |_|_| |_|\___|\___|\__| |
# | |
# '----------------------------------------------------------------------'
mk_ora_db_connect() {
local sid="$1"
local CFGTNSALIAS=""
ORADBUSER=""
DBPASSWORD=""
offset=${offset:-0}
if [[ "$sid" =~ ^REMOTE_INSTANCE_.* ]]; then
# working on REMOTE_-Mode!
ORACFGLINE=$(eval "echo \${$1}")
ORACLE_SID=$(echo "${ORACFGLINE}" | cut -d":" -f7)
CFGTNSALIAS=$(echo "${ORACFGLINE}" | cut -d":" -f9)
else
# working with locally running instances
TNSALIAS=${ORACLE_SID}
# ASM use '+' as 1st character in SID!
if [ "${ORACLE_SID:0:1}" = '+' ]; then
ORACFGLINE=${ASMUSER}
else
# use an individuel user or the default DBUSER from mk_oracle.cfg
# It's possible that the config file uses only upper case, but
# the fetched SID is lower case. And vice versa. To handle this, we
# need to try both cases.
# Important: This does not cover config variables that uses both
# upper and lower case letters. We simply cannot handle that!
#
# Disabling shellcheck because false-positive warning regarding
# single quotes. AWK needs these single quotes to work as inteded.
# shellcheck disable=SC2016
dummy_upper="DBUSER_$(echo "$ORACLE_SID" | "$AWK" '{ print toupper($0) }')"
# shellcheck disable=SC2016
dummy_lower="DBUSER_$(echo "$ORACLE_SID" | "$AWK" '{ print tolower($0) }')"
if [ "${!dummy_upper}" = '' ]; then
if [ "${!dummy_lower}" = '' ]; then
ORACFGLINE=${DBUSER}
else
ORACFGLINE=${!dummy_lower}
fi
else
ORACFGLINE=${!dummy_upper}
fi
fi
CFGTNSALIAS=$(echo "${ORACFGLINE}" | cut -d":" -f$((6 + offset)))
fi
if [ -n "$SQLS_DBUSER" ]; then
ORADBUSER=${SQLS_DBUSER}
DBPASSWORD=${SQLS_DBPASSWORD}
DBSYSCONNECT=${SQLS_DBSYSCONNECT:-}
else
ORADBUSER=$(echo "${ORACFGLINE}" | cut -d":" -f$((1 + offset)))
DBPASSWORD=$(echo "${ORACFGLINE}" | cut -d":" -f$((2 + offset)))
DBSYSCONNECT=$(echo "${ORACFGLINE}" | cut -d":" -f$((3 + offset)))
fi
DBHOST=$(echo "${ORACFGLINE}" | cut -d":" -f$((4 + offset)))
DBPORT=$(echo "${ORACFGLINE}" | cut -d":" -f$((5 + offset)))
if [ -f $OLRLOC ] && [ -n "$crs_home" ]; then
if [ -d "$crs_home" ]; then
# we found GI/Restart
# => Use hostname instead of localhost
DBHOST=${DBHOST:-$(hostname)}
logging "[${sid}] [mk_ora_db_connect]" "OLR detected. crs_home: $crs_home"
else
DBHOST=${DBHOST:-"localhost"}
logging "[${sid}] [mk_ora_db_connect]" "OLR detected, crs_home missing"
fi
else
DBHOST=${DBHOST:-"localhost"}
logging "[${sid}] [mk_ora_db_connect]" "Single Instance"
fi
if [ -n "$CFGTNSALIAS" ]; then
logging "[${sid}] [mk_ora_db_connect]" "TNS CFGTNSALIAS: $CFGTNSALIAS"
fi
TNSPINGOK=no
if [ -f "${TNS_ADMIN}/tnsnames.ora" ]; then
if [ "${ORADBUSER:0:2}" = '/@' ]; then
P_TNSALIAS_P=$(eval echo "${ORADBUSER:2}")
else
# use TNSALIAS from ORACFGLINE or ORACLE_SID when empty
P_TNSALIAS_P=${CFGTNSALIAS:-${ORACLE_SID}}
fi
# PREFIX / POSTFIX makes no sense for ASM
# => only 1 Instance with known connection possible
if [ -n "$P_TNSALIAS_P" ] && [ ! "${ORACLE_SID:0:1}" = '+' ]; then
PREFIX_SID="PREFIX_$sid"
PREFIX_SID=${!PREFIX_SID}
if [ -n "$PREFIX_SID" ]; then
P_TNSALIAS_P="$PREFIX_SID$P_TNSALIAS_P"
elif [ -n "$PREFIX" ]; then
P_TNSALIAS_P="$PREFIX$P_TNSALIAS_P"
fi
POSTFIX_SID="POSTFIX_$sid"
POSTFIX_SID=${!POSTFIX_SID}
if [ -n "$POSTFIX_SID" ]; then
P_TNSALIAS_P="$P_TNSALIAS_P$POSTFIX_SID"
elif [ -n "$POSTFIX" ]; then
P_TNSALIAS_P="$P_TNSALIAS_P$POSTFIX"
fi
fi
TNSALIAS=${SQLS_TNSALIAS:-${P_TNSALIAS_P}}
logging "[${sid}] [mk_ora_db_connect]" "TNS Alias PING: $TNSALIAS"
tnsping_path="${ORACLE_HOME}"/bin/tnsping
if [ -f "${tnsping_path}" ]; then
EXECUTION_MODE="$(get_binary_execution_mode "$tnsping_path" "$(get_binary_owner "$tnsping_path")")"
if $EXECUTION_MODE "\"${tnsping_path}\" \"$TNSALIAS\"" >/dev/null 2>&1; then
TNSPINGOK=yes
else
unset TNSALIAS
fi
else
# if the binary tnsping does not exist we can not check whether the
# database is reachable or not, so we assume the database is
# reachable:
TNSPINGOK=yes
fi
else
if [ -z "$CFGTNSALIAS" ]; then
unset TNSALIAS
else
TNSALIAS=${SQLS_TNSALIAS:-${CFGTNSALIAS}}
fi
fi
logging "[${sid}] [mk_ora_db_connect]" \
"ORA DB user : $ORADBUSER" \
"DB sys connect : $DBSYSCONNECT" \
"DB host : $DBHOST" \
"DB port : $DBPORT" \
"TNS alias : $TNSALIAS" \
"TNS PING : ${TNSPINGOK}"
if [ ! "${ORACFGLINE}" ]; then
# no configuration found
# => use the wallet with tnsnames.ora or EZCONNECT
TNSALIAS=${TNSALIAS:-"localhost:1521/${ORACLE_SID}"}
else
if [ "${DBSYSCONNECT}" ]; then
assysdbaconnect=" as "${DBSYSCONNECT}
fi
TNSALIAS=${TNSALIAS:-"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=${DBHOST})(PORT=${DBPORT:-1521}))(CONNECT_DATA=(SID=${ORACLE_SID})(SERVER=DEDICATED)(UR=A)))"}
# ORADBUSER = '/'? => ignore DBPASSWORD and use the wallet
if [ "${ORADBUSER:0:1}" = '/' ]; then
# connect with / and wallet
ORADBUSER=""
DBPASSWORD=""
if [ -z "$CFGTNSALIAS" ] && [ "$TNSPINGOK" = 'no' ]; then
# create an EZCONNECT string when no tnsnames.ora is usable
# or no alias is configured in CFGTNSALIAS
# defaults to localhost:1521/<ORACLE_SID>
TNSALIAS="${DBHOST}:${DBPORT:-1521}/${ORACLE_SID}"
fi
fi
fi
logging "[${sid}] [mk_ora_db_connect]" "DB connection: ${ORADBUSER}/${DBPASSWORD//?/*}@${TNSALIAS}${assysdbaconnect}"
echo "${ORADBUSER}/${DBPASSWORD}@${TNSALIAS}${assysdbaconnect}"
}
# .
# .--mk ora sqlplus------------------------------------------------------.
# | _ _ _ |
# | _ __ ___ | | __ ___ _ __ __ _ ___ __ _| |_ __ | |_ _ ___ |
# | | '_ ` _ \| |/ / / _ \| '__/ _` | / __|/ _` | | '_ \| | | | / __| |
# | | | | | | | < | (_) | | | (_| | \__ \ (_| | | |_) | | |_| \__ \ |
# | |_| |_| |_|_|\_\ \___/|_| \__,_| |___/\__, |_| .__/|_|\__,_|___/ |
# | |_| |_| |
# '----------------------------------------------------------------------'
mk_ora_sqlplus() {
# Executes a SQL query by using sqlplus binary.
# The query will be piped-in and consumed via cat - so always execute cat at the very beginning of the function
function_stdin="$(cat)"
local from_where="$1"
local print_elapsed_time="$2"
local start_time=
local elapsed_time=
local output=
logging "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "Piggyback host: $MK_PIGGYBACK_HOST"
start_time="$(perl -MTime::HiRes=time -wle 'print time')"
read -r -d '' pipe_input <<EOM
WHENEVER SQLERROR EXIT 1
connect ${MK_DB_CONNECT}
WHENEVER SQLERROR CONTINUE
$(ora_session_environment)${function_stdin}
EOM
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
if [ ! -x "${SQLPLUS}" ]; then
logging -w -e "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "SQLplus '${SQLPLUS}' not found or ORACLE_HOME '${ORACLE_HOME}' wrong."
return 1
fi
if [ -n "$MK_PIGGYBACK_HOST" ]; then
echo "<<<<${MK_PIGGYBACK_HOST}>>>>"
fi
EXECUTION_USER="$(get_binary_owner "$SQLPLUS")"
EXECUTION_MODE="$(get_binary_execution_mode "$SQLPLUS" "$EXECUTION_USER")"
if ! $EXECUTION_MODE "test -r \"${TNS_ADMIN}/sqlnet.ora\""; then
# we can not read sqlnet.ora!
# set_ora_env already checked that the file exists
# and we want to transport a meaningful error message to the ui and in logging
local sqlnet_ora_error="${TNS_ADMIN}/sqlnet.ora can not be read by user \"${EXECUTION_USER}\"! Either use 'sqlnet.ora permission group' bakery rule, or directly modify permissions of the file."
logging -c -e "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "$sqlnet_ora_error"
echo '<<<oracle_instance:sep(124)>>>'
echo "$(echo "$MK_SID" | tr '[:lower:]' '[:upper:]')|FAILURE|$sqlnet_ora_error"
exit 1
fi
if output=$(echo "$pipe_input" | $EXECUTION_MODE "\"$SQLPLUS\" -L -s /nolog"); then
echo -e "$output"
elapsed_time=$(bc <<<"$(perl -MTime::HiRes=time -wle 'print time')-$start_time")
if [ "$print_elapsed_time" = "yes" ]; then
echo "elapsed:$elapsed_time"
fi
logging "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "Elapsed time: $elapsed_time"
else
output=$(
echo -e "$output" | "${GREP}" -v "^ERROR at line" | tr '\n' ' ' |
sed "s/^/$(echo "$MK_SID" | tr '[:lower:]' '[:upper:]')|FAILURE|/"
echo
)
logging -w "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "Found '^ERROR at line'"
if $MK_ORA_DEBUG_CONNECT; then
echo " Logindetails: ${MK_DB_CONNECT}" >&2
echo -e " Error Message: ${output:0:100}"
exit 1
else
echo '<<<oracle_instance:sep(124)>>>'
echo -e "$output"
echo
fi
fi
if [ -n "$MK_PIGGYBACK_HOST" ]; then
echo "<<<<>>>>"
fi
}
# .
# .--do checks-----------------------------------------------------------.
# | _ _ _ |
# | __| | ___ ___| |__ ___ ___| | _____ |
# | / _` |/ _ \ / __| '_ \ / _ \/ __| |/ / __| |
# | | (_| | (_) | | (__| | | | __/ (__| <\__ \ |
# | \__,_|\___/ \___|_| |_|\___|\___|_|\_\___/ |
# | |
# '----------------------------------------------------------------------'
# Create one SQL statements for several sections and run
# these with sqlplus. The exitcode is preserved.
do_sync_checks() {
logging "[${MK_SID}] [do_sync_checks]" "Query: $MK_SYNC_SECTIONS_QUERY"
echo "$MK_SYNC_SECTIONS_QUERY" | mk_ora_sqlplus "do_sync_checks"
}
do_async_checks() {
logging "[${MK_SID}] [do_async_checks]" "Query: $MK_ASYNC_SECTIONS_QUERY"
echo "$MK_ASYNC_SECTIONS_QUERY" | mk_ora_sqlplus "do_async_checks"
}
do_suggest_group() {
local MODE=$1
local USER=$2
local FILE=$3
if id -Gn "$USER" | "${GREP}" '\boinstall\b' >/dev/null; then
echo " We suggest to change the group to oinstall and give $MODE permission for the group:"
echo " chgrp oinstall \"$FILE\""
local m="x"
if [ "$MODE" = "read" ]; then
m="r"
fi
echo " chmod g+$m \"$FILE\""
fi
}
do_test_file_permission() {
local MODE=$1
local USER=$2
local FILE=$3
local m="-x"
if [ "$MODE" = "read" ]; then
m="-r"
fi
if su "${USER}" -c "test $m \"$FILE\""; then
echo "* user \"$USER\" can $MODE $FILE"
true
else
echo "* ERROR! user \"$USER\" can NOT $MODE $FILE"
echo " $(ls -dl "$FILE")"
do_suggest_group "$MODE" "$USER" "$FILE"
echo
false
fi
}
do_test_permissions() {
local DEFAULT_SQLNET="LOG_DIRECTORY_CLIENT=/var/log/check_mk/oracle_clientDIAG_ADR_ENABLED=OFFSQLNET.WALLET_OVERRIDE=TRUEWALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/etc/check_mk/oracle_wallet)))"
echo
echo "---checking permissions-------------------------------------------------"
echo "see https://checkmk.atlassian.net/wiki/spaces/KB/pages/70582273/Troubleshooting+mk+oracle+for+Windows+and+Linux"
echo
local SQLPLUS="${ORACLE_HOME}/bin/sqlplus"
echo "* sqlplus binary: ${SQLPLUS}"
local BINARY_OWNER
BINARY_OWNER="$(get_binary_owner "$SQLPLUS")"
echo "* sqlplus binary owner: ${BINARY_OWNER}"
if ! needs_user_switch_before_executing "$SQLPLUS"; then
echo "* change user: false"
echo "------------------------------------------------------------------------"
return
fi
echo "* change user: true"
echo "* \$TNS_ADMIN: ${TNS_ADMIN}"
local PATH_SQLNET="$TNS_ADMIN/sqlnet.ora"
local PATH_TNSNAMES="$TNS_ADMIN/tnsnames.ora"
if ! do_test_file_permission "read" "$BINARY_OWNER" "$PATH_SQLNET"; then
echo " If you use the AGENT BAKERY you have to use the rule 'sqlnet.ora premission group' to make this change permanently, otherwise it will be overwritten by an agent update."
fi
if [ -f "$PATH_TNSNAMES" ]; then
do_test_file_permission "read" "$BINARY_OWNER" "$PATH_TNSNAMES"
fi
echo
local GENERIC_ERROR_MESSAGE="
Could not login. In case you are using a wallet to connect, there might be a permission error.
Make sure that the wallet folder can be read and executed by user \"$BINARY_OWNER\" and
the files inside the wallet can be read by the user.
Consult your ora files for hints where the wallet is located:
$PATH_SQLNET
$PATH_TNSNAMES
"
# let's go all in and see if the connection works:
if error=$(echo "" | mk_ora_sqlplus "do_testmode" "no" 2>&1); then
echo "* test login works"
else
echo "* test-login does not work!"
if echo "$error" | ${GREP} "ORA-12578" >/dev/null; then
echo " ORA-12578 suggests, that there is an error reading the wallet."
if [ "$(sed '/^#/d' </etc/check_mk/sqlnet.ora | tr -d '\n' | sed -e 's/\s\+//g')" == "$DEFAULT_SQLNET" ]; then
echo " Detected sqlnet.ora as suggested in the documentation."
local WALLET_PATH="/etc/check_mk/oracle_wallet"
if [ ! -d "$WALLET_PATH" ]; then
echo
echo "* '$WALLET_PATH' does not exist or is not a directory!"
else
echo
do_test_file_permission "read" "$BINARY_OWNER" "$WALLET_PATH"
do_test_file_permission "execute" "$BINARY_OWNER" "$WALLET_PATH"
for f in "$WALLET_PATH"/{c,e}wallet.*; do
do_test_file_permission "read" "$BINARY_OWNER" "$f"
done
fi
else
echo "$GENERIC_ERROR_MESSAGE"
fi
else
echo "$GENERIC_ERROR_MESSAGE"
fi
fi
echo "------------------------------------------------------------------------"
}
do_testmode() {
local sections=$1
local asections=$2
do_test_permissions
echo
echo "---login----------------------------------------------------------------"
echo " Operating System: ${OS_TYPE}"
echo " ORACLE_HOME ${ORA_HOME_SOURCE} ${ORACLE_HOME}"
echo " Logincheck to Instance: ${MK_SID}"
echo " Version: ${ORACLE_VERSION}"
echo "select ' Login ok User: ' || user || ' on ' || host_name ||' Instance ' || instance_name
from v\$instance;" | mk_ora_sqlplus "do_testmode"
echo " SYNC_SECTIONS: $sections"
echo " ASYNC_SECTIONS: $asections"
if [ "$IGNORE_DB_NAME" ]; then
echo " IGNORE_DB_NAME found. Ignoring DB_NAME in all SQLs!"
fi
if [ "$DISABLE_ORA_SESSION_SETTINGS" ]; then
echo " Paramter DISABLE_ORA_SESSION_SETTINGS found!"
fi
if [ "$HINT_RMAN" ]; then
echo " Using HINT_RMAN for this Instance!"
fi
echo "------------------------------------------------------------------------"
echo
}
do_testmode_custom_sql() {
echo "---custom SQL ----------------------------------------------------------"
echo " section: ${section}"
echo " SQL: ${sql}"
echo " MAX_CACHE_AGE: ${max_cache_age}"
echo "------------------------------------------------------------------------"
}
fetch_custom_section_variable() {
local section_name=$1
local section_var=
local section_dummy=
local sid_upper=
local sid_lower=
# Disabling shellcheck because false-positive warning regarding
# single quotes. AWK needs these single quotes to work as inteded.
# shellcheck disable=SC2016
sid_upper=$(echo "$ORACLE_SID" | "$AWK" '{ print toupper($0) }')
# shellcheck disable=SC2016
sid_lower=$(echo "$ORACLE_SID" | "$AWK" '{ print tolower($0) }')
if declare -p "$1_${sid_upper}" >/dev/null 2>&1; then
section_dummy="$1_${sid_upper}"
section_var=${!section_dummy}
else
if declare -p "$1_${sid_lower}" >/dev/null 2>&1; then
section_dummy="$1_${sid_lower}"
section_var=${!section_dummy}
else
section_var="${!section_name}"
fi
fi
echo "$section_var"
}
do_checks() {
local sections=
local asections=
if [ "${ORACLE_SID:0:1}" = '+' ]; then
# ASM sections
sections="$SYNC_ASM_SECTIONS"
asections="$ASYNC_ASM_SECTIONS"
else
local SYNC_SECTIONS_SID=
local ASYNC_SECTIONS_SID=
local do_sync_sections=
local do_async_sections=
SYNC_SECTIONS_SID=$(fetch_custom_section_variable "SYNC_SECTIONS")
ASYNC_SECTIONS_SID=$(fetch_custom_section_variable "ASYNC_SECTIONS")
if [ "$SYNC_SECTIONS_SID" ] && [ "$MK_ORA_SECTIONS" ]; then
do_sync_sections=$(for section in $SYNC_SECTIONS_SID; do
if [[ "$MK_ORA_SECTIONS" == *"$section"* ]]; then
echo "$section"
fi
done)
else
do_sync_sections=${SYNC_SECTIONS_SID:-${SYNC_SECTIONS}}
fi
if [ "$ASYNC_SECTIONS_SID" ] && [ "$MK_ORA_SECTIONS" ]; then
do_async_sections=$(for section in $ASYNC_SECTIONS_SID; do
if [[ "$MK_ORA_SECTIONS" == *"$section"* ]]; then
echo "$section"
fi
done)
else
do_async_sections=${ASYNC_SECTIONS_SID:-${ASYNC_SECTIONS}}
fi
local excluded=
excluded=$(eval "echo \$EXCLUDE_$ORACLE_SID")
if [ "$excluded" ]; then
sections=$(remove_excluded_sections "$do_sync_sections" "$excluded")
asections=$(remove_excluded_sections "$do_async_sections" "$excluded")
else
sections="$do_sync_sections"
asections="$do_async_sections"
fi
fi
logging "[${ORACLE_SID}] [do_checks]" "Excluded: $excluded" \
"Sections: $sections" "Async sections: $asections"
if $MK_ORA_DEBUG_CONNECT; then
do_testmode "$sections" "$asections"
return
fi
if $MK_DEBUG_MODE; then
MK_SYNC_SECTIONS_QUERY=$(for section in $sections $asections; do eval "sql_$section"; done)
MK_ASYNC_SECTIONS_QUERY=
else
MK_SYNC_SECTIONS_QUERY=$(for section in $sections; do eval "sql_$section"; done)
MK_ASYNC_SECTIONS_QUERY=$(for section in $asections; do eval "sql_$section"; done)
fi
export MK_ASYNC_SECTIONS_QUERY
if [ "$MK_SYNC_SECTIONS_QUERY" ]; then
do_sync_checks
fi
if [ "$MK_ASYNC_SECTIONS_QUERY" ]; then
run_cached "$CACHE_MAXAGE" do_async_checks
fi
}
execute_queries_for_sid() {
local sid="$1"
set_ora_env "$sid"
if [ $? -eq 2 ]; then
# we have to skip this SID due to missing/unknown ORACLE_HOME
return
fi
set_ora_version
logging "[${sid}] [local]" \
"ORACLE_SID : ${ORACLE_SID}" \
"ORACLE_HOME : ${ORACLE_HOME}" \
"TNS_ADMIN : ${TNS_ADMIN}" \
"ORACLE_VERSION : ${ORACLE_VERSION} (${NUMERIC_ORACLE_VERSION})" \
"ORACLE_VERSION_FOUR_PARTS : ${NUMERIC_ORACLE_VERSION_FOUR_PARTS}"
# MK_SID is feauture replacement for sid
export MK_SID=$sid
db_connect=$(mk_ora_db_connect "$MK_SID")
export MK_DB_CONNECT=$db_connect
do_checks
# MK_DB_CONNECT could be changed by do_custom_sqls!
do_custom_sqls
}
# .
# .--main----------------------------------------------------------------.
# | _ |
# | _ __ ___ __ _(_)_ __ |
# | | '_ ` _ \ / _` | | '_ \ |
# | | | | | | | (_| | | | | | |
# | |_| |_| |_|\__,_|_|_| |_| |
# | |
# +----------------------------------------------------------------------+
# | Iterate over all instances and execute sync and async sections. |
# '----------------------------------------------------------------------'
# Make sure that the new shell that is being run by run_cached inherits
# our functions
export -f logging
export -f mk_ora_sqlplus
export -f ora_session_environment
export -f do_async_checks
export -f set_ora_env
export -f unset_custom_sqls_vars
export -f custom_sql_section
export -f do_async_custom_sqls
export -f handle_custom_sql_errors
export -f get_binary_execution_mode
export -f needs_user_switch_before_executing
export -f get_binary_owner
export -f only_root_can_modify
set_up_tmpdirs() {
ORA_TMPDIR="${MK_VARDIR}/tmp/mk_oracle_tmp_dir"
# why don't we use /tmp to make cleanup easier?
ORA_TASKS_TMPDIR="${ORA_TMPDIR}/tasks"
mkdir -p "$ORA_TASKS_TMPDIR"
}
main_queries() {
if $MK_ORA_LOGGING; then
# cleanup old leftovers
rm -f "${MK_VARDIR}"/log/mk_oracle_task_*.log
echo "Logging to file: ${MK_VARDIR}/log/mk_oracle.log" >&2
fi
logging "--------------------------------------------------------------------"
# ---preliminaries--------------------------------------------------------
set_os_env
set_up_get_epoch
# Get list of all running databases. Do not work on ASM in this plugin.
# => Ignore a running ASM-Instance!
# shellcheck disable=SC2016
SIDS=$(UNIX95=true ps -ef | "${AWK}" '{print $NF}' |
"${GREP}" -E '^asm_pmon_|^ora_pmon_|^xe_pmon_|^db_pmon_' |
cut -d"_" -f3- | sort)
# Load config AFTER we got the SIDS since we allow users to use this variable in their config
load_config
# Are there any remote configurations?
for remote_instance in $(compgen -A variable | "${GREP}" -E "^REMOTE_INSTANCE_.*"); do
REMOTE_INSTANCES="${REMOTE_INSTANCES} ${remote_instance}"
# shellcheck disable=SC2005
PIGGYBACK_HOSTS="$(echo "$(eval "echo \${$remote_instance}")" | cut -d":" -f6) ${PIGGYBACK_HOSTS}"
done
PIGGYBACK_HOSTS="$(echo "$PIGGYBACK_HOSTS" | tr ' ' '\n' | sort | uniq)"
logging "[preliminaries]" \
"SIDs : ${SIDS//$'\n'/ }" \
"Remote instances : ${REMOTE_INSTANCES}" \
"Piggyback hosts : ${PIGGYBACK_HOSTS//$'\n'/ }" \
"OS : ${OS_TYPE}" \
"SYNC_SECTIONS : ${SYNC_SECTIONS}" \
"ASYNC_SECTIONS : ${ASYNC_SECTIONS}" \
"SYNC_ASM_SECTIONS : ${SYNC_ASM_SECTIONS}" \
"ASYNC_ASM_SECTIONS : ${ASYNC_ASM_SECTIONS}" \
"CACHE_MAXAGE : ${CACHE_MAXAGE}" \
"ONLY_SIDS : ${ONLY_SIDS}" \
"SKIP_SIDS : ${SKIP_SIDS}" \
"Custom SQLs sections: $custom_sqls_sections"
# From now on we expect databases on this system (for ever)
if [ -n "$SIDS" ]; then
touch "$MK_VARDIR/mk_oracle.found"
fi
# If we do not have found any running database instance, then either
# no ORACLE is present on this system or it's just currently not running.
# In the later case we ouput empty agent sections so that Checkmk will be
# happy and execute the actual check functions.
if [ ! -e "$MK_VARDIR/mk_oracle.found" ] && [ -z "$REMOTE_INSTANCES" ]; then
logging -c -e "[preliminaries]" "\$MK_VARDIR/mk_oracle.found not found and no remote instances configured"
exit 1
fi
# Make sure that always all sections are present, even
# in case of an error. Note: the section <<<oracle_instance>>>
# section shows the general state of a database instance. If
# that section fails for an instance then all other sections
# do not contain valid data anyway.
do_dummy_sections
# ---local----------------------------------------------------------------
if [ -n "$REMOTE" ]; then
_QUERY_PREFIX="${REMOTE}-$$"
else
_QUERY_PREFIX=$$
fi
logging "[local] setting up intermediate query result directory '${ORA_TASKS_TMPDIR}/${_QUERY_PREFIX}'"
mkdir -p "${ORA_TASKS_TMPDIR}/${_QUERY_PREFIX}"
TASK_NR=0
for sid in $SIDS; do
if skip_sid "$sid"; then
logging "[${sid}] [local]" "Skipping this SID"
continue
fi
((TASK_NR++))
logging "[${sid}] [local] starting background task '${TASK_NR}'"
SID_QUERY_FILE="${ORA_TASKS_TMPDIR}/${_QUERY_PREFIX}/SID-$sid"
(execute_queries_for_sid "$sid" >"${SID_QUERY_FILE}") &
if [[ $(jobs -r -p | wc -l) -ge ${MAX_TASKS} ]]; then
logging "[${sid}] [local] max parallel task running, waiting for new slot"
if ((BASH_VERSINFO[0] > 4 || BASH_VERSINFO[0] == 4 && BASH_VERSINFO[1] >= 3)); then
wait -n
else
wait
fi
fi
if [ ! -f "${SID_QUERY_FILE}" ]; then
logging "[local] No intermediate query result file '${SID_QUERY_FILE}' exists"
fi
done
wait
cat "${ORA_TASKS_TMPDIR}/${_QUERY_PREFIX}/SID"*
cleanup_queries() {
logging "[local] cleaning up intermediate query result directory '${ORA_TASKS_TMPDIR}/${_QUERY_PREFIX}'"
rm -rf "${ORA_TASKS_TMPDIR}/${_QUERY_PREFIX:?}" >/dev/null 2>&1
logging "[local] cleaning up any old query result directories in '${ORA_TASKS_TMPDIR}'"
# in case process was previously stopped via kill -9 and could not clean up
find "${ORA_TASKS_TMPDIR}/*" -type d -mtime +1 -exec rm -r {} + >/dev/null 2>&1
}
trap cleanup_queries HUP INT QUIT ABRT ALRM TERM EXIT
# ---remote---------------------------------------------------------------
for remote_instance in $REMOTE_INSTANCES; do
remote_instance_line=$(eval "echo \${$remote_instance}")
set_ora_env "$remote_instance"
set_ora_version "$(echo "$remote_instance_line" | cut -d":" -f8)"
logging "[${remote_instance}] [remote]" \
"ORACLE_SID : ${ORACLE_SID}" \
"ORACLE_HOME : ${ORACLE_HOME}" \
"TNS_ADMIN : ${TNS_ADMIN}" \
"ORACLE_VERSION : ${ORACLE_VERSION} (${NUMERIC_ORACLE_VERSION})"
db_connect=$(mk_ora_db_connect "$remote_instance")
piggyback_host=$(echo "${remote_instance_line}" | cut -d":" -f6)
export MK_SID=$remote_instance
export MK_DB_CONNECT=$db_connect
export MK_PIGGYBACK_HOST=$piggyback_host
do_checks
do_custom_sqls
done
if ${MK_ORA_LOGGING}; then
cat "${MK_VARDIR}"/log/mk_oracle_task_*.log >>"${MK_VARDIR}"/log/mk_oracle.log
rm -f "${MK_VARDIR}"/log/mk_oracle_task_*.log
fi
}
main() {
# See werk 13732 for more details about this main function
parent_dir=$(basename "$(
cd "$(dirname "$0")" || {
echo "Can't access the parent directory" >&2
exit 1
}
pwd
)")
if $MK_ORA_NOSPOOL || [ "$parent_dir" = plugins ]; then
# Does not work properly (async sections) on distros
# where check_mk_agent.sync.service is a systemd service
main_mode() { main_queries; }
elif [[ "$parent_dir" =~ ^[1-9][0-9]* ]]; then
main_mode() {
if [ -n "$REMOTE" ]; then
TMPFILE="$ORA_TMPDIR/mk_oracle.out.${REMOTE}"
else
TMPFILE="$ORA_TMPDIR/mk_oracle.out"
fi
limit=$((parent_dir * 3))
main_queries >"$TMPFILE" && mv "$TMPFILE" "${MK_VARDIR}/spool/${limit}_mk_oracle.spool"
}
else
echo "Unhandled location" >&2
exit 1
fi
set_up_tmpdirs
main_mode
}
[ -z "${MK_SOURCE_ONLY}" ] && main