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.
Hi Coskan
We wrote a framework in pearl, which achieves the same as your .profile, but does this for all operating systems for which current Oracle versions are certified. Yes, you can also use it with Windows!
What do you gain by this? Navigation works the same on all your database servers, independent of the OS.
In addition we provide some scripts for starting, stopping servers (db, listener, application server….) or showing the current status of the system.
The framework is freeware and available through http://www.opitz-consulting.com/veroeffentlichungen/oc_oracle_base_freeware.php
You can find a short example of the status script on my blog http://danirey.wordpress.com/2010/05/10/ocob-opitz-consulting-oracle-base-in-english
Cheers Dani
Comment by danirey — September 15, 2010 @ 5:30 am
I think I missed this one because the Documentation is in German 🙂
Thanks for sharing the links ,currently I do not have anything on windows but pearl looks definitely better than having different file types for different os. And the additional options for managing the databases are all good. it is basically a compact framework of what I was doing with multiple scripts.
Good work from both of you guys.
Comment by coskan — September 15, 2010 @ 8:45 am
The language problem on our homepage is an issue, I know and I’m sorry for that.
Fortunately the framework as well as the installation and usage guide are in English. http://www.opitz-consulting.com/index.php?eID=tx_nawsecuredl&u=0&file=fileadmin/redaktion/veroeffentlichungen/pdf/OCOB_installation_guide_en_sicher.pdf&t=1284631098&hash=5f72293e803e888c08bad39d22832dbb
Comment by danirey — September 15, 2010 @ 8:50 am
Thank you Coskan for this blog post. I will test and modify this functions according to my needs and certainly start using aliases.
Regards,
Marko
Comment by Marko Sutic — September 15, 2010 @ 8:30 am
Be careful, Once you get used to use aliases there is no go back 🙂
thanks for the comments
Comment by coskan — September 15, 2010 @ 8:46 am
Thanks for the mention. Glad you found it useful
John
Comment by John Hallas — September 16, 2010 @ 9:20 pm
Hi Coskan,
There is a little bug there for database > 11g at this
tail -f $ORACLE_BASE/`adrci exec=”show homes” | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log
[oracle@taral oracle]$ adrci exec=”show homes”
ADR Homes:
diag/tnslsnr/taral/listener
diag/asm/+asm/+ASM
diag/rdbms/taral/taral
So, this will give
[oracle@taral ~]$ adrci exec=”show homes” | grep taral
diag/tnslsnr/taral/listener
diag/rdbms/taral/taral
So, showsid will give wrong result
alias talert=’tail -f /u01/app/oracle/diag/tnslsnr/taral/listener
diag/rdbms/taral/taral/trace/alert_taral.log’
Comment by Taral — September 17, 2010 @ 5:15 am
that is a good catch indeed but not many things I can do. On our homes our tns listener is under diag/tnslsnr/hostname/listener
so If your hostname is same with db which is more likely then it is your homework to change the script like
grep rdbms/$ORACLE_SID and grep tnslsnr/$ORACLE_SID
hope it helps
Comment by coskan — September 17, 2010 @ 9:30 am
Hi Coskan,
there are too many code.
there are array, PS3 and select command in UNIX shell.
Comment by chen — September 22, 2010 @ 12:48 am
Chen The day I understand that I could not manage software engineering was the date I am intruduced to arrarys at university 🙂
I wish I could write better code but I think I do not have intention. maybe I will give my last chance to pearl and learn pearl good enough to use array
Comment by coskan — September 22, 2010 @ 7:55 pm
Hi Coskan-
thanks for sharing scripts with us, really useful.
I added 2 more alias.
after setting SID
alias s=’sqlplus /’
alias ss=’sqlplus / as sysdba’
Thanks
Comment by Jagjeet Singh — September 22, 2010 @ 12:38 pm
Coskan,
really great ideas. Both the functions and the aliases must make your life so much easier. I’m now inspired to attempt similar levels of laziness 🙂
Thanks for explaining this so well.
Mike
Comment by mikesmithers — September 22, 2010 @ 6:43 pm
Mike Jagjeet I am glad that you find it useful, and thanks for leaving feedback
Comment by coskan — September 22, 2010 @ 7:53 pm
Hello Coskan,
to view alert log in 11g i use following syntax:
~~
alias alert11g=’adrci exec=”show homes; set home diag/rdbms/$(echo $ORACLE_SID|tr A-Z a-z)/$ORACLE_SID; show alert”‘
~~
thanks
Comment by Eugene — September 26, 2010 @ 9:26 am
[…] recently read a really good article by Coskan on the use of aliases in Linux. Following on from my recent adventures with OPAL, I’ve found a perfect excuse to use them. […]
Pingback by Apache, Aliases and Zenity on Ubuntu – how to control your ego « The Anti-Kyte — November 21, 2010 @ 11:16 pm