Getting Connected to Oracle Database Connections (ODBC)

Installing the Oracle Instant Client 19.3

Users needing to use an Oracle Database Connection (ODBC) will have the 64-bit Oracle Instant Client 19.3 available to download in Software Center. This should put all of the necessary setup components in place for you so that you do not need to take any extra steps.

  1. If you previously had the Oracle Instant Client 11.2 installed (for 32-bit connections), you do not necessarily have to remove it first
    • Neither version appears to cause any conflicts with the other
    • If you use Stellent or Captovation Capture, do not uninstall the 11.2 client for 32-bit connections, as these programs still rely on those connections
    • If you do not use Stellent or Captovation Capture, then you may safely follow the instructions in the "Switching from Oracle Instant Client 11.2 to 19.3" section before installing the 19.3 client, if desired
  2. Install the Oracle Instant Client from Software Center
  3. Open up c:\windows\system32\odbcad32.exe (ODBC Data Sources (64-bit)) to add a connection
    • Go to the "System DSN" tab and click "Add"
    • Select "Oracle in instantclient_19_3_x64" and click "Finish"
    • Enter the Data Source Name, Description, and username, and select the proper TNS Service Name
      • If there is nothing under the TNS Service Name, then check the section "Troubleshooting the Oracle Instant Client"
        • Typing the Service Name will still work even if the list is not populated with available service names
    • Test the connection to ensure the client can connect
      • If the user needs to sync their password with the database(s) they use, then reach out to the ITS Database Administrator for assistance.

Switching from Oracle Instant Client 11.2 to 19.3 (For Office 365 Access)

ODBC Error Message

Please do not follow these steps if you use Stellent or Captovation Capture! Those programs still rely on the 32-bit connections

Traditionally, Microsoft Office at the U of I has been running from 32-bit installs and required 32-bit data sources for their Oracle Database Connections. However, the transition to Office 365 is moving to a 64-bit environment and thus requires 64-bit data sources. As such, we will need to uninstall the 11.2 client before installing the 19.3 client to eliminate any incompatibility between the two versions. You will first need to uninstall the Oracle Instant Client 11.2 by doing the following:

  1. Open up c:\windows\SysWOW64\odbcad32.exe (ODBC Data Sources (32-bit)) and remove any references to the Oracle Instant Client 11.2 under the System DSN tab
    • Take note of the entries that were removed so they can be re-added later
    • Please Note: not doing this step before uninstalling can cause unintended consequences if you need to re-install the Instant Client. Please contact your TSP or Local Support Personnel if you need assistance
  2. Open up the Add or Remove Programs menu and uninstall the Oracle Instant Client 11.2 (if it exists)
    • Verify "c:\oracle\instantclient_11_2" does not exist
  3. Open the Environment Variables (Control Panel\System and Security\System -> Advanced System Settings -> Environment Variables) and make sure references to the Oracle Instant Client are removed (such as an old "TSN_Admin" entry, or references to it in the "Path" variable)

Once this is done, follow the instructions in the previous section to install the Oracle Instant Client 19.3. For any data sources removed in step 1 for the 32-bit client, you will want to re-add them for the 64-bit client

  • If the user previously had a 32-bit connection, those will need to be removed from the ODBC Datasources (32-bit) System DSN. They cannot be removed from the 64-bit sources and vice-versa.

     

Troubleshooting the Oracle Instant Client

Sometimes users can have some connection problems. If you have trouble connecting, here are troubleshooting steps we can take

  1. Ensure the newest versions of TNSnames.ora and sqlnet.ora are up to date in C:\Oracle\instantclient_19_3_x64\network\admin
    • The most up-to-date versions of these files should have been automatically downloaded if you installed version 19.3 from Software Center
    • If they are old or outdated, your TSP may be able to find more up-to-date ones in S:\downloads\Imaging or S:\downloads\Support\Oracle
    • These two files ensure that the database connections point to the proper U of I servers
  2. Ensure that there is a TNS_Admin object in Environment Variables (Control Panel\System and Security\System -> Advanced System Settings -> Environment Variables)
    • This should have automatically happened during the install if you installed version 19.3 from Software Center after 1/27/2020
    • If this object doesn't exist, add it with the name "TNS_Admin" and the path "C:\Oracle\instantclient_19_3_x64\network\admin"
    • This ensures that the TNSnames.ora and sqlnet.ora are properly loaded
      • If using both the 11.2 and 19.3 client, it doesn't matter where the Environment Variable points to as long as the two .ora files are in the specified directory
  3. Ensure you are using the correct odbcad32.exe for the connection
    1. The 64-bit version is in C:\Windows\system32, and the 32-bit version is in C:\Windows\SysWOW64
    2. If you can't remove an entry in the 64-bit Window then it is a 32-bit connection and will need to be opened from the 32-bit odbcad32.exe
    3. 32-bit and 64-bit entries can have the same name, but still need to be edited in their respective version of odbcad32.exe

If you are still having problems signing in, your account may have fallen out of sync with PROD. Please work with your TSP and/or the ITS Database Administrator to sync this back up.

Installing the Oracle Instant Client 11.2 (Legacy)

Note: This section remains for historical reasons and should not be used unless there is a need to do so

Some staff and faculty may still need to use the older Oracle Instant Client 11.2 for 32-bit data sources, such as if they use Stellent, Captovation Capture, or a 32-bit version of Microsoft Office. This section will explain how the Oracle Instant Client 11.2 client can still be installed. If you require assistance with this process, the App Admin team may be able to provide some supplemental knowledge.

  1. First, you need to install the Microsoft Visual C++ Redistributable 2005 (x86) packages that are applicable for your system
    • If you have already installed Oracle Imaging (Stellent), this package was already installed and you may skip this step
  2. Next, you need to locate the Oracle InstantClient 11.2 at S:\downloads\Support\Oracle-Instant-Client-11.2-Installer.exe
    • Do NOT use 10.1.5, it will not work
  3. Right click the installer, go to properties, go to the Compatibility tab, select Troubleshoot Compatibility for Windows 7, and select "Test the Program"--otherwise, the install will fail
  4. After the install has completed, you must copy sqlnet.ora and tnsnames.ora from S:\downloads\imaging into C:\oracle\instant client\TNSnames
    • The Oracle folder may be in Program Files, even though it is a 32-bit program
    • If you want to check that the IP address for PROD and DSS are correct in tnsnames.ora, edit the file and compare the IP address to the machine oracledb01 in NMS
  5. Search for "ODBC" from the start menu or locate C:\Windows\SysWOW64\odbcad32.exe to open the program
  6. Go to the System DSN Tab, Click Add, and Find the Oracle Instant Client 11.2 Driver from the list
  7. Configure this new entry with the source name (such as "DFA Warehouse"), a description (same as the name), select DSS or PROD from the dropdown menu as the TNS Service Name (again, whichever is appropriate), and then the employee's username and password
    • If they get an incorrect password error, the password may need to be re-synchronized in the PROD database, which can be done by the ITS Database Administrator
    • If you test the connection and don't immediately get a login window, the settings may not be properly configured or the IP address in tnsnames.ora file is incorrect

After following the steps above, it should be possible to make the ODBC connection. If you run into problems, your TSP or the App Admin team may be able to assist you.

Details

Article ID: 1054
Created
Thu 8/23/18 3:19 PM
Modified
Wed 1/29/20 9:46 AM