Coskan’s Approach to Oracle

September 14, 2010

Easy/Fast Navigation with aliases in profiles

Filed under: Linux, Tips — coskan @ 8:45 pm

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.

September 11, 2010

“DBCA could not startup the ASM instance configured on this node” error for lower versions with 11GR2 GI

Filed under: RAC — coskan @ 9:14 am

Last 3 days ,I have been trying to have the toy environment at home, for 11GR2 upgrade project at work.

The challenge was building exactly same environment on virtual machine. Problem is that I did not built the environment at work so I had no idea about tricks which are not clear in doc.

The environment is;

Oracle 2 node RAC with 11GR2 clusterware July PSU with ASM storage

Database with 10.2.0.4.2  with no local listener.

I installed the grid with some minor problems which are not todays topic. Problems solved and grid was completely healthy.

I then installed 10.2.0.4 binaries pinned the nodes for lower version installation as documented in official installation doc like below from GI home


[root@cosarac1 ~]$
crsctl pin css -n cosarac1,cosarac2
[oracle@cosarac1 ~]$ olsnodes -t -n
cosarac1        1       Pinned
cosarac2        2       Pinned

And I ran dbca from 10.2.0.4  and when I chose storage as ASM I started to get “DBCA could not startup the ASM instance configured on this node. To proceed with the database creation using ASM, ASM instance needs to be up and running. Do you want to recreate ASM instance on this node?”  warning.

Checked if ASM is up and running and it was and also being listened by my default listener. So what was the problem ?

Google+otn forums is again the answer

Basically during the installation you need to link the listener.ora in oracle home to listener.ora in grid home

cd <10G_DB_HOME>/network/admin

ln –s <11G_GRID_HOME>/network/admin/endpoints_listener.ora listener.ora

After you create the link it, please do not expect that  it will  just work , because it won’t:)

You also need to have TNS_ADMIN env setting to make dbca work with ASM. (You don’t need to set it on both nodes you only need to set it in the session of the node you run dbca.)

export TNS_ADMIN=<10G_DB_HOME>/network/admin

There is also a bug and one off patch available (8288940) but because our environment did not have 10.2.0.4 home patched with that one of patch  (At office DB was installed before GI upgrade) I did not bother to install the patch when there is a beautiful easy peasy solution.

What I learnt from this  creating the same toy environment at home practice is that,  building a VM RAC following a guide is just 3-4 hours work (for this particular one I followed Tim Halls 11GR2 RAC installation Guide for RAC on Virtualbox )but once you change some of the things in the tutorial  you follow,  then you invite the trouble which is very teaching practice. I learned hell a lot just with this 2 node RAC installation.  Once you learn how to install a rac I suggest you to try to learn how to break a RAC installation :)

Next challenge is building the DR of the Same environment (not with RAC due to resource problem). Maybe I can find something to write about.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers