Installing Oracle Client

On this Page:

1 ORACLE CLIENT INSTALLATION README
2 TROUBLESHOOTING DATABASE CONNECTIVITY

OneDrive folder with the installers can be found here: Pitt IT - Oracle Client Files

ORACLE CLIENT INSTALLATION README

Last updated 2.2.2023

The folder linked above provides the supported versions of Oracle Clients we use in PittIT.
Each folder is named for the version provided in the zip file. Download the zip file to your PC and extract it and install it from somewhere on your PC (don't unzip in OneDrive).

Do I want Oracle 32-bit or 64-bit?

If you will be using Alteryx, select Oracle 12.1.0.2 64-bit.

If you are unsure, select Oracle 12.1.0.2 32-bit.
The 64-bit client is needed for some applications or connectivity protocols but basic connectivity with common SQL tools (i.e. Toad, SQL Developer) have issues with 64-bit clients, so we recommend the 32-bit client.
NOTE: Newer Oracle Clients have not yet been approved for all environments in PittIT as of the last time this document was updated.

How do I perform the install?

Download the zip file locally to your PC and unzip.
Right click on the installer (setup.exe) and 'Run as Administrator'.
SELECT ADMINISTRATOR INSTALLATION TYPE - ** This is very important because this ensures you will have all of the security dll files needed to connect with various authentication means used in Pitt IT.
Place the file in a location similar to "C:\app\oracle".
Remaining prompts just select defaults/ok.

What about forward/backward compatibility?

12.1.0.2 clients can connect to Oracle 11.2, 12.1 and 12.2 (19c), etc.

Should I upgrade or deinstall if I have an older version of the client?

All clients are FULL-INSTALLS. You are never going to be upgrading an existing client.
You can just install the new client in a new location and leave the old one. However, it is recommended to uninstall the older one so you don't have confusion.

To DEINSTALL, you can not use 'Add/Remove programs'. You have to run “Universal Installer” (right-click and run as Administrator) in the Oracle programs listing. Then click on ‘Deinstall Products’ and select your old Oracle Home to deinstall. You may get an error at the end because you are running a program from the directory that you are deinstalling – if that happens just click OK then try and manually remove the file/files it couldn’t do.

How do I set up connectivity?

Obtain the zip file called "ora-files.zip". Download and extract (there will be 3 .ora files). Place these 3 files in the location: [ORACLE_HOME]/network/admin. You will be overwriting default ones that came with the install - that is ok. These files are necessary for names resolution.

I still can't connect...

If you are still having issues, review the Troubleshooting guide below.

TROUBLESHOOTING DATABASE CONNECTIVITY

NOTE: This document is intended for troubleshooting connection issues to the PRDXDW_QUERY service. This document assumes your workstation has been granted network access, either through an sremote role, direct firewall rules, or GlobalProtect.

STEP #1 – Install the Oracle Client (Runtime or Administrator version)

An Oracle client must be installed on the user’s workstation by a user with Administrator access. Oracle 11g (11.2.0.4) and Oracle 12c (12.1.0.2) are the most recent supported versions. These clients are available for download in 32-bit or 64-bit versions.

Important! The installation method must be either “RUNTIME” or “ADMINISTRATOR”. Installing Oracle Instant Client will not provide the security features required to connect to the Data Warehouse.

STEP #2 – Copy in the TNS_ADMIN ORA files

The ORACLE_HOME is the folder where the Oracle Client was installed. In this example, we are using:

C:\oracle\product\11.2.0\client_1

Each ORACLE_HOME has a network\admin directory that defines database connectivity. This is referred to as the TNS_ADMIN directory. Typically, TNS_ADMIN points to ORACLE_HOME/network/admin.

There are 3 files that need to be copied to this folder. They are:

SQLNET.ORA
LDAP.ORA
TNSNAMES.ORA

Request the 3 files above from a member of CSSD Business Intelligence, and copy them into the TNS_ADMIN folder (ex. C:\oracle\product\11.2.0\client_1\network\admin). Keep in mind you may need Administrator access to overwrite existing files in this directory.

STEP #3 – Test Firewall Rules

Even with the understanding that network access is in place, it is a good idea to try testing each layer of access. This will help determine where a problem may reside. Here are some Command Prompt troubleshooting tests.

Launch your SREMOTE connection, if necessary.

Launch the Command Prompt on you PC.

Check that the database is resolving properly by typing the following – you should see ‘OK” returned at the bottom:

C:\> tnsping prdxdw_query

Used parameter files: C:\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pdx1-scan.cssd.pitt.edu)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prdxdw_query.cssd.pitt.edu))) OK (20 msec)

For advanced troubleshooting, test the actual firewall access. (Some Windows 7 PCs need to enable telnet commands – to do this go to ‘Start’ – ‘Control Panel’ – ‘Programs and Features’ – ‘Turn Windows features on or off’ – Check ‘Telnet Client’ – Hit ‘OK’.)

C:\> telnet pdx1-scan.cssd.pitt.edu 1521

C:\> telnet pdx1db01-vip.cssd.pitt.edu 1521

C:\> telnet pdx1db02-vip.cssd.pitt.edu 1521

Each of the above telnet tests should return a blank screen (opening the session). If you get a timeout error, there may be a firewall issue.

If any of the above return incorrect results, please send a screen shot of the problem along with your workstation IP address.

Other Troubleshooting tests:

Check if you have a leftover TNS_ADMIN variable defined from another Oracle installation. This has been known to cause a conflict. There are 2 ways to check this: 1. At the Command Prompt, type: “set”. This shows what is set in the environment. 2. Right-click on the Computer icon on the desktop, choose Properties. In the System window click on Advanced System Settings. Then click on Environment Variables.

Try connecting at someone else’s computer. This will define if the issue is your account or your PC configuration.

Try to access the database using another Oracle program on your PC. For instance, if you can not connect with Discoverer, try to connect with a SQL*Plus client connection.

Have you recently changed your Pitt password? Depending on the SQL tool you use, some characters and/or character positions are not acceptable when connecting with some Oracle tools. The #, _, $ and ! have rarely caused a problem. However, the \, @, &, and any quote-mark have been known to cause issues with Oracle connectivity.

If you are using Oracle SQL Developer, there are additional settings needed to use RADIUS authentication (PITT authentication). See the following screen shot under ‘Tools’ à ‘Preferences’ à ‘Database’ à ‘Advanced Parameters’.

SELECT THE FOLLOWING: Use OCI/Thick driver

When opening a Helpdesk Ticket for connectivity problems, going through the above troubleshooting tests will help pinpoint the problem you are having and more quickly resolve your connection issues.