#!/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 *#
#************************************************************************#