Map database to running instance from oratab?

  • From: Ethan Post <post.ethan@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 18 Jul 2012 15:27:36 -0500

Anyone know of a simpler way to map the database name to the running
instance ID? Assume we could have a non-rac database called FOO1, and a rac
database called FOO with instance FOO2 running on the same server. Below
fails in this case because it will find FOO1 for rac database FOO before it
finds FOO2. Not a big deal because that is really an edge case but perhaps
there is a single file I could grab this from someplace that I don't know
# ASM smon process begins with asm so this script will not find an instance
for ASM.

# listdb will just list the databases from oratab file.
listdb -l | while read DATABASE; do
   export ORACLE_SID="${DATABASE}"
   . oraenv 1> /dev/null
   INSTANCE=$(ps -ef | grep "ora_smon_${DATABASE}$" | grep -v "grep" | cut
-d"_" -f3)
   if [[ -z "${INSTANCE}" ]]; then
      while (( ${i} < 5 )); do
         INSTANCE=$(ps -ef | grep "ora_smon_${DATABASE}${i}$" | grep -v
"grep" | cut -d"_" -f3)
         [[ -n "${INSTANCE}" ]] && break
   echo "${DATABASE} ${INSTANCE}"


Other related posts: