-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathocenv
2729 lines (2610 loc) · 96 KB
/
ocenv
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
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#!/usr/bin/env bash
## Allow debugging of script, including line-number information and runtime-information
## We use "²" as delimiter, to allow separation of the output (e.g. in Excel)
if [[ "$1" == --debug ]]
then
export PS4='+ ²$(date "+%s.%N")²$LINENO² '
set -x
fi
# 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="2024-11-29"
###############################################################################
## 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
set_nls_env_vars() {
if [[ -n "${GV_NLS_LANG}" ]]
then
export NLS_LANG="${GV_NLS_LANG}"
else
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
fi
if [[ -n "${GV_NLS_DATE_FORMAT}" ]]
then
export NLS_DATE_FORMAT="${GV_NLS_DATE_FORMAT}"
else
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
fi
}
set_nls_env_vars
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"
Further optional lines in this file are
# The following 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.
export GV_CUSTOM_LOGIN_SQL=""
# The following can be used to change the initial selected environment after
# sourcing this script or re-listing the environment. The value must be the
# name of an ORACLE_HOME or a valid instance-environment alias.
export GV_DEFAULT_ENV_NAME=""
# The following can be used to change the NLS configuration. If not set,
# this script will default to the values shown here.
export GV_NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export GV_NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
The variable "GV_CUSTOM_LOGIN_SQL" can be used to define a SQL file that should
be executed at the end of the login.sql file that is auto-generated by ocenv.
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.)
* compatible_info
Shows additional information about the value of the compatible
parameter of the currently selected database
== 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"
* get_sqlzauberkasten_oracle
Tries to download the current "master" version of the SQL-Zauberkasten
repository from GitHub and extract it to
"${GV_SQL_DIR}/sql/zauberkasten"
== 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
* GV_SCRIPT_DIR Directory where ocenv is located
* GV_CUSTOM_LOGIN_SQL If defined, this file is executed at the
end of the login.sql when starting SQL*Plus
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
if [[ -d "${GV_SQL_DIR}zauberkasten/sql/" ]]
then
export SQLPATH="${GV_SQL_DIR}zauberkasten/sql/:${SQLPATH}"
export ORACLE_PATH="${GV_SQL_DIR}zauberkasten/sql/:${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 "$(command which vim 2>/dev/null)" && -f "$(command 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
# shellcheck disable=SC2034 # GV_T_YELLOW is currently unused
GV_T_YELLOW="$(printf "\033[00;33m")" # 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
GV_B_GREEN="$(printf "\033[01;32m")" # Uses 8 invisible chars in printf format width
GV_B_YELLOW="$(printf "\033[01;33m")" # 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_B_BOLD="\[${GV_B_WHITE}\]"
GV_P_CCLR="\[${GV_CCLR}\]"
export HISTTIMEFORMAT="%F %T "
if [[ "${USER}" == "root" ]]
then
export PROMPT_COMMAND='LAST_RETCODE=$?; builtin echo -ne "\n[${GV_T_GREEN}${USER}${GV_T_WHITE}@${HOSTNAME}] [${PWD}]"; [ $LAST_RETCODE = 0 ] && builtin echo -ne "\e[$((COLUMNS - ${#LAST_RETCODE}))G\e[1;32m${LAST_RETCODE}\e[0m\n" || builtin echo -ne "\e[$((COLUMNS - ${#LAST_RETCODE}))G\e[31m${LAST_RETCODE}\e[0m\n"'
export PS1="${GV_P_B_BOLD}#${GV_P_CCLR} "
export PS2="${GV_P_B_BOLD}#${GV_P_CCLR} "
else
export PROMPT_COMMAND='LAST_RETCODE=$?; builtin echo -ne "\n[${GV_T_GREEN}${USER}${GV_T_WHITE}@${HOSTNAME}] [${GV_T_RED}${ORACLE_SID}${GV_T_WHITE}] [${PWD}]"; [ $LAST_RETCODE = 0 ] && builtin echo -ne "\e[$((COLUMNS - ${#LAST_RETCODE}))G\e[1;32m${LAST_RETCODE}\e[0m\n" || builtin echo -ne "\e[$((COLUMNS - ${#LAST_RETCODE}))G\e[31m${LAST_RETCODE}\e[0m\n"'
export PS1="${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 "$(command which rlwrap 2>/dev/null)" ]]
then
GV_RLWRAP="$(command 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
if [[ -f "${GV_CUSTOM_LOGIN_SQL}" ]]
then
echo "@${GV_CUSTOM_LOGIN_SQL}" >> "${GV_SQL_DIR}/login.sql"
fi
GV_LS_CMD="$(command which ls)"
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 "$(command which amon 2>/dev/null)" ]] && alias amon="\$(command 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 "$(command 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_DB_VERSION
unset GV_DB_COMPATIBLE
unset GV_COMPAT_COLOR
unset GV_COMPAT_HINT
unset GV_COMPAT_MSG
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 "Selected environment doesn't have PDBs."
return 2
elif [[ ${#GV_PDB_LIST[@]} -eq 1 ]]
then
echo "Connecting with only existing PDB \"${GV_PDB_LIST[*]}\"."
LV_PDB_NAME="${GV_PDB_LIST[*]}"
elif [[ -z "${1}" || ( "${1}" != "sqlplus" && "${1}" != "sqlcl" ) ]]
then
echo "Internal method, first parameter must be \"sqlplus\" or \"sqlcl\"."
return 1
elif [[ -z "${2}" ]]
then
echo "Please provide PDB-name as first parameter."
echo "Possible names are:"
echo "${GV_PDB_LIST[@]}" | awk '{printf(" %s\n", $0)}'
return 1
elif ! in_list "${2}" "${GV_PDB_LIST[@]}"
then
echo "Provided PDB '${2}' does not exist in current CDB."
echo "Possible names are:"
echo "${GV_PDB_LIST[@]}" | awk '{printf(" %s\n", $0)}'
echo ""
echo "This list is updated when setting the DB-environment"
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 "$@");