Archive

Archive for the ‘Oracle’ Category

Oracle development notes

After many years (Oracle 8..) i’m back to develop on Oracle (using Microsoft Windows). So, after some problem, here a little quick guide for starting with the current versions (April 2015). Before of all, must be installed the java JDK, the 64 bit package jdk-8u40-windows-x64.exe or 32 (jdk-8u40-windows-i586.exe) depending from your system. Then must be installed , as already written in this post, the ODAC component: in this page for the 64 bit version (is recommended the version with installer, not the Xcopy version) ; the 32 bit version here. The main product for the developer is Oracle SQL Developer from here. The Oracle SQL Developer is installed by default in c:\app, the folder c:\app\client\<your_windows_user_name>\product\12.1.0\client_1\Network\Admin is the default place where to place the tnsnames.ora file, needed for the Oracle connections. We have 2 servers, one for development and the other is the official database:

VJSVIL =
  (DESCRIPTION =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 10.86.13.xxx )
          (Port = 1521)
        )
    (CONNECT_DATA = (SID = VJSVIL)
    )
  )

VJ4SAP =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = srv-vrj)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = XE)
     )
   )

Note that the development entry is using the IP , instead for the official database is used the hostname. If must be used the hostname for some reason (as in this case) could be useful to map the hostname to the ip in C:\Windows\System32\drivers\etc\hosts, in our case:

# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

# localhost name resolution is handled within DNS itself.
#        127.0.0.1       localhost
#        ::1             localhost
10.86.13.xxx        srv-vrj

In order to work, it must be verified that the 1521 port is opened in the firewalls. Launching SQLDeveloper there are 2 problems that you can encounter. The first is the language , for example the software is working in French or Italian and in the preferences menu there is nothing for the language. The language setting in English is done by adding the line AddVMOption -Duser.language=en In the file <path where you unzipped SQL developer>\sqldeveloper\bin\sqldeveloper.conf; another strange problem could be the error ORA-01882: timezone region not found.
This is resolved by adding in sqldeveloper.conf AddVMOption -Duser.timezone=CET.
In SQL Developer we can add a connection selecting the TNS item: 02-04-2015 15-51-19 And we can expand and see the list of tables, views, etc. below the connection name node. 02-04-2015 16-15-14

Advertisements
Categories: .NET, Oracle, Vs2013

Oracle RAC Linked server in Sql 2014 64 bit

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


Categories: .NET, Oracle, SQL Server