Setting up an Oracle ODBC Driver and Data Source in 64 bit OS

Setting up an Oracle ODBC Driver and Data Source
Setting up an Oracle ODBC Driver and Data Source requires more steps than the Microsoft SQL Server database setup. This article explains the steps including tnsnames.ora configuration.
Connecting to an Oracle database via and ODBC driver requires the following steps:
  1. Install the ODAC (Oracle Data Access Components) or Oracle Client on the Workstation or Application Server.
  2. Setup the SQLNET and TNSNAMES files in the Client or ODAC home’s Admin directory.
  3. Use the ODBC Data Source configuration tools to create the ODBC Data Source.
The following blog shows the process involved for each of these steps to get you a working connection to an Oracle database.
Prerequisites: Java JRE or SDK 6 Update 27 or later. These are required by the Oracle Universal Installer to run the ODAC installation program.
Step 1 – Install the ODAC or Oracle Client
In this example we will be installing the ODAC client to complete an ODBC setup on a Windows 7 (64-bit) operating system.  You can install the full administrative option of the Oracle Client to obtain the ODBC driver.
Download the ODAC112030_x64.zip file from Oracle’s web site:
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
Unzip the file to a location of your choosing.
Locate the Setup.exe file under the root directory of the extracted zip file’s directories.

Double-click to run the setup program.
In the ‘Oracle Universal Installer’, click the Next button.

In the ‘Select a Product to Install’ dialog, go with the default ‘Oracle Data Access Components for Oracle Client 11.2.0.3.0’ option.
In the ‘Install Location’ dialog, enter a path to install the ODAC home directory to in the ‘Oracle Base:’ field.
In the ‘Available Product Components’ dialog, go with the default options.
In the ‘Summary’ dialog, verify that the components you have selected for install include the ‘Oracle ODBC Driver for Instant Client 11.2.0.3.0’.
Click ‘Install’.
When the installation is complete, click ‘Exit’.
This concludes the steps for installing the ODBC drivers.
Step 2 – Setup the SQLNET and TNSNAMES files
Locate the SQLNET.ora and TNSNAMES.ora in the ‘samples’ directory of the newly installed Oracle client home directory. This is typically:
C:\app\johndoe\product\11.2.0\client_1\Network\Admin\Sample
Where ‘johndoe’ is the windows user name.
Copy these two files into the ‘Admin’ directory above.
Open the ‘TNSNAMES.ora’ file in Notepad and enter your Oracle database details in the file.
Save your changes.
Note: the ‘SQLNET.ora’ generally doesn’t need to be changed.
Step 3 – Create the ODBC Data Source
In the Windows Start menu, click on the Data Sources (ODBC) icon that was created by the ODAC client install.
In the ‘User DSN’ tab of the ‘ODBC Data Source Administrator’ dialog, click Add.
In the ‘Create New Data Source’ dialog, select the ‘Oracle in OraClient 11g_home1’ driver.
Click ‘Finish’.
In the ‘Oracle ODBC Driver Configuration’ dialog, enter the details of your data source.
Note: you should be able to select the TNS Service Name from the drop down list. This name will be that which you named in the TNSNAMES.ora file’s Alias = section.
Click ‘Test Connection’ to ensure your settings are all correct.
When prompted, enter the database login credentials.
Click ‘OK’.
 
Click OK to complete the process.

 

Comments

  1. UnknownDecember 11, 2017 at 9:31 AM

    Hello sir,
    Can you include the Database upgrades would be great.
    thanks

    ReplyDelete
  2. maheshDecember 11, 2017 at 9:41 AM

    Hi Swathy, Will try my best to include them.

    Thank you.

    ReplyDelete

Post a Comment

Popular posts from this blog

Data Migration Testing Startegy

Image
1.   Purpose of the Article The purpose of this Aricle is to describe the different test stages that will be performed; how the testing will be prepared and executed for each stage, and the criteria that need to be met before transition from one stage to the next can occu r. 2.   Phases involved in Data Migration Testing Below are the phases involved in testing data migration. ·         Fusion ·         Release 1.       Design 2.       Implementation a)       Ear b)       Iterations 3.       SIT (System Integration Testing) 4.       UAT (User Acceptance Testing) 2.1.         Fusion (Discover and Define) In this Phase the requirements for the Data Migration project is gathered and identified from the Testing perspective. QA is responsible for defining the scope of testing; high level estimation and Requirement Specification docs. This phase we will perform pre-migration testing, these tests occur
Read more

Informatica DVO installation steps

1      About This Article The purpose of this article is to explain the steps involved in installing and configuring Data Validation Option for usage with Informatica PowerCenter installed on a local machine. Informatica PowerCenter Server can be installed on a remote machine other than the local machine. But installation of Data Validation Option should be done on the same machine as the Informatica PowerCenter Client. 2      Functional Overview Informatica Data Validation Option enables you to test and validate data. It requires the installation of, and does much of its work through, Informatica PowerCenter. Data Validation Option reads table definitions from PowerCenter metadata repositories, and checks the data at either end of the process. It does not check the correctness of transformations or mappings. Data Validation Option identifies problems or inconsistencies. It does not attempt to identify the source of the problem in the ETL process. 3     
Read more

ETL Testing

Image
Data ware house is relational database which is subject oriented, integrated, time-variant and nonvolatile collection of data used to support strategic decision making process Subject Oriented Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a warehouse that concentrates on sales. Using this warehouse, you can answer questions like "Who was our best customer for this item last year?" This ability to define a data warehouse by subject matter, sales in this case, makes the data warehouse subject oriented. Integrated Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated. Nonvolatile Nonvolatile means that, once entered into the warehouse, data should not cha
Read more