#!/bin/bash
# +------------------------------------------------------------------+
# |             ____ _               _        __  __ _  __           |
# |            / ___| |__   ___  ___| | __   |  \/  | |/ /           |
# |           | |   | '_ \ / _ \/ __| |/ /   | |\/| | ' /            |
# |           | |___| | | |  __/ (__|   <    | |  | | . \            |
# |            \____|_| |_|\___|\___|_|\_\___|_|  |_|_|\_\           |
# |                                                                  |
# | Copyright Mathias Kettner 2014             mk@mathias-kettner.de |
# +------------------------------------------------------------------+
#
# This file is part of Check_MK.
# The official homepage is at http://mathias-kettner.de/check_mk.
#
# check_mk is free software;  you can redistribute it and/or modify it
# under the  terms of the  GNU General Public License  as published by
# the Free Software Foundation in version 2.  check_mk is  distributed
# in the hope that it will be useful, but WITHOUT ANY WARRANTY;  with-
# out even the implied warranty of  MERCHANTABILITY  or  FITNESS FOR A
# PARTICULAR PURPOSE. See the  GNU General Public License for more de-
# tails. You should have  received  a copy of the  GNU  General Public
# License along with GNU Make; see the file  COPYING.  If  not,  write
# to the Free Software Foundation, Inc., 51 Franklin St,  Fifth Floor,
# Boston, MA 02110-1301 USA.

# Check_MK 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://mathias-kettner.de/cms_monitoring_oracle.html
# for more details regarding configuration.


OS_TYPE=$(uname -s)


display_usage () {
    echo ""
    echo "USAGE:"
    echo "  mk_oracle [OPTIONS]"
    echo ""
    echo "DESCRIPTION:"
    echo "  Check_MK agent plugin for monitoring ORACLE databases."
    echo "  This plugin works for Linux, Solaris and AIX."
    echo "  See http://mathias-kettner.de/cms_monitoring_oracle.html"
    echo "  for more details regarding configuration."
    echo ""
    echo "OPTIONS:"
    echo "  -h, --help              Shows this help message and exit"
    echo "  -d                      Enable full bash debugging"
    echo "  -t                      Just check the connection"
    echo "  -l, --log               Logs certain steps while execution"
    echo "                          ('flock' needs to be installed)"
    echo "  -s, --sections S1,S2,.. Only execute stated sections"
    echo "                          Note: asynchronous sections are execute"
    echo "                          as synchronous sections"
    echo ""
}


while test -n "$1"; do
    case "$1" in
        -h|--help)
            display_usage >&2
            exit 0
            ;;

        -d)
            set -xv
            MK_ORA_DEBUG=1
            shift
            ;;

        -t)
            MK_ORA_DEBUG_CONNECT=1
            shift
            ;;

        -l|--log)
            if type flock > /dev/null 2>&1; then
                MK_ORA_LOGGING=1
                export MK_ORA_LOGGING
            else
                echo "You cannot use the option '--log': flock needs to be installed."
                echo "Please run mk_oracle without '--log'."
                display_usage >&2
                exit 2
            fi
            shift
            ;;

        -s|--sections)
            shift
            MK_ORA_SECTIONS=($(echo "$1" | tr ',' '\n'))
            shift
            ;;

        *)
            shift
            ;;
    esac
done


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


#TODO ma: Logging texte + stellen pruefen
#TODO si: fehlerhandling raus => check plugins
#TODO si: log file size ??
#TODO mk_ora_db_connect: aufraeumen und def local vars


#   .--OS env--------------------------------------------------------------.
#   |                     ___  ____                                        |
#   |                    / _ \/ ___|    ___ _ ____   __                    |
#   |                   | | | \___ \   / _ \ '_ \ \ / /                    |
#   |                   | |_| |___) | |  __/ | | \ V /                     |
#   |                    \___/|____/   \___|_| |_|\_/                      |
#   |                                                                      |
#   '----------------------------------------------------------------------'

file_mtime () {
    /usr/bin/perl -e 'if (! -f $ARGV[0]){die "0000000"};$mtime=(stat($ARGV[0]))[9];print ($mtime);' "$1"
}


set_os_env () {
    if [ "$OS_TYPE" = 'Linux' ]; then
        GREP=$(which grep)
        AWK=$(which awk)
        STATCX='stat -c %X'
        STATCY='stat -c %Y'

    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 "[set_os_env]" "Please make sure that ${GREP} is existing on Solaris. Aborting mk_oracle plugin."
            echo "Please make sure that $GREP is existing on Solaris!" >&2
            echo "Aborting mk_oracle plugin." >&2
            exit 127
        fi

        AWK=$(which nawk)
        STATCX='file_mtime'
        STATCY='file_mtime'

    elif [ "$OS_TYPE" = 'AIX' ]; then
        GREP=$(which grep)
        AWK=$(which awk)
        STATCX='file_mtime'
        STATCY='file_mtime'

    elif [ "$OS_TYPE" = 'HP-UX' ]; then
        GREP=$(which grep)
        AWK=$(which awk)
        STATCX="perl -le 'print((stat shift)[8])'"
        STATCY="perl -le 'print((stat shift)[9])'"

    else
        logging -c "[set_os_env]" "Unsupported OS: ${OS_TYPE}"
        echo "Unsupported OS: $OS_TYPE" >&2
        exit 1

    fi

    export GREP AWK STATCX STATCY
}

#.
#   .--config--------------------------------------------------------------.
#   |                                      __ _                            |
#   |                      ___ ___  _ __  / _(_) __ _                      |
#   |                     / __/ _ \| '_ \| |_| |/ _` |                     |
#   |                    | (_| (_) | | | |  _| | (_| |                     |
#   |                     \___\___/|_| |_|_| |_|\__, |                     |
#   |                                           |___/                      |
#   '----------------------------------------------------------------------'


# Sections that run fast and do no caching
SYNC_SECTIONS="instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance locks"

# Sections that are run in the background and at a larger interval.
# Note: sections not listed in SYNC_SECTIONS or ASYNC_SECTIONS will not be
# executed at all!
ASYNC_SECTIONS="tablespaces rman jobs ts_quotas resumable"

# Sections that are run in the background and at a larger interval.
# Note: _ASM_ sections are only executed when SID starts with '+'
#       sections listed in SYNC_SECTIONS or ASYNC_SECTIONS are not
#       executed for ASM.
SYNC_ASM_SECTIONS="instance processes"
ASYNC_ASM_SECTIONS="asm_diskgroup"

# Interval for running async checks (in seconds)
CACHE_MAXAGE=600

# set OLRLOC=/dev/null in mk_oracle.cfg to 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.
OLRLOC=/etc/oracle/olr.loc

# You can specify a list of SIDs to monitor. Those databases will
# only be handled, if they are found running, though!
#
#   ONLY_SIDS="XE ORCL FOO BAR"
#
# You can specify a list of SIDs to be skipped.
#
#   SKIP_SIDS="FOO BAR"
#
# It is possible to filter SIDS negatively. Just add the following to
# the mk_oracle.cfg file:
#
#   EXCLUDE_<sid>="ALL"
#
# Another option is to filter single checks for SIDS. Just add
# lines as follows to the mk_oracle.cfg file. One service per
# line:
#
#   EXCLUDE_<sid>="<service>"
#
# For example skip oracle_sessions and oracle_logswitches checks
# for the instance "mysid".
#
#   EXCLUDE_mysid="sessions logswitches"
#
# NOTE: SKIP_SIDS same as 'EXCLUDE_<SID>=ALL' but
# also +ASM instances are possible to be excluded:
#   SKIP_SIDS="+ASM1 +ASM2"
# vs.
#   EXCLUDE_+ASM=ALL
#   => No valid variable indirection


# Source the optional configuration file for this agent plugin
if [ -e "$MK_CONFDIR/mk_oracle.cfg" ]; then
    # shellcheck source=/dev/null
    . "$MK_CONFDIR/mk_oracle.cfg"
fi


#.
#   .--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)         ?
#
# 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" == "1" ]; then
        (
            flock 200

            local log_file="$MK_VARDIR/log/mk_oracle.log"
            local criticality=
            local args=
            local header=

            let 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
                        ;;

                    *)
                        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"
            else
                for arg in "${args[@]:1}"; do
                    echo "${header} $arg" >> "$log_file"
                done
            fi

        ) 200>|/tmp/mk_oracle.lock
    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
            # shellcheck source=/dev/null
            . ${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
                # get ORACLE_HOME with crsctl from Oracle Grid Infrastructure / Restart
                ORACLE_HOME=$("${crs_home}"/bin/crsctl stat res -p -w "((TYPE = ora.database.type) and (GEN_USR_ORA_INST_NAME = ${ORACLE_SID}))" | grep "^ORACLE_HOME=" | cut -d"=" -f2)
            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 "[${sid}] [set_ora_env]" "ORA-99999 oratab not found in local mode"
                echo "ORA-99999 oratab not found" >&2
                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

    LD_LIBRARY_PATH=$ORACLE_HOME/lib
    if [ ! -d "${ORACLE_HOME:-'not_found'}" ]; then
        logging -c "[${sid}] [set_ora_env]" "ORA-99999 ORACLE_HOME for ORACLE_SID=${ORACLE_SID} not found or not existing!"
        echo "ORA-99999 ORACLE_HOME for ORACLE_SID=${ORACLE_SID} not found or not existing!" >&2
        return 2
    fi

    TNS_ADMIN=${TNS_ADMIN:-$MK_CONFDIR}
    if ! test -f "${TNS_ADMIN}/sqlnet.ora"; then
        logging -c "[${sid}] [set_ora_env]" "TNS_ADMIN/sqlnet.ora: ${TNS_ADMIN}/sqlnet.ora"
        echo "ORA-99998 Couldn't find ${TNS_ADMIN}/sqlnet.ora" >&2
        exit 1
    fi
    export ORACLE_HOME TNS_ADMIN ORACLE_SID LD_LIBRARY_PATH
}


set_ora_version () {
    ORACLE_VERSION="$1"
    NUMERIC_ORACLE_VERSION=${ORACLE_VERSION//./}
    export NUMERIC_ORACLE_VERSION

}

#.
#   .--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.                                                |
#   '----------------------------------------------------------------------'

#TODO Create subsections in query and parse them in related check plugin.
sql_performance () {


    if [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; 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 ||'.'||vd.name)
                     ||'|'|| 'sys_time_model'
                     ||'|'|| S.STAT_NAME
                     ||'|'|| Round(s.value/1000000)
              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
              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;"

    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
              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 v\$managed_standby ms on ms.process = 'MRP0'
              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#
                     ||'|'|| vb.STATUS
                     ||'|'|| round((sysdate-vb.TIME)*24*60*60)
              FROM  V\$datafile_header dh
              JOIN v\$database d on 1=1
              JOIN v\$instance i on 1=1
              JOIN v\$backup vb on 1=1
              LEFT OUTER JOIN V\$PDBS vp on dh.con_id = vp.con_id
              WHERE vb.file# = dh.file#
              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

        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 = 'PRIMARY'
                             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_instance () {
    echo 'prompt <<<oracle_instance:sep(124)>>>'
    if [ "${ORACLE_SID:0:1}" = '+' ]; then
        # ASM
        echo "select upper(i.instance_name)
                     || '|' || i.VERSION
                     || '|' || i.STATUS
                     || '|' || i.LOGINS
                     || '|' || i.ARCHIVER
                     || '|' || round((sysdate - i.startup_time) * 24*60*60)
                     || '|' || '0'
                     || '|' || 'NO'
                     || '|' || 'ASM'
                     || '|' || 'NO'
                     || '|' || i.instance_name
                from v\$instance i;"

    elif [ "$NUMERIC_ORACLE_VERSION" -ge 121 ]; 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
                  from(
                      select i.instance_name, i.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.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')
                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----------------------------------------------------------.
#   |                       _                    ____   ___  _             |
#   |         ___ _   _ ___| |_ ___  _ __ ___   / ___| / _ \| |            |
#   |        / __| | | / __| __/ _ \| '_ ` _ \  \___ \| | | | |            |
#   |       | (__| |_| \__ \ || (_) | | | | | |  ___) | |_| | |___         |
#   |        \___|\__,_|___/\__\___/|_| |_| |_| |____/ \__\_\_____|        |
#   |                                                                      |
#   '----------------------------------------------------------------------'

# ==== Configuration of custom SQLs
#   == Globals ==
#     SQLS_SECTIONS=secA,secB,...           (comma separated)
#
#   == Globals and locals ==
#     SQLS_SECTION_NAME=sec_name            (optional; if not stated, 'oracle_sql' is used)
#     SQLS_SECTION_SEP=separator            (optional; only useful if SQLS_SECTION_NAME is stated;
#                                            ASCII code, eg. '58' means ':')
#     SQLS_SID=sid1,sid2,...                (comma separated)
#     SQLS_DIR=/path/to/dir                 (no trailing '/')
#     SQLS_SQL=name.sql                     ()
#     SQLS_PARAMETERS=params                (parameters as string, double quotes are removed)
#     SQLS_MAX_CACHE_AGE=X                  (x in seconds)
#
#   == Locals ==
#     SQLS_ITEM_NAME=item_name              (optional, if not stated, sql name is used)
#
#   A section is a function which may contain some of above parameters, eg:
#   my_sec () {
#       SQLS_SID=sid1,sid2
#       SQLS_SQL=name.sql
#   }
#   and is configure in mk_oracle.cfg
#
#   == SQLS_SECTION_NAME ==
#     If specified then data has to be evaluated on
#     Check_MK side by related check plugin.
#     Otherwise "<<<oracle_sql:sep(58)>>>"-check plugin
#     is used which process a fixed format, ie.
#         [[[SID-1|SQL-A]]]
#         details:DETAILS
#         perfdata:NAME=VAL;WARN;CRIT;MIN;MAX NAME=VAL;WARN;CRIT;MIN;MAX ...
#         long:LONG
#         long:LONG
#         ...
#         exit:CODE
#         [[[SID-2|SQL-B]]]
#         details:DETAILS
#         perfdata:
#         long:LONG
#         long:LONG
#         ...
#         exit:CODE
#         ...


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
}


do_custom_sqls () {
    for section in "${custom_sqls_sections_arr[@]}"; do
        if [ "$(do_section "$section")" = "no" ]; then
            logging -w "[${MK_SID}] [${section}] [custom_sql]"\
                    "The option '--sections' does not include section '${section}'"
            continue
        fi

        if ! type "$section" > /dev/null 2>&1; then
            logging -w "[${MK_SID}] [${section}] [custom_sql]"\
                    "Definition of '${section}' not found in configuration"
            continue
        fi

        $section

        local sids=${SQLS_SIDS:-$custom_sqls_sids}
        sids=${sids//,/ }

        # If SID is not part of sids we can skip the rest
        if ! echo "$sids" | "${GREP}" -q "$MK_SID"; then
            logging -w "[${MK_SID}] [${section}] [custom_sql]"\
                    "SID '${MK_SID}' is not part of stated 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}] [${section}] [custom_sql]" "Empty section name"
            unset_custom_sqls_vars
            continue
        fi

        if [ ! -d "$sql_dir" ] || [ ! -r "$sql_dir" ] ; then
            logging -w "[${MK_SID}] [${section}] [custom_sql]"\
                    "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}] [${section}] [custom_sql]"\
                    "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 [ -n "$SQLS_ITEM_NAME" ]; then
                local item="${MK_SID}|${SQLS_ITEM_NAME}"
            else
                local item="${MK_SID}|${sql}"
            fi
        else
            local item=
        fi

        local parameters=${SQLS_PARAMETERS:-$custom_sqls_parameters}

        if [ -n "${MK_ORA_SECTIONS[*]}" ]; 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

        # set custom credentials from section
        db_connect=$(mk_ora_db_connect "$MK_SID")
        export MK_DB_CONNECT=$db_connect

        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}] [${section}] [custom_sql]"\
                "Section name: $MK_CUSTOM_SQLS_SECTION" "Section header: $MK_CUSTOM_SQLS_SECTION_HEADER"\
                "Section item: $MK_CUSTOM_SQLS_ITEM" "Custom SQLS query: $MK_CUSTOM_SQLS_SECTION_QUERY"\
                "Max cache age: $max_cache_age"

        if [ "$MK_ORA_DEBUG_CONNECT" = "1" ]; 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")
                logging "[${MK_SID}] [${section}] [custom_sql]" "Custom SQLS query output: $output"
                handle_custom_sql_errors "$output"
            else
                run_cached "$max_cache_age" do_async_custom_sqls "_custom_sql_${MK_CUSTOM_SQLS_SECTION}"
            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
}


export -f unset_custom_sqls_vars
export -f custom_sql_section
export -f do_async_custom_sqls
export -f handle_custom_sql_errors


# globals
custom_sqls_sections_arr=($(echo "$SQLS_SECTIONS" | tr ',' '\n'))

# 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


#.
#   .--helper--------------------------------------------------------------.
#   |                    _          _                                      |
#   |                   | |__   ___| |_ __   ___ _ __                      |
#   |                   | '_ \ / _ \ | '_ \ / _ \ '__|                     |
#   |                   | | | |  __/ | |_) |  __/ |                        |
#   |                   |_| |_|\___|_| .__/ \___|_|                        |
#   |                                |_|                                   |
#   '----------------------------------------------------------------------'

do_dummy_sections () {
    if [ "$MK_ORA_LOGGING" != "1" ]; then
        for section in $SYNC_SECTIONS $ASYNC_SECTIONS $SYNC_ASM_SECTIONS $ASYNC_ASM_SECTIONS; do
            echo "<<<oracle_${section}>>>"
        done
    fi
}


do_section () {
    local section=$1
    if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
        for mk_ora_section in "${MK_ORA_SECTIONS[@]}"; do
            if [ "$mk_ora_section" = "${section}" ]; then
                echo "yes"
                return
            fi
        done
        echo "no"
    else
        echo "yes"
    fi
}


skip_sid () {
    local sid="$1"
    if [ "$ONLY_SIDS" ]; then
        if echo "$ONLY_SIDS" | "${GREP}" -q "$sid"; then
            echo "no"
            return
        fi
        echo "yes"
        return
    fi

    if [ "$SKIP_SIDS" ]; then
        if echo "$SKIP_SIDS" | "${GREP}" -q "$sid"; then
            echo "yes"
            return
        fi
        echo "no"
        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}
        if [ "$EXCLUDE" = "ALL" ]; then
            echo "yes"
            return
        fi
        echo "no"
        return
    fi
    echo "no"
}


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"
    local result=
    local skip=
    for section in $sections; do
        skip=
        for exclude in $excluded; do
            if [ "$exclude" = "$section" ]; then
                skip=yes
                break
            fi
        done
        if [ "$skip" != yes ]; then
            result=${result:+"$result "}"${section}"
        fi
    done
    echo "$result"
}


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 [ -n "${MK_ORA_SECTIONS[*]}" ]; then
        return
    fi

    local maxage="$1"
    local cmd_name="$2"

    local cache_file="$MK_VARDIR/cache/oracle_${MK_SID}${3}.cache"
    local use_cache_file=
    local now=
    local cf_atime=
    local mtime=

    logging "[${MK_SID}] [run_cached]" "Cache file: $cache_file"\
            "Cache max age: $maxage" "Command name: $cmd_name"

    if [ ! -d "$MK_VARDIR/cache" ]; then
        mkdir -p "$MK_VARDIR/cache"
    fi

    # Check if the creation of the cache takes suspiciously long and return
    # nothing if the age (access time) of $cache_file.new is twice the 'maxage'
    # perl is needed for Solaris => no date +%s availible
    now=$(perl -le "print time()")
    if [ -e "${cache_file}.new" ]; then
        cf_atime=$(eval "$STATCX" "${cache_file}.new")
        if [ $((now - cf_atime)) -ge $((maxage * 2)) ]; then
            # Kill the process still accessing that file in case
            # it is still running. This avoids overlapping processes!
            fuser -k -9 "${cache_file}.new" >/dev/null 2>&1
            rm -f "${cache_file}.new"
            logging "[${MK_SID}] [run_cached]" "Creation of the cache takes suspiciously long"
            return
        fi
    fi

    # Check if cache file exists and is recent enough
    if [ -s "$cache_file" ]; then
        mtime=$(eval "$STATCY" "$cache_file")
        if [ $((now - mtime)) -le "$maxage" ]; then
            use_cache_file=1
        fi
        # Output the file in any case, even if it is
        # outdated. The new file will not yet be available
        logging "[${MK_SID}] [run_cached]" "Cache file exists and is recent enough"
        cat "$cache_file"
    fi

    # Cache file outdated and new job not yet running? Start it
    if [ -z "$use_cache_file" ] && [ ! -e "${cache_file}.new" ]; then
        logging "[${MK_SID}] [run_cached]" "Cache file outdated, start it."
        if [ "$MK_ORA_DEBUG" ]; then
            echo "set -o noclobber; exec > \"${cache_file}.new\" || exit 1; ${cmd_name} && mv \"${cache_file}.new\" \"${cache_file}\" || rm -f \"${cache_file}\" \"${cache_file}.new\"" | /bin/bash
        else
            # When the command fails, the output is throws away ignored
            echo "set -o noclobber; exec > \"${cache_file}.new\" || exit 1; ${cmd_name} && mv \"${cache_file}.new\" \"${cache_file}\" || rm -f \"${cache_file}\" \"${cache_file}.new\"" | nohup /bin/bash >/dev/null 2>&1 &
        fi
    fi
}

#.
#   .--DB connect----------------------------------------------------------.
#   |          ____  ____                                    _             |
#   |         |  _ \| __ )    ___ ___  _ __  _ __   ___  ___| |_           |
#   |         | | | |  _ \   / __/ _ \| '_ \| '_ \ / _ \/ __| __|          |
#   |         | |_| | |_) | | (_| (_) | | | | | | |  __/ (__| |_           |
#   |         |____/|____/   \___\___/|_| |_|_| |_|\___|\___|\__|          |
#   |                                                                      |
#   '----------------------------------------------------------------------'

mk_ora_db_connect () {
    local sid="$1"

    ORADBUSER=""
    DBPASSWORD=""

    if [[ "$sid" =~ ^REMOTE_INSTANCE_.* ]]; then
        # working on REMOTE_-Mode!
        ORACFGLINE=$(eval "echo \${$1}")
        ORACLE_SID=$(echo "${ORACFGLINE}" | cut -d":" -f7)
        TNSALIAS=$(echo "${REMOTE_VARNAME}" | cut -d"_" -f3-)

    else
        # working with locally running instances
        # mk_oracle_dbusers.conf is for compatibility. Do not use it anymore
        ORACLE_USERCONF=${MK_CONFDIR}/mk_oracle_dbuser.conf
        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
            dummy="DBUSER_${ORACLE_SID}"
            ORACFGLINE=${!dummy}
            if [ "$ORACFGLINE" = '' ]; then
                ORACFGLINE=${DBUSER}
            fi
        fi

        if [ -f "${ORACLE_USERCONF}" ] && [ "${ORACFGLINE}" = '' ]; then
            # mk_oracle_dbuser.conf
            ORACFGLINE=$("${GREP}" "^${ORACLE_SID}:" < "${ORACLE_USERCONF}")
            # mk_oracle_dbuser has ORACLE_SID as 1. parameter. we need an offset for all values
            offset=1
        fi
    fi

    offset=${offset:-0}
    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 ] && [ ! -z "$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

    TNSPINGOK=no
    if [ -f "${TNS_ADMIN}/tnsnames.ora" ]; then
        if [ "${ORADBUSER:0:2}" = '/@' ]; then
            P_TNSALIAS_P=$(eval echo "${ORADBUSER:2}")
        else
            P_TNSALIAS_P=$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"
            else
                P_TNSALIAS_P="$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"
            else
                P_TNSALIAS_P="$P_TNSALIAS_P"
            fi
        fi

        TNSALIAS=${SQLS_TNSALIAS:-${P_TNSALIAS_P}}
        if "${ORACLE_HOME}"/bin/tnsping "$TNSALIAS" >/dev/null 2>&1; then
            TNSPINGOK=yes
        else
            unset TNSALIAS
        fi
    else
        unset TNSALIAS
    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 [ "$TNSPINGOK" = 'no' ]; then
                # create an EZCONNECT string when no tnsnames.ora is usable
                # 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 () {
    local from_where="$1"
    local print_elapsed_time="$2"
    local start_time=
    local elapsed_time=
    local loc_stdin=
    local output=

    logging "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "Piggyback host: $MK_PIGGYBACK_HOST"

    start_time="$(perl -MTime::HiRes=time -wle 'print time')"
    loc_stdin=$(cat)

    SQLPLUS=${ORACLE_HOME}/bin/sqlplus
    if [ ! -x "${SQLPLUS}" ]; then
        logging -w "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "SQLplus '${SQLPLUS}' not found or ORACLE_HOME '${ORACLE_HOME}' wrong."
        echo "sqlplus not found or ORACLE_HOME wrong!"
        echo "SQLPLUS=${SQLPLUS}"
        return 1
    fi

    if [ -n "$MK_PIGGYBACK_HOST" ]; then
        echo "<<<<${MK_PIGGYBACK_HOST}>>>>"
    fi

    if output=$(echo "$(ora_session_environment)${loc_stdin}" | "$SQLPLUS" -L -s "$MK_DB_CONNECT"); 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
        logging -w "[${MK_SID}] [${from_where}] [mk_ora_sqlplus]" "OUTPUT '$output'"
        output=$(echo -e "$output" | "${GREP}" -v "^ERROR at line" | tr '\n' ' ' |\
                                     sed "s/^/$(echo "$MK_SID" | tr '[:lower:]' '[:upper:]')|FAILURE|/" ; echo)
        if [ "$MK_ORA_DEBUG_CONNECT" ]; then
            echo "Logindetails: ${DBCONNECT}" >&2
            echo -e "$output"
        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_testmode () {
    local sections=$1
    local asections=$2

    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 "------------------------------------------------------------------------"
}

do_checks () {
    local excluded=
    local sections=
    local asections=

    if [ "${ORACLE_SID:0:1}" = '+' ]; then
        # ASM sections
        local do_async_sections=${ASYNC_ASM_SECTIONS}
        local do_sync_sections=${SYNC_ASM_SECTIONS}
    else
        local dummy="SYNC_SECTIONS_${ORACLE_SID}"
        local SYNC_SECTIONS_SID=${!dummy}
        local do_sync_sections=${SYNC_SECTIONS_SID:-${SYNC_SECTIONS}}
        unset dummy

        local dummy="ASYNC_SECTIONS_${ORACLE_SID}"
        local ASYNC_SECTIONS_SID=${!dummy}
        local do_async_sections=${ASYNC_SECTIONS_SID:-${ASYNC_SECTIONS}}
    fi

    if [ -n "${MK_ORA_SECTIONS[*]}" ]; then
        sections=$(
            for section in $do_sync_sections $do_async_sections; do
                if [ "$(do_section "$section")" = "yes" ]; then
                    sections=${sections:+"$sections "}"${section}"
                fi
            done; echo "$sections")
        logging "[${MK_SID}] [do_checks]" "Do single sections: $sections"

        MK_SYNC_SECTIONS_QUERY=$(for section in $sections; do eval "sql_$section" ; done)
        MK_ASYNC_SECTIONS_QUERY=

    else
        excluded=$(eval "echo \$EXCLUDE_$MK_SID")
        sections=$(remove_excluded_sections "$do_sync_sections" "$excluded")
        asections=$(remove_excluded_sections "$do_async_sections" "$excluded")
        logging "[${MK_SID}] [do_checks]" "Excluded: $excluded"\
                "Sections: $sections" "Async sections: $asections"

        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_ORA_LOGGING" = "1" ]; then
        logging "[${MK_SID}] [do_checks]" "Testmode: $(echo \
                "select user || ' on ' || host_name || ' instance ' || instance_name from v\$instance;" | mk_ora_sqlplus "do_checks")"
    fi

    if [ "$MK_ORA_DEBUG_CONNECT" = "1" ]; then
        do_testmode "$sections" "$asections" 2>&1
    else
        do_sync_checks
        run_cached "$CACHE_MAXAGE" do_async_checks
    fi
}

#.
#   .--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

#   ---preliminaries--------------------------------------------------------

logging "--------------------------------------------------------------------"
if [ "$MK_ORA_LOGGING" == "1" ]; then
    echo "Start logging to file: $MK_VARDIR/log/mk_oracle.log" >&2
fi

set_os_env
logging "[preliminaries]" "OS: ${OS_TYPE}"\
        "GREP: ${GREP}" "AWK: ${AWK}" "STATCX: ${STATCX}" "STATCY: ${STATCY}"


logging "[preliminaries]" "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}"


# 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_' | cut -d"_" -f3-)


# 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


logging "[preliminaries]" "SIDs: ${SIDS//\\n/ }"\
        "Remote instances: ${REMOTE_INSTANCES}"\
        "Piggyback hosts: ${PIGGYBACK_HOSTS}"


if [ "$PIGGYBACK_HOSTS" ]; then
    PIGGYBACK_HOSTS=$(echo "$PIGGYBACK_HOSTS" | tr ' ' '\n' | sort | uniq)
    logging "[preliminaries]" "Removed duplicate piggyback hosts"\
            "Remaining piggyback hosts: ${PIGGYBACK_HOSTS}"

    if [ ! -e "$MK_VARDIR/mk_oracle.found" ]; then
        touch "$MK_VARDIR/mk_oracle.found"
    fi
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 Check_MK will be
# happy and execute the actual check functions.
if [ -z "$SIDS" ] && [ ! -e "$MK_VARDIR/mk_oracle.found" ]; then
    logging -c "[preliminaries]" "No SIDs and \$MK_VARDIR/mk_oracle.found not found"
    echo "No SIDs and \$MK_VARDIR/mk_oracle.found not found" >&2
    exit 1
fi


# From now on we expect databases on this system (for ever)
touch "$MK_VARDIR/mk_oracle.found"


# 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


if [ "$PIGGYBACK_HOSTS" ]; then
    for piggyback_host in $PIGGYBACK_HOSTS; do
        echo "<<<<${piggyback_host}>>>>"
        do_dummy_sections
        echo "<<<<>>>>"
    done
fi

#   ---local----------------------------------------------------------------

for sid in $SIDS; do
    skip=$(skip_sid "$sid")
    logging "[${sid}] [local]" "Skipping: $skip"
    if [ "$skip" == "yes" ]; then
        continue
    fi

    set_ora_env "$sid"
    if [ $? -eq 2 ] ; then
        # we have to skip this SID due to missing/unknown ORACLE_HOME
        continue
    fi
    set_ora_version "$("${ORACLE_HOME}"/bin/sqlplus -V | "${GREP}" ^SQL | cut -d" " -f3 | cut -d"." -f-2)"
    logging "[${sid}] [local]" "ORACLE_HOME: ${ORACLE_HOME}" "TNS_ADMIN: ${TNS_ADMIN}"\
            "ORACLE_SID: ${ORACLE_SID}" "LD_LIBRARY_PATH: ${LD_LIBRARY_PATH}"\
            "ORACLE_VERSION: ${ORACLE_VERSION} (${NUMERIC_ORACLE_VERSION})"

    # 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
done

#   ---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_HOME: ${ORACLE_HOME}" "TNS_ADMIN: ${TNS_ADMIN}"\
            "ORACLE_SID: ${ORACLE_SID}" "LD_LIBRARY_PATH: ${LD_LIBRARY_PATH}"\
            "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
done
