Connecting Oracle to a Microsoft Data Source (SQL Server)

There are several steps to correctly configure an implementation of an Oracle database reading from a SQL Server database. The setup at my workplace is an ORACLE 11g (11.1.0.7) database connecting to SQL Server 2000. Although i haven’t tested for 2005 or 2008, i believe the general steps to be the same or similar.

Oracle databases use Heterogeneous services (HS) to access remote non-oracle sources as shown below.

Description of Figure 9-1 follows

The Heterogeneous Services component in the database communicates with the Heterogeneous Services agent process. The agent process, in turn, communicates with the remote database.

The agent process consists of agent-generic code and a system-specific driver. All agents contain the same agent-generic code. But each agent has a different driver depending on the type of data being sourced.

How to Configure:

1. On the SQL Server machine, install the oracle client.

2. On the SQL Server machine, Create a System DSN from within the ODBC Data Source Administrator. Set it up so that it connects to the database required. The name of the DSN is important as it is used to configure the HS. eg: a data source name of MY_DB1 which connects to DB1 using a sql authenticated login. Test the connection.

3. To configure the agent, you must set the initialization parameters in the heterogeneous services initialization file. Each agent has its own heterogeneous services initialization file. The name of the Heterogeneous Services initialization file is initSID.ora, where SID is the Oracle system identifier used for the agent. This file is located in the ORACLE_HOME/hs/admin directory.

Create the initMY_DB1.ora file in the ORACLE_HOME/hs/admin directory as follows:

##################################################################################

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#

HS_FDS_CONNECT_INFO = PUB_CARS
HS_FDS_TRACE_LEVEL = 0

##################################################################################

4. Modify the listener.ora file

Set up the listener on the agent to listen for incoming requests from the Oracle Database. When a request is received, the agent spawns a Heterogeneous Services agent. To set up the listener, modify the entries in the listener.ora file located in the DATABASE_ORACLE_HOME/network/admin directory as follows:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\Oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME=MY_DB1)
      (ORACLE_HOME=C:\Oracle\ora92)
      (PROGRAM=hsodbc)
    )
   )

  1. For the SID_NAME parameter, use the SID that you specified when creating the initialization parameter file for the Heterogeneous Services, which, in this case, is MY_DB1.

  2. Ensure that the ORACLE_HOME parameter value is the path to your Oracle Database home directory.

  3. The value associated with the PROGRAM keyword defines the name of the agent executable.

Remember to restart the listener after making these modifications.

5. Amend the tnsnames.ora file on the oracle server to represent the connection to the SQL Server database. Following the example above:

MY_DB1_SQL_SERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = MY_DB1)
    )
    (HS=)
  )

6. Create the database link on the oracle database

  CREATE DATABASE LINK MY_DB1_SQL_SERVER
  CONNECT TO <login>
  IDENTIFIED BY <pass)
  USING 'MY_DB1_TNS_ENTRY';

Test the link and it should return:

*********************************************************************
Link Name   : "MY_DB1"
Connection  : Successful
*********************************************************************

Troubleshooting

Error

ORA-28546: connection initialization failed, probable Net8 admin error
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(PORT=1521)))(CONNECT_DATA=(SID=oracledb)))

ORA-02063: preceeding 2 lines from OWB###

Probable Cause

Providing the same SID name as that of your database.

Action

Provide an SID name different from the SID name of your database.

Error

ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[Generic Connectivity Using ODBC][H006] The init parameter <HS_FDS_CONNECT_INFO> 
is not set. Please set it in init<orasid>.ora file.

Probable Cause

Name mismatch between SID name provided in the listener.ora file and the name of the initSID.ora file in ORACLE_HOME/hs/admin.

Action

Ensure that the name of the initSID.ora file and the value provided for the SID_NAME parameter in listener.ora file is the same.

Advertisements

2 Responses to Connecting Oracle to a Microsoft Data Source (SQL Server)

  1. Aryana Deane says:

    Very good blog.Really thank you! Will read on…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: