The Soup: Installing The Oracle 11g Client
Chances are higher than 90% that if you work in a corporate environment you are logging onto a Windows OS every morning. If you happen to dabble in the Oracle domain, chances are also highly likely that you have (or will need) the Oracle client installed on your machine. The Oracle client installer, like many things Oracle-branded, is based on Java, which can best be described as “tolerated” by Windows (though some corporations are becoming less so). Frankly, I dislike Java-based installers as they tend not to tie in well with the Windows operating system, and the Oracle client installer is no exception. Regardless of the underlying technology or my opinion, my need to have the client installed is still very real as I work with both SQL Server and Oracle database solutions. In my experience, it’s quite easy to end up with either a bad, broken, or partially working Oracle 11g Client installation without even knowing how or why. This 2-part post will help avoid these situations before they begin or help rectify them if you already find yourself in a bad spot. Part 1 will outline how and what to install using the installer, and Part 2 will outline how to uninstall the client, both automatically (if possible) and manually (if necessary).
Start out on the Right Foot
I don’t understand the mentality here, but Oracle only publishes the 11.2.0.1 client on the web. This version of the client is riddled with issues, and as of writing this post (May 28, 2013), the most current version is 11.2.0.3. The first step, which will resolve a good deal of issues in and of itself is to download the 11.2.0.3 client. You will not find this version of the client publicly available on the web; rather, go here: https://updates.oracle.com/download/10404530.html (side-note: it looks like they now have a dedicated url for this patchset though I never found it when I originally wrote this article), you will have to go to support.oracle.com, sign in using your Oracle site account, click on the Patches & Updates tab, enter 10404530 into the search field, and select the Windows bit version you require. If you’re downloading the 64-bit version, download part 4 of 7 for the client, and for the 32-bit version, download part 3 of 6. These zip files will contain the client installer which is the only thing you’re interested in (as related to this post).
In addition, to run either installer, you will need to have Administrator rights on the box your installing it to. This is because there will be some modifications made to the registry, folder permission changes, and conceptually some dlls will be registered (more to come on this later) as well. If you don’t have Administrator rights on your machine, submit a request for it or bribe someone with the appropriate rights to do the client installation on your behalf.
What client (bit-level) do I need to Install?
This is an incredibly common question, which thankfully has a simple answer: the same bit-level as the app making use of the driver. For example, if you need a 64-bit SQL Server instance to connect to Oracle, you would need to install the 64-bit client. Alternatively, if you had a 32-bit web application that needed to connect to Oracle, you need to install the 32-bit client. There may be situations where you will need both 32 and 64-bit clients installed on the same machine due to the nature of the apps you’re running. This shouldn’t be an issue so long as you approach it with some foresight (as we will discuss below). The key about the bit-level is that it’s the calling app that matters; not the bit-level of the Oracle database you’re connecting to. A 32-bit SQL Server Instance will have no issues connecting to a 64-bit Oracle database so long as you’re using the 32-bit driver.
What Installation Option Do I Choose?
Regarding what install option to go with, I will default to the classic Database Administrator answer: it depends. If you truly don’t know and would rather just be prepared for anything, go with the Administrator option as it installs everything you would need to connect to Oracle from a Windows box. If you want to minimize the footprint of the Oracle client though (and it can be fairly substantial as you can see below), choose the Custom installation option, and keep reading as I discuss some of the more common features you should think about if customizing your install.
Choose the Right Path
While an install path may seem trivial, picking one (or using the default path) can lead to frustration down the road. How to set the path depends if there is a need to install multiple bit-level clients on the machine now or at any point in time in the future. If it’s a 32-bit OS, then this is irrelevant, but chances are you’re installing this onto a 64-bit machine running a 64-bit OS. Since most folks are on a 64-bit architecture and are running applications both in the 32 and 64-bit domains, I will suggest taking the safe approach to keep the option open that multiple bit-level clients will be needed at some point in time.
Setting up the path for this level of flexibility is simple, as it involves making a quick change to the Oracle Base directory during the Installation Location screen of the installer. This should update the Software Location path automatically, but ensure the Oracle Base and the initial path of the Software Location match before proceeding to the next screen. My recommendation is to set your Oracle Base to reflect the bit-level of the client install, as this takes any guess work out of what install base is located where. For example, the Oracle Base of a typical 64-bit install I do is as follows:
C:\oracle\64
Avoid the pitfall of only changing the Software Location directory. If you do this, any future runs of the installer will balk and throw errors if the same Oracle Base is entered, as an Oracle Inventory check reports it’s already in use. I don’t fully understand the reasoning behind this as it’s really just a path, but the Oracle Inventory check insures no two Oracle installs utilize the same Oracle Base directory, hence why I suggest a bit-level identifier in the Oracle Base path instead of only the Software Location path.
Custom Install Components
If you decided to go with a Custom install, try your best to identify the minimum set of components you will be utilizing. Two components I recommend you always install are the SQL*Plus and Oracle Net options.
SQL Plus is a command line utility that you can use to connect to any Oracle database instance. Think of it as a SQLCMD equivalent in the Oracle domain. This is very helpful for troubleshooting issues that range from a basic connectivity check to deployment of complex scripts.
Oracle Net is the component that will be quite helpful when it comes to generating TNS aliases. This will be a critical tool if you ever find yourself connecting Oracle and MSSQL for cross-platform replication or if you don’t have your Oracle Services properly setup on the domain. This will also build out the proper directory structure for the tnsnames.ora and sqlnet.ora files (important for general configuration purposes).
Other items I recommend installing are the Oracle drivers, specifically the Oracle JDBC/THIN Interfaces, Oracle ODBC Driver, and the Oracle Provider for OLE DB (the ODBC or OLE DB driver is required if you want to make a Linked Server for Oracle from SQL Server). These sets of tools will provide the most common connectivity functionality for developers, DBAs, application servers, and database servers alike.
Here the pitfall to avoid is installing SQL Developer from the client install interface. It will be an out of date version, and it’s in your best interest to get the latest version directly from the Oracle site, found here. I’m a big fan of the latest version of SQL Developer and find it just as good as a lot of the proprietary Oracle clients out there, so don’t shy away from it if you want a verbose GUI for Oracle.
POST-Install Configurations
The installer loves to fiddle with directory permissions. The first thing it will do is modify the permissions on the Oracle Base directory so only the Administrator account can gain access to the subdirectories and files. To prevent needless headaches, I suggest you grant the account the calling app is running as (this may be any user on the machine, a specific service account, or a group) full access to the Oracle Base directory and all sub directories. To do this, you need to navigate to the folder using Windows Explorer -> Right-Click on the folder in question -> Properties -> Security Tab -> Edit… -> Add… -> User/Group of your Choice -> Allow Full Control -> Ok. This will avoid those annoying permissions errors you will inevitably come across unless you do this.
I’ve also found that the 11.2.0.3 client doesn’t do the best job at registering the OLE DB drivers on the machine during the install. To remedy this, you should manually register the oraOLEDB11.dll file. First, open command prompt (cmd.exe) as Administrator, navigate to the (Oracle installation root)/product/11.2.0/client_1/BIN directory and type the following:
%>regsvr32 OraOLEDB11.dll
If you’re on a 64-bit OS and are registering the 32-bit driver, you will need to make sure you’re calling the 32-bit regsvr32 command as follows:
%>C:\Windows\SysWOW64\regsvr32.exe OraOLEDB11.dll
If you setup DNS alias entries properly, you can make connections to Oracle environments without ever using a tnsnames.ora file. This is advantageous as it eliminates a file you need to maintain (or maintain at an enterprise level). This can become a problem if you move a database to a different host, change the listener to a different port, etc. The key to avoiding a tnsnames.ora file is to edit your sqlnet.ora file so that it shows the following entries:
SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME) HOSTNAME.DEFAULT_SERVICE_IS_HOST = 1
The first line is there by default. Changes we will be making are regarding the second and third lines. The change in the second line is adding HOSTNAME to the connection options. Appending this value allows the client to connect using DNS to resolve the database name. Of course this requires you have DNS aliases setup properly on your domain, but this will eliminate the need for tnsnames at the enterprise level. The third line is to help rectify bug 9271246 (i.e. TNS:LISTENER RECEIVED NO CONNECT_DATA FROM CLIENT) which admittedly may not be needed, but the platform it is applicable to is Windows so I choose to be safe rather than sorry.
If you are going to be referencing Oracle dlls, the tnsnames.ora file, etc. chances are you will also need to setup a new Environment variable, ORACLE_HOME. This variable is used by all manner of applications ranging from proprietary clients to SQL Server (as it pertains to setting up aliased Oracle publishers or subscribers). To create this environment variable, go to the Computer Properties (right-click on Computer -> Properties) -> Advanced System Settings -> Environment Variables… -> New… (System Variables) and enter ORACLE_HOME as the Variable Name and enter the path of the Oracle client (directory) as follows:
Programs that make use of this variable should be restarted if you create this variable while they are running. The key things these applications may be looking for are the drivers located in the BIN directory or the tnsnames.ora file located in the NETWORK\ADMIN directory, and they use this variable to navigate to the root path of the installer looking for these folders.
Give it a spin
The final step is to connect to an Oracle instance using whatever app you needed to install the client for. This may be setting up a Linked Server or even simply connecting through the SQL Plus prompt. Validate your connection and hopefully you will be issue free making connections to your Oracle databases from here on out. If you run into issues, you can always deinstall (aka uninstall) the client and start over. For instructions on this, head to my 2nd post, where I go through the dirty details of the de/uninstallation process.
Hope this was helpful and happy connecting!
– John
John Eisbrener, DBA 
Nicely explained in detail.
Best on the Web! Installing on a Virtual Windows Server 2008 64-bit. Can’t get it to work. Created a new instance. A group of us followed this. It did a great job of summing up all the little tidbits found all over the web.
However, Listener didn’t work. TNSPing doesn’t work either.
There are hundreds of post about the Oracle 11g Client install not working on Windows server.
Thanks for a great article. We now know the dozen pitfalls and avoided them. Now, we need to figure out why this won’t work.
Thanks for the feedback! Regarding the listener issue, I’ll reach out to you via email for more information.
Great post! Do you need to append the ORACLE_HOME location if installing both 64 and 32 bit on a 64 bit server?
I’ve not had any luck listing multiple paths in the ORACLE_HOME (system environment) variable. If push comes to shove and you have to reference the 32-bit path for one app and the 64-bit path for another, hopefully each app can be run as a separate Service Account. If this is the case, remove any ORACLE_HOME system environment variables, and instead make a USER ORACLE_HOME variable for each respective service account, listing the appropriate 32/64-bit path as needed. I’ve not done this personally, but conceptually this should do it for you. Hopefully you don’t have to run into this situation as I’m hoping all server software you are installing is 64-bit.