Home > .NET, Oracle, SQL Server > Oracle RAC Linked server in Sql 2014 64 bit

Oracle RAC Linked server in Sql 2014 64 bit

2014/06/25

I was needing to create a linked server from SQL Server 2014 64 bit, on a pc with Windows 8.1 64 bit , to an Oracle 10.2.0.3 configured in RAC (Real Application Clusters).

The RAC configuration is composed from 3 servers .

The first step is to install the Oracle software from http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Here must be downloaded 64-bit ODAC 12c Release 2 (12.1.0.1.2) for Windows x64 , the file ODAC121012_x64.zip because there is an guided installer.

After a while it starts then use the default choice, that is the Oracle Data Access Components which installs the Data Provider for .NET, Oracle Provider for OLEDB, Oracle Services for Microsoft Transaction Server, Oracle instant client.

Then use the Next button accepting all defaults until the end.

At the end there is a wizard for the compilation of tnsnames.ora file, but in this case we are working with a RAC and the wizard cannot manage this configuration.

After the installation is recommended to reboot, then we should see in Sql Management Studio the OraOLEDB provider listed:


By default in C:\app\studio\product\12.1.0\client_1\Network\Admin there is our TNSNAMES.ORA file

We must know the NETBIOS or the IP of the servers in RAC and the name of the Oracle instance, in our case TRAFSRV for example.

So the TNSNAMES.ORA , indicating the IP for each or our 3 RAC server, should be :

TRAFSRV =
  (DESCRIPTION =
   (LOAD_BALANCE = ON)
   (FAILOVER = ON)
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.13.19)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.13.21)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.13.23)(PORT = 1521))
   )
   (CONNECT_DATA =
    (SERVICE_NAME = trafsrv.world)
    (FAILOVER_MODE =
     (TYPE = SESSION)
     (METHOD = BASIC)
     (RETRIES = 5)
     (DELAY = 3)
    )
   )
  )

It should be verified that the 1521 port is opened in the firewall, in both Sql and Oracle servers.

Now we can create the linked server , for example

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ORAARTA33', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'TRAFSRV'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORAARTA33',@useself=N'False',@locallogin=NULL,@rmtuser=N'ORACLEUSER',@rmtpassword='ORACLEUSERPWD'

In this sample ORAARTA33 is the name of the linked server, it there are no problems we should see


Advertisements
Categories: .NET, Oracle, SQL Server
%d bloggers like this: