Finding all Oracle SW installations on a host

Shell Programming and Scripting
1

Hi. I'm trying to find the best way to locate the latest version of Oracle on a Linux host.

There could be multiple SW installs or there could be none. It could be a client or a full RDBMS installation. I need to find and set the environment in order to run the sqlplus binary. Rather than just running a find command, is there a smarter way? Many thanks.

---------- Post updated at 10:34 PM ---------- Previous update was at 09:58 PM ----------

Up until now I've used this command:

SQLPLUS_BINARY=$(find /u01 -name sqlplus -type f 2>/dev/null | sort -n | sed -n 1p)

but there must be a more elegant way to do this.

2

If there are multiple installs, you might start with querying the package manager. For APT, try dpkg -l , perhaps piped into a grep -i ORACLE or similar / adequate keywords.

3

Oracle has a tool - $ORACLE_HOME/bin.tnsping . You should have it installed on your desktop machine if you have an oracle client install.

This will tell you if you connect to a server on a remote host - i.e., does one exist and is it up. sqlplus binary exists on any box that has either the client software set or the server software set. So, locating sqlplus in each $ORACLE_HOME on a machine tells you the instance name(s). e.g., /u01/blah/blah/bin/sqlplus , plus it shows the version as well.

If you want to connect to every server you can (you need passwords and usernames) see, you should consider having all of the versions like 9, 10, 11 client installed on the box doing the searching.

4

I should've mentioned that I won't know the $ORACLE_HOME setting. There may actually be no Oracle SW on there. It's a Linux VM and the script won't be run as the oracle user. I need to work out the ORACLE_HOME from scratch on whichever host the script is run on. Connecting to a DB afterwards is straightforward. But how can my script determine the correct HOME? I think I need to check the Oracle Inventory ... oraInst.loc

---------- Post updated at 12:17 PM ---------- Previous update was at 10:57 AM ----------

This command will list all Oracle inventory files:

locate oraInst.loc
5

You could simply use the id command. If oracle is installed on a UNIX box, that box has an oracle user id, unless someone did something against oracle installation instructions.

while read remote
do
   echo "$remote"
   ssh user@$remote 'id oracle &&  locate oraInst.loc  || echo "not OK" '
done < list_of_servers.txt  > report.txt
6

Thanks for that. Trouble is, there will definitely be an Oracle account on the host but there may be no Oracle SW installed.

In the cases where the SW is installed, more than likely there will multiple SW installations and the 'locate' cmd will return many records. I'd need to know which is the latest valid installation and an actual RDBMS or client installation ... there could also be some Oracle SW plugin entries.

I'm only interested in checking the local host (it's part of a script that gets rolled out there), so I won't be looping through a server list. I can use the find command mentioned earlier and just check the timestamps of the multiple sqlplus binaries. Was just hoping there was a more elegant way to do it.

7

I haven't had a system with more than one database running for some times (virtualisation does that to a system) but you might look at the file /etc/oratab for the relevant information. It should tell you exactly what you want.

I hope this helps.

bakunin

PS: �No pasar�n!

8

Thanks for the response. It's possible that there won't be any running databases or Oracle SW so there's no guarantee an oratab file will exist. All sqlplus connections will be to remote DBs.

To be fair it's an awkward question as there are many ways to get the result but not all of these methods are 100% portable between hosts with differing setups and SW versions. They're not error proof. I just thought perhaps somebody may have encountered this before and developed a script that scans an entire host (as a user other than oracle) and finds the latest Oracle RDBMS/Client install regardless of which directory tree (ORACLE_HOME) it's in or the naming convention used for that home.

9

You can cat the /etc/oraInst.loc file to find the primary Oracle inventory. For me its the /home/oracle/oraInventory directory. Then look at the /home/oracle/oraInventory/ContentsXML/inventory.xml file. As the name suggests it is an xml file. Grep for the following:

 grep "HOME NAME" /home/oracle/oraInventory/ContentsXML/inventory.xml | sort -u

The problem with looking at the /etc/oraInst.loc is that you can have more than one inventory location. You can have Oracle running as more than one user. But you should not have either situation. It should be easy for any DBA to look at your server and know what Oracle homes are being used and which database uses which Oracle home. You can also look at the /etc/oratab file. Which should be the file of record for what database runs from what home.

10

Yes, absolutely, that's one of the methods - to check that xml file but (once you find the correct xml file) there are multiple homes in there. And to a DBA it's obvious but this has to be handled automatically by the script so it takes a clever bit of code to determine the correct one. Remember that file and those homes will differ from system to system. That's why I went with searching for the newest sqlplus binary originally. It works but it's not elegant. I'm sure there's a better way to do it.

11

Why doesn't your /etc/oratab file reflect every database running on a server and which Oracle Home that database uses? When I log in to a server I have my .bashrc script run the following commands.

export DEFAULT_SID=`grep -v "^$" /etc/oratab | grep -v "^#" | grep -v "^\*" | head -1 | cut -d":" -f1`
. ${HOME}/scripts/bin/set_oracle_env.sh ${DEFAULT_SID}

The set_oracle_env.sh script set the ORACLE_HOME, PATH, LD_LIBRARY_PATH, TNS_ADMIN which is in /etc, and the ORACLE_SID. You can write your own and have it set anything that you want. I also create the following alias's for my convenience.

alias getenv="env | egrep 'ORACLE|TNS' | sort"
alias setenv=". ${HOME}/scripts/bin/set_oracle_env.sh "
alias sqlsys="cd ${HOME}/scripts/sql ; sqlplus '/as sysdba'"

Then setting my environment is dead simple. Especially with writing scripts where I start with the set_oracle_env.sh script to set the Oracle environment variables.

12

Thanks for the reply Gandolf.
In answer to your first question ... it's because it's not a database server. It's an application server. There won't be any DBs running there. I'm just connecting from there to a remote DB host (as a non Oracle user). There may not be any Oracle client or RDBMS SW installed at all locally which I have to factor in as well.

13

Perhaps you could use a tool like nmap to walk around your network looking for port 1521 being open, the default for the TNS listener. You could then explore the listener on each server you find with (I think)

lsnrctrl status

Be aware that nmap can be a heavy impact tool on the network so you should try to target it. Of course, if you know the server, then forget about nmap and have a look at the output from the listener. It might give you version information in there, depending how you have installed it.

Additionally, you could look in /etc/oratab for clues.

It's probably a bit late for installation advice, but directing the installer to use a sensible path name with the version in it is very useful. I have three versions of the full RDBMS installed on the same server. They are in:-

  • /u09/home/oracle/software/7.3.3
  • /u09/home/oracle/software/8.1.7.4
  • /u09/home/oracle/software/11G.1.1

Can you guess the versions? Yes, I know they're old! :o

We have a set of environment scripts in the PATH under /usr/local/scripts/env which we can just source into the current shell. After login, you can just run your choice of (note the preceding dot space):-

  • . unidevda
  • . unidevdb
  • . unidevdc
  • etc.

Each one of these will set up the environment as requested including ORACLE_HOME, application software version, application home, scripts directory etc., in fact anything you want. People soon get very used to signing on and sourcing the environment. You can even add them into match scripts, if that is appropriate.

Have I helped or missed the point entirely?

Robin

14

Thanks Robin. All responses are very welcome.

To summarise quickly - imagine a script that is rolled out to a linux host that is not a database server. The script is run as user root. There are no DBs running there and there's no oratab. There could be client and/or RDBMS software installed or there could be no Oracle SW at all. There's no way of knowing beforehand as it's an automated process. I need to find the latest working version of any valid Oracle SW in order to set ORACLE_HOME so I can connect to a DB on a remote host.

There could be multiple oracle inventory files with multiple homes listed in each one and the locations could be anywhere ... in any directory tree. It's basically a search on a completely unknown host.

15

Could you do a one-time or infrequent scan of the server and create your own soft link? e.g. /usr/local/bin/sqlplus You could then point your scripts to this only.

For the scan, try running each sqlplus you find with the -v flag or perhaps run this to hopefully load and quite without trying to connect to a database:-

sqlplus -v </dev/null

... or ...

sqlplus -v <<-EOSQL
quit
EOSQL

Does that help?

Robin