Installing the Full Oracle Database Client on Windows
*This is not the Oracle Instant Client
If you want to connect to and work with a database via query tools, e.g., TOAD, SQL Developer, etc., then you need the full client and not the instant client. If you are a developer, then you want the full client – if not the entire database. Most people make the mistake and download the instant client because the full database client isn’t as easy to find. There are lots of instructions, but I haven’t found any that are comprehensive so that is why I put this together. Hopefully it will help you get moving on your project quicker. The screen shots were taken as of the time of this writing so they may change over time. After you install the product see the end of this writing for some important connection notes.
- Be sure you have permission to install software on the machine for which you want to install the Oracle client.
- Go to Oracle.com > Downloads > Database > Oracle Database
- On the Downloads tab find the database version you are needing to support/connect to. In this example I am using 12c release 1.
- Click See All next to the files for the OS you want to install the client. In this example I am using Windows 64 bit.
- On the Downloads tab click See All
- Click “Accept License Agreement” at the top of the page.
- Scroll down to “Oracle Database Client” and click the .zip file for the download you want to use.
- If you are asked to log in then do so and the download will start once you are authenticated. If you don’t have an account you will need to create one – don’t worry it’s free and you won’t receive hundreds of emails.
- Once the file is downloaded then unzip the contents to a location on the local machine that you want to install the client on, i.e., not a network drive. Attempting to install from a network directory may take longer and may also present other issues.
- Once unzipped open the client folder and right click “setup.exe” and choose “Run as Administrator”.
- A command window will open that will start the installer. After a minute or two the installer will open.
- On the first screen, “Select Installation Type”, choose “Administrator” and click Next.
- On the next screen, “Select Product Languages”, select any additional languages needed and click the to add them to the “Selected languages” section. Click Next.
- On the next screen, “Oracle Home User Selection”, leave the “Use Windows Built-In Account” selected unless for some reason you need to run the service under a special user account. I have never needed to do so. Click Next.
- On the next screen, “Specify Installation Location”, you can change the location if you want to. I generally do to indicate which client it is, specifically to indicate the bit, but this is completely optional. Click Next.
- Prerequisite checks are performed on the next screen, “Preform Prerequisite Checks”. If there are issues they will need to be resolved. Once this is complete, assuming there are no issues, click Install.
- The next screen, “Install Product” will complete the remaining process for installing the client onto the machine. You may get a “Windows Security Alert – Windows Firewall…”. Check both boxes and click “Allow Access”.
- Once it is installed you will be directed to the “Finish” page. Click Close.
- You are done! See next section for the important connection info.
Where is this mysterious TNSnames.ora file?
The location of where the TNSnames.ora file needs to go (following the client install) is in the network\admin directory of the software location (from the install). In my case the TNSnames.ora file will go here: C:\app\OraClient_x64\Cody\product\12.1.0\client_1\network\admin. By default following the client install, a TNSnames.ora file isn’t created; you need to create one with your TNS entries.
How do I use/create a TNSnames.ora file?
Once you have the TNS entry that you need (from your DBA) you can open a text editor (Notepad, for example) and put the TNS entry in the file and save it as TNSnames.ora in the network\admin directory discussed above. The TNS entry will have a certain format: alias = (tns path here). The alias portion is up to you to customize and the rest is to be left alone. For example, if the database name is xaborausdnx123 which is your HRS dev environment you are free to give the alias a meaningful name that you will use for connections in your query tools. In this example I might use HRS_DEV as the alias so I don’t need to remember that cryptic database name. So in Toad, for example, when you create a new connection you would use the TNS tab and enter the alias you used in your TNSnames.ora file for the database name. If using SQLPlus as user1 you would connect using user1@HRS_DEV. One point of a TNS file is to prevent you from having to use a full TNS path in your connections. Each time you get a new TNS entry you simply add it to the bottom of the file. This file can have as many TNS entries as needed.
Where do I get the TNS entry for the TNSnames.ora file?
In most cases once you get direct access to a database that requires you to maintain a TNSnames.ora file the DBA will send you the TNS entry to add to your file, but depending on the DBA this may not happen by default.
In most cases you will need to set up your TNSnames.ora file so you can connect to certain databases. Not ALL environments require this to be done; it depends on how the database is configured on the database server. Sometimes the service name of the database (generally the DB name) will resolve simply by using the database name. Sometimes you will need the full TNS entry in order to connect. The best way to know is to use tnsping. To use tnsping, just open the command prompt and type tnsping following by a space and then the database name you are trying to connect to, e.g., tnsping oradb1. If the name is resolved (via LDAP for example) you will see a message indicating how it was resolved as well as the full TNS path. If it is not resolved then you will get a message indicating such. If the name is not resolved then you need to contact the DBA so he or she can provide you with the database’s TNS entry for your TNSnames.ora file.