Because I am a lazy guy who wants to do everything fast I really like to have aliases in user profiles. When I login on a box different version of Oracle Homes and multiple ORacle databases on the same machine I feel naked without aliases. I am a guy who wants to tail alert log/any related log while I am doing something and not having an alias for tailing not just slows me down it also makes me really annoyed in 5 seconds time. At current job I suffered for the first week and I immediately requested to have a “standard” profile and got acception from my manager and 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 modifications for RAC. ( Probably most of you already realized that I am good with modifying a code but rubbish with my own, old habit from university never liked programming and needed to pass some classes:))
Here are the two functions I added to the profiles. First one (setsid) sets env settings of the database listed in /etc/oratab, second one (showsid) shows the aliases you can use after you call setsid. Couple of things to bear in mind, setsid assumes that you have $ORACLE_BASE which is hardcoded /u01/app/oracle in the script and it also assumes that you use ORACLE_SID for alert log directories. oradiag and ldiag aliases are default set because we use 11G diag home on all the boxes. You may also need to check ora_version variable it may not suit to your settings. (OFA is needed for everything run without modification)
SETSID
setsid ()
{
unset ORATAB
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
ORACLE_BASE=/u01/app/oracle
ORATAB=/etc/oratab
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 ;;
*)
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 with given number [1]:"
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
#
# SET ORACLE_SID
#
export ORACLE_SID=${sid[$sid_selected]}
echo "Your profile configured for $ORACLE_SID with information below:"
echo ""
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/bin
unset LD_LIBRARY_PATH
ORAENV_ASK=NO
. oraenv
alias oh="cd ${ORACLE_HOME}"
ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'`
if [ $ora_version -lt 11 ]
then
alias bdump="cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump"
alias talert="tail -f /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log"
alias valert="view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log"
fi
if [ $ora_version -ge 11 ]
then
export ORACLE_UNQNAME=$ORACLE_SID
alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'
alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'
alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace'
alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace'
fi
alias pmon='ps -fu oracle | grep pmon | grep -v grep'
alias tns='ps -fu oracle | grep tns | grep -v grep'
alias oradiag='cd $ORACLE_BASE/diag/'
alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'
unset ORAENV_ASK
echo
#
#GIVE MESSAGE
#
else
echo "No entries in $ORATAB. no environment set"
fi
fi
fi
}
SHOWSID
showsid()
{
echo ""
echo "ORACLE_SID=$ORACLE_SID"
echo "ORACLE_BASE=$ORACLE_BASE"
echo "ORACLE_HOME=$ORACLE_HOME"
echo "alias oh=cd ${ORACLE_HOME}"
echo "alias pmon='ps -fu oracle | grep pmon | grep -v grep'"
echo "alias tns='ps -fu oracle | grep tns | grep -v grep'"
ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'`
if [ $ora_version -ge 11 ]
then
echo "alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'"
echo "alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'"
echo "alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace'"
echo "alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace'"
echo "alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'"
echo "alias oradiag='cd $ORACLE_BASE/diag/'"
fi
if [ $ora_version -lt 11 ]
then
echo "alias bdump=cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump"
echo "alias talert=tail -f \"/u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log\""
echo "alias valert=\"view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log\""
echo "alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'"
fi
echo ""
}
Usage : (no setting when showsid runs for the first time)
gundogar@SNOW:~#cosarac1 oracle@192.168.1.111's password: Last login: Sun Sep 12 14:31:24 2010 from cosarac1.oel.com [oracle@cosarac1 ~]$ showsid ORACLE_SID= ORACLE_BASE= ORACLE_HOME= alias oh=cd alias pmon='ps -fu oracle | grep pmon | grep -v grep' alias tns='ps -fu oracle | grep tns | grep -v grep' -bash: [: -ge: unary operator expected -bash: [: -lt: unary operator expected [oracle@cosarac1 ~]$ setsid 1- +ASM1 2- oracos1 Select the Oracle SID with given number [1]: 2 Your profile configured for oracos1 with information below: The Oracle base for ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 is /u01/app/oracle/product/10.2.0/db_1 [oracle@cosarac1 ~]$ showsid ORACLE_SID=oracos1 ORACLE_BASE=/u01/app/oracle/product/10.2.0/db_1 ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 alias oh=cd /u01/app/oracle/product/10.2.0/db_1 alias pmon='ps -fu oracle | grep pmon | grep -v grep' alias tns='ps -fu oracle | grep tns | grep -v grep' alias bdump=cd /u01/app/oracle/admin/oracos/bdump alias talert=tail -f "/u01/app/oracle/admin/oracos/bdump/alert_oracos1.log" alias valert="view /u01/app/oracle/admin/oracos/bdump/alert_oracos1.log" [oracle@cosarac1 ~]$ bdump [oracle@cosarac1 bdump]$ pwd /u01/app/oracle/admin/oracos/bdump [oracle@cosarac1 bdump]$ pmpn -bash: pmpn: command not found [oracle@cosarac1 bdump]$ pmon oracle 2860 1 0 Sep10 ? 00:00:21 asm_pmon_+ASM1 oracle 19925 1 0 Sep12 ? 00:00:12 ora_pmon_oracos1 [oracle@cosarac1 bdump]$ oh [oracle@cosarac1 db_1]$ pwd /u01/app/oracle/product/10.2.0/db_1 [oracle@cosarac1 db_1]$ tns oracle 3287 1 0 Sep10 ? 00:00:05 /u01/crs/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit oracle 26861 1 0 Sep13 ? 00:00:06 /u01/crs/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit [oracle@cosarac1 db_1]$ talert Tue Sep 14 18:25:07 2010 Thread 1 advanced to log sequence 70 (LGWR switch) Current log# 2 seq# 70 mem# 0: +DATA/oracos/onlinelog/group_2.257.729388121 Current log# 2 seq# 70 mem# 1: +FRA/oracos/onlinelog/group_2.258.729388123
Believe or not at the end of a year this settings will save lots of your time when you start using them .
If you have problems with formatting please contact me so I can send you the txt file.
