#!/bin/bash
# Fred Denis -- October 28th 2021 -- fred.denis3@gmail.com -- http://unknowndba.blogspot.com
# oraenv++ - a step forward towards an easy and powerful tool to set up your Oracle environment (https://bit.ly/3GHJMFv)
# Copyright (C) 2021 Fred Denis
#
# 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/>.
#
#
# More info and git repo: https://bit.ly/3GHJMFv -- https://github.com/freddenis/oracle-scripts
#
# The current script version is 20220428
#
# History :
#
# 20220428 - Fred Denis - Small bug fixzed with orabase
# 20211111 - Fred Denis - GPLv3 licence
# 20211109 - Fred Denis - Use -attr which speeds up crsctl (and then oraenv++) in a significant way
# 20211108 - Fred Denis - Fixed a bug with Oracle restart, removed crsctl -v which is not needed
#                         OPP_HOSTNAME for when I debug with external files
# 20211103 - Fred Denis - Initial release
#
set -o pipefail
#
# Variables
#
           ME="oraenv++"                     # For the usage function
           TS="date "+%Y-%m-%d_%H%M%S""      # A timestamp for a nice outut in a logfile
          TMP=$(mktemp -u)                   # A tempfile 
          OLR="/etc/oracle/olr.loc"          # olr file to set up crs env
if [[ -z "${OPP_HOSTNAME}" ]]; then          # For debug mainly
     HOSTNAME=$(hostname -s)                 # Local hostname
else
     HOSTNAME="${OPP_HOSTNAME}"
fi
    CRSFILTER="(TYPE = ora.database.type"    # crsctl stat res command filter (no closing parenthesis)
    ASMFILTER="(TYPE = ora.asm.type)"        # crsctl stat res command filter for ASM
           nb=0                              # A counter
       COL_DB=6
      COL_SID=8
       COL_OH=20
       COL_ID=3
      COL_SEP=""
   OLD_SILENT="${SILENT}"
       SILENT="${OPP_SILENT}"                # Silent output with env variable OPP_SILENT="True"
         GREP="."                            # -g/--grep option
       UNGREP="donotgrepme$$"                # -v/--ungrep option
    unset  DB                                # -d/--db option
    unset PDB                                # -p/--pdb option
    unset  IN                                # -f option
    unset OUT                                # -o option
    unset tab                                # An aray
#
# oraenv++ needs to be sourced (. oraenv++) and not executed (./oraenv++) as it sets environment variable in the current shell
#
if [[ "${0}" == "${BASH_SOURCE[0]}" ]]; then
    printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] oraenv++ needs to be sourced (. oraenv++) and not executed (./oraenv++) as it sets environment variable in the current shell; cannot continue." >&2
    exit 123
fi
#
# To make it easy, DB can be the first parameter but we need to check as more options will also be possible
#
         DB=$1
    if [[ "${DB:0:1}" == "-" ]]; then DB=""; fi
    if [[ $(echo "${DB}" | tr '[:upper:]' '[:lower:]') == "asm" ]]; then DB="+ASM"; PDB=""; fi    # Useful alias
#
# Usage function
#
usage() {
    printf "\n\033[1;37m%-8s\033[m\n" "NAME"                ;
    cat << END
    ${ME} - a step forward towards an easy and powerful tool to set up your Oracle environment (https://bit.ly/3GHJMFv)
END

    printf "\n\033[1;37m%-8s\033[m\n" "SYNOPSIS"            ;
    cat << END
    . ${ME} <DB_NAME>
    . ${ME} [-d]   [-p]    [-g]     [-v]       [-s] [-S]  [-h]
    . ${ME} [--db] [--pdb] [--grep] [--ungrep] [--silent] [--help]
END

    printf "\n\033[1;37m%-8s\033[m\n" "DESCRIPTION"         ;
    cat << END
    ${ME} is meant to set up your Oracle environment without the limitations of other tools
    ${ME} is NOT based on oratab but gets the environment information from a GI/Oracle restart
             so you do not have to hardcode any configuration file nor maintain oratab
    ${ME} is then able to set up the correct SID of a RAC database for example
    If ${ME} finds more than 1 database matching your grep/ungrep combinaison, it will show you a menu to choose from
    If no parameters are given, ${ME} will show you all the databases resgistered in the GI and a menu to choose from
    As ${ME} sets environment variable in a parent shell, it needs to be invocated with "." or "source"
    oraenv++ does not list all the PDBs per CDB (yet) but can setup ORACLE_PDB_SID with the --pdb option
    As CRS/GI saves the databases names in lowercase regardless of their real case, --db, --grep and --ungrep are non key sensitive
 
END

    printf "\n\033[1;37m%-8s\033[m\n" "OPTIONS"             ;
    cat << END
    -d | --db         ) Database to set the environment (this is the DB_NAME registered into the GI/CRS)
                        to make is easy, this option can also not be specified if grep and ungrep are not used (see examples below)
    -p | --pdb        ) PDB to set the ORACLE_PDB_SID variable to; requires DB to be set
    -g | --grep       ) grep one or multiple comma separated patterns in the database list registered in the GI/CRS
    -v | --ungrep     ) ungrep (grep -v) one or multiple comma separated patterns in the database list registered in the GI/CRS
    -s | -S | --silent) Silent output; the same can be achieved by setting the env variable OPP_SILENT="True"

    -h | --help       ) Shows this help
END

    printf "\n\033[1;37m%-8s\033[m\n" "EXAMPLES"            ;
    cat << END
    . ${ME}                                # Show a menu with all the databases registered in the cluster
    . ${ME} PROD                           # Set the env for the PROD database
    . ${ME} prod                           # Same as above
    . ${ME} --db PROD                      # Same as above
    . ${ME} PROD --pdb REPORTING           # Set env to PROD CDB and REPORTING CDB
    . ${ME} +ASM1                          # Set the ASM environment
    . ${ME} asm                            # Set the ASM environment (a useful alias for the asm instances, just enter "asm")
    . ${ME} --grep PROD --ungrep PS        # All the "PROD" databases not containing "PS"
    . ${ME} -g PROD,DEV -v ab.[1-9],^xyz   # grep and ungrep also support regexp; try it out :)

END
}
#
# Just print a "-" line
#
print_a_line() {
    local l_i=$1
    for x in $(seq 1 ${l_i}); do
        printf "%s" "-"
    done
    printf "\n"
}
#
# Make a menu with a list of DB/Instances to choose from
#
makemenu() {
    local l_i
    # First, we check the length to adapt the table for a nice output
    for l_i in $(seq 1 ${#tab[@]}); do
         DB=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $1}')
         OH=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $2}')
        SID=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $3}')

        if [[ "${#DB}"  -gt "${COL_DB}"  ]]; then  COL_DB="${#DB}" ; fi
        if [[ "${#OH}"  -gt "${COL_OH}"  ]]; then  COL_OH="${#OH}" ; fi
        if [[ "${#SID}" -gt "${COL_SID}" ]]; then COL_SID="${#SID}"; fi
    done
    # Add a few spaces to have a better looking table
    ((  COL_DB+=3 ))
    ((  COL_OH+=3 ))
    (( COL_SID+=3 ))

    LINESIZE=$((COL_DB+COL_OH+COL_SID+COL_ID))

    printf "\n"                                              ;
    printf "%${COL_ID}s "            ""                      ; 
    printf "%-${COL_DB}s${COL_SEP}"  " Database"             ; # DB
    printf "%-${COL_SID}s${COL_SEP}" "  SID"                 ; # SID
    printf "%-${COL_OH}s${COL_SEP}"  "    ORACLE_HOME"       ; # OH
    printf "\n"                                              ;
    print_a_line "${LINESIZE}"

    for l_i in $(seq 1 ${#tab[@]}); do
         DB=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $1}')
         OH=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $2}')
        SID=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $3}')

        printf "%${COL_ID}s/"            " ${l_i}"           ; 
        printf "%-${COL_DB}s${COL_SEP}"  " ${DB}"            ; # DB
        printf "%-${COL_SID}s${COL_SEP}" " ${SID}"           ; # SID
        printf "%-${COL_OH}s${COL_SEP}"  " ${OH}"            ; # OH
        printf "\n"                                          ;
    done
    print_a_line "${LINESIZE}"
    printf "\n"                                              ;
}
#
# Just show the current env
#
showenv() {
    if [[ "${SILENT}" != "True" ]]; then
        printf "\n"
        printf "%-20s: %-s\n" "Database"        "${DB}"
        printf "%-20s: %-s\n" "ORACLE_HOME"     "${ORACLE_HOME}"
        printf "%-20s: %-s\n" "ORACLE_BASE"     "${ORACLE_BASE}"
        printf "%-20s: %-s\n" "ORACLE_SID"      "${ORACLE_SID}"
        printf "%-20s: %-s\n" "ORACLE_PDB_SID"  "${ORACLE_PDB_SID}"
        printf "%-20s: %-s\n" "sqlplus is"      $(type sqlplus 2>/dev/null | awk '{print $3}')
        printf "\n"
    fi
}
#
# Get an indice as a parameter and export the env variables from the tab array for this indice
#
exportvar() {
    local l_i=$1
    local  DB=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $1}')
    local  OH=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $2}')
    local SID=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $3}')
    local PDB=$(echo "${tab[${l_i}]}" | awk -F ":" '{print $4}')

    export    ORACLE_HOME="${OH}"
    export     ORACLE_SID="${SID}"
    export    ORACLE_BASE=$(${ORACLE_HOME}/bin/orabase)
    export           PATH="${ORACLE_HOME}/bin:${PATH}"
    export ORACLE_PDB_SID="${PDB}"

    showenv
}
#
# Setup env
#
setupenv() {
    if [[ ${#tab[@]} == 0  ]]; then     # nothing to do !
        printf "\033[1;34m%s\033[m\n" "$($TS) [INFO] Found no DB/Instance, nothing to do !"
        return 123
    fi
    if [[ ${#tab[@]} -gt 1 ]]; then     # Many DB/Instances, lets make a menu to choose from
        makemenu
        printf "\033[1;36m%s\033[m\n" "Which environment you want to set up ? (CTRL+C for exit)"
        read answer
        if ! [[ "${answer}" =~ ^[0-9]+$ ]] || [[ "${answer}" -eq 0 ]] || [[ "${answer}" -gt ${#tab[@]} ]] ; then
            printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] This does not look like a valid choice, exiting."
            return 124
        else
            exportvar "${answer}"
        fi
    else                                # 1 DB/Instance, we set up the env
        exportvar 1
    fi
}
#
# Options -- Long and Short, options needs to be separa
# Options are comma separated list, options requiring a parameter need to be followed by a ":"
#
SHORT="d:,g:,v:,p:,o:,f:,s,S,h"
 LONG="db:,grep:,ungrep:,pdb:,silent,help"
# Check if the specified options are good
options=$(getopt -a --longoptions "${LONG}" --options "${SHORT}" -n "$0" -- "$@")
# If not, show the usage and exit
if [[ $? -ne 0 ]]; then
    printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Invalid options provided: $*; use -h for help; cannot continue." >&2
    return 864
fi
#
eval set -- "${options}"
# Option management, not the "shift 2" when an option requires a parameter and "shift" when no parameter needed
while true; do
    case "$1" in
        -d | --db         )         DB="$2"            ; shift 2 ;;
        -g | --grep       )       GREP="$2"            ; shift 2 ;;
        -v | --ungrep     )     UNGREP="$2"            ; shift 2 ;;
        -p | --pdb        )        PDB="$2"            ; shift 2 ;;
        -s | -S | --silent)     SILENT="True"          ; shift   ;;
        -o                )        OUT="$2"            ; shift 2 ;;
        -f                )         IN="$2"            ; shift 2 ;;
        -h | --help       ) usage && return 999        ; shift   ;;
        --                ) shift                      ; break   ;;
    esac
done
#
# Options verification
#
# CRS has all the databases in lowercase regardless of their real case
# I do not do that in the getopt to keep it short and neat
#
        DB=$(echo "${DB}"     | tr '[:upper:]' '[:lower:]')
      GREP=$(echo "${GREP}"   | tr '[:upper:]' '[:lower:]')
    UNGREP=$(echo "${UNGREP}" | tr '[:upper:]' '[:lower:]')
#
# A database name is specified, we update the CRS filter to get info of only this DB
#
if [[ -n "${DB}" ]]; then
    if [[ "${DB}" != "+"* ]]; then                          # Not ASM
        CRSFILTER="${CRSFILTER} AND (NAME = ora.${DB}.db)"
    else                                                    # ASM
              PDB=""                                        # ASM has no PDB
        CRSFILTER="${ASMFILTER}"
    fi
else                                                        # No database specified
        CRSFILTER="${CRSFILTER}) OR ${ASMFILTER}"
fi
#
# Do things :)
#
if [[ -n "${IN}" ]]; then                   # An input file is specified
    if [[ ! -f "${IN}" ]]; then             # File does not exist
        printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find file ${IN} specified with -o option; cannot continue." >&2
        return 16
    else
        cp -f "${IN}" "${TMP}"
#        cat "${TMP}"                        # Useful when I debug with an input file
    fi
else                                        # No input file specified, we get the info ourselves
    if [[ -f "${OLR}" ]]; then
        export ORACLE_HOME=$(cat "${OLR}" | grep "^crs_home" | awk -F "=" '{print $2}')
        export ORACLE_BASE=$(${ORACLE_HOME}/bin/orabase)
        export        PATH="${PATH}:${ORACLE_HOME}/bin"
    else
        printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find ${OLR} file to set the ASM env; cannot continue." >&2
        return 17
    fi
    printf "\033[1;36m%-60s\033[m" "Reading the system configuration . . ."
    # Generate the GEN_USR_ORA_INST_NAME@SERVERNAME(servername) for all the nodes for the -attr option
    GENATSERVERNAME=$(for N in $(olsnodes); do printf "GEN_USR_ORA_INST_NAME@SERVERNAME(%s)," "${N}"; done)
    crsctl stat res -p -w "${CRSFILTER}" -attr "NAME,TYPE,GEN_USR_ORA_INST_NAME,ORACLE_HOME,${GENATSERVERNAME}" >  "${TMP}"
    echo -en "\033[2K"
    tput hpa 0
fi
if [[ -n "${OUT}" ]]; then         # We save the info into an output file and exit (-o)
    cp "${TMP}" "${OUT}"
    printf "\033[1;36m%s\033[m\n" "$($TS) [INFO] Info saved in ${OUT}."
    rm -f "${TMP}"
    return 0
fi
for X in $(cat "${TMP}" | \
    awk -F "=" -v I_GREP="${GREP}" -v I_UNGREP="${UNGREP}" -v I_HOSTNAME="${HOSTNAME}" -v I_CRS_HOME="${ORACLE_HOME}" -v I_PDB="${PDB}" ' \
        BEGIN {
            split(I_GREP  , tab_grep  , ",")                            ; to grep multiple patterns
            split(I_UNGREP, tab_ungrep, ",")                            ; to ungrep multiple patterns
        }
        { if ($1 == "NAME") {
              sub(/\(.*$/, "", $2)                                      ; # Cleaning up: asm(ora.asmgroup)
              sub("^ora.", "", $2)                                      ;
              l_name = $2                                               ; # Name.type
              getline                                                   ; # Next line is the TYPE
              if (($2 == "ora.database.type") || ($2 == "ora.asm.type")){  # This is a database
                  sub(".db$", "", l_name)                               ; # Remove the consumer group
                  for (i=1; i<=length(tab_ungrep); i++) {
                      if (l_name ~  tab_ungrep[i]) {
                          next                                          ;
                      }
                  }
                  for (i=1; i<=length(tab_grep); i++) {
                      if (l_name ~  tab_grep[i]) {
                          sub(".db$", "", l_name)                       ; # Remove the consumer group
                          if (tab_db[l_name] == l_name) {
                              next                                      ;
                          } else {
                              tab_db[l_name] = l_name                   ; # List of databases
                          }
                      }
                  }                      
              } # End if ($2 == "ora.database.type")
              if ($2 == "ora.asm.type") {                                 # GI does not explicitly give CRS HOME path so I set it here
                  tab_oh[l_name] = I_CRS_HOME                           ; # CRS HOME (OH for ASM)
              }
              while(getline)
              {   
                  if ($1 == "ORACLE_HOME") {
                      tab_oh[l_name] = $2                               ; # List of OH
                  }
                  if ($1 == "GEN_USR_ORA_INST_NAME") {
                      sid_restart = $2                                  ; # In case it is Oracle Restart (SINGLE database_type)
                  }
                  if ($1 ~ /^GEN_USR_ORA_INST_NAME@SERVERNAME/) {         # RAC and RACOneNode database_type only
                      sid_restart  = ""                                 ;
                      sub("GEN_USR_ORA_INST_NAME@SERVERNAME[(]", "", $1);
                      sub(")", "", $1)                                  ;
                      if ($1 == I_HOSTNAME) {
                          sid_hostname = $2                             ; # SID for the host
                      }
                  }
                  if ($0 ~ /^$/){ 
                      if (sid_hostname != "") {
                          tab_sid[l_name] = sid_hostname                ; # RAC and RACOneNode
                      } else {
                          tab_sid[l_name] = sid_restart                 ; # Oracle Restart only
                      }
                      sid_hostname = ""                                 ;
                      sid_restart  = ""                                 ;
                      break                                             ;
                  }
              } # End while(getline)
          } # End if ($1 == "NAME")
        } END {
            for (x in tab_db) {
                if (tab_sid[x] != "") {
                    printf("%s:", tab_db[x])                            ; # DB
                    printf("%s:", tab_oh[x])                            ; # OH
                    printf("%s:", tab_sid[x])                           ; # SID
                    printf("%s:", I_PDB)                                ; # PDB
                    printf("\n")                                        ;
                }
            }
        }' | sort); do
        ((nb++))
        tab[${nb}]="${X}"
done

setupenv

rm -f "${TMP}"
SILENT="${OLD_SILENT}"
return 0

#************************************************************************#
#*                     E N D        O F      S O U R C E                *#
#************************************************************************#