Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Heterogeneous Database connections - Oracle to SQL Server

Oracle Database Tips by Donald Burleson

QUESTION:  What are some methods for connecting from Oracle to other databases such as SQL Server?

ANSWER: By Boobal Ganesan

 

By John Palinski


Using Oracle Heterogeneous Services in an Operational Data Store or Data Warehouse

By John Adolph Palinski

I recently received a user request to bring information into the data warehouse for use in combination with other data warehouse tables. On the surface this is a routine task. After all, I often bring transaction processing data into the warehouse using materialized views and other tools such as the Merge statement. However, the database for this request was a non-Oracle database. This created a small problem. I could no longer use the normal Oracle tools to bring this data into the warehouse. I would need a different device.

In the past, whenever I needed to bring non-Oracle data into the warehouse, I required the production data to be placed into a flat file and I loaded the file into Oracle using SQL*Loader. Creating the source system extraction process, the SQL*Loader script, and the batch processes that move and execute the files required quite a bit of work. In the past, we used this technique for single files. However, the current request was to move an entire production system schema into the warehouse for transformation and loading. This would require SQL*Loader and batch files for three hundred and seventy tables. I really did not want to do this much work so I began to look for an easier method.

Investigating various sources I identified Oracle's Hetergeneous Services. This Oracle feature allows you to set up an ODBC connection inside the Oracle database. This was the answer to my problem. With a little configuration, Oracle can read the non-Oracle database files directly. No batch programs, flat files, or SQL*Loader files. Before setting this up I researched the topic in a variety of sources. Some of them were incorrect, some were not. However, none of them discussed setting up hetergenous services using a remote database. All of the articles discussed setting using Oracle on a local Windows server that had an Oracle installation and the ODBC drivers installed on the server. However, our data warehouse was a Unix server and the ODBC drivers could not be installed on this server. I really needed instructions that discussed how to connect from a Unix platform using Oracle to ODBC drivers located on a Windows server. This article describes the steps needed to set up heterogeneous services on a Unix data warehouse server that reads a Sybase database on another server. These same steps can be applied to any non-Oracle database that has ODBC connectivity.

Infrastructure

The infrastructure is illustrated in Figure 1. The following bullets describe the structure:

- The target system is a Sybase database residing on Windows XP server X.
- Server X has Sybase ODBC drivers installed.
- Server X has an Oracle server or code tree installed. Server X does not have the Oracle database installed.
- The data warehouse resides in an Oracle database on Unix server Z.
- The users will access the Sybase database through the Oracle installation on server Z.



Problem

The data warehouse residing on the Oracle installation on server Z needs to communicate and query the Sybase database on server X.

Setup Tasks

Below is a series of steps that are needed to set up the hetergeneous services. The actual code will be discussed following these steps.

1. Install ODBC drivers on the server (server X) that is to actually access the Sybase database.
2. Setup the ODBC connection on server X using the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.
4. Ensure the Global_names parameter is set to False.
5. Install an Oracle server (or code tree) or an Oracle database on the same server (server X) as the ODBC drivers.
6. Configure the Hetergeneous services. This consists of creating an initodbc.ora file within the Oracle installation on server X.
7. Modify the Listener.ora file on server X used to connect to the Oracle instance and ODBC drivers.
8. Modify the Tnsnames.ora file on server Z . This is the Oracle database installation accessed by the users to connect to the ODBC drivers
9. Reload the listener on server X
10. Create a database link on the Oracle installation (server Z) that accesses the heterogeneous connection.
11. Run a Select statement for the Oracle installation (server Z) using the database link.


Step 1

The first step in setting up hetergeneous services is to install the ODBC drivers on the Windows server (server X) that is to directly access the non-Oracle database. In the case of Sybase, this is Adaptive Server Anywhere 9.0. You can identify the current installed drivers on the ODBC Data Source Administrator. Launch this dialog box using this path: Start/Settings/Control Panel/Administrative Tools/Data Sources. Figure 2 depicts the dialog box. The System DSN tab page is shown. This tab page shows the System data sources defined and the associated drivers. You will need to define a DSN for the non-Oracle Sybase database.

Steps 2 and 3 - Seting up and testing the ODBC

The first task in setting up the ODBC driver is to configure the ODBC. This requires you to set up a System DSN (or data source name). Perform the following:

- Launch the Create New Data Source dialog. On the System DSN tab page (Figure 2), press the Add button. This opens the Create New Data Source dialog box (Figure 3).

- Select the appropriate driver and press the Finish button. This launches the ODBC Configuration Dialog box displayed in Figure 3.

The ODBC Configuration dialog box (Figure 4) is a five tab dialog box and settings will be needed on the first four tabs. The tabs and modifications are:

ODBC - Enter a name for the remote data source (mandatory). This can be a custom name. For our example the name will be Cas30c. You may also enter a description for the remote data source (optional). This can be the actual network sever name or a TCP/IP alias
Login - Enter a remote data source user id and password or use the integrated login
Database - Enter the remote server name. This is the System DSN created on the remote database. Enter the actual remote database name.
Network - Select the appropriate network protocols. For example, check TCP/IP and IP=Network_server_name. Network_server_name is the actual network server name.

- Return to the ODBC tab. Press the Test Connection button. If it returns a success message you are ready to begin setting up the Heterogeneous services in Oracle.

Step 4 - Ensure the Global_names parameter is set to false

Execute the following query to determine the parameter setting:

Select *
from v$parameter
where name like 'global_names%'

If the parameter is set to True, you must modify the Init.ora file and restart the database.

Step 5 - Install an Oracle server on server X

In this step you must install an Oracle server on the same server as the ODBC drivers. This installation can be a database or simply the code tree. If the users will not access Oracle directly from this server the code tree is perfectly acceptable.


Step 6 - Setup the Heterogeneous Services file on server x

You will need to document the parameters that Oracle needs to locate and execute the ODBC drivers. Follow these instructions:

- Locate the Oracle_home/hs/admin/initsodbc.ora file.
- Save a copy of this file renaming it per the following template:

initDSNNAME.ora (i.e. initcas30c.ora)
DSNNAME is the DSN value set up on server X containing the ODBC (Figure 4).
- Open the init.ora file.

Locate the line "HS_FDS_TRACE_LEVEL = ". Change to OFF
Locate the line "HS_FDS_CONNECT_INFO = ". Change to the data source name.
# 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 = Cas30C
HS_FDS_TRACE_LEVEL = off

#
# Environment variables required for the non-Oracle system
#
#set =

Step 7 - Configuring the Listener

The Listener on server X accessing the Oracle database must be configured to point to the Heterogeneous Services entry created in step 6. Create an entry in the SID _LIST similar to the one below. The bolded part is the actual entry. Note the following:

SID_NAME is the DSN for the remote database.
ORACLE_HOME is the actual Oracle home file path.
PROGRAM tells Oracle to use heterogeneous services. This particular entry tell Oracle to look for the initcas30c.ora file defined in step 5.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=Cas30C) -- Enter the DSN on this line
(ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
(PROGRAM = hsodbc)
)
)

Step 8 - Configuring Tnsnames.ora

Oracle needs to know where to look for the remote database when it is called. This requires an entry in the Tnsnames.ora file in every Oracle instance that will be calling the remote database. In the case of our article, the Tnsnames.ora file entry must be on server Z. The Oracle installation on server X does not require this modification since this Oracle installation will not be directly used by a user. The server X Oracle installation is simply the path from Oracle's listener to the ODBC drivers. The following example can be followed:

Cas30c.world = -- This name can be customized
(DESCRIPTION=
(ADDRESS_LIST=
(Address=(PROTOCOL=TCP)
(HOST=
-- (Server x)
(PORT=1521))) -- Enter the port on which the server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to use hetergeneous
-- services
)

Step 9 - Reloading the listener

At this point the infrastructure is in place. You will need to reload the Listener.ora settings you defined in Step 7. They must be in place on server X. There are several methods. Execute one of the following from the command line:

Lsnrctl reload

or

Lsnrctl stop
Lsnrctl start

After completing this step, you should check whether the listener and Tnsnames.ora file are configured correctly. On the server accessing the data (server Z) issue a Tnsping command from the command prompt. If Oracle can contact the server X Oracle installation, the configuration is correct and a response is seen. The following exemplifies the command.

Tnsping cas30c

Step 10 - Create a database link

The last step in the procedure is to create a database link on each installation that will view the non-Oracle data (Server Z). In order to acccess the non-Oracle database, each Oracle ID must know how to contact the database. The database link is placed after the non-Oracle data source reference. The database link contains the Tnsnames reference (Cas30c) along with the remote data user id and password. When the SQL statement containing the link is executed, Oracle reviews the Tnsnames.ora file to locate the server and listener port for the ODBC database. A message is then sent to that server. The listener identifies the signal, locates the hetergeneous services entry, and uses the ODBC drivers to perform the work. The results are sent back through this channel.

There are a variety of database link options. This example is one that contains the remote database userid and password.

Note:  it's important that the username and password must be in double quotes.

create database link ODBC connect to "sa" identified by "pencil" using 'hsodbc'.

create [public] database link link_name connect to "user_name" identified by "password" using 'cas30c';

You have now completed the configuration. You can test the configuration by executing a Select statement against the non-Oracle database.

Select * from table_name@link_name;

If you need to have data outside the Oracle database, you will find this a great tool for making it transparent to the Oracle user.

Common errors in heterogeneous services:

A common error when connecting to SQL Server from Oracle through a  database link is the ORA-28500, suggesting that the Oracle database login is incorrect.

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Transparent gateway for ODBC]DRV_InitTdp: [DATADIRECT][ODBC SQL Server Driver][SQL Server] Login failed (SQL State: 28000; SQL Code: 4002)

ORA-02063: preceding 3 lines from HSTEST

Cause: The Oracle database link created for the foreign datasource has either no credentials or incorrect credentials.


Action: Recreate the Oracle database link with the proper username and password.

 When  resolving the ORA-28500 error it's important to note that the username and password must be in double quotes:

create [public] database link
   link_name
connect to
   "user_name"
identified by
   "password"
using
   'cas30c';

For example, this would create a viable ODBC database link between Oracle and SQL server, being careful to encase the user name and password in double quotes.  This is perfect for migrating to Oracle:

create database link ODBC connect to "sa" identified by "pencil" using 'hsodbc'.

If you encase the login and password in double quotes and the tnsnames reference in single quotes, then it works perfectly.   (Special thanks to Steve Wales for this tip!)

 

By Boobal Ganesan

Oracle Database Gateway Configuration for SQL Server

 

These parameters help us to fine-tune the performance and memory utilization of the heterogeneous services component and the gateway. They also inform the gateway and the heterogeneous services about the configuration of the non-Oracle database system. For e.g., The language in which the non-Oracle system runs and their properties.

 

These initialization parameters can be set in the gateway initialization files and can be tested for a session by querying the V$HS_PARAMETER system view.

Capabilities

Capabilities tell the users about the limitations of the non-Oracle database system like their datatype and SQL equivalents. These are non-configurable and cannot be changed by the users.

 

The tasks below are to be performed to configure the database gateway for the SQL Server 2014.

The Gateway Process Flow

The below steps explain the sequence of events when we query the non-Oracle database through the gateway.

 

1)       The client application sends a query to the Oracle database using the Oracle Net services.

 

2)       The heterogeneous services translate the SQL dialect from the Oracle database to the SQL statement that is understood by the non-Oracle database.

 

3)       Now, the translated SQL statement is sent to the gateway from Oracle Net services.

 

4)       During the first transaction, an authenticated session is created in the non-Oracle database system.

 

5)       The gateway retrieves the data from non-Oracle database using the translated SQL statement.

 

6)       The gateway converts the data retrieved from the non-Oracle database to the format that is compatible with the Oracle database. Mostly, datatype conversion takes place.

 

7)       The gateway returns the data to the Oracle database using the Oracle Net services.

 

8)       Finally, the Oracle database passes the query results to the client application using the Oracle Net services. The DB link remains open until the DB session is closed or the DB link is explicitly closed.

Gateway Initialization Parameter Configuration

The gateway system identifier (SID) is an alphanumeric string which is used for identifying a gateway instance. We need one gateway SID for every SQL Server database that we are planning to access.

 

We can define our gateway SID, which in-turn has to be the initialization parameter file name in the location,

 

$ORACLE_HOME\HS\ADMIN\initSQLEXPRESS.ora

 

Where init in the filename represents it as the initialization file and the remaining string SQLEXPRESS represents the gateway SID.

 

This ORA file needs the below parameters to be configured based on the userís interest in the SQL Server database characteristics.

 

HS_FDS_CONNECT_INFO = SQLEXPRESS

HS_FDS_TRACE_LEVEL = TRUE

HS_RPC_FETCH_REBLOCKING= OFF

HS_FDS_FETCH_ROWS = 1

HS_TRANSACTION_MODEL=SINGLE_SITE

HS_NLS_NCHAR=UCS2

HS_LANGUAGE=AMERICAN_AMERICA.UTF8

 

         HS_FDS_CONNECT_INFO parameter describes the connection to the SQL Server database. The system DSN name created in Windows for the SQL Server database is set to this parameter.

 

The system DSN can be created by following the below steps on a Windows machine.

 

1)       Go to Control Panel -> System and Security -> Administrative Tools -> Double click on the ODBC Data Sources (Either 32 or 64 based on the SQL Server bit version).

2)       Go to the System DSN tab of the ODBC Data Source Administrator pop-up.

3)       Click on the Add option to select the SQL Server driver and click Finish.

4)       The Create a New Data Source wizard opens up requesting for the DSN name, description, and the SQL serverís hostname respectively. Click Next.

5)       The next tab provides the authentication method. Choose the preferred one and click Next and select the default database for the DSN. Click Next to move on to the final tab.

6)       The next tab provides some checkboxes for language change, logging, translation of character data, and etc. Click Finish to create a system DSN.

 

         HS_FDS_TRACE_LEVEL parameter is set to true or false to enable or disable the error tracing respectively.

         HS_RPC_FETCH_REBLOCKING parameter controls whether the heterogeneous services should optimize the data transfer between the Oracle database and the HS agent connected to the SQL Server database.

Ť Setting this parameter to ON enables re-blocking, meaning that the data fetched from the SQL Server buffer are not sent to Oracle until the fetched amount of data is equal or higher than the value set for the HS_RPC_FETCH_SIZE parameter.

Ť Setting this parameter to OFF, disabling the re-blocking, thus the data is sent immediately from the agent to the server.

 

         HS_FDS_FETCH_ROWS parameter specifies the fetch array size. This is the number of rows that has to be fetched from the SQL Server database to the Oracle database at a one-time instance.

 

         HS_TRANSACTION_MODEL parameter specifies the type of transmission model to be used when the SQL Server database is updated by a transaction.

Ť The value READ_ONLY allows the Oracle database only to read the SQL Server database.

Ť The value SINGLE_SITE allows the Oracle database to both reads and write the SQL Server database.

 

         HS_NLS_NCHAR parameter specifies the character set that the gateway agent must use to retrieve the graphic data.

 

         HS_LANGUAGE parameter specifies the character set that the gateway agent must use to access the SQL Server data source. The format is territory[.]character_set.

Oracle Net Listener Configuration

The Oracle net listener listens to the incoming requests for an Oracle instance. For the Oracle gateway to listen to an instance, we must register an entry to the listener.ora file in the below location,

 

$ORACLE_HOME\NETWORK\ADMIN\listener.ora

 

The prototype of the listener.ora file entry is shown below,

 

LISTENER= 

       (ADDRESS=

          (PROTOCOL=TCP)

          (HOST=host_name)

          (PORT=port_number)

        )

 

Where, the ADDRESS subparameter in the LISTENER parameter contains the hostname and the port number of the SQL Server database on which the Oracle Net listener listens.

 

In our case, the below entry is added to the existing LISTENER parameter,

 

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1433)

 

To direct the listener to start the gateway in response to the incoming requests, an entry has to be made to the SID_LIST_LISTENER parameter in the listener.ora file.

 

The prototype of the SID_LIST_LISTENER parameter entry is shown below,

 

SID_LIST_LISTENER=

   (SID_LIST=

      (SID_DESC=

         (SID_NAME=<Gateway_sid>)

         (ORACLE_HOME=<Oracle_home>)

         (PROGRAM=dg4odbc)

      )

   )

 

Where dg4odbc is the value for the PROGRAM parameter, which is the executable name of the Oracle database gateway for ODBC (Open Database Connectivity).

 

In our case, the below entry is added to the existing SID_LIST_LISTENER parameter,

 

(SID_DESC=

      (SID_NAME = SQLEXPRESS)

      (ORACLE_HOME=C:\app\BoobalGanesan\product\12.1.0\dbhome_1)

      (PROGRAM = dg4odbc)

    )

 

After these entries are added to the listener.ora, the listener has to be reloaded to initiate the new settings to get into effect. Use the lsnrctl stop and lsnrctl start commands to reload the listener using the command prompt.

Oracle Database Gateway Access Configuration

The Oracle database configuration has to be performed before communicating with the gateway over Oracle Net. To configure, we must add the connection descriptors of the SQL Server database to the tnsnames.ora in the below default location,

 

$ORACLE_HOME\NETWORK\ADMIN\tnsnames.ora

 

The prototype of the connection descriptor for the SQL Server database for the gateway access is shown below,

 

<Connect_descriptor>=

   (DESCRIPTION=

      (ADDRESS=

         (PROTOCOL=TCP)

         (HOST=host_name)

         (PORT=port_number)

      )

      (CONNECT_DATA=

         (SID=gateway_sid))

      (HS=OK))

 

In our case, the below entry is added to the existing tnsnames.ora file,

 

SQLEXPRESS =

  (DESCRIPTION=

    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1433))

      (CONNECT_DATA=(SID=SQLEXPRESS))

      (HS=OK)

    )

Creation of Database Links in Oracle to MS-SQL

The connection between the Oracle database and the gateway is established through a database link, which stays until the Oracle session is explicitly terminated or the DB link is closed.

 

The prototype of the database link in Oracle database is shown below,

 

CREATE PUBLIC DATABASE LINK <DB_link_name> CONNECT TO "user" IDENTIFIED BY "password" USING '<Connect_descriptor>';

 

In our case, the below script is used for creating the database link which is used for establishing the connection.

 

CREATE PUBLIC DATABASE LINK sqlexpress USING 'SQLEXPRESS';

 

% Note: There is no authentication provided in the above DB link creation as we have selected windows authentication in the first place.

After the DB link has been created, we can test the data fetch from the Oracle database from the SQL Server database by querying through any of the tables in the master database, as that is the DB which we have configured during the system DSN creation.

 

% Note: We need to make sure the necessary privileges are placed upon the table that is being queried.

When the CUSTOMER table, which was already created in the SQL Server database is queried, it returns the complete data set as shown below,

 

SELECT * FROM customer@sqlexpress;

Script Output:

CUST_ID

CUST_NAME

CUST_DOB

CUST_ADD

CUST_EMAIL

CUST_PHONE

1001

Steven Markle

1975-03-08

10 Av - NY

steven@markle.com

6501241434

1002

James Marlow

1985-02-16

12 Av - NY

james@marlow.com

6501247234

1003

Jason Mallin

1984-06-14

15 Av - NY

jason@malin.com

6501241968

1004

Stephen Stiles

1979-10-26

17 Av - NY

stephen@stiles.com

6501242030

1005

John Russell

1968-10-01

21 Av - NY

john@russell.com

6501241322

1006

Peter Tucker

1973-01-30

25 Av - NY

peter@tucker.com

6501241011

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational