RE: Map database to running instance from oratab?

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Sun, 29 Jul 2012 19:27:57 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4E3CD3E20_at_LITIGMBCRP02.Corp.Acxiom.net>

Ethan,

As has been raised previously, the db_unique_name doesn't have to be tied to the instance name. Data Guard adds a nice twist. Throw in RAC and DG with mulitple standbys and you get all sorts of fun.

As for your script, be VERY careful what shell you run it in. Only ksh allows you to change variables in a while-loop and have those changed values visible outside of the loop. In other words if you run that code in Borne or Bash it won't work (I haven't tested csh). I researched this a bit a number of years ago when we first shifted from ksh to bash and now can't remember to reason for this situation. All I can remember is the OS folks not understanding why I'd code so much in shell. If I find details I'll make sure to share. Anyway, below is a test showing this behavior. Take the same script and change "#!/bin/bash" to "#/bin/ksh" and it'll work as you expect:

% cat env_test.sh
#!/bin/bash

set -x
export INSTANCE=

echo "Instance variable BEFORE loop: $INSTANCE" grep -vE '(^#|^$)' /etc/oratab | while read DATABASE do

   export INSTANCE=`echo $DATABASE | cut -d':' -f1`    echo "Instance variable IN loop: $INSTANCE"    break
done

echo "Instance variable AFTER loop: $INSTANCE"

% env_test.sh
+ export INSTANCE=
+ INSTANCE=
+ echo 'Instance variable BEFORE loop: '
Instance variable BEFORE loop:
+ grep -vE '(^#|^$)' /etc/oratab
+ read DATABASE

++ echo +ASM1:/grid/app/11.2.0/oracle:N
++ cut -d: -f1

+ export INSTANCE=+ASM1
+ INSTANCE=+ASM1
+ echo 'Instance variable IN loop: +ASM1'
Instance variable IN loop: +ASM1
+ break
+ echo 'Instance variable AFTER loop: '

Instance variable AFTER loop:

DAVID HERRING
DBA
Acxiom Corporation

EML   dave.herring_at_acxiom.com
TEL    630.944.4762
MBL   630.430.5988 

1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ethan Post Sent: Tuesday, July 24, 2012 12:14 PM
To: Radoulov, Dimitre
Cc: oracle-l
Subject: Re: Map database to running instance from oratab?

Thanks for all who contributed answers, seems there are no easy answers. Ideally some of these things would be exposed externally without having to look into the database. There are things I might like to know before I even log in with a script. Is it running, is it a standby, is it rac non-rac, what is the instance name... Anyway here is what I came up with, this is my

.get-active-instance-from-database.sh script. Makes use of a couple other custom scripts.

. .require.sh DATABASE "${1}"

.list-all-databases.sh | grep "^${1}$" | while read DATABASE; do

   INSTANCE=$(ps -ef | grep "[ora|asm]_smon_${DATABASE}$" | grep -v "grep" | cut -d"_" -f3)

   if [[ -z "${INSTANCE}" ]]; then

      i=0
      while (( ${i} < 5 )); do
         ((i=i+1))
         INSTANCE=$(ps -ef | grep "[ora|asm]_smon_${DATABASE}${i}$" | grep -v "grep" | cut -d"_" -f3)
         [[ -n "${INSTANCE}" ]] && break
      done

   fi
done

if [[ -z ${INSTANCE} ]]; then

   .warning.sh "$0" "Failed to map instance to database ${1}. Verify database is running and in oratab file." else

   echo ${INSTANCE}
fi

--
http://www.freelists.org/webpage/oracle-l


***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jul 29 2012 - 14:27:57 CDT