User Rating: 3 / 5

Star ActiveStar ActiveStar ActiveStar InactiveStar Inactive
 

Oraenv2 is a script which adds some enhancements on top of oraenv script like choosing from a list of available SID or directly set the SID in command line.

(oraenv2 zip file is attached in this article)

The script has been tested ...
... on Oracle Linux 7
... with ksh and bash

How to install

oraenv2 shell script is available for downloading at the end of this article.

Install this script as follow

  • If you have root access:
    • Copy oraenv2 to /usr/local/bin/
    • Change its permissions to 755 : chmod 755 /usr/local/bin/oraenv2
    • Make sure directory is in PATH variable, if not add export PATH=$PATH:/usr/local/bin to ~/.bash_profile
  • If you don't have root access:
    • Copy oraenv2 to $HOME/bin/
    • Change its permissions to 755 : chmod 755 $HOME/bin/oraenv2
    • Make sure directory is in PATH variable, if not add export PATH=$PATH:$HOME/bin to ~/.bash_profile
  • Create an alias by adding echo alias oenv='. oraenv2' to ~/.bash_profile
  • Reload your profile: . ~/.bash_profile

How to use

 

# Print help
$ oenv -h
     SYNOPSIS
        Usage: . oraenv2 [-hsv] [SID]

     DESCRIPTION
        This script is a little enhancement on top of oraenv script.
        If no SID specified, it will show a list of all available databases
            on your machine to choose from.
        If dot(.) is specified, it will automaticaly select $ORACLE_SID
            if invalid SID, it will select the first SID in /etc/oratab

     OPTIONS
        -h            Print this help
        -s            Silent mode
        -v            Print script information

     EXAMPLES
        . oraenv2 oraclesid
        source oraenv2 -s oraclesid


# Select by SID
$ oenv

    Valid Oracle SIDs are :
         1  -MGMTDB (online)
         2  UXOCDB1 (online)
         3  +ASM1   (online)

    ORACLE_SID = [ASM1] ? UXOCDB1

    The Oracle base remains unchanged with value /u01/app/oracle

    ORACLE_SID is UXOCDB1
    ORACLE_HOME is /u01/app/oracle/product/12.2.0/db1


# Select by Number
$ oenv

    Valid Oracle SIDs are :
         1  -MGMTDB (online)
         2  UXOCDB1 (online)
         3  +ASM1   (online)

    ORACLE_SID = [UXOCDB1] ? 3

    ORACLE_BASE environment variable is not being set since this
    information is not available for the current user ID oracle.
    You can set ORACLE_BASE manually if it is required.
    Resetting ORACLE_BASE to its previous value or ORACLE_HOME
    The Oracle base remains unchanged with value /u01/app/oracle

    ORACLE_SID is +ASM1
    ORACLE_HOME is /u01/app/12.2.0/grid


# Set SID in command line
$ oenv UXOCDB1
    The Oracle base remains unchanged with value /u01/app/oracle

    ORACLE_SID is UXOCDB1
    ORACLE_HOME is /u01/app/oracle/product/12.2.0/db1


# Set SID in silent mode
$ oenv -s UXOCDB1


# Choose SID in silent mode
$ oenv -s

     1  -MGMTDB (online)
     2  UXOCDB1 (online)
     3  +ASM1   (online)

ORACLE_SID = [UXOCDB1] ? 1

Script source code

#!/bin/ksh
#================================================================
# HEADER
#================================================================
#% SYNOPSIS
#+    Usage: . ${SCRIPT_NAME} [-hsv] [SID]
#%
#% DESCRIPTION
#%    This script does little enhancement on top of the oraenv script.
#%    If no SID specified, it will show a list of all available databases
#%        on your machine to choose from.
#%    If dot(.) is specified, it will automaticaly select $ORACLE_SID
#%        if invalid SID, it will select the first SID in /etc/oratab
#%
#% OPTIONS
#%    -h            Print this help
#%    -s            Silent mode
#%    -v            Print script information
#%
#% EXAMPLES
#%    . ${SCRIPT_NAME} oraclesid
#%    source ${SCRIPT_NAME} -s oraclesid
#%
#================================================================
#- IMPLEMENTATION
#-    version         ${SCRIPT_NAME} 0.0.1
#-    author          Michel VONGVILAY (https://www.uxora.com)
#-    license         CC-BY-SA Creative Commons License
#-    script_id       0
#-    based on        http://www.orafaq.com/scripts/unix/oraenv2.txt
#-
#================================================================
# END_OF_HEADER
#================================================================

  #== usage function ==#
scriptinfo() { headFilter="^#+"
[[ "$1" = "ful" ]] && headFilter="^#[%+]"
[[ "$1" = "ver" ]] && headFilter="^#-"
head -${SCRIPT_HEADSIZE:-99} ${SCRIPT_CALL} | grep -e "${headFilter}" | sed -e "s/${headFilter}//g" -e "s/\${SCRIPT_NAME}/${SCRIPT_NAME}/g"; }
fecho() { [[ $flagOptSilent -eq 1 ]] || echo -e ${*};}
error() { echo -e "ERROR: ${SCRIPT_NAME}: ${*}" 1>&2; }
clean() { unset SCRIPT_HEADSIZE SCRIPT_NAME SCRIPT_CALL DEFAULT_SID ORATAB_FILE ORASID_LIST flagOptSilent;
	unset -f scriptinfo error fecho clean; return 0;}

  #== general variables ==#
SCRIPT_CALL="${BASH_SOURCE:-${.sh.file}}" # Get script file when being sourced
SCRIPT_NAME="$(basename ${SCRIPT_CALL})"
SCRIPT_HEADSIZE=$(grep -sn "^# END_OF_HEADER" ${SCRIPT_CALL} | head -1 | cut -f1 -d: 2>/dev/null)

  #== manage options ==#
flagOptSilent=0
case "$1" in
	-h ) scriptinfo ful; clean; return 0;
	;;
	-s ) flagOptSilent=1; shift;
	;;
	-v ) scriptinfo ver; clean; return 0;
	;;
	-* ) error "OPT: $1: unknown option"; scriptinfo; clean; return 1;
	;;
esac

  #== Check/Set arguments ==#
[[ $# -gt 1 ]] && error "Too many arguments" && scriptinfo 1>&2 && clean && return 2

  #== main script ==#
ORATAB_FILE="/etc/oratab"
[[ ! -e $ORATAB_FILE ]] && error "Cannot find $ORATAB_FILE" && clean && return 3

ORAENV_FILE="$(which oraenv 2>/dev/null)"
[[ "x$ORAENV_FILE" == "x" ]] && error "Cannot find oraenv" && clean && return 3

ORASID_LIST=$(cat $ORATAB_FILE | grep -v "^#" | cut -d: -f1 -s | tr -s "[:blank:]" 2>/dev/null)
DEFAULT_SID=$(echo $ORASID_LIST" " | cut -d' ' -f1 -s 2>/dev/null)
if [[ "x$DEFAULT_SID" == "x" ]]; then
	error "No SID found in $ORATAB_FILE"
	clean; return 4
elif [[ "x$ORACLE_SID" != "x" ]]; then
	ORACLE_SID=$(printf "\n$ORASID_LIST" | grep -i "^${ORACLE_SID}$" 2>/dev/null)
	[[ "x$ORACLE_SID" != "x" ]] && DEFAULT_SID=$ORACLE_SID
	ORACLE_SID=
fi

if [[ "x$1" == "x." ]]; then
	ORACLE_SID=$DEFAULT_SID
elif [[ "x$1" != "x" ]]; then
	ORACLE_SID=$(printf "\n$ORASID_LIST" | grep -i "^${1}$" 2>/dev/null)
fi

[ "x${1}" != "x" ] && [ "x${ORACLE_SID}" = "x" ] \
	&& error "ARG: ${1}: Invalid Oracle SID"

if [ -t 0 ]; then	# Command executed from a terminal
	while [ "x${ORACLE_SID}" = "x" ]; do
		echo
		fecho "$(tput rev)Valid Oracle SIDs are :$(tput rmso)"
		for osid in $ORASID_LIST; do
			osidActive="offline"
			[[ "x$( ps -ef | grep "_pmon_$osid" | grep -v "grep" 2>/dev/null)" != "x" ]] && osidActive="online"
			echo -e "$osid\t($osidActive)"
		done | cat -ub -
		#printf "\n$ORASID_LIST" | awk 'NF' | cat -ub -
		echo -e "\nORACLE_SID = [$DEFAULT_SID] ? \c"
		read ANSWER
		echo
		
		ANSWER=$(echo $ANSWER | tr -s "[:blank:]")
		if [ "x${ANSWER}" = "x" ]; then #Empty Answer
			ANSWER=$DEFAULT_SID
		elif [ -z "${ANSWER//[0-9]}" ]; then # Number Answer
			ANSWER_TMP=$(echo $ORASID_LIST" " | cut -d' ' -f${ANSWER} -s 2>/dev/null)
			[ "x${ANSWER_TMP}" != "x" ] && ANSWER=$ANSWER_TMP
			unset ANSWER_TMP
		fi

		ORACLE_SID=$(printf "\n$ORASID_LIST" | grep -i "^${ANSWER}$" 2>/dev/null)
		[ "x${ORACLE_SID}" = "x" ] && error "ANSWER: ${ANSWER}: Invalid Oracle SID"
	done
elif [ "x$ORACLE_SID" = "x" ]; then	# Set to first entry in oratab
	ORACLE_SID=$DEFAULT_SID
fi

ORACLE_HOME=$(grep "^${ORACLE_SID}:" $ORATAB_FILE | cut -d: -f2 -s 2>/dev/null)

[[ "x$(echo ${PATH} | grep "${ORACLE_HOME}/bin")" = "x" ]] \
	&& PATH=${PATH}:${ORACLE_HOME}/bin

export ORACLE_SID
export ORACLE_HOME
export PATH

ORAENV_ASK=NO
[[ $flagOptSilent -eq 1 ]] \
	&& . ${ORAENV_FILE} > /dev/null \
	|| . ${ORAENV_FILE}
ORAENV_ASK=

fecho "\nORACLE_SID is $(tput rev)$ORACLE_SID$(tput rmso)"
fecho "ORACLE_HOME is $(tput rev)$ORACLE_HOME$(tput rmso)\n"

clean; return 0;

 

HTH,
Michel.

Attachments:
Download this file (oraenv2.zip)oraenv2.zip1 kB

Enjoyed this article? Please like it or share it.

Add comment

Please connect with one of social login below (or fill up name and email)

     


Security code
Refresh