-
Notifications
You must be signed in to change notification settings - Fork 56
/
oraenv++
408 lines (391 loc) · 18.6 KB
/
oraenv++
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
#!/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 *#
#************************************************************************#