If you are an Oracle customer who has several database versions running, you have to deal with scripts that become more and more complex to maintain. Depending on the version or the edition of your database, you may want to run different pieces of code. This forces you to get programmatically more information about your database version and edition (e.g., in order to run a statspack or AWR report if your software is either Enterprise or Standard).
The most common way to get information about the software is connecting to the database and getting it through a couple of selects. But what if you don’t have any running databases?
The ORACLE_HOME inventory has such information, and you can get it with a short shell function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
function ohversion () { ORACLE_VERSION=`grep "<PATCH NAME=\"oracle.server\"" $ORACLE_HOME/inventory/ContentsXML/comps.xml 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`; if [ -z "$ORACLE_VERSION" ]; then ORACLE_VERSION=`grep "<COMP NAME=\"oracle.server\"" $ORACLE_HOME/inventory/ContentsXML/comps.xml 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`; fi; if [ -z "$ORACLE_VERSION" ]; then echo "OH not set"; fi; ORACLE_MAJOR=`echo $ORACLE_VERSION | cut -d . -f 1`; case $ORACLE_MAJOR in 11 | 12) EDITION=`grep "oracle_install_db_InstallType" $ORACLE_HOME/inventory/globalvariables/oracle.server/globalvariables.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'` ;; 10) EDITION=`grep "s_serverInstallType" $ORACLE_HOME/inventory/Components21/oracle.server/*/context.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'` ;; *) ;; esac; export ORACLE_VERSION EDITION; echo $ORACLE_VERSION $EDITION } |
The snippet searches for a patchset entry in comps.xml to get the patch version rather than the base version (for releases prior to 11gR2 where out-of-place patching occurs). If a patchset cannot be found, it looks for the base version. Depending on the major release, the information about the edition is either in globalvariables.xml (11g, 12c) or in context.xml (10g).
When you call this “ohversion” function, you get both the Oracle version and the edition of your current ORACLE_HOME.
If you’re using the bash as user shell, you may want to take one step forward and include this information in a much fancier bash prompt than the prompt by default:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
function ora_prompt () { PSERR=$?; colylw='\033[0;33m'; colcyn='\033[0;36m'; colured='\033[4;31m'; colugrn='\033[4;32m'; coluylw='\033[4;33m'; colrst='\033[0m'; PS1="\n# [ \u@\h:${colcyn}$PWD${colrst} [\\t] [${colylw}\$(ohversion) SID=${coluylw}${ORACLE_SID:-\"not set\"}${colrst}] \$( if [[ \$PSERR -eq 0 ]]; then echo \"${colugrn}0${colrst}\" ; else echo \"${colured}\$PSERR${colrst}\";fi) ] #\\n# " } export PROMPT_COMMAND=ora_prompt |
Although this prompt may seem long, it has several advantages that save you a lot of typing:
• The newline character inside the prompt let’s you start typing commands on an almost empty line so you don’t have to worry about how long your command is.
• The full username@host:path can be copied and pasted quickly for scp commands.
• The time inside the square brackets is helpful to track timings.
• The indication of the current environment (version, edition, SID) lets you know which environment you’re working on.
• The leading number is the exit code of the last command ($?). It’s green when the exit code is zero and red for all other exit codes.
• Hash characters before and after the prompt mitigate the risk of copying and pasting the wrong line by mistake inside your session.
Note: this post originally appeared on IOUG Tips & Best Practices Booklet 9th edition.