5/10/2016

Oracle Linked Server Setup

First things first, you're going to need to know which version of Oracle you're going to be hitting and download the corresponding client.  I'm downloading the 11g 64 bit client from here today:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

Thanks to @Oracle there's a whole rigmarole where you have to register for a login, blah, blah, blah, boo

At this point it's probably a good idea to make sure your Oracle DBAs have assigned you a User /Schema and a password.  You may also want to setup your tnsnames.ora file (or just make a copy of a working one), more on the tnsnames.ora file later.

OK, so now you've sawed off your right arm and provided it to Oracle for the privilege of downloading their client.  Unzip the win64_11gR2_client.zip file and drill down to ..\Oracle11g_Client\win64_11gR2_client\client\ and launch setup.exe.  I'm running SQL Server 2012 on Windows Server 2012 R2 and I get this annoying "Oracle Client Installer" error message because it doesn't seem to recognize current software.

You can safely ignore this error message, by clicking "Yes"

Next you'll be prompted with "What type of installation do you want?"
More installation type details are here: https://docs.oracle.com/cd/B28359_01/install.111/b32003/install_overview.htm

I chose Administrator because it, "also provides tools that enable you to administer Oracle Database."

Pick your language, click next
On the Specify Installation Location, I like to change the Oracle Base directory so that it's not tied to my login.  By default the Base directory will have the login at the end:
...I strip off the login from the end like so:
It is helpful to have a consistent location across multiple servers.
Click Next and Finish to have the installer do its thing.

Once the installer is done, the next thing to do is setup that tnsnames.ora file.  If your Oracle DBA handed you a file simply copy it to C:\app\product\11.2.0\client_1\network\admin.

If you need to make one from scratch, there's some basics here:
http://www.orafaq.com/wiki/Tnsnames.ora

There are plenty of other options besides those basics.  You can dig in here to Load Balancing and the like if you're so inclined:
https://docs.oracle.com/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF262

Now that we have the client installed and our tnsnames file configured, we need to setup our ODBC data source.  Start this process by launching ODBC Data Sources:
Click System DSN and the Add... button:
Select "Oracle in OraClient11g_home1":
You'll next be prompted by the "Oracle ODBC Driver Configuration" Dialog Box:
In this dialog box, the "TNS Service Name" drop down box should display your entries from the tnsnames.ora file.  Next, enter your Oracle User ID and click "Test Connection", at which point you'll be prompted for your password.  Everything should test successfully at this point.

Now would be a good time to restart.  Unfortunately, yes you need to restart...

You can do an additional test via sqlplus.  Open a windows command prompt and enter the following:

sqlplus user/pass@[addressname]
(Where addressname is one of your connections from tnsnames.ora)

Next we need to setup our linked server. Pop open SQL Server Management Studio (SSMS) and drill down to "Server Objects" > "Linked Servers", right-click and choose "New Linked Server"

Change the Provider to "Oracle Provider for OLE DB".  Enter a product name of Oracle.  For the "Data Source" enter your [addressname] from the tnsnames.ora file.

Next is setting up the security.  If you have a User ID and Password from your Oracle DBA, click Security and change the bottom radio button to "Be made using this security context", entering your credentials:
The last page of the "New Linked Server" Dialog Box is Server Options.  I typically leave the defaults, with one exception: I do like to change the "Lazy Schema Validation" from False to True:
"The lazy schema validation option is set to true for performance reasons.  It allows the query processor to skip schema checking of remote tables if the query can be satisfied on a single member server."

One final test would be to attempt an OPENQUERY:

Select * from openquery
(NAME, 'SELECT * FROM [WHEREVER]') ALIAS

No comments:

Post a Comment