Oracle DBA – A lifelong learning experience

The use of functions in a .profile file

Posted by John Hallas on January 22, 2010

My first public presentation is over now and whilst I was very nervous beforehand I felt quite comfortable once I started. To anybody who was there, thanks for putting up with me.

I promised to upload the contents of a .profile we use for the oracle account as that includes a number of useful functions and aliases. This is rolled out to every database server to ensure that we have a similar feel to every server.
We also have the oratab files set up so that the primary database is first (if there is more than one), ASM is next and the Grid agent home is next. That way the default SID setup when logging in is the main database we are likely to be using.

#!/bin/sh
# Standard Oracle .profile.
#
#
##################################################################
# Version Control
##################################################################
# Who Date Description
# Initial Version
# 21/12/2009 Added lsum and lsh functions
# 31/12/2009 Added export USER for agent startup
#
##################################################################

###############################################
# Global Variables NOT exported
###############################################
ORATAB=/etc/oratab

###############################################
# Functions
###############################################

###############################################
# Check for ASM and if present set TNS_ADMIN
###############################################
asmcheck()
{
#
# Check for ASM
# Look for + at start of line to indicate ASM instance
#
QUERY_ASM=`awk ‘BEGIN {FS =”:”} $1 ~ /^[+]/ && $3 ~ /[N-Y]$/ {print $2}’ $ORATAB`
if [ -d $QUERY_ASM/network/admin ]
then
export TNS_ADMIN=$QUERY_ASM/network/admin
else
unset TNS_ADMIN
fi
}

########################################################
# Show list of available Oracle SIDs if on a Terminal
########################################################
showsid ()
{
if tty -s
then
if [ -f $ORATAB ]
then
i=1
echo “”
awk ‘BEGIN {FS =”:”} $1 ~ /^[+,A-Z,a-z]/ && $3 ~ /[N-Y]$/ {print $1 ” : ” $2}’ ${ORATAB} | while read file_line
do
printf “%2d. %s\n” $i “$file_line”
let i=$i+1
done
echo “”
fi
fi
}

########################################################
# Oracle Environment Set Function if it is a terminal
########################################################
setsid ()
{
if tty -s
then
if [ -f $ORATAB ]
then
line_count=`cat $ORATAB | grep -v ^# | sed ‘s/:.*//’ | wc -l`
# check that the oratab file has some contents
if [ $line_count -ge 1 ]
then
sid_selected=0
while [ sid_selected -eq 0 ]
do
sid_available=0
for i in `cat $ORATAB | grep -v ^# | sed ‘s/:.*//’`
do
sid_available=`expr $sid_available + 1`
sid[$sid_available]=$i
done
# get the required SID
case ${SETSID_AUTO:-“”} in
YES) # Auto set use 1st entry
sid_selected=1 ;;
*)
echo “”
echo “Please select a SID from the list below by entering”
echo “the associated number.”
echo “”
i=1
while [ $i -le $sid_available ]
do
printf “%2d. %10s\n” $i ${sid[$i]}
i=`expr $i + 1`
done
echo “”
echo “Select the Oracle SID [1]: \c”
read entry
if [ -n "$entry" ]
then
entry=`echo “$entry” | sed ‘s/[a-z,A-Z]//g’`
if [ -n "$entry" ]
then
entry=`expr $entry`
if [ $entry -ge 1 ] && [ $entry -le $sid_available ]
then
sid_selected=$entry
fi
fi
else
sid_selected=1
fi
esac
done
#
# At this point we have a valid sid reference
#
export ORACLE_SID=${sid[$sid_selected]}
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=NO
echo
echo “Setting ORACLE_SID = $ORACLE_SID” ;
echo

#
# Amend variables based on environment
#
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/OPatch:$PATH
asmcheck

else
echo “No entries in $ORATAB. no environment set”
fi
fi
fi
}

########################################################
# Sum the sizes of the files in a directory
########################################################
lsum ()
{
ls -l |awk ‘NR == 1 {d=1024 ; z=”Kb”}
{sz+=$5}
sz > 1048575 {d=1048576 ; z=”Mb”}
sz > 1073741823 {d=1073741824 ; z=”Gb”}
END {printf (“%.1f %s\n”, sz/d,z)}’
}

########################################################
# This function implements the human readable output form in ls which gets you the size of the file in Mb’s and GB’s rather than bytes
# based on the Solaris -ls -lh command
########################################################
lsh ()
{
ls -l |awk ‘$5 1048575 && $5 1073741823 {printf (“%s %+10s %+6s %10.1f%s %s %+2s %+5s %s\n”, $1,$3,$4,$5/1073741824,”Gb”,$6,$7,$8,$9)}’
}
########################################################
# Some Unix Environment Defaults
########################################################
umask 022
export TMOUT=0
export HISTFILE=/.sh_history
export EDITOR=vi
export PATH=$PATH:.:/usr/sbin
export LD_LIBRARY_PATH=/usr/lib/hpux64
export UNIX95=””
export USER=$LOGNAME
export PS1=”[\${LOGNAME}@`hostname`][\${ORACLE_SID}]\${PWD} $”

########################################################
# Set up some standard Oracle Environment components
########################################################
export SQLPATH=/shared/oracle
export ORACLE_BASE=/app/oracle
export NLS_DATE_FORMAT=”YYYY-MM-DD:HH24:MI:SS”

########################################################
# Standard Alias settings (no dependency on environment)
########################################################

alias asm=’asmcmd -p’
alias lss=’ls -ltr’
alias sysdba=’sqlplus “/ as sysdba”‘
alias sysasm=’sqlplus “/ as sysasm”‘
alias rmant=’rman target / nocatalog’
alias pmon=’ps -fu oracle | grep pmon | grep -v grep’

#############################################################
# The Main profile code starts here
#############################################################

#
# If you are on terminal then process
#
if tty -s
then

#
# Set up the Terminal
#
if [ "$TERM" = "" ]
then
eval ` tset -s -Q -m ‘:?hp’ `
else
eval ` tset -s -Q `
fi
stty erase “^?” kill “^U” intr “^C” eof “^D”
stty hupcl ixon ixoff
tabs
set -u

#
# Mail Configuration
#
export MAIL=/var/mail/oracle
export MAILMSG=”You have mail!”
if [ -s "$MAIL" ]
then
echo “$MAILMSG”
fi

#
# Set the default Oracle Environment
#
SETSID_AUTO=”YES” # setsid AUTO function enabled set 1st entry in oratab
setsid
SETSID_AUTO=”” # setsid AUTO function disabled

#
# Environment Dependent Alias Settings
#
alias oh=’cd $ORACLE_HOME’
alias vahome=’if [ -d /oradata/$ORACLE_SID/VirtualAgent ] ; then cd /oradata/$ORACLE_SID/VirtualAgent;fi’
alias diagdest=’cd $ORACLE_BASE/`adrci exec=”show homes” | grep $ORACLE_SID`/trace’
alias diagdestasm=’cd $ORACLE_BASE/`adrci exec=”show homes” | grep ASM`/trace’
fi

About these ads

2 Responses to “The use of functions in a .profile file”

  1. [...] I wrote/modified the one below. Most of the ideas are taken from perfect blog entry of John Hallas, The use of functions in a .profile file . I needed to modify because we have 10G databases with 11GR2 GI for ASM and some more [...]

  2. [...] John Hallas lays out the use of functions in a .profile file. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 196 other followers

%d bloggers like this: