#!/usr/bin/env bash

# Bash script to configure the environment for an Oracle DBA
# Copyright (C) 2021  OPITZ CONSULTING Deutschland GmbH
#
# This program 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, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <https://www.gnu.org/licenses/>.

################################################################################
# Hints (for usage end enhancements)
#  * This script is not for execution. It must be sourced to set the environment
#  * This script should be sourceable in bash
#    * Testing is mainly done on bash version 4 and above
#  * Sourcing the script without a working TTY must not produce any output
#    * Allows usage in CRON-jobs
#    * Allows sourcing in .profile/.bash_profile/.bashrc without breaking
#      automated tasks or SSH "batch-mode" logins
#    * Presence of a TTY can be verified by checking that variable "GV_TTY" is 0
#    * When sourcing without working TTY the detection of known instances
#      and listeners is skipped. Only GV_GRID_HOME (if it exists) is detected
################################################################################

## Version of this script. Simply uses the date in YYYY-MM-DD format
GV_OCENV_VERSION="2023-06-06"

###############################################################################
## Environment support homogenization
GV_OS_TYPE=$(uname -s)
if [[ -z "${GC_CMD_READLINK}" ]]
then
  case "${GV_OS_TYPE}" in
    "Linux")
      GC_CMD_READLINK="readlink -f"
    ;;
    "AIX")
      GC_CMD_READLINK="PwdResolvePath"
    ;;
    *)
      printf "\e[00;31mOS Typ \"%s\" nicht unterstuetzt. Abbruch.\e[00m\n" "${GV_OS_TYPE}" 1>&2
      exit 1
    ;;
  esac
  typeset -r GC_CMD_READLINK
fi
###############################################################################
# If readlink is not available on the system (e.g. on AIX) the fallback is to
# use pwd -P and the "cd"-approach to resolve a symbolic link.
###############################################################################
function PwdResolvePath {
  if [[ "$1" == "-f" ]]; then shift; fi
  typeset LV_PATH LV_CMD LV_DIR LV_LINK
  LV_PATH="${1}"

  if [[ -d "${LV_PATH}" ]]
  then
    LV_CMD=
    LV_DIR="${LV_PATH}"
  else
    LV_CMD="$(basename "${LV_PATH}")"
    LV_DIR="$(dirname "${LV_PATH}")"
  fi

  # shellcheck disable=2164
  cd "${LV_DIR}" >/dev/null 2>&1

  if [[ ! -d "${LV_PATH}" ]]
  then
    while [[ -h "${LV_CMD}" ]]
    do
      # shellcheck disable=2012
      LV_LINK="$(ls -l "${LV_CMD}" | cut -d\> -f2 | cut -c2-)"
      LV_CMD="$(basename "${LV_LINK}")"
      LV_DIR="$(dirname "${LV_LINK}")"
      # shellcheck disable=2164
      cd "${LV_DIR}"
    done
    LV_CMD="/${LV_CMD}"
  fi

  echo "$(pwd -P)${LV_CMD}"
}
## END Environment support homogenization
###############################################################################

GV_TTY=$(tty -s; echo $?)

if [[ -n "${BASH_SOURCE[0]}" ]]
then
  GV_SOURCED_SCRIPT="${BASH_SOURCE[0]}"
fi
if [[ -n "${GV_SOURCED_SCRIPT}" ]]
then
  GV_SOURCED_SCRIPT="$(${GC_CMD_READLINK} "${GV_SOURCED_SCRIPT}")"
fi

if [[ -f "${HOME}/.ocenvrc" ]]
then
  # shellcheck disable=SC1091
  . "${HOME}/.ocenvrc"
else
  if [[ -f "${GV_SOURCED_SCRIPT}" ]]
  then
    GV_SCRIPT_DIR="$(dirname "${GV_SOURCED_SCRIPT}")"
    echo "export GV_SCRIPT_DIR=\"${GV_SCRIPT_DIR}\"" > "${HOME}/.ocenvrc"
  else
    echo "Script base directory cannot be determined. Please configure by creating"
    echo "the file \"\${HOME}/.ocenvrc\" containing just the following line:"
    echo
    echo "export GV_SCRIPT_DIR=\"/path/where/your/ocenv/file/is/located/filename\""
    echo
    echo "In that directory this script will create subdirectories (bin and sql)"
  fi
fi

if [[ -d "${GV_SCRIPT_DIR}" ]]
  then
  GV_SQL_DIR=${GV_SCRIPT_DIR}/sql/
  GV_BIN_DIR=${GV_SCRIPT_DIR}/bin/
  if [[ -d ${GV_BIN_DIR} ]]; then export PATH="${GV_BIN_DIR}:${PATH}"; fi
fi
# On AIX, this allows the installation of GNU-Tools (and rlwrap) via
# the IBM Open Source Toolbox
if [[ -d "/opt/freeware/bin/" ]]; then export PATH="/opt/freeware/bin/:${PATH}"; fi
if [[ -z "${GV_INITIAL_VARS_SAVED}" ]]
then
  export ORIGINAL_SQLPATH_PRE_ENVLOAD="${SQLPATH}"
  export ORIGINAL_ORACLE_PATH_PRE_ENVLOAD="${ORACLE_PATH}"
  export ORIGINAL_PATH_PRE_ENVLOAD="${PATH}"
  export ORIGINAL_LIBPATH_PRE_ENVLOAD="${LIBPATH}"
  export ORIGINAL_LD_LIBRARY_PATH_PRE_ENVLOAD="${LD_LIBRARY_PATH}"

  export GV_INITIAL_VARS_SAVED=TRUE
  declare -a GV_ENV_SPECIFIC_VARS
fi

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

envhelp() {
  cat <<EOT
usage: This script must be sourced, executing it will not be of any use.
       During sourcing it will print an overview of all installed ORACLE_HOMEs,
       database instances and listeners.

While sourcing the script it will set the environment of the active GRID_HOME
(if one exists). If there is none, the environment will be \"empty\".

This script will create a file "${HOME}/.ocenvrc" containing a line like this:

export GV_SCRIPT_DIR="/path/where/your/ocenv/file/is/located/filename"

This can be used to change the base-directory used for the subdirectories used
by this environemnt. The default value for GV_SCRIPT_DIR is the directory where
the script file is sourced from.

Example overview:
===========================================================================
home                OraHome_OraDB19Home1             /u01/app/oracle/product/19.3.0/dbhome_1
db     oracle       CDB19            (up)            /u01/app/oracle/product/19.3.0/dbhome_1
db     oracle       ORCL             (up)            /u01/app/oracle/product/19.3.0/dbhome_1
lsnr                LISTENER                         /u01/app/oracle/product/19.3.0/dbhome_1
===========================================================================

The names in the third column (names of ORACLE_HOMEs, instances and listeners)
can be executed as commands. Alias definitions with these names will be
dynamically generated to allow setting the environment for these targets.

While sourcing these configurations will be set
 * A login.sql will be created in
      ${GV_SQL_DIR}/sql
   and the variables SQL_PATH and ORACLE_PATH will be defined to point to this
   directory, to allow autoexecution of this script when starting SQL*Plus.
 * An alias definition \"ocenv\" will be appended to .bashrc if not present
 * The shell prompt has 2 lines and contains the complete working directory path,
   the name of the active environment, the current user and the hostname of the
   system.

Available commands:
  == Changing Directories
    * cda       Switch to ORACLE_BASE/admin directory for the current instance
    * cdd       Switch to ADR trace directory for the current instance
    * cdh       Switch to the ORACLE_HOME for the current environment
    * cdob      Switch to the ORACLE_BASE for the current environment
    * cdt       Switch to the TNS_ADMIN directory for the current environment
                (ORACLE_HOME/network/admin if TNS_ADMIN is not set)
  == Open Files
    * grplogs   searches files for a given regexp. Every match is prepended with
                the last found timestamp in that file prior to the match.
    * via       open alertlog with less/more
    * vil       open listener.ora with VI(M) (if more than one file is found,
                a selection dialog is presented)
    * vilsnr    open listener.log with less/more
    * vio       open /etc/oratab with VI(M)
    * vit       open tnsnames.ora with VI(M) (if more than one file is found,
                a selection dialog is presented)
  == Tail Files
    * taa       "tail -f" on alertlog
    * tad       "tail -f" on dataguard broker logfile
    * talsnr    "tail -f" on listener.log
                Parameter 1: name of the listener, OPTIONAL
  == Process and Memory Information
    * hugepageinfo
                Output of /proc/meminfo sorted by descending segment size,
                hugepage elements are highlighted in red
    * pmon      all running PMON processes
    * psg       alias for "ps -ef | grep"
  == Database Tools
    * amon      if the tool "amon" is found in the PATH an alias is auto-
                generated to start amon with disabled Diagnostic and Tuning Pack
                usage
    * dg        start dgmgrl, if "rlwrap" is found in the PATH, it is used to
                wrap dgmgrl
    * myoraenv  sets the environment for the given ORACLE_SID. If the instance
                is registered in Grid Infrastructure, the ORACLE_HOME and other
                environment parameters are read from there
    * pdbsql    connect a PDB directly via BEQUEATH protocoll, wrap in "rlwrap"
                if present
                Parameter 1: Name of the PDB
                Parameter 2: Login ("/ as sysdba" possible), OPTIONAL
                All following parameters: will be passed to SQL*Plus
    * pdbsqlcl  like "pdbsql" but uses sqlcl in the sqldeveloper subdirectory
                in the ORACLE_HOME
    * rmanc     "rman target /", if available wrapped with "rlwrap"
    * sql       "sqlplus / as sysdba", if available wrapped with "rlwrap"
    * sqlcl     like "sql"  but uses sqlcl in the sqldeveloper subdirectory
                in the ORACLE_HOME
    * sta       print short summary for active environment. Defines variables
                for paths and files (alertlog, etc.)
  == ASM and Grid Infrastructure
    * asmcmd    spawns a subshell, defines ASM environment and calls ASMCMD
                will use "SYSDBA" as privilege if current user is not ASM owner
    * asmdu     DiskUsage for ASM diskgroups
    * asmsql    spawns a subshell, defines ASM environment and calls SQL*Plus
    * crsstat   colorized and reformateted GI overview similar to "crsctl stat res -t"
  == Oracle Cloud Infrastructure
    * bkup_api_list_jobs
                list the bkup_api jobs for the currently active environment
                Parameter 1: Number of lines (optional)
                Parameter 2: Type of job (DB|FULL|INCREMENTAL|KEEP|ARCH) (optional)
    * bkup_api_last_job
                show status of last executed bkup_api job for the currently
                active environment
                Parameter 1: Type of job (DB|FULL|INCREMENTAL|KEEP|ARCH)
  == Filesystems
    * edf       enhanced version of "df"
    * edu       enhanced version of "du"
    * gdf       call "edf"
                Parameter 1: use as RegExp for AWK script
    * llh       enhanced version of "ls -lh" (very helpful on AIX)
    * rotate_file
                Renames the file given as parameter by appending the current
                timestamp. After this the original file will be recreated.
  == Third-party Tools
    * get_tpt_oracle
                Tries to download the current "master" version of the tpt-oracle
                repository from GitHub and extract it to
                "${GV_SQL_DIR}/sql/tpt"
  == Miscellaneous
    * envhelp   This help text
    * varhelp   information about all variables defined in this script
    * ocenv     alias to source the environment script
    * u         reload the complete environment without sourcing the script again
    * show_env_version
                show header block with version information
    * distribute_env
                distributes environment file for the current user on all other
                RAC nodes
    * pull_ocenv_main
                Downloads the current version of the script from the git repository
                https://github.com/opitzconsulting/oracle-scripts
                and puts it to "${GV_SOURCED_SCRIPT}".
EOT
}

varhelp() {
  cat <<EOT
This script defines a big number of global variables.

In addition to the known variables defined or required by Oracle (ORACLE_SID,
ORACLE_HOME, PATH, LD_LIBRARY_PATH/LIBPATH, TWO_TASK) the following variables
are filles with information about the currently selected environment (if
applicable):
  == Global Environment Informationen
    * GV_ALIAS_LIST                Array of all aliases generated by this script
    * GV_OS_TYPE                   Running OS
    * GV_PAGER                     less or more
    * GV_RLWRAP                    rlwrap binary, if found in PATH
    * GV_OCENV_VERSION             Version of this script
    * GV_TTY                       Value of "0" indicates, that the session has
                                   a TTY
                                   Other values indicate an execution in batch
                                   mode

  == Grid Infrastructure
    * GV_GRID_HOME                 ORACLE_HOME of the active Grid Infrastructure
    * GV_ASM_SID                   ORACLE_SID der active ASM instance

  == Database Information (active environment, if it can be determined)
    * GV_DB_FLASHBACK_ON           Status of FLASHBACK LOGGING
    * GV_DB_FORCE_LOGGING          Status of FORCE LOGGING
    * GV_DB_ID                     DBID
    * GV_DB_LOG_MODE               Archivelog Mode
    * GV_DB_NAME                   DB_NAME
    * GV_DB_ROLE                   PRIMARY/STANDBY
    * GV_DB_UNIQUE_NAME            DB_UNIQUE_NAME
    * GV_DB_UNIQUE_NAME_LC         DB_UNIQUE_NAME in lower case letters
    * GV_IS_CDB                    YES/NO if CDB-instance
    * GV_PDB_LIST                  Array of PDBs, if CDB-environment

  == Instance Information
    * GV_ORACLE_SID_LC             ORACLE_SID in lower case letters
    * GV_INSTANCE_STARTUP          Timestamp when instance was started
    * GV_INSTANCE_STATUS           Startup status of the instance
    * GV_INSTANCE_ALERT_LOG        complete path of the Alertlog
    * GV_INSTANCE_BDUMP_DEST       complete path of the BDUMP_DEST
    * GV_INSTANCE_DIAGNOSTIC_DEST  complete path of the ADR Home
    * GV_INSTANCE_DRC_LOG          complete path of the Data Guard Broker Log

  == Files and Paths
    * GV_SQL_DIR                   Directory for SQL script files
    * GV_BIN_DIR                   Directory for executables
EOT
}

set_sql_path_vars() {
  export SQLPATH="${ORIGINAL_SQLPATH_PRE_ENVLOAD}"
  export ORACLE_PATH="${ORIGINAL_ORACLE_PATH_PRE_ENVLOAD}"
  if [[ -d "${GV_SQL_DIR}/tpt" ]]
  then
    export SQLPATH="${GV_SQL_DIR}/tpt:${SQLPATH}"
    export ORACLE_PATH="${GV_SQL_DIR}/tpt:${ORACLE_PATH}"
  fi
  export SQLPATH="${GV_SQL_DIR}:${SQLPATH}"
  export ORACLE_PATH="${GV_SQL_DIR}:${ORACLE_PATH}"
}
set_sql_path_vars

## Configure syntax highlighting in VIM, but only if not configured already
if [[ -n "$(which vim 2>/dev/null)" && -f "$(which vim)" ]]
then
  export EDITOR=vim
  alias vi=vim
  if ! grep -q "^:colorscheme .*$" ~/.vimrc 2>/dev/null
  then
    echo ":colorscheme elflord" >> ~/.vimrc
  fi
  if ! grep -q "^:syntax .*$" ~/.vimrc 2>/dev/null
  then
    echo ":syntax on" >> ~/.vimrc
  fi
else
  export EDITOR=vi
fi

## COLOR/FORMAT codesequences
## if the following codes are used in printf statements, the width for the
## string-placeholder must be increased by the sum of the number of invisible chars
## otherwise the width-formatting will not be correct
GV_T_RED="$(printf "\033[00;31m")"     # Uses 8 invisible chars in printf format width
GV_T_GREEN="$(printf "\033[00;32m")"   # Uses 8 invisible chars in printf format width
GV_T_WHITE="$(printf "\033[00;39m")"   # Uses 8 invisible chars in printf format width
GV_B_RED="$(printf "\033[01;31m")"     # Uses 8 invisible chars in printf format width
# shellcheck disable=SC2034 # GV_B_GREEN is currently unused
GV_B_GREEN="$(printf "\033[01;32m")"   # Uses 8 invisible chars in printf format width
GV_B_WHITE="$(printf "\033[01;39m")"   # Uses 8 invisible chars in printf format width
# shellcheck disable=SC2034 # GV_BOLD is currently unused
GV_BOLD="$(printf "\033[1m")"          # Uses 4 invisible chars in printf format width
GV_CCLR="$(printf "\033[0m")"          # Uses 4 invisible chars in printf format width

if [[ "${GV_TTY}" -eq 0 ]]
then
  # enclose unprintable parts in "\[" and "\]" to tell bash that they do not affect the lenght of the prompt
  ## Variables used in PS1 and PS2 have \[ and \] to correct width-calculation of prompt
  GV_P_T_RED="$(printf "\[%s\]" "${GV_T_RED}")"
  GV_P_T_GREEN="$(printf "\[%s\]" "${GV_T_GREEN}")"
  GV_P_T_WHITE="$(printf "\[%s\]" "${GV_T_WHITE}")"
  GV_P_B_BOLD="\[${GV_B_WHITE}\]"
  GV_P_CCLR="\[${GV_CCLR}\]"

  declare -a GV_ALIAS_LIST
  declare -a GV_PDB_LIST
  export HISTTIMEFORMAT="%F %T "
  if [[ "${USER}" == "root" ]]
  then
    export PS1="[${GV_P_T_RED}\u${GV_P_T_WHITE}@\h] [\w]\n${GV_P_B_BOLD}#${GV_P_CCLR} "
    export PS2="${GV_P_B_BOLD}#${GV_P_CCLR} "
  else
    export PS1="[${GV_P_T_GREEN}\u${GV_P_T_WHITE}@\h] [${GV_P_T_RED}\${ORACLE_SID}${GV_P_T_WHITE}] [\w]\n${GV_P_B_BOLD}\$${GV_P_CCLR} "
    export PS2="${GV_P_B_BOLD}\$${GV_P_CCLR} "
  fi

  if ! grep -q "^alias ocenv" ~/.bashrc
  then
    printf "\nalias ocenv='. \"%s\"'\n" "${GV_SOURCED_SCRIPT}" >> ~/.bashrc
  fi
fi

if [[ -z "${GV_RLWRAP}" || ! -x "${GV_RLWRAP}" ]]
then
  if [[ -x "$(which rlwrap 2>/dev/null)" ]]
  then
    GV_RLWRAP="$(which rlwrap 2>/dev/null)"
  elif [[ "${GV_OS_TYPE}" == 'Linux' ]]
  then
    # Look for "custom" rlwrap binaries in $HOME of current user
    # Use first hit that is executable without error
    while read -r LV_RLWRAP_BINARY
    do
      if "${LV_RLWRAP_BINARY}" -v 1>/dev/null 2>&1
      then
        GV_RLWRAP="${LV_RLWRAP_BINARY}"
        break;
      fi
    done <<< "$(find ~ -type f -executable -name rlwrap 2>/dev/null)"
  fi
fi

mkdir -p "${GV_SQL_DIR}"
cat > "${GV_SQL_DIR}/login.sql" <<-"EOT"
  SET FEEDBACK   OFF
  SET TERMOUT    OFF
  SET TERMOUT    OFF
  SET PAGESIZE    50
  SET LINESIZE   200

  DEFINE GE12 = ""
  DEFINE LT12 = ""
  DEFINE GE10 = ""
  DEFINE LT10 = ""

  -- Get helper-variables to allow version-dependent modification of following query
  COL ge12 NEW_VALUE GE12
  COL lt12 NEW_VALUE LT12
  COL ge10 NEW_VALUE GE10
  COL lt10 NEW_VALUE LT10
  SELECT CASE WHEN rel <  12 THEN '--' END ge12,
         CASE WHEN rel >= 12 THEN '--' END lt12,
         CASE WHEN rel <  10 THEN '--' END ge10,
         CASE WHEN rel >= 10 THEN '--' END lt10
  FROM (SELECT RTRIM(SUBSTR(banner, INSTR(banner, 'Release ', 1, 1)+8, 2), '.') rel
        FROM v$version
        WHERE ROWNUM = 1);

  DEFINE gv_gname="IDLE> "
  COLUMN global_name NEW_VALUE gv_gname

  -- Query username, instance-name and (on 12c) container-name
  -- Set SQLPROMPT to combination of the results
  SELECT   UPPER(user) || '@'||
  &&GE12     CASE WHEN SYS_CONTEXT('USERENV','CON_NAME') != 'CDB$ROOT' THEN UPPER(SYS_CONTEXT('USERENV','CON_NAME'))
  &&GE12     ELSE
           UPPER(instance_name)
  &&GE12   END
         ||CHR(10)
         ||'SQL> ' global_name
  FROM v$instance;

  SET SQLPROMPT   "&gv_gname"

  ALTER SESSION SET NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";
  ALTER SESSION SET NLS_TIMESTAMP_FORMAT="YYYY-MM-DD HH24:MI:SS.FF";
  ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT="YYYY-MM-DD HH24:MI:SS.FF TZR";

  -- Various columns adjustment
  COLUMN name                       FORMAT A40
  COLUMN tablespace_name            FORMAT A40
  COLUMN member                     FORMAT A80
  COLUMN file_name                  FORMAT A50
  COLUMN sql_text                   FORMAT A100 WRAPPED

  -- Used for the SHOW ERRORS command
  COLUMN LINE/COL                   FORMAT A8
  COLUMN ERROR                      FORMAT A65  WORD_WRAPPED

  -- Used for the SHOW SGA command
  COLUMN name_col_plus_show_sga     FORMAT A30
  COLUMN units_col_plus_show_sga    FORMAT A15

  -- Defaults for SHOW PARAMETERS
  COLUMN name_col_plus_show_param   FORMAT A36 HEADING NAME
  COLUMN value_col_plus_show_param  FORMAT A70 HEADING VALUE

  -- Defaults for SHOW RECYCLEBIN
  COLUMN origname_plus_show_recyc   FORMAT A16 HEADING 'ORIGINAL NAME'
  COLUMN objectname_plus_show_recyc FORMAT A30 HEADING 'RECYCLEBIN NAME'
  COLUMN objtype_plus_show_recyc    FORMAT A12 HEADING 'OBJECT TYPE'
  COLUMN droptime_plus_show_recyc   FORMAT A19 HEADING 'DROP TIME'

  -- Miscelaneous
  COLUMN column_name                FORMAT A30
  COLUMN segment_name               FORMAT A30 TRUNC
  COLUMN program                    FORMAT A30 TRUNC
  COLUMN what                       FORMAT A50 WORD_WRAPPED
  COLUMN plan_plus_exp              FORMAT A100

  SET FEEDBACK    ON
  SET EXITCOMMIT OFF
  SET VERIFY      ON
  SET LONG     10000
  SET TAB        OFF
  SET TERMOUT     ON
EOT

alias cdh='cd ${ORACLE_HOME}'
unalias cdt 2>/dev/null
# shellcheck disable=2164
cdt() { if [[ -n "${TNS_ADMIN}" ]]; then cd "${TNS_ADMIN}"; elif [[ -n "${ORACLE_HOME}" ]]; then cd "${ORACLE_HOME}/network/admin"; fi;}
alias cdob='cd ${ORACLE_BASE}'
alias cda='if [[ -d "${ORACLE_BASE}/admin/${ORACLE_SID}" ]]; then cd "${ORACLE_BASE}/admin/${ORACLE_SID}"; elif [[ -d "${ORACLE_BASE}/admin/${GV_DB_NAME}" ]]; then cd "${ORACLE_BASE}/admin/${GV_DB_NAME}"; elif [[ -d "${ORACLE_BASE}/admin/" ]]; then echo "No admin dir for instance/database found"; cd "${ORACLE_BASE}/admin/"; else echo "No admin base directory found"; fi'

alias cdd='cd ${GV_INSTANCE_DIAGNOSTIC_DEST}/trace'
alias pmon='ps -ef | grep -w -E "ora_pmon_[a-zA-Z0-9]+"'
## amon uses ASH if not called with "-l n" parameter
[[ -n "$(which amon 2>/dev/null)" ]] && alias amon="\$(which amon) -l n"
if [[ "${GV_OS_TYPE}" == "AIX" ]]
then
  alias psg='ps -ef | grep -v grep | grep '
else
  alias psg='ps aux | grep -v grep | grep --color=auto'
fi
if [[ -x "$(which less 2>/dev/null)" ]]
then
  export GV_PAGER=less
else
  export GV_PAGER=more
fi
alias via='${GV_PAGER} ${GV_INSTANCE_ALERT_LOG}'
alias vio='${EDITOR} /etc/oratab'
alias taa='tail -n50 -f ${GV_INSTANCE_ALERT_LOG}'
alias tad='tail -n50 -f ${GV_INSTANCE_DRC_LOG}'
alias u=list_env
unalias sql 2>/dev/null

echo_tty() {
  if [[ "${GV_TTY}" -eq 0 ]]
  then
    echo "$@"
  fi
}

##############################################################################
# Queries /etc/oracle/ocr.loc for information about installed Grid Infrastructure
##############################################################################
is_rac() {
  if [[ -e /etc/oracle/ocr.loc && -r /etc/oracle/ocr.loc ]]
  then
    if grep -q -i "^local_only=FALSE" /etc/oracle/ocr.loc
    then
      return 0
    else
      return 1
    fi
  else
    # shellcheck disable=2009 # no pgrep on AIX
    ps -ef | grep -v grep | grep -q crsd.bin
    return $?
  fi
}

detect_grid_infrastructure_and_asm() {
  local LV_IS_RAC
  LV_IS_RAC=$(is_rac && echo TRUE || echo FALSE);

  GV_GRID_HOME=$(awk -F= '/crs_home/{print $2}' /etc/oracle/olr.loc 2>/dev/null)
  export GV_GRID_HOME

  local LV_ASM_LINE_LIST
  LV_ASM_LINE_LIST=$(grep '^+ASM[0-9]*:[^:]\+:.*$' /etc/oratab 2>/dev/null)
  local LV_ASM_LINE_COUNT
  LV_ASM_LINE_COUNT="$(echo "${LV_ASM_LINE_LIST}" | wc -l)"
  if [[ "${LV_ASM_LINE_COUNT}" -gt 1 ]]
  then
    echo_tty "${GV_B_RED}More than one ASM-instance in /etc/oratab! Using only the first.${GV_CCLR}" 1>&2
  fi

  ## ASM Instance from Grid Infrastructure
  if [[ -n "${GV_GRID_HOME}" ]]
  then
    local LV_PROC LV_ASM_SID
    LV_PROC=$(ps -eo user,args | awk /[[:alpha:]]_smon_\\+ASM/'{printf("%s,%s\n", $1, $2)}')
    LV_ASM_SID="$(echo "${LV_PROC}" | cut -d, -f2 | cut -d_ -f3)"
    if [[ -z "${LV_ASM_SID}" ]]
    then
      LV_ASM_SID="$("${GV_GRID_HOME}/bin/crsctl" stat res ora.asm -p -n "$(hostname | cut -d. -f1)" | grep "^GEN_USR_ORA_INST_NAME=" | cut -d= -f2)"
    fi
    if [[ -n "${LV_ASM_SID}" ]]
    then
      export GV_ASM_SID="${LV_ASM_SID}"
    fi
  fi
}
clroraenv() {
  unset GV_DB_FLASHBACK_ON
  unset GV_DB_FORCE_LOGGING
  unset GV_DB_ID
  unset GV_DB_LOG_MODE
  unset GV_DB_NAME
  unset GV_DB_ROLE
  unset GV_DB_UNIQUE_NAME_LC
  unset GV_DB_UNIQUE_NAME
  unset GV_INSTANCE_ALERT_LOG
  unset GV_INSTANCE_BDUMP_DEST
  unset GV_INSTANCE_DIAGNOSTIC_DEST
  unset GV_INSTANCE_DRC_LOG
  unset GV_INSTANCE_STARTUP
  unset GV_INSTANCE_STATUS
  unset GV_IS_CDB
  unset GV_ORACLE_SID_LC
  for LV_VAR_NAME in "${GV_ENV_SPECIFIC_VARS[@]}"
  do
    unset "${LV_VAR_NAME}"
  done
  # do not use "declare -a" since that would declare a local variable
  GV_ENV_SPECIFIC_VARS=()
  unset ORACLE_BASE
  unset ORACLE_HOME
  unset ORACLE_SID
  unset GV_PDB_LIST
  unset TNS_ADMIN
  export PATH="${ORIGINAL_PATH_PRE_ENVLOAD}"
}
add_env_specific_var() {
  if [[ -n "${1}" ]]
  then
    GV_ENV_SPECIFIC_VARS[${#GV_ENV_SPECIFIC_VARS[@]}]="${1}"
  fi
}
set_ora_home_env() {
  if [[ -z "$1" ]]
  then
    echo "No ORACLE_HOME given as parameter." 1>&2
    return 1
  fi
  export ORACLE_HOME="$1"
  if [[ -x "${ORACLE_HOME}/bin/orabase" ]]
  then
    ORACLE_BASE="$("${ORACLE_HOME}/bin/orabase")"
    export ORACLE_BASE
  fi
  case "$(uname -s)" in
    "Linux")
      export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${ORIGINAL_LD_LIBRARY_PATH_PRE_ENVLOAD}
    ;;
    "AIX")
      export LIBPATH=${ORACLE_HOME}/lib:${ORIGINAL_LIBPATH_PRE_ENVLOAD}
    ;;
  esac
  export PATH=${ORACLE_HOME}/bin:${ORACLE_HOME}/OPatch:${ORIGINAL_PATH_PRE_ENVLOAD}
  unset GV_PDB_LIST
}
myoraenv_simple() {
  if [[ -z "${2}" ]]
  then
    echo "usage: myoraenv_simple <ORACLE_SID> <ORACLE_HOME> <TNS_ADMIN>"
    return 1
  fi
  unset GV_DB_NAME                 2>/dev/null
  unset GV_DB_UNIQUE_NAME          2>/dev/null
  unset GV_DB_UNIQUE_NAME_LC       2>/dev/null
  unset TNS_ADMIN                  2>/dev/null
  ## ORACLE_UNQNAME is not set by this method, but often sourced from srvctl in OCI environments
  unset ORACLE_UNQNAME             2>/dev/null
  if [[ -n "${3}" ]]
  then
    export TNS_ADMIN="${3}"
  fi
  export ORACLE_SID="${1}";
  if ! (echo "${PATH}" | grep -q "/usr/local/bin")
  then
    export PATH="${PATH}:/usr/local/bin"
  fi
  set_ora_home_env "${2}"
}
myoraenv() {
  if [[ -z "${1}" ]]
  then
    echo "usage: myoraenv <ORACLE_SID>"
    return 1
  fi

  local LV_ORACLE_SID LV_ORACLE_HOME LV_TNS_ADMIN LV_DB_NAME LV_DB_UNIQUE_NAME

  LV_ORACLE_SID="${1}";
  if [[ "${LV_ORACLE_SID}" == "${GV_ASM_SID}" ]]
  then
    LV_ORACLE_HOME="${GV_GRID_HOME}"
  elif [[ -n "${GV_GRID_HOME}" ]]
  then
    LV_ORACLE_HOME=$("${GV_GRID_HOME}"/bin/crsctl stat res -p -w "((TYPE = ora.database.type) and (GEN_USR_ORA_INST_NAME = ${LV_ORACLE_SID}))" | grep "^ORACLE_HOME=" | cut -d"=" -f2)
    if [[ -n "${LV_ORACLE_HOME}" ]]
    then
      LV_TNS_ADMIN=$("${GV_GRID_HOME}"/bin/crsctl stat res -p -w "((TYPE = ora.database.type) and (GEN_USR_ORA_INST_NAME = ${LV_ORACLE_SID}))" | grep "^USR_ORA_ENV=TNS_ADMIN=" | cut -d"=" -f3)
      LV_DB_NAME=$("${GV_GRID_HOME}"/bin/crsctl stat res -p -w "((TYPE = ora.database.type) and (GEN_USR_ORA_INST_NAME = ${LV_ORACLE_SID}))" | grep "^USR_ORA_DB_NAME=" | cut -d"=" -f2)
      LV_DB_UNIQUE_NAME=$("${GV_GRID_HOME}"/bin/crsctl stat res -p -w "((TYPE = ora.database.type) and (GEN_USR_ORA_INST_NAME = ${LV_ORACLE_SID}))" | grep "^DB_UNIQUE_NAME=" | cut -d"=" -f2)
    fi
  fi
  if [[ -z "${LV_ORACLE_HOME}" ]]
  then
    LV_ORACLE_HOME=$(grep "^${LV_ORACLE_SID}:" /etc/oratab | cut -d":" -f2)
  fi
  if [[ -z "${LV_ORACLE_HOME}" ]]
  then
    echo "Cannot determine ORACLE_HOME for instance \"${LV_ORACLE_SID}\"" 1>&2
    return 1
  fi
  myoraenv_simple "${LV_ORACLE_SID}" "${LV_ORACLE_HOME}" "${LV_TNS_ADMIN}"
  if [[ -n "${GV_GRID_HOME}" ]]
  then
    ## Read environment variables from GridInfrastructure ressource configuration
    while read -r LV_VAR_ASSIGNMENT
    do
      ## Make sure, that the variable actually contains something that can plausibly be a variable assignment
      if [[ "${LV_VAR_ASSIGNMENT}" =~ .+= ]]
      then
        local LV_VAR_NAME="${LV_VAR_ASSIGNMENT%=*}"
        eval "export ${LV_VAR_ASSIGNMENT}"
        add_env_specific_var "${LV_VAR_NAME}"
      fi
    done <<< "$("${LV_ORACLE_HOME}"/bin/srvctl getenv database -db "${LV_DB_UNIQUE_NAME}" | grep "=")"
  fi

  if [[ -n "${LV_DB_NAME}" ]]
  then
    GV_DB_NAME="${LV_DB_NAME}"
  fi
  if [[ -n "${LV_DB_UNIQUE_NAME}" ]]
  then
    if [[ -z "${ORACLE_UNQNAME}" ]]
    then
      export ORACLE_UNQNAME="${LV_DB_UNIQUE_NAME}"
    fi
    GV_DB_UNIQUE_NAME="${LV_DB_UNIQUE_NAME}"
    GV_DB_UNIQUE_NAME_LC="$(echo "${GV_DB_UNIQUE_NAME}" | to_lower)"
  fi
}
get_cs_db_unique_name_from_gi() {
  if [[ -z "${1}" ]]
  then
    echo "usage: get_cs_db_unique_name_from_gi <DB_UNIQUE_NAME_CASE_INSENSITIVE>" 1>&2
    return 1
  fi

  if [[ -z "${GV_GRID_HOME}" ]]
  then
    echo "no Grid Infrastructure found, resolving DB_UNIQUE_NAME from CRS not possible" 1>&2
    return 1
  fi

  local LV_DB_UNIQUE_NAME LV_DB_UNIQUE_NAME_CI

  LV_DB_UNIQUE_NAME_CI="${1}";
  if [[ -n "${GV_GRID_HOME}" ]]
  then
    LV_DB_UNIQUE_NAME=$("${GV_GRID_HOME}"/bin/crsctl stat res -p -w "((TYPE = ora.database.type))" | grep '^DB_UNIQUE_NAME=' | sort -u | grep -i "=${LV_DB_UNIQUE_NAME_CI}$" | cut -d= -f2)
  fi
  if [[ -z "${LV_DB_UNIQUE_NAME}" ]]
  then
    echo "The DB_UNIQUE_NAME \"${LV_DB_UNIQUE_NAME_CI}\" is not registered in the Grid Infrastructure" 1>&2
    echo "Returning parameter as given" 1>&2
    echo "${LV_DB_UNIQUE_NAME_CI}"
    return 1
  elif [[ $(echo "${LV_DB_UNIQUE_NAME}" | wc -l) -gt 1 ]]
  then
    echo "The DB_UNIQUE_NAME \"${LV_DB_UNIQUE_NAME_CI}\" is registered multiple times in the Grid Infrastructure" 1>&2
    echo "Returning parameter as given" 1>&2
    echo "${LV_DB_UNIQUE_NAME_CI}"
    return 1
  fi
  echo "${LV_DB_UNIQUE_NAME}"
}
wintitle() {
  printf "\033]0;%s\007" "$1"
}
unalias dg 2>/dev/null
dg() {
  if [[ -n "${TWO_TASK}" ]]
  then
    local LV_TWO_TASK="${TWO_TASK}"
    unset TWO_TASK
  fi
  ${GV_RLWRAP} "${ORACLE_HOME}/bin/dgmgrl" / "$@"
  if [[ -n "${LV_TWO_TASK}" ]]
  then
    export TWO_TASK="${LV_TWO_TASK}"
  fi
}
unalias rmanc 2>/dev/null
rmanc() {
  if [[ -n "${TWO_TASK}" ]]
  then
    local LV_TWO_TASK="${TWO_TASK}"
    unset TWO_TASK
  fi
  ${GV_RLWRAP} "${ORACLE_HOME}/bin/rman" target / "$@"
  if [[ -n "${LV_TWO_TASK}" ]]
  then
    export TWO_TASK="${LV_TWO_TASK}"
  fi
}
sqlint() {
  local LV_BINARY

  if [[ -z "${1}" || ( "${1}" != "sqlplus" && "${1}" != "sqlcl" ) ]]
  then
    echo "Interne Methode, erster Parameter muss \"sqlplus\" oder \"sqlcl\" sein."
    return 1
  fi

  LV_BINARY="${1}"
  shift

  if [[ "${LV_BINARY}" == "sqlplus" ]]
  then
    ${GV_RLWRAP} "${ORACLE_HOME}/bin/sqlplus" "$@"
  elif [[ "${LV_BINARY}" == "sqlcl" ]]
  then
    bash "${ORACLE_HOME}/sqldeveloper/sqldeveloper/bin/sql" "$@"
  fi

}
unalias sql 2>/dev/null
sql() {
  if [[ -n "${TWO_TASK}" ]]
  then
    local LV_TWO_TASK="${TWO_TASK}"
    unset TWO_TASK
  fi
  sqlint "sqlplus" / as sysdba "$@"
  if [[ -n "${LV_TWO_TASK}" ]]
  then
    export TWO_TASK="${LV_TWO_TASK}"
  fi
}
unalias sqlcl 2>/dev/null
sqlcl() {
  if [[ -n "${TWO_TASK}" ]]
  then
    local LV_TWO_TASK="${TWO_TASK}"
    unset TWO_TASK
  fi
  sqlint "sqlcl" / as sysdba "$@"
  if [[ -n "${LV_TWO_TASK}" ]]
  then
    export TWO_TASK="${LV_TWO_TASK}"
  fi
}
pdbsqlint() {
  local LV_PDB_NAME LV_BINARY
  if [[ ${#GV_PDB_LIST[@]} -eq 0 ]]
  then
    echo "Aktuell ausgewaehltes Environment enthaelt keine PDBs."
    return 2
  elif [[ ${#GV_PDB_LIST[@]} -eq 1 ]]
  then
    echo "Verbinde mit einziger PDB \"${GV_PDB_LIST[*]}\"."
    LV_PDB_NAME="${GV_PDB_LIST[*]}"
  elif [[ -z "${1}" || ( "${1}" != "sqlplus" && "${1}" != "sqlcl" ) ]]
  then
    echo "Interne Methode, erster Parameter muss \"sqlplus\" oder \"sqlcl\" sein."
    return 1
  elif [[ -z "${2}" ]]
  then
    echo "Bitte PDB-Namen als ersten Parameter angeben."
    echo "Moegliche Namen sind:"
    echo "${GV_PDB_LIST[@]}" | awk '{printf("   %s\n", $0)}'
    return 1
  fi
  LV_PDB_NAME="${2}"
  if [[ -n "${TWO_TASK}" ]]
  then
    local LV_TWO_TASK="${TWO_TASK}"
    unset TWO_TASK
  fi
  LV_BINARY="${1}"
  shift
  export TWO_TASK="(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGS='(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)))'))(CONNECT_DATA=(SERVICE_NAME=${LV_PDB_NAME})))"
  shift

  sqlint "${LV_BINARY}" "$@"

  unset TWO_TASK
  if [[ -n "${LV_TWO_TASK}" ]]
  then
    export TWO_TASK="${LV_TWO_TASK}"
  fi
}
unalias pdbsql 2>/dev/null
pdbsql() {
  pdbsqlint "sqlplus" "$@"
}
unalias pdbsqlcl 2>/dev/null
pdbsqlcl() {
  pdbsqlint "sqlcl" "$@"
}
unalias asmsql 2>/dev/null
asmsql(){
  local LV_ASM_USER LV_PRIVILEGE
  # shellcheck disable=2009 # no pgrep on AIX
  LV_ASM_USER="$(ps -eo user,cmd | grep "asm_[s]mon" | cut -d" " -f1)"
  if [[ "${LV_ASM_USER}" != "${USER}" ]]
  then
    LV_PRIVILEGE="sysdba"
  else
    LV_PRIVILEGE="sysasm"
  fi
  (myoraenv_simple "${GV_ASM_SID}" "${GV_GRID_HOME}" && ${GV_RLWRAP} "${ORACLE_HOME}/bin/sqlplus" / as "${LV_PRIVILEGE}" "$@");
}
unalias asmcmd 2>/dev/null
asmcmd(){
  local LV_ASM_USER LV_PRIVILEGE
  # shellcheck disable=2009 # no pgrep on AIX
  LV_ASM_USER="$(ps -eo user,cmd | grep "asm_[s]mon" | cut -d" " -f1)"
  if [[ "${LV_ASM_USER}" != "${USER}" ]]
  then
    LV_PRIVILEGE="sysdba"
  else
    LV_PRIVILEGE="sysasm"
  fi
  (myoraenv_simple "${GV_ASM_SID}" "${GV_GRID_HOME}" && ${GV_RLWRAP} "${ORACLE_HOME}/bin/asmcmd" --privilege "${LV_PRIVILEGE}" -p "$@");
}
unalias asmdu 2>/dev/null
asmdu() {
  # Fred Denis -- denis@pythian.com -- 2016
  # Jan Schnackenberg - jan.schnackenberg@opitz-consulting.com
  # - If no parameter specified, show a du of each DiskGroup
  # - If a parameter, print a du of each subdirectory
  ## Removing leading "+" if present
  typeset LV_BASEDIR="${1#+}"
  ## Forcing trailing "/" regardless if present or not
  LV_BASEDIR="${LV_BASEDIR%/}/"

  # Colored thresholds (Red, Yellow, Green)
  typeset LC_CRITICAL=90
  typeset LC_WARNING=75

  if ! is_asm_running
  then
    echo "No running ASM found. Abort."
    exit 3
  fi

  typeset LV_DG_LIST LV_LIST_SUBDIRS
  # Manage parameters
  if [[ -z "${LV_BASEDIR%/}" ]]
  then # No directory provided, will check all the DG
    LV_DG_LIST=$(asmcmd lsdg | grep -v State | awk '{print $NF}' | sed s'/\///')
    LV_LIST_SUBDIRS="No" # Do not show the subdirectories details if no directory is specified
  else
    LV_DG_LIST=$(echo "${LV_BASEDIR}" | sed s'/\/.*$//g')
    LV_LIST_SUBDIRS="YES" # Show the subdirectories details if directory is specified
  fi

  LV_DG_LIST="$(echo "${LV_DG_LIST}" | tr '\n' ' ')"

  # Show DG overview
  asmcmd lsdg | sed s'/\/$//' | awk -v DG_LIST="${LV_DG_LIST}" -v W="${LC_WARNING}" -v C="${LC_CRITICAL}" '
    BEGIN {
      COLOR_BEGIN = "\033[1;" ;
      COLOR_END = "\033[m" ;
      RED = COLOR_BEGIN"31m" ;
      GREEN = COLOR_BEGIN"32m" ;
      YELLOW = COLOR_BEGIN"33m" ;
      split(DG_LIST, DG_TMP);
      for (i in DG_TMP) DG_ARR[DG_TMP[i]]="";
    }
    {
      if (NR == 1) {
        for (i=1; i<=NF; i++) {
          if ($i == "Total_MB") {
            i_tmb=i
          } else if ($i == "Free_MB") {
            i_fmb=i
          } else if ($i == "Usable_file_MB") {
            i_umb=i
          }
        }
        printf("\n%25s%10s%10s%10s%10s\n", "DiskGroup",         "Total_GB",  "Free_GB",   "% Free",    "Usable_GB")
        printf(  "%25s%10s%10s%10s%10s\n", "-----------------", "---------", "---------", "---------", "---------")
      } else if ($NF in DG_ARR) {
        FREE_PCT = sprintf("%10d", $i_fmb/$i_tmb*100) ;

        COLOR=GREEN;
        if ((100-FREE_PCT) > W) {COLOR=YELLOW ;}
        if ((100-FREE_PCT) > C) {COLOR=RED ;}
        printf("%25s%10d%10d%10s%10d\n", $NF, $i_tmb/1024, $i_fmb/1024, COLOR FREE_PCT COLOR_END, $i_umb/1024) ;
      }
    }'
  printf "\n"

  # Subdirs info
  if [[ ${LV_LIST_SUBDIRS} == "YES" ]]
  then
    exec_asm_sql_data "
      COL base_path FORMAT A100
      COL sub_item  FORMAT A50
      /*+ ----------------------------------------------------------------
          Query will return the total sum of the contents of all
          sub-items for a given ASM path, ordered by sub-item name
          ---------------------------------------------------------------*/

      SELECT --base_path,
             sub_item,
             ROUND(SUM(mbytes)) mbytes,
             ROUND(SUM(mirror_mbytes)) mirror_mbytes
      FROM (SELECT '+${LV_BASEDIR}' base_path,
                   REGEXP_REPLACE(full_alias_path, '^[+]${LV_BASEDIR}([^/]*)/.*', '\1', 1, 0, 'i') sub_item,
                   mbytes,
                   mirror_mbytes
            FROM (SELECT x.full_alias_path, mbytes, DECODE(redundancy, 'UNPROT', mbytes, 'MIRROR', mbytes*2, mbytes*3) mirror_mbytes
                  FROM (SELECT gnum, filnum, CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname, '/')) full_alias_path, system_created
                        FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
                                    a.reference_index rindex,a.group_number gnum,a.file_number filnum, system_created
                              FROM v\$asm_alias a,
                                   v\$asm_diskgroup g
                              WHERE a.group_number = g.group_number
                             )
                        START WITH (MOD(pindex, POWER(2, 24))) = 0
                          CONNECT BY PRIOR rindex = pindex
                       ) x,
                       (SELECT group_number gnum, file_number filnum, type ftype, bytes/1024/1024 mbytes, redundancy
                        FROM v\$asm_file
                        ORDER BY group_number, file_number
                       ) f
                  WHERE x.filnum != 4294967295
                    AND x.gnum   = f.gnum
                    AND x.filnum = f.filnum
                    AND x.system_created = 'Y') q
            WHERE UPPER(full_alias_path) LIKE UPPER('+${LV_BASEDIR}%'))
      GROUP BY base_path, sub_item
      ORDER BY base_path, sub_item
      " | awk -v D="$LV_BASEDIR" '
      BEGIN {
        printf("\n\t\t%40s\n\n", D " subdirectories size") ;
        printf("%33s%16s%16s\n", "Subdir", "Used MB", "Mirror MB") ;
        printf("%33s%16s%16s\n", "------", "-------", "---------") ;
      }
      {
        printf("%33s%16s%16s\n", $1, $2, $3) ;
        use += $2 ;
        mir += $3 ;
      }
      END {
        printf("\n\n%33s%16s%16s\n", "------", "-------", "---------") ;
        printf("%33s%16s%16s\n\n", "Total", use, mir) ;
      } '
  fi
}
unalias crsstat 2>/dev/null
crsstat(){
  if [[ -z "${GV_GRID_HOME}" ]]
  then
    echo "Kein GRID_HOME und damit keine Grid Infrastructure gefunden."
    return 0
  fi
  (
    set_ora_home_env "${GV_GRID_HOME}" || return 1
    LV_COLS=$(tput cols)
    LV_MAX_NAME_LEN=$(crsctl status resource -t | grep -v -e "^[[:space:]]" -e "^--" -e "^Local Resources" -e "^Cluster Resources" -e "^Name " | wc -L)
    crsctl status resource -v | awk -v v_line_len="${LV_COLS}" -v v_name_len="${LV_MAX_NAME_LEN}" -F= '
      BEGIN {
        v_crt="\033[00;31m";
        v_wrn="\033[01;33m";
        v_ok="\033[00;32m";
        v_clr="\033[0m";
        v_rest=v_line_len - v_name_len - 47 - 15;
        v_name="";
        v_print_name="";
        v_last_server="";
        v_type="";
        v_cardinality="";
        v_target="";
        v_state_details="";
        v_internal_state="";
        v_locals="";
        v_clusters="";
        v_delim_line="-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------";
        printf("%."v_name_len"s   %.4s   %.24s   %.7s   %.12s   %."v_rest"s\n", v_delim_line, v_delim_line, v_delim_line, v_delim_line, v_delim_line, v_delim_line, v_delim_line);
        printf("%-"v_name_len"s   %-4s   %-24s   %-7s   %-12s   %."v_rest"s\n", "Name", "Inst", "Server", "Target", "State", "State Details");
        printf("%."v_name_len"s   %.4s   %.24s   %.7s   %.12s   %."v_rest"s\n", v_delim_line, v_delim_line, v_delim_line, v_delim_line, v_delim_line, v_delim_line, v_delim_line);
      }
      /^NAME=/ {
        if (v_name != "") {
          printf("%-"v_name_len"s   %-4s   %-24s   %-7s   %s%-12s%s   %s%s\n", v_print_name, v_cardinality, v_state[3], v_target, v_state_color, v_state[1], v_clr, v_state_details, v_internal_state);
        }
        v_name=$2;
        v_print_name=$2
        v_last_server="";
        v_type="";
        v_cardinality="";
        v_target="";
        v_state_details="";
        v_internal_state="";
        v_locals="";
        v_clusters="";
      }
      /^LAST_SERVER=/ {
        if (v_last_server != "") {
          printf("%-"v_name_len"s   %-4s   %-24s   %-7s   %s%-12s%s   %s%s\n", v_print_name, v_cardinality, v_state[3], v_target, v_state_color, v_state[1], v_clr, v_state_details, v_internal_state);
          v_print_name="";
        }
        v_last_server=$2;
        v_type="";
        v_cardinality="";
        v_target="";
        v_state_details="";
        v_internal_state="";
        v_locals="";
        v_clusters="";
      }
      /^TYPE=/ {
        v_type=$2
      }
      /^CARDINALITY_ID=/ {
        if ($2 != "ONLINE" && $2 != "OFFLINE") {
          v_cardinality=$2
        }
      }
      /^TARGET=/ {
        v_target=$2
        if (v_target != v_state[1]) {
          if (v_state[1] == "OFFLINE") {
            v_state_color=v_crt;
          } else {
            v_state_color=v_wrn;
          }
        } else {
          v_state_color=v_ok;
        }
      }
      /^STATE=/ {
        split($2, v_state, " ");
        if (v_target != v_state[1]) {
          v_state_color=v_crt;
        } else {
          v_state_color=v_ok;
        }
      }
      /^STATE_DETAILS=/ {
        if ($2 != "") {
          v_state_details=$2", "
        }
      }
      /^INTERNAL_STATE=/ {
        v_internal_state=$2
      }
      END {
        if (v_name != "") {
          printf("%-"v_name_len"s   %-4s   %-24s   %-7s   %s%-12s%s   %s%s\n", v_print_name, v_cardinality, v_state[3], v_target, v_state_color, v_state[1], v_clr, v_state_details, v_internal_state);
        }
      }'
  )
}
unalias llh 2>/dev/null
llh() {
  # shellcheck disable=2012 # find on AIX does not have "-ls" option
  ls -lA "$@" 2>/dev/null | awk '
    {
      if (NR == 1 && $1 == "total") {
        print $0
      }
      if (NR == 1) {
        printf("%-10s %-4s %-8s %-8s %-10s %-12s %s\n", "Privs", "Ref#", "owner", "group", "size", "mtime", "name");
      }
      if ($1 != "total") {
        v_size = $5
        v_unit = "B "
        if ( v_size >= 1024 ) {
          v_size = v_size / 1024
          v_unit = "KB"
          if ( v_size >= 1024 ) {
            v_size = v_size / 1024
            v_unit = "MB"
            if ( v_size >= 1024 ) {
              v_size = v_size / 1024
              v_unit = "GB"
            }
          }
        }
        printf("%10s %4d %8s %-8s %7.2f %2s %3s %2s %5s %s\n", $1, $2, $3, $4, v_size, v_unit, $6, $7, $8, $9);
      }
    }'
}
## implement some enhanced logic
## * simulate the "only local filesystems", even on AIX, where this
##   is not supported out of the box
## * Implicit "list all sub-elements of current directory" when called
##   without parameters
## * Combine those two, by ommitting sub-elements of current directory
##   that are not on the same filesystem than the current directory
unalias edu 2>/dev/null
edu() {
  declare -a LV_FILEDIR_LIST
  local LV_IFS_ORIG
  LV_IFS_ORIG="${IFS}"
  IFS=$'\n'
  if [[ $# -eq 0 || ( $# -eq 1 && -d "$1" ) ]]
  then
    local LV_DIR LV_PREFIX
    LV_DIR="${PWD}"
    if [[ $# -eq 1 ]]
    then
      LV_PREFIX="${1}/"
      LV_DIR="${1}"
    fi
    local LV_ACT_FS
    LV_ACT_FS="$(df -P "${LV_DIR}" | awk 'NR>1{print $NF}')"
    while read -r LV_ELEM
    do
      LV_ELEM_FS="$(df -P "${LV_DIR}/${LV_ELEM}" | awk 'NR>1{print $NF}')"
      if [[ "${LV_ELEM_FS}" == "${LV_ACT_FS}" ]]
      then
        LV_FILEDIR_LIST[${#LV_FILEDIR_LIST[@]}]="${LV_PREFIX}${LV_ELEM}"
      fi
    done <<< "$(ls -1A "${LV_DIR}/")"
  else
    while read -r LV_ELEM
    do
      LV_FILEDIR_LIST[${#LV_FILEDIR_LIST[@]}]="${LV_ELEM}"
    done <<< "$(ls -1Ad "$@")"
  fi
  IFS="${LV_IFS_ORIG}"

  for LV_ELEM in "${LV_FILEDIR_LIST[@]}"
  do
    du -xks "${LV_ELEM}" 2>/dev/null
  done | sort -n | awk '
    BEGIN {
      printf("%10s %s\n", "Size", "Objectname");
    }
    {
      v_size = $1
      $1 = ""
      v_unit = "KB"
      if ( v_size >= 1024 ) {
        v_size = v_size / 1024
        v_unit = "MB"
        if ( v_size >= 1024 ) {
          v_size = v_size / 1024
          v_unit = "GB"
          if ( v_size >= 1024 ) {
            v_size = v_size / 1024
            v_unit = "TB"
          }
        }
      }
      printf("%7.2f %2s %s\n", v_size, v_unit, $0);
    }'
}
unalias edf 2>/dev/null
# shellcheck disable=2120 # This message occurs because this public function is called in this script without parameters
edf() {
  local LV_THRESHOLD_CRIT=95
  local LV_THRESHOLD_WARN=85

  if [[ "$(uname -s)" == "AIX" ]] && ! (which df 2>/dev/null| grep -q "/opt/freeware/")
  then
    local LV_DF="df -Pg"
  else
    local LV_DF="df -Ph"
  fi
  ${LV_DF} "$@" | sed 's/GB blocks/GB_blocks/' | awk -v W="${LV_THRESHOLD_WARN}" -v C="${LV_THRESHOLD_CRIT}" '
    BEGIN {
      v_crt="\033[00;31m";
      v_wrn="\033[01;33m";
      v_ok="\033[00;32m";
      v_clr="\033[0m";
      v_maxelems=0;
    }
    {
      a_lines[NR]=$0;
      for (i = 1; i <= NF; i++) {
        if (length($i) > a_lengths[i]) {
          a_lengths[i] = length($i);
        }
      }
    }
    END {
      for (i = 1; i <= NR; i++) {
        v_nf = split(a_lines[i], a_line);
        if (i == 1) {
          printf( "%-"a_lengths[1]"s  %"a_lengths[2]"s  %"a_lengths[3]"s  %"a_lengths[4]"s  %"a_lengths[5]"s  %s %s\n",
                  a_line[1], a_line[2], a_line[3], a_line[4], a_line[5], a_line[6], a_line[7]);
        } else {
          v_used=a_line[5] + 0;
          if ( v_used > C )
            v_color_used=v_crt
          else if ( v_used > W )
            v_color_used=v_wrn
          else
            v_color_used=v_ok;
          printf( "%-"a_lengths[1]"s  %"a_lengths[2]"s  %"a_lengths[3]"s  %"a_lengths[4]"s  %s%"a_lengths[5]"s%s  %s %s\n",
                  a_line[1], a_line[2], a_line[3], a_line[4], v_color_used, a_line[5], v_clr, a_line[6], a_line[7]);
        }
      }
    }'
}
unalias gdf 2>/dev/null
gdf() {
  # shellcheck disable=SC2119 # edf is called without parameters on purpose
  edf | awk "
    NR == 1 {
      print \$0
    }
    NR > 1 {
      if (\$0 ~ \"$*\") print \$0
    }"
}
unalias vit 2>/dev/null
vit() {
  unset LV_FILE_ARR
  if [[ -f ${HOME}/.tnsnames.ora ]]
  then
    LV_FILE_ARR[${#LV_FILE_ARR[@]}]="${HOME}/.tnsnames.ora"
  fi
  if [[ -n "${TNS_ADMIN}" && -f ${TNS_ADMIN}/tnsnames.ora ]]
  then
    LV_FILE_ARR[${#LV_FILE_ARR[@]}]="${TNS_ADMIN}/tnsnames.ora"
  fi
  if [[ -f /etc/tnsnames.ora ]]
  then
    LV_FILE_ARR[${#LV_FILE_ARR[@]}]="/etc/tnsnames.ora"
  fi
  if [[ -f ${ORACLE_HOME}/network/admin/tnsnames.ora ]]
  then
    LV_FILE_ARR[${#LV_FILE_ARR[@]}]="${ORACLE_HOME}/network/admin/tnsnames.ora"
  fi
  while read -r LV_FILE
  do
    if [[ -e "${LV_FILE}" ]]
    then
      LV_FILE_ARR[${#LV_FILE_ARR[@]}]="${LV_FILE}"
    fi
  done <<< "$(grep -v -e '^[[:space:]]*$' -e '^[ ]*#' -e '^[*]:' /etc/oratab 2>/dev/null | awk -F: '{printf("%s/network/admin/tnsnames.ora\n", $2);}' | sort -u)"
  if [[ "${#LV_FILE_ARR[@]}" -eq 0 ]]
  then
    echo "Keine tnsnames.ora gefunden."
  elif [[ "${#LV_FILE_ARR[@]}" -eq 1 ]]
  then
    vi "${LV_FILE_ARR[@]}"
  else
    local PS3_SAVE="${PS3}"
    PS3="Bitte zu editierende tnsnames.ora auswaehlen (CTRL-C um abzubrechen): "
    export PS3
    select LV_FILE in "${LV_FILE_ARR[@]}"
    do
      if [[ -n "${LV_FILE}" ]]
      then
        vi "${LV_FILE}"
        break;
      fi
    done
    PS3="${PS3_SAVE}"
    export PS3
  fi
  unset LV_FILE_ARR
}
unalias vil 2>/dev/null
vil() {
  unset LV_FILE_ARR
  if [[ -f ${HOME}/.listener.ora ]]
  then
    LV_FILE_ARR[${#LV_FILE_ARR[@]}]="${HOME}/.listener.ora"
  fi
  if [[ -n "${TNS_ADMIN}" && -f ${TNS_ADMIN}/listener.ora ]]
  then
    LV_FILE_ARR[${#LV_FILE_ARR[@]}]="${TNS_ADMIN}/listener.ora"
  fi
  if [[ -f /etc/listener.ora ]]
  then
    LV_FILE_ARR[${#LV_FILE_ARR[@]}]="/etc/listener.ora"
  fi
  while read -r LV_FILE
  do
    if [[ -e "${LV_FILE}" ]]
    then
      LV_FILE_ARR[${#LV_FILE_ARR[@]}]="${LV_FILE}"
    fi
  done <<< "$(grep -v -e '^[[:space:]]*$' -e '^[ ]*#' -e '^[*]:' /etc/oratab 2>/dev/null | awk -F: '{printf("%s/network/admin/listener.ora\n", $2);}' | sort -u)"
  if [[ -f ${ORACLE_HOME}/network/admin/listener.ora ]]
  then
    if ! in_list "${ORACLE_HOME}/network/admin/listener.ora" "${LV_FILE_ARR[@]}"
    then
      LV_FILE_ARR[${#LV_FILE_ARR[@]}]="${ORACLE_HOME}/network/admin/listener.ora"
    fi
  fi
  if [[ "${#LV_FILE_ARR[@]}" -eq 0 ]]
  then
    echo "Keine listener.ora gefunden."
  elif [[ "${#LV_FILE_ARR[@]}" -eq 1 ]]
  then
    vi "${LV_FILE_ARR[@]}"
  else
    local PS3_SAVE="${PS3}"
    PS3="Bitte zu editierende listener.ora auswaehlen (CTRL-C um abzubrechen): "
    export PS3
    select LV_FILE in "${LV_FILE_ARR[@]}"
    do
      if [[ -n "${LV_FILE}" ]]
      then
        vi "${LV_FILE}"
        break;
      fi
    done
    PS3="${PS3_SAVE}"
    export PS3
  fi
  unset LV_FILE_ARR
}
hugepageinfo() {
  awk '
    /Hugepagesize:/{p=$2}
    / 0 /{next}/ kB$/{
      v[sprintf("%9d GB %-s",int($2/1024/1024),$0)]=$0;
      next
    }
    {h[$0]=$2}
    END{
      for(k in v) print k;
      for (k in h) print sprintf("%9d GB %-s",p*h[k]/1024/1024,k)
    }' /proc/meminfo|sort -nr|grep --color=auto -iE "^|(HugePage)[^:]*";
}
grplogs() {
  if [[ "$#" -lt 2 ]]
  then
    echo "usage: grplogs <REGEXP> <LIST_OF_FILES>"
    return 1
  fi
  local LV_REGEXP="$1"
  shift
  awk -v V_SEARCH_REGEX="${LV_REGEXP}" '
    function myprint(filename, linenumber, date, line)
    {
      printf("%-30s [%d][%s]: %s\n", filename, linenumber, date, line);
    }
    BEGIN {
      search_ora_regex = V_SEARCH_REGEX;
      printf("Using regexp: %s\n\n", V_SEARCH_REGEX);
      search_date_regex = "^([A-Z][a-z][a-z] [A-Z][a-z][a-z] .. ..:..:.. ...."
      search_date_regex = search_date_regex"|....-..-.. ..:..:..\\...."
      search_date_regex = search_date_regex"|../../.... ..:..:.."
      search_date_regex = search_date_regex"|....-..-..T..:..:..\\....\\+..:.."
      search_date_regex = search_date_regex"|....-..-..T..:..:..\\.......\\+..:..)$";
      v_date = "";
      v_date_printed = 0;
      v_date_NR = -1;
    }
    {
      if ( $0 ~ search_date_regex) {
        v_date = $0;
        v_date_printed = 0;
        v_date_NR = NR;
      } else if ( $0 ~ search_ora_regex ) {
        myprint(FILENAME, NR, v_date, $0);
      }
    }
  ' "$@"
}
rotate_file() {
  local LV_SOURCE="$1"
  if [[ -z "${LV_SOURCE}" || ! -f "${LV_SOURCE}" ]]
  then
    echo "usage: rotate_file <filename>"
    echo ""
    echo "The file \"filename\" will be renamed by appending date and time."
    echo "The original file will be recreated as an empty file."
    return 1
  fi
  local LV_TIMESTAMP
  LV_TIMESTAMP="$(date +%F_%T)"
  local LV_TARGET="${LV_SOURCE}.${LV_TIMESTAMP}"
  if [[ -f "${LV_SOURCE}" ]]
  then
    mv "${LV_SOURCE}" "${LV_TARGET}"
    echo "Original file renamed to \"${LV_SOURCE}.${LV_TIMESTAMP}\"." > "${LV_SOURCE}"
  fi
}

bkup_api_list_jobs () {
  local P_LINE_COUNT P_BACKUP_TYPE
  local re='^[0-9]+$'
  if [[ -n "${1}" ]]
  then
    if [[ "${1}" =~ $re ]]
    then
      P_LINE_COUNT="${1}"
    else
      P_BACKUP_TYPE="${1}"
    fi
  fi
  if [[ -n "${2}" ]]
  then
    if [[ "${2}" =~ $re ]]
    then
      P_LINE_COUNT="${2}"
    else
      P_BACKUP_TYPE="${2}"
    fi
  fi
  local LV_LINE_FILTER
  if [[ -z "${GV_DB_UNIQUE_NAME}" ]]
  then
    echo "No DB environment set (GV_DB_UNIQUE_NAME is empty). Abort" 1>&2
    return 1
  fi
  if [[ ! -x /var/opt/oracle/bkup_api/bkup_api ]]
  then
    echo "The BKUP_API (/var/opt/oracle/bkup_api/bkup_api) not found. Abort" 1>&2
    return 1
  fi
  if [[ -z "${P_LINE_COUNT}" ]]
  then
    echo "listing last 1000 jobs" 1>&2
    P_LINE_COUNT=1000
  fi
  #P_BACKUP_TYPE=DB|FULL|INCREMENTAL|KEEP|ARCH
  case "${P_BACKUP_TYPE}" in
    "DB")
      LV_LINE_FILTER="/create-backup-/"
    ;;
    "FULL")
      LV_LINE_FILTER="/create-backup-full/"
    ;;
    "INCREMENTAL")
      LV_LINE_FILTER="/create-backup-incremental/"
    ;;
    "KEEP")
      LV_LINE_FILTER="/create-backup-keep-forever/"
    ;;
    "ARCH")
      LV_LINE_FILTER="/archivelog-backup/"
    ;;
    *)
      LV_LINE_FILTER=""
    ;;
  esac
  /var/opt/oracle/bkup_api/bkup_api --dbname="${GV_DB_UNIQUE_NAME}" list jobs | awk -v cnt=10 'BEGIN{v_list_started=0} /^UUID/{v_list_started=1; v_head=$0} '"${LV_LINE_FILTER}"'{if (v_list_started==1) {v_buffer=v_buffer RS $0}} v_buffer~/(\n.*){'"${P_LINE_COUNT}"'}/{sub(/[^\n]*\n/,"",v_buffer)} END{print v_head; print v_buffer}'
}
bkup_api_last_job () {
  local P_BACKUP_TYPE LV_UUID
  P_BACKUP_TYPE="${1}"
  if [[ -z "${GV_DB_UNIQUE_NAME}" ]]
  then
    echo "No DB environment set (GV_DB_UNIQUE_NAME is empty). Abort" 1>&2
    return 1
  fi
  if [[ ! -x /var/opt/oracle/bkup_api/bkup_api ]]
  then
    echo "The BKUP_API (/var/opt/oracle/bkup_api/bkup_api) not found. Abort" 1>&2
    return 1
  fi
  #P_BACKUP_TYPE=DB|FULL|INCREMENTAL|KEEP|ARCH
  case "${P_BACKUP_TYPE}" in
    "DB")
    ;;
    "FULL")
    ;;
    "INCREMENTAL")
    ;;
    "KEEP")
    ;;
    "ARCH")
    ;;
    *)
      printf "\e[00;31mBackup type \"%s\" not supported, must be one of [DB|FULL|INCREMENTAL|KEEP|ARCH].\e[00m\n" "${P_BACKUP_TYPE}" 1>&2
      return 1
    ;;
  esac
  LV_UUID="$(bkup_api_list_jobs 1 "${P_BACKUP_TYPE}" | tail -n 1 | cut -f1 -d" ")"
  /var/opt/oracle/bkup_api/bkup_api --dbname="${GV_DB_UNIQUE_NAME}" --uuid="${LV_UUID}"
}

################################################################################
# Find the first parameter in the rest of the parameters and write the index
# of the first hit to stdout
#
# Parameters: $1     - value to search for
#             $2..$# - list of values to search in
# Return code: 0 if found
#              1 if not found
# Output: The index at which the first value is found in the rest of the values
#         is written to stdout.
#         Example
#           "in_list_index wert ein wert"
#         will return "0" and write "1" to stdout
#         Example
#           "in_list_index wert nicht gefunden"
#         will return "1" and write "-1" to stdout
################################################################################
in_list_index() {
  local LV_SEARCH LV_INDEX=0
  LV_SEARCH="$1"
  shift
  for LV_ELEM in "$@"
  do
    if [[ "${LV_ELEM}" == "${LV_SEARCH}" ]]
    then
      echo "${LV_INDEX}"
      return 0
    fi
    (( LV_INDEX = LV_INDEX + 1 ))
  done
  echo "-1"
  return 1
}

################################################################################
# check if the first parameter is an element of the list that is made up by the
# remaining parameters.
#
# Parameters: $1     - value to search for
#             $2..$# - list of values to search in
# Return code: 0 if found
#              1 if not found
# Output: NONE
################################################################################
in_list() {
  local LV_INDEX
  LV_INDEX="$(in_list_index "$@")"
  return $?
}
##############################################################################
# Pipeline-function to convert stdin to lowercase
##############################################################################
to_lower() {
  tr '[:upper:]' '[:lower:]'
}
##############################################################################
# Quickly list all running instances
#
# Prerequisites: None
##############################################################################
get_running_sids() {
  ps -eo user,args | awk '/[[:alpha:]]_smon_[[:alnum:]]/{split($2, a, "_"); print a[3];}' | sort -u
}
##############################################################################
# Find out if database-instance is running by looking for ora_smon process
# Oracle XE databases use "xe_smon" as process-prefix
# Oracle 23c FREE databases use "db_smon" as process-prefix
#
# Prerequisites: None
##############################################################################
is_db_running() {
  local LV_ORACLE_SID
  LV_ORACLE_SID=$1
  # shellcheck disable=2009 # no pgrep on AIX
  ps -ef | grep -q -E -w "(ora|xe|db)_smon_${LV_ORACLE_SID}"
}
##############################################################################
# Find out if ASM-instance is running by looking for asm_smon process
##############################################################################
is_asm_running() {
  [[ -n "${GV_ASM_SID}" ]] || return 1
  # No Index for the array, we only use the first hit
  # include a backslash in the RegExp to escape the "+" of the ASM SID
  # shellcheck disable=2009 # no pgrep on AIX
  ps -ef | grep -q -E -w "(asm)_smon_\\${GV_ASM_SID}"
}
##############################################################################
# Execute an SQL statement against the current database.
# Output will contain only resulting data, without header.
##############################################################################
exec_sql_data() {
  if [[ -n "${TWO_TASK}" ]]
  then
    local LV_TWO_TASK="${TWO_TASK}"
    unset TWO_TASK
  fi
  if [[ -z "$1" ]]
  then
    echo "Method \"exec_sql_data\" needs one (non-empty) parameter. Abort" 1>&2
    return 1
  elif ! is_db_running "${ORACLE_SID}" && ! ( [[ "${ORACLE_SID}" == "${GV_ASM_SID}" ]] && is_asm_running )
  then
    echo "Database-instance \"${ORACLE_SID}\" is not running. Cannot execute query. Abort" 1>&2
    return 1
  else
    "${ORACLE_HOME}/bin/sqlplus" -L -S "/ as sysdba" <<-EOT
			SET LINES 2000 PAGES 0 FEEDBACK OFF VERIFY OFF
			WHENEVER SQLERROR EXIT FAILURE;
			${1};
			EXIT;
		EOT
    local LV_RET_CODE=$?
  fi
  if [[ -n "${LV_TWO_TASK}" ]]
  then
    export TWO_TASK="${LV_TWO_TASK}"
  fi
  return ${LV_RET_CODE}
}
##############################################################################
# Execute an SQL statement against all running instances
# Output will contain only resulting data, without header.
##############################################################################
exec_on_all_sids() {
  shopt -s expand_aliases
  for LV_SID in $(get_running_sids)
  do
    ## Call alias to set environment
    eval "${LV_SID}" >/dev/null
    exec_sql_data "$1"
  done
  shopt -u expand_aliases
}
##############################################################################
# Execute an SQL statement against the ASM-instance.
# Output will contain only resulting data, without header.
##############################################################################
exec_asm_sql_data() {
  (
    if myoraenv "${GV_ASM_SID}"
    then
      exec_sql_data "$1"
      local LV_RET_CODE=$?
    else
      echo "Evironment for ASM-instance \"${GV_ASM_SID}\" could not be set. Abort" 1>&2
      local LV_RET_CODE=1
    fi
    return $LV_RET_CODE
  )
}
get_lsnr_tracefile(){
  local LV_LSNR_NAME="$1"
  [[ -z "${LV_LSNR_NAME}" ]] && LV_LSNR_NAME=LISTENER
  local LV_LSNR_ORA_HOMES_STRING
  # shellcheck disable=2009 # no pgrep on AIX
  LV_LSNR_ORA_HOMES_STRING="$(ps -eo args | grep -E "tnslsnr[[:space:]]+${LV_LSNR_NAME}[[:space:]]" | awk '{print $1}' | sed 's|/bin/tnslsnr||' | sort -u)"

  unset LV_LSNR_ORA_HOMES
  for LV_HOME in ${LV_LSNR_ORA_HOMES_STRING}
  do
    LV_LSNR_ORA_HOMES[${#LV_LSNR_ORA_HOMES[@]}]="${LV_HOME}"
  done

  if [[ "${#LV_LSNR_ORA_HOMES[@]}" -eq 0 ]]
  then
    echo "Cannot determine ORACLE_HOME for listener \"${LV_LSNR_NAME}\"."
    return 1
  elif [[ "${#LV_LSNR_ORA_HOMES[@]}" -eq 1 ]]
  then
    LV_LSNR_ORA_HOME="${LV_LSNR_ORA_HOMES[0]}"
  else
    local LV_PS3_SAVE="${PS3}"
    PS3="Bitte zu verwendendes Listener ORACLE_HOME auswaehlen (CTRL-C um abzubrechen): "
    export PS3
    select LV_HOME in "${LV_LSNR_ORA_HOMES[@]}"
    do
      if [[ -n "${LV_HOME}" ]]
      then
        LV_LSNR_ORA_HOME="${LV_HOME}"
        break;
      fi
    done
    PS3="${LV_PS3_SAVE}"
    export PS3
  fi
  if [[ -z "${LV_LSNR_ORA_HOME}" ]]
  then
    echo "Cannot determine ORACLE_HOME for listener \"${LV_LSNR_NAME}\"." 1>&2
    return 1
  fi
  local LV_LSNR_NAME_LOWER
  LV_LSNR_NAME_LOWER="$(echo "${LV_LSNR_NAME}" | to_lower)"
  local LV_LSNR_STATUS
  if LV_LSNR_STATUS="$(set_ora_home_env "${LV_LSNR_ORA_HOME}"; "${ORACLE_HOME}/bin/lsnrctl" status "${LV_LSNR_NAME}")"
  then
    local LV_LSNR_LOG
    LV_LSNR_LOG="$(echo "${LV_LSNR_STATUS}" | awk '/Listener Log File/{print $4}' | sed "s|alert/log.xml|trace/${LV_LSNR_NAME_LOWER}.log|")"
    echo "${LV_LSNR_LOG}"
  else
    echo "Cannot determine logfile for listener \"${LV_LSNR_NAME}\"."  1>&2
    return 1
  fi
}
talsnr(){
  local LV_LSNR_LOG
  if LV_LSNR_LOG=$(get_lsnr_tracefile "$1")
  then
    tail -n50 -f "${LV_LSNR_LOG}"
  fi
}
vilsnr(){
  local LV_LSNR_LOG
  if LV_LSNR_LOG=$(get_lsnr_tracefile "$1")
  then
    ${GV_PAGER} "${LV_LSNR_LOG}"
  fi
}
fill_pdb_details_list () {
  unset GV_PDB_LIST_DETAILS

  if [[ "${GV_IS_CDB}" == "YES" ]]
  then
    while read -r LV_PDB_ELEM
    do
      GV_PDB_LIST_DETAILS[${#GV_PDB_LIST_DETAILS[@]}]="${LV_PDB_ELEM}"
    done <<< "$(exec_sql_data "SELECT name ||'|'|| open_mode ||'|'|| restricted ||'|'|| TO_CHAR(open_time, 'YYYY-MM-DD HH24:MI:SS') ||'|'|| DECODE(local_undo, 1, 'YES', 'NO') FROM v\$pdbs WHERE name <> 'PDB\$SEED' ORDER BY name")"
  fi
}
list_pdbs() {
  if [[ "${GV_IS_CDB}" == "YES" ]]
  then
    exec_sql_data "SELECT name FROM v\$pdbs WHERE name <> 'PDB\$SEED' ORDER BY name"
  fi
}
list_user_sessions() {
  exec_sql_data "
    SET PAGES 50
    COLUMN username FORMAT A40
    COLUMN service_name FORMAT A30
    SELECT username,
           service_name,
           inst_id,
           COUNT(*)
    FROM gv\$session
    GROUP BY username,
             service_name,
             inst_id
    ORDER BY 1, 2, 3"
}
get_sid_info() {
  local LV_ENV_VAR_QUERY
  local LV_ENV_VAR_CMDS
  local LV_ADR_INST_TYPE
  unset GV_INSTANCE_STATUS    2>/dev/null
  unset GV_INSTANCE_STARTUP   2>/dev/null
  unset GV_DB_ID              2>/dev/null
  unset GV_DB_LOG_MODE        2>/dev/null
  unset GV_DB_ROLE            2>/dev/null
  unset GV_DB_FORCE_LOGGING   2>/dev/null
  unset GV_DB_FLASHBACK_ON    2>/dev/null
  GV_ORACLE_SID_LC="$(echo "${ORACLE_SID}" | to_lower)"
  export GV_ORACLE_SID_LC

  LV_ENV_VAR_QUERY="SET LINES 2000 PAGES 0 HEAD OFF FEEDBACK OFF
WHENEVER SQLERROR CONTINUE;
SELECT 'REAL'||'OUTPUT::export GV_INSTANCE_STATUS=\"'||status||'\"' FROM v\$instance;
SELECT 'REAL'||'OUTPUT::export GV_INSTANCE_STARTUP=\"'||TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS')||'\"' FROM v\$instance;"

  if [[ "$(echo "${ORACLE_SID}" | cut -b1)" != "+" ]]
  then
    LV_ADR_INST_TYPE=rdbms
  else
    LV_ADR_INST_TYPE=asm
  fi

  if [[ "$(echo "${ORACLE_SID}" | cut -b1)" != "+" ]]
  then
    LV_ENV_VAR_QUERY="${LV_ENV_VAR_QUERY}
SET SERVEROUTPUT ON
DECLARE
  v_maj_ver NUMBER(2);
  v_cdb   VARCHAR2(3);
BEGIN
  SELECT SUBSTR(version, 1, INSTR(version, '.')-1)
  INTO v_maj_ver
  FROM sys.v\$instance;

  IF v_maj_ver >= 12
  THEN
    EXECUTE IMMEDIATE 'SELECT cdb FROM sys.v\$database' INTO v_cdb;  ELSE
    v_cdb := 'NO';
  END IF;

  DBMS_OUTPUT.ENABLE;
  DBMS_OUTPUT.PUT_LINE('REAL'||'OUTPUT::export GV_IS_CDB='||v_cdb);
END;
/"
  fi

  LV_ENV_VAR_QUERY="${LV_ENV_VAR_QUERY}
SELECT 'REAL'||'OUTPUT::export GV_INSTANCE_DIAGNOSTIC_DEST=\"'||value||'\"' FROM v\$parameter WHERE name = 'diagnostic_dest';
SELECT 'REAL'||'OUTPUT::export GV_INSTANCE_BDUMP_DEST=\"'||value||'\"' FROM v\$parameter WHERE name = 'background_dump_dest';
SELECT 'REAL'||'OUTPUT::export GV_DB_UNIQUE_NAME=\"'||value||'\"' FROM v\$parameter WHERE name = 'db_unique_name';
-- If instance is only 'STARTED' then this statement will fail. That's ok, the variable is already set
-- correctly in this case.
WHENEVER SQLERROR CONTINUE;
SELECT CASE
         WHEN vi.status = 'MOUNTED' THEN 'REAL'||'OUTPUT::export GV_INSTANCE_STATUS=\"'||vi.status||'\"'
         ELSE 'REAL'||'OUTPUT::export GV_INSTANCE_STATUS=\"'||vi.status||' ('||vd.open_mode||')\"'
       END
FROM v\$instance vi, v\$database vd;"
  if [[ "$(echo "${ORACLE_SID}" | cut -b1)" != "+" ]]
  then
    LV_ENV_VAR_QUERY="${LV_ENV_VAR_QUERY}
SELECT 'REAL'||'OUTPUT::export GV_DB_NAME='||name FROM v\$database;
SELECT 'REAL'||'OUTPUT::export GV_DB_ID='||dbid FROM v\$database;
SELECT 'REAL'||'OUTPUT::export GV_DB_LOG_MODE='||log_mode FROM v\$database;
SELECT 'REAL'||'OUTPUT::export GV_DB_ROLE=\"'||database_role||'\"' FROM v\$database;
SELECT 'REAL'||'OUTPUT::export GV_DB_FORCE_LOGGING=\"'||force_logging||'\"' FROM v\$database;
SELECT 'REAL'||'OUTPUT::export GV_DB_FLASHBACK_ON=\"'||flashback_on||'\"' FROM v\$database;"
  else
    LV_ENV_VAR_QUERY="${LV_ENV_VAR_QUERY}
SELECT 'REAL'||'OUTPUT::export GV_DB_NAME=+ASM' FROM dual;"
  fi

  ## Dummy-Query at the end. Last query in "exec_sql_data" must not end with a ";"
  LV_ENV_VAR_QUERY="${LV_ENV_VAR_QUERY}
SELECT 'x' FROM dual"

  set -o pipefail
  if ! LV_ENV_VAR_CMDS="$(exec_sql_data "${LV_ENV_VAR_QUERY}" 2>/dev/null | grep "^REALOUTPUT::" | sed 's/^REALOUTPUT:://g')"
  then
    export GV_INSTANCE_STATUS=IDLE
    unset GV_INSTANCE_STARTUP
  fi
  set +o pipefail

  if [[ -n "${LV_ENV_VAR_CMDS}" ]]
  then
    eval "$(echo "${LV_ENV_VAR_CMDS}" | grep '^export')"
    # shellcheck disable=2153
    GV_DB_UNIQUE_NAME_LC="$(echo "${GV_DB_UNIQUE_NAME}" | to_lower)"
    export GV_DB_UNIQUE_NAME_LC
    local LV_BDUMP_ALERT="${GV_INSTANCE_BDUMP_DEST}/alert_${ORACLE_SID}.log"
    local LV_BDUMP_DRC="${GV_INSTANCE_BDUMP_DEST}/drc${ORACLE_SID}.log"
    local LV_DIAG_ALERT="${GV_INSTANCE_DIAGNOSTIC_DEST}/diag/${LV_ADR_INST_TYPE}/${GV_DB_UNIQUE_NAME_LC}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log"
    local LV_DIAG_DRC="${GV_INSTANCE_DIAGNOSTIC_DEST}/diag/${LV_ADR_INST_TYPE}/${GV_DB_UNIQUE_NAME_LC}/${ORACLE_SID}/trace/drc${ORACLE_SID}.log"
    if [[ -f "${LV_BDUMP_ALERT}" && "${LV_DIAG_ALERT}" -ot "${LV_BDUMP_ALERT}" ]]
    then
      export GV_INSTANCE_ALERT_LOG="${LV_BDUMP_ALERT}"
      export GV_INSTANCE_DRC_LOG="${LV_BDUMP_DRC}"
    else
      export GV_INSTANCE_ALERT_LOG="${LV_DIAG_ALERT}"
      export GV_INSTANCE_DRC_LOG="${LV_DIAG_DRC}"
      export GV_INSTANCE_DIAGNOSTIC_DEST="${GV_INSTANCE_DIAGNOSTIC_DEST}/diag/${LV_ADR_INST_TYPE}/${GV_DB_UNIQUE_NAME_LC}/${ORACLE_SID}"
    fi
  elif [[ ${LV_SQL_RET_CODE} -eq 1 ]]
  then
    export GV_INSTANCE_STATUS=IDLE
    unset GV_INSTANCE_STARTUP
  else
    eval "$(echo "${LV_ENV_VAR_CMDS}" | grep '^export')"
  fi
}
print_pdb_listing() {
  unset GV_PDB_LIST
  printf "   %-15s %-10s %-10s %-19s %-10s\n" "PDB NAME" "STATUS" "RESTRICTED" "OPEN TIME" "LOCAL UNDO"
  printf "   %-15s %-10s %-10s %-19s %-10s\n" "---------------" "----------" "----------" "-------------------" "----------"
  for LV_PDB_DETAIL in "${GV_PDB_LIST_DETAILS[@]}"
  do
    GV_PDB_LIST[${#GV_PDB_LIST[@]}]=$(echo "${LV_PDB_DETAIL}" | cut -d"|" -f1)
    echo "${LV_PDB_DETAIL}" | awk -F"|" '{printf("   %-15s %-10s %-10s %-19s %-10s\n", $1, $2, $3, $4, $5)}'
  done
}
unalias sta 2>/dev/null
sta() {
  get_sid_info
  echo "================================="
  echo "INSTANCE NAME       : ${ORACLE_SID}"
  echo "INSTANCE STATUS     : ${GV_INSTANCE_STATUS}"
  # shellcheck disable=2153
  echo "INSTANCE START TIME : ${GV_INSTANCE_STARTUP}"
  unset GV_PDB_LIST
  if [[ "$(echo "${ORACLE_SID}" | cut -b1)" != "+" ]]
  then
    if [[ -n "${GV_DB_NAME}" || -n "${GV_DB_NAME}" ]]
    then
      echo "DB NAME             : ${GV_DB_NAME}"
      echo "DB UNIQUE NAME      : ${GV_DB_UNIQUE_NAME}"
    fi
    if [[ -n "${GV_DB_ID}" ]]
    then
      echo "DB ID               : ${GV_DB_ID}"
      echo "DB LOGGING MODE     : ${GV_DB_LOG_MODE}"
      echo "DB ROLE             : ${GV_DB_ROLE}"
      echo "DB FORCE LOGGING    : ${GV_DB_FORCE_LOGGING}"
      echo "DB FLASHBACK ON     : ${GV_DB_FLASHBACK_ON}"
      echo "DB is CDB           : ${GV_IS_CDB}"
      if [[ "${GV_IS_CDB}" == "YES" ]]
      then
        echo "PDBs"

        fill_pdb_details_list

        print_pdb_listing
      fi
    fi
  fi
  echo "================================="
}
generate_alias_for_home() {
  local LV_ORA_HOME_NAME=$1
  local LV_ORA_HOME_PATH=$2
  # shellcheck disable=2139,2086
  alias ${LV_ORA_HOME_NAME}="clroraenv; set_ora_home_env '${LV_ORA_HOME_PATH}'; export ORACLE_SID='${LV_ORA_HOME_NAME}'"
}
generate_alias_for_dbuniquename() {
  local LV_DB_UNIQUE_NAME LV_DB_UNIQUE_NAME_LC
  LV_DB_UNIQUE_NAME="$1"
  LV_DB_UNIQUE_NAME_LC="$(echo "$LV_DB_UNIQUE_NAME" | to_lower)"
  LV_SID="$2"
  ## If GV_GRID_HOME is set, then Grid Infrastructure is assumed to be running
  ## In this case, myoraenv will read basic information from there, even if
  ## get_sid_info cannot read it from the instance (e.g. because it's not running)
  if [[ -n "${GV_GRID_HOME}" ]]
  then
    # shellcheck disable=2139,2086
    alias ${LV_DB_UNIQUE_NAME}="clroraenv; myoraenv '${LV_SID}'; sta"
    # shellcheck disable=2139,2086
    alias ${LV_DB_UNIQUE_NAME_LC}="clroraenv; myoraenv '${LV_SID}'; sta"
  fi
}
generate_alias_for_sid() {
  local LV_SID LV_ORA_HOME LV_TNS_ADMIN LV_SID_LC
  LV_SID="$1"
  LV_ORA_HOME="$2"
  LV_TNS_ADMIN="$3"
  LV_SID_LC="$(echo "$LV_SID" | to_lower)"
  ## If GV_GRID_HOME is set, then Grid Infrastructure is assumed to be running
  ## In this case, myoraenv will read basic information from there, even if
  ## get_sid_info cannot read it from the instance (e.g. because it's not running)
  if [[ -n "${GV_GRID_HOME}" ]]
  then
    # shellcheck disable=2139,2086
    alias ${LV_SID}="clroraenv; myoraenv '${LV_SID}'; sta"
    # shellcheck disable=2139,2086
    alias ${LV_SID_LC}="clroraenv; myoraenv '${LV_SID}'; sta"
  else
    # shellcheck disable=2139,2086
    alias ${LV_SID}="clroraenv; myoraenv_simple '${LV_SID}' '${LV_ORA_HOME}' '${LV_TNS_ADMIN}'; sta"
    # shellcheck disable=2139,2086
    alias ${LV_SID_LC}="clroraenv; myoraenv_simple '${LV_SID}' '${LV_ORA_HOME}' '${LV_TNS_ADMIN}'; sta"
  fi
}
generate_alias_for_listener() {
  local LV_ORA_LSNR_NAME=$1
  local LV_ORA_LSNR_HOME=$2
  # shellcheck disable=2139,2086
  alias ${LV_ORA_LSNR_NAME}="clroraenv; set_ora_home_env \"${LV_ORA_LSNR_HOME}\"; export ORACLE_SID=\"${LV_ORA_LSNR_NAME}\"; GV_INSTANCE_DIAGNOSTIC_DEST=\"$(dirname "$(get_lsnr_tracefile "${LV_ORA_LSNR_NAME}")")/..\""
}
get_oracle_home_owner() {
  local LV_ORACLE_HOME
  LV_ORACLE_HOME="${1}"
  if [[ -f "${LV_ORACLE_HOME}/bin/oracle" ]]
  then
    mystat "${LV_ORACLE_HOME}/bin/oracle"
  elif [[ -f "${LV_ORACLE_HOME}/bin/emctl" ]]
  then
    mystat "${LV_ORACLE_HOME}/bin/emctl"
  else
    mystat "${LV_ORACLE_HOME}"
  fi
}
list_homes() {
  declare -a LV_ORA_INSTLOC_LIST
  while read -r LV_ORA_INSTLOC_ELEM
  do
    if [[ -n "${LV_ORA_INSTLOC_ELEM}" ]]
    then
      LV_ORA_INSTLOC_LIST[${#LV_ORA_INSTLOC_LIST[@]}]="${LV_ORA_INSTLOC_ELEM}"
    fi
  done <<< "$( ( awk -F: '/^[^#]*:[^#]+:/{printf("%s/oraInst.loc\n", $2)}' /etc/oratab 2>/dev/null; awk -F: '/[^[:space:]]/{printf("%s/oraInst.loc\n", $1)}' /etc/oragchomelist 2>/dev/null; awk -F= '/crs_home/{printf("%s/oraInst.loc\n", $2)}' /etc/oracle/olr.loc 2>/dev/null; ls -1 /etc/oraInst.loc 2>/dev/null) | sort -u )"
  if [[ "${#LV_ORA_INSTLOC_LIST[@]}" -gt 0 ]]
  then
    local LV_ORA_INVENTORY_LIST
    for LV_INSTLOC_ELEM in "${LV_ORA_INSTLOC_LIST[@]}"
    do
      LV_ORA_INVENTORY_ELEM="$(awk -F= '/inventory_loc/{printf("%s/ContentsXML/inventory.xml\n", $2)}' "${LV_INSTLOC_ELEM}" 2>/dev/null | sort -u)"
      if ! in_list "${LV_ORA_INVENTORY_ELEM}" "${LV_ORA_INVENTORY_LIST[@]}"
      then
        LV_ORA_INVENTORY_LIST[${#LV_ORA_INVENTORY_LIST[@]}]="${LV_ORA_INVENTORY_ELEM}"
      fi
    done
    # shellcheck disable=SC2086 # the variable "LV_ORA_INVENTORY" must not be quoted, it contains multiple filenames
    while read -r LV_DUMMY_HOME
    do
      local LV_ORA_HOME_NAME="${LV_DUMMY_HOME%=*}"
      local LV_ORA_HOME_PATH="${LV_DUMMY_HOME#*=}"
      if [[ -d "${LV_ORA_HOME_PATH}" ]]
      then
        generate_alias_for_home "${LV_ORA_HOME_NAME}" "${LV_ORA_HOME_PATH}"
        GV_ALIAS_LIST[${#GV_ALIAS_LIST[@]}]="${LV_ORA_HOME_NAME}"
        printf "%-4s   %-10s   %-40s   %s\n"  "home" "$(get_oracle_home_owner "${LV_ORA_HOME_PATH}")" "${LV_ORA_HOME_NAME}" "${LV_ORA_HOME_PATH}"
        ## if this is the GRID_HOME, then activate this environment now
        if [[ -n "${GV_GRID_HOME}" && "${LV_ORA_HOME_PATH}" == "${GV_GRID_HOME}" ]]
        then
          eval "${LV_ORA_HOME_NAME}"
          shopt -s expand_aliases
        fi
      fi
    done <<< "$(awk '/<HOME /{split($0, a, " ");
                              for (b in a) {
                                if (a[b] ~ "LOC") {
                                  split(a[b], c, "=");
                                  home_loc=c[2]
                                } else if (a[b] ~ "NAME") {
                                  split(a[b], c, "=");
                                  home_name=c[2]
                                } else if (a[b] ~ "REMOVED") {
                                  next;
                                }
                              };
                              gsub("\"", "", home_name);
                              gsub("\"", "", home_loc);
                              printf("OraHome_%s=%s\n", home_name, home_loc);
                             }' "${LV_ORA_INVENTORY_LIST[@]}" 2>/dev/null | sort -u)"
  fi
}
mystat() {
  local LV_TARGET="${1}"
  if [[ "$(uname -s)" == "AIX" ]] && ! (which stat 2>/dev/null| grep -q "/opt/freeware/")
  then
    istat "${LV_TARGET}" | awk '/Owner/{split($2, v_a, "[()]"); print v_a[2]; }'
  else
    stat -c "%U" "${LV_TARGET}"
  fi
}
list_sids() {
  local LV_IS_RAC LV_ASM_LINE_LIST LV_ASM_LINE_COUNT
  LV_IS_RAC=$(is_rac && echo TRUE || echo FALSE);
  LV_ASM_LINE_LIST=$(grep '^+ASM[0-9]*:[^:]\+:.*$' /etc/oratab 2>/dev/null)
  LV_ASM_LINE_COUNT="$(echo "${LV_ASM_LINE_LIST}" | wc -l)"
  if [[ "${LV_ASM_LINE_COUNT}" -gt 1 ]]
  then
    echo "${GV_B_RED}More than one ASM-instance in /etc/oratab! Using only the first.${GV_CCLR}"
  fi
  ## ASM Instance from Grid Infrastructure
  if [[ -n "${GV_GRID_HOME}" ]]
  then
    local LV_PROC LV_ASM_SID
    if [[ -n "${GV_ASM_SID}" ]]
    then
      ## IMPORTANT: The "\\" in the awk line-filter escapes the "+" at the beginning of the ASM SID
      LV_PROC=$(ps -eo user,args | awk "/[[:alpha:]]_smon_\\${GV_ASM_SID}/"'{printf("%s,%s\n", $1, $2)}')
      if [[ -z "${LV_PROC}" ]]
      then
        LV_PROC=",asm_smon_${GV_ASM_SID}"
      fi
      if alias "${GV_ASM_SID}" >/dev/null 2>&1
      then
        unalias "${GV_ASM_SID}"
      fi
      local LV_PROC_USER LV_PROC_ARGS LV_TYPE LV_COMMENT
      LV_PROC_USER="$(echo "${LV_PROC}" | cut -d, -f1)"
      LV_PROC_ARGS="$(echo "${LV_PROC}" | cut -d, -f2)"
      LV_TYPE="${LV_PROC_ARGS:0:3}"
      if [[ -z "${LV_PROC_USER}" ]]
      then
        LV_COMMENT="(crs,${GV_T_RED}down${GV_CCLR})"
      else
        LV_COMMENT="(crs,${GV_T_GREEN}up${GV_CCLR})"
      fi
      ## Format-width for "COMMENT"-column adjusted for 12 (8+4) invisible format characters (see comments with
      ## color-constant definitions at top of script)
      printf "%-4s   %-10s   %-16s %-25s             %s\n"  "${LV_TYPE}" "${LV_PROC_USER}" "${GV_ASM_SID}" "${LV_COMMENT}" "${GV_GRID_HOME}"
      generate_alias_for_sid "${GV_ASM_SID}" "${GV_GRID_HOME}"
      GV_ALIAS_LIST[${#GV_ALIAS_LIST[@]}]="${GV_ASM_SID}"
    fi
  fi
  ## DB Instances from Grid Infrastructure
  if [[ -n "${GV_GRID_HOME}" ]]
  then
    typeset LV_DB_RESOURCE_LIST
    LV_DB_RESOURCE_LIST=$("${GV_GRID_HOME}/bin/crsctl" stat res -w "((TYPE = ora.database.type))" | awk -F= '/^NAME=/{print $2}')
    for LV_RES in ${LV_DB_RESOURCE_LIST}
    do
      typeset LV_ENV_VARS
      LV_ENV_VARS="$("${GV_GRID_HOME}/bin/crsctl" stat res "${LV_RES}" -p | awk -v hn="$(hostname | cut -d. -f1)" -F= 'BEGIN{sid_set = 0} /^(DB_UNIQUE_NAME|USR_ORA_INST_NAME|USR_ORA_INST_NAME@SERVERNAME\(.*\)|ORACLE_HOME|USR_ORA_ENV=TNS_ADMIN)=/{if ($1 == "DB_UNIQUE_NAME") {printf("typeset LV_DB_UNIQUE_NAME=%s\n", $2)} else if ($1 == "USR_ORA_ENV" && $2 == "TNS_ADMIN") {printf("typeset LV_TNS_ADMIN=%s\n", $3)} else if ($1 == "ORACLE_HOME") {printf("typeset LV_ORA_HOME=%s\n", $2)} else if ($1 == "USR_ORA_INST_NAME" && $2 != "") {printf("typeset LV_INST_NAME=%s\n", $2);sid_set = 1;} else if (sid_set == 0 && $1 == "USR_ORA_INST_NAME@SERVERNAME("hn")") {printf("typeset LV_INST_NAME=%s\n", $2);}}')"
      eval "${LV_ENV_VARS}"
      if [[ -n "${LV_ENV_VARS}" && -n "${LV_INST_NAME}" ]]
      then
        if alias "${LV_INST_NAME}" >/dev/null 2>&1
        then
          unalias "${LV_INST_NAME}"
        fi
        local LV_PROC LV_PROC_USER LV_PROC_ARGS LV_TYPE LV_COMMENT
        LV_PROC=$(ps -eo user,args | awk "/[[:alpha:]]_smon_${LV_INST_NAME}"'$/{printf("%s,%s\n", $1, $2)}')
        LV_PROC_USER="$(echo "${LV_PROC}" | cut -d, -f1)"
        LV_PROC_ARGS="$(echo "${LV_PROC}" | cut -d, -f2)"
        LV_TYPE="${LV_PROC_ARGS:0:3}"
        LV_COMMENT="(crs,${GV_T_GREEN}up${GV_CCLR})"
        if [[ "${LV_TYPE}" == "ora" ]]
        then
          LV_TYPE="db"
        elif [[ -z "${LV_TYPE}" ]]
        then
          LV_TYPE="db"
          LV_COMMENT="(crs,${GV_T_RED}down${GV_CCLR})"
        fi
        if [[ -z "${LV_PROC_USER}" ]]
        then
          LV_PROC_USER="$(stat -c "%U" "${LV_ORA_HOME}")"
        fi
        ## Format-width for "COMMENT"-column adjusted for 12 (8+4) invisible format characters (see top of script)
        printf "%-4s   %-10s   %-16s %-25s             %s\n"  "${LV_TYPE}" "${LV_PROC_USER}" "${LV_INST_NAME}" "${LV_COMMENT}" "${LV_ORA_HOME}"
        generate_alias_for_sid "${LV_INST_NAME}" "${LV_ORA_HOME}" "${LV_TNS_ADMIN}"
        generate_alias_for_dbuniquename "${LV_DB_UNIQUE_NAME}" "${LV_INST_NAME}"
        GV_ALIAS_LIST[${#GV_ALIAS_LIST[@]}]="${LV_INST_NAME}"
      fi
    done
  fi
  ## DB Instances from running processes
  LV_PROC_LIST=$(ps -eo user,args | sort | awk /[[:alpha:]]_smon_[[:alnum:]]/'{printf("%s,%s\n", $1, $2)}')
  for LV_PROC in ${LV_PROC_LIST}
  do
    local LV_PROC_USER LV_PROC_ARGS LV_TYPE LV_COMMENT LV_SID
    LV_PROC_USER="$(echo "${LV_PROC}" | cut -d, -f1)"
    LV_PROC_ARGS="$(echo "${LV_PROC}" | cut -d, -f2)"
    LV_TYPE="$(echo "${LV_PROC_ARGS}" | cut -d_ -f1)"
    LV_COMMENT="(${GV_T_GREEN}up${GV_CCLR})"
    if [[ "${LV_TYPE}" == "ora" ]]
    then
      LV_TYPE="db"
    fi
    LV_SID="$(echo "${LV_PROC_ARGS}" | cut -d_ -f3-)"
    if ! in_list "${LV_SID}" "${GV_ALIAS_LIST[@]}" && [[ -n "${LV_PROC_USER}" ]]
    then
      local LV_SID_LC LV_ORATAB LV_ORA_HOME
      LV_SID_LC="$(echo "$LV_SID" | to_lower)"
      LV_ORATAB="$(grep "^${LV_SID}:" /etc/oratab 2>/dev/null)"
      LV_ORA_HOME="$(echo "${LV_ORATAB}" | cut -d: -f2)"
      if [[ "${LV_IS_RAC}" == "TRUE" && -z "${LV_ORA_HOME}" ]]
      then
        LV_ORATAB="$(grep "^${LV_SID%?}:" /etc/oratab)"
        LV_ORA_HOME="$(echo "${LV_ORATAB}" | cut -d: -f2)"
      fi
      if [[ "${LV_SID}" != "-MGMTDB" ]]
      then
        ## Format-width for "COMMENT"-column adjusted for 12 (8+4) invisible format characters (see top of script)
        printf "%-5s  %-10s   %-16s %-25s             %s\n"  "${LV_TYPE}" "${LV_PROC_USER}" "${LV_SID}" "${LV_COMMENT}" "${LV_ORA_HOME}"
        if [[ "${LV_TYPE}" != "asm" ]] && ! in_list "${LV_SID}" "${GV_ALIAS_LIST[@]}"
        then
          generate_alias_for_sid "${LV_SID}" "${LV_ORA_HOME}"
          GV_ALIAS_LIST[${#GV_ALIAS_LIST[@]}]="${LV_SID}"
        fi
      fi
    fi
  done
  ## Not running instances from oratab
  typeset LV_SID_LIST
  LV_SID_LIST=$(grep -v -e '^[[:space:]]*$' -e '^[[:space:]]*#' -e '^[*]:' /etc/oratab 2>/dev/null | sed 's/:.*$//g')
  for LV_SID in ${LV_SID_LIST}
  do
    if in_list "${LV_SID}" "${GV_ALIAS_LIST[@]}"
    then
      continue
    fi

    typeset LV_ORATAB LV_ORA_HOME LV_HOME_OWNER
    LV_ORATAB="$(grep "^${LV_SID}:" /etc/oratab 2>/dev/null)"
    LV_ORA_HOME="$(echo "${LV_ORATAB}" | cut -d: -f2)"
    LV_HOME_OWNER="$(mystat "${LV_ORA_HOME}")"

    if [[ -n "${GV_GRID_HOME}" ]]
    then
      typeset LV_DB_RESOURCE_LIST LV_ENV_VARS
      typeset -l LV_SID_LOWER=${LV_SID}
      if alias "${LV_SID_LOWER}" 1>/dev/null 2>&1
      then
        # We already created an alias for this SID (or DB_UNIQUE_NAME) -> ignore
        continue
      fi
    fi

    local LV_COMMENT="(oratab,${GV_T_RED}down${GV_CCLR})"
    if [[ "${LV_SID}" != "-MGMTDB" ]]
    then
      if echo "${LV_SID}" | grep -q "^\+ASM"
      then
        LV_TYPE="asm"
        LV_HOME_OWNER=""
      elif [[ -x "${LV_ORA_HOME}/bin/oracle" ]]
      then
        LV_TYPE="db"
      else
        if [[ -x "${LV_ORA_HOME}/bin/emagent" ]]
        then
          LV_TYPE="agent"
        elif [[ -x "${LV_ORA_HOME}/bin/omsca" ]]
        then
          LV_TYPE="oms"
        else
          LV_TYPE="unkn"
        fi
      	## "dummy" format codes, to prevent problems with formatting in following printf
        LV_COMMENT="${GV_T_WHITE}(oratab)${GV_CCLR}"
      fi
      ## Format-width for "COMMENT"-column adjusted for 12 (8+4) invisible format characters (see top of script)
      printf "%-5s  %-10s   %-16s %-25s             %s\n"  "${LV_TYPE}" "${LV_HOME_OWNER}" "${LV_SID}" "${LV_COMMENT}" "${LV_ORA_HOME}"
      if [[ "${LV_TYPE}" != "asm" ]]
      then
        generate_alias_for_sid "${LV_SID}" "${LV_ORA_HOME}"
        GV_ALIAS_LIST[${#GV_ALIAS_LIST[@]}]="${LV_SID}"
      fi
    fi
  done
}
list_listener() {
  local LV_ORA_LSNR_HOME LV_ORA_LSNR_USER
  # shellcheck disable=2009 # no pgrep on AIX
  LV_ORA_LSNR_LIST=$(ps -eo args | grep -E "tnslsnr[ ]+" | awk '{print $2}')
  for LV_ORA_LSNR_NAME in ${LV_ORA_LSNR_LIST}
  do
    # shellcheck disable=2009 # no pgrep on AIX
    LV_ORA_LSNR_HOME="$(ps -eo args | grep -E "tnslsnr[ ]+${LV_ORA_LSNR_NAME}[ ]+" | awk '{print $1}' | sed 's|/bin/tnslsnr||')"
    # shellcheck disable=2009 # no pgrep on AIX
    LV_ORA_LSNR_USER="$(ps -eo user,args | grep -E "tnslsnr[ ]+${LV_ORA_LSNR_NAME}[ ]+" | awk '{print $1}')"
    if [[ -z "${LV_ORA_LSNR_HOME}" ]]
    then
      echo "Cannot determine listener ORACLE_HOME for listener \"${LV_ORA_LSNR_NAME}\"." 1>&2
      continue
    fi
    generate_alias_for_listener "${LV_ORA_LSNR_NAME}" "${LV_ORA_LSNR_HOME}"
    GV_ALIAS_LIST[${#GV_ALIAS_LIST[@]}]="${LV_ORA_LSNR_NAME}"
    printf "%-4s   %-10s   %-30s             %s\n"  "lsnr" "${LV_ORA_LSNR_USER}" "${LV_ORA_LSNR_NAME}" "${LV_ORA_LSNR_HOME}"
  done
}
clear_aliases() {
  local LV_CLEARED_ALIASES
  local LV_ALIAS_LC
  for LV_ALIAS in "${GV_ALIAS_LIST[@]}"
  do
    if ! in_list "${LV_ALIAS}" "${LV_CLEARED_ALIASES[@]}"
    then
      if alias "${LV_ALIAS}" 1>/dev/null 2>&1
      then
        if ! unalias "${LV_ALIAS}" 2>/dev/null
        then
          echo "Error when removing alias \"${LV_ALIAS}\"..."
        fi
        LV_CLEARED_ALIASES[${#LV_CLEARED_ALIASES[@]}]="${LV_ALIAS}"
      fi
    fi

    LV_ALIAS_LC=$(echo "${LV_ALIAS}" | to_lower)
    if ! in_list "${LV_ALIAS_LC}" "${LV_CLEARED_ALIASES[@]}"
    then
      if alias "${LV_ALIAS_LC}" 1>/dev/null 2>&1
      then
        if ! unalias "${LV_ALIAS_LC}" 2>/dev/null
        then
          echo "Error when removing alias \"${LV_ALIAS_LC}\"..."
        fi
        LV_CLEARED_ALIASES[${#LV_CLEARED_ALIASES[@]}]="${LV_ALIAS_LC}"
      fi
    fi
  done
  unset GV_ALIAS_LIST
}
distribute_env () {
  for LV_HOST in $("${GV_GRID_HOME}/bin/olsnodes" | grep -v "$(hostname -s)" )
  do
    # shellcheck disable=2029 # The variable is supposed to be expanded on the client side
    ssh "${LV_HOST}" "mkdir -p \"${GV_SCRIPT_DIR}\""
    scp "${GV_SOURCED_SCRIPT}" "${LV_HOST}":"${GV_SOURCED_SCRIPT}"
  done
}
read_proxy() {
  local LV_PROXY_URL
  printf "================================================================\n"
  printf "Please enter a valid https proxy definition to access github.com\n"
  printf "Currently the environment the following proxy defined:\n"
  printf "  HTTPS_PROXY=\"%s\"\n" "${HTTPS_PROXY}"
  printf "Press <ENTER> to keep this unchanged\n"
  printf "\n"
  read -r -p "Enter proxy (Format: [protocol://]<host>[:port]) to access git.opitz-consulting.com: " LV_PROXY_URL
  if [[ -n "${LV_PROXY_URL}" ]]
  then
    export HTTPS_PROXY="${LV_PROXY_URL}"
  else
    unset HTTPS_PROXY
  fi
}
pull_ocenv_main() {
  local RET_CODE
  read_proxy
  RET_CODE=$?

  if [[ "${RET_CODE}" -eq 0 ]]
  then
    if curl https://raw.githubusercontent.com/opitzconsulting/oracle-scripts/main/ocenv > "${GV_SOURCED_SCRIPT}.tmp"
    then
      mv "${GV_SOURCED_SCRIPT}.tmp" "${GV_SOURCED_SCRIPT}"
    fi
  fi
}

get_tpt_oracle() {
  local RET_CODE
  read_proxy
  RET_CODE=$?

  if [[ "${RET_CODE}" -eq 0 ]]
  then
    if curl -G https://codeload.github.com/tanelpoder/tpt-oracle/zip/master > "${GV_SQL_DIR}/tpt-oracle-master.zip"
    then
      if cd "${GV_SQL_DIR}"
      then
        unzip tpt-oracle-master.zip -d "${GV_SQL_DIR}"
        rm -rf "${GV_SQL_DIR}/tpt"
        mv "${GV_SQL_DIR}/tpt-oracle-master" "${GV_SQL_DIR}/tpt"
      fi
    fi
    set_sql_path_vars
  fi
}

list_env() {
  clroraenv
  clear_aliases
  list_homes
  list_sids
  list_listener
}
show_env_version() {
  echo "#####################################################################################"
  echo "## Version: ${GV_OCENV_VERSION}"
  echo "## get command overview by typing envhelp"
  echo "#####################################################################################"
}
unset ORACLE_SID
unset ORACLE_HOME
detect_grid_infrastructure_and_asm
if [[ "${GV_TTY}" -eq 0 ]]
then
  wintitle "${USER}@$(hostname -s)"
  show_env_version
  list_env
fi
