|How to Setup DG4ODBC on Linux x86 32bit|
Starting with 11g Oracle now provides a Database Gateway for ODBC for 32bit Linux operating systems.
The difference between the older product (Generic Connectivity or sometimes also called HSODBC) is DG4ODBC is now a stand alone product. It is no longer required to have DG4ODBC running on the same machine as the Oracle database.
The gateway is certified
also for older Oracle releases 184.108.40.206, 10.1.0.5, or 10.2.0.3. But
please be aware those pre-11g Oracle databases require a patch to work
properly with V11 Gateways.
The patch can be found on MetaLink by performing a simple search under the 'Patches & Updates' tab at the top of the page. Choose 'Simple Search' and enter 5965763 in the block for the patch number and select the appropriate platform. from the Platform. or Language list.
If there is not a patch available for your database version (i.e. 220.127.116.11, 10.1.0.5, or 10.2.0.3) for the platform. you selected, please log a Service Request with Oracle Support requesting a backport for 'unpublished' Bug <5965763> to your platform.
Oracle Database Gateway for ODBC for 32bit Linux x86 platforms comes on
a separate CD. It can be installed into an existing 11g database
Oracle_Home (please pay attention: if the Oracle_Home contains an
already patched release of the database, you MUST apply this patchset
again. The reason is the gateway installation might overwrite already
patched libraries with the base version as delivered on the CD. To get
a proper environment again, the already applied patchset needs to be
After the installation the following items must be configured:
0) ODBC driver
.ora of the hs subsystem
5) Oracle database
0) The ODBC driver
There are several ODBC vendors for UNIX platforms. Below is a description using the ODBC driver from Data Direct Technologies. They also provide a 15 day trial license for Linux based platforms. More information is available at the following URL:
The libraries of other ODBC vendors will vary; so please make sure to change the libraries to the libraries of your odbc vendor.
As mentioned, Data Direct Technologies provides a trial version. If you download this trial version, please make sure you download also the service pack for the odbc driver -if it exists-.
Another feature of this driver is that it also contains some mechanism to check the ODBC connectivity.
Install the ODBC driver into a separate home directory. Let's call this home directory ODBC_HOME (i.e. /home/odbc/dd) directory.
To install the driver, you can create a new user called odbc who owns the software.
Install the ODBC driver as mentioned in the documentation.
- Configuring the odbc.ini file:
The odbc.ini file is similar to an address book for the odbc driver. It is located by default in the ODBC_HOME directory, but can be placed anywhere you like.
A side note how odbc works: The odbc driver (nothing else than a library) gets a request to connect to a server described in the odbc.ini file. The alias for the description of the server is called:
Data Source Name (=DSN).
Then the driver reads the information from the odbc.ini file according to the specified DSN and connects to the server.
The prerequisite for the odbc driver to connect to the server is the configured odbc.ini.
It is divided into 3 different sections:
[ODBC Data Sources]
[ODBC Data Source] is the section that contains all the available DSNs.
] contains the different names of the DSNs and specifies the connect details.
[ODBC] is the general section for the odbc driver
A very simple file of the odbc.ini file may look like:
[ODBC Data Sources]
mssql=MS SQL Server
Description=DataDirect 5.3 SQL Server Wire Protocol
The section [ODBC Data Sources] contains one datasource called mssql. The configuration behind the data source mssql is found in the section [mssql].
It contains the address (and port) of the database server to contact, the driver library being used and the user id and password of the remote database server.
The [ODBC] section contains general paremeters like tracing (Trace=1 enables tracing, Trace=0 disables it).
After configuring the odbc.ini file, the first step is to check if the ODBC configuration works. Data Direct Technologies provides a demo program to test the connectivity and fetches some data from the remote server.
The directory $ODBC_HOME/demo contains some sql scripts to create a demo table called EMP on the foreign database.
For the Microsoft SQL Server for example the script. is called empsqlsrv.sql. Run this script. on the SQL Server to create the EMP table. (Please make sure that you don't overwrite/delete any tables you need).
Before calling the program demoodbc, you need to set two environment variables:
a) LD_LIBRARY_PATH must contain the odbc library path
The ODBCINI parameter will guarantee the newly configured odbc.ini file from above is used.
Now calling the odbc demo program to query the remote MS SQL Server:
demoodbc -uid sa -pwd sa mssql
should connect to the server and query the EMP table.
Please make sure you can successfully query the table AND don't proceed if this configuration fails!
If you have problems configuring the odbc driver, please contact the vendor of the driver.
Normally the ODBC driver is installed as a different user then the Oracle user. To test the odbc connectivity for the Oracle user the following should be performed:
Set the ODBCINI and ODBC_HOME environment variable and add the $ODBC_HOME/lib directory to the $LD_LIBRARY_PATH. (The details how to do it are described in Part I.)
Now execute as the ORACLE User (who starts the listener) the demoodbc program:
$ODBC_HOME/demo/demoodbc -uid sa -pwd sa mssql
A similar output should be generated:
DataDirect Technologies, Inc. ODBC Sample Application.
will connect to data source 'mssql' as user 'sa/sa'.
First Name Last Name Hire Date Salary Dept
---------- --------- --------- ------ ----
Tyler Bennett 1977-01-06 00:00:00.000 32000.0 D101
George Woltman 1982-07-08 00:00:00.000 53500.0 D101
Rich Holcomb 1983-01-06 00:00:00.000 49500.0 D202
Richard Potter 1986-12-04 00:00:00.000 15900.0 D101
David Motsinger 1985-05-05 00:00:00.000 19250.0 D202
Tim Sampair 1987-02-12 00:00:00.000 27000.0 D101
SQLFetch returns: SQL_NO_DATA_FOUND
SIDE NOTE: Please make sure QuotedId is set to yes; else DG4ODBC might fail with error:
hgopoer, line 159: got native error 170 and sqlstate 42000; message follows...
[DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 's
Exiting hgopoer, rc=0 at 2008/06/19-14:10:17
hgopars, line 347: calling SQLNumResultCols got sqlstate 42000
Exiting hgopars, rc=28500 at 2008/06/19-14:10:17 with error ptr FILE:hgopars.c L
INE:377 FUNCTION:hgopars() ID:Preprocess number of columns
1) The listener needs a new SID entry like:
Please correct the ORACLE_HOME entry and the ENVS entry according to your installation.
We strongly recommend to add the LD_LIBARARY_PATH to the listener.ora file to avoid any conflicts with already existing ODBC driver managers.
The LD_LIBRARY_PATH must contain the fully qualified path to the $ORACLE_HOME/lib and also the library paths of the ODBC driver manager and the ODBC driver itself.
Please do NOT use $ORACLE_HOME variable in the ENVS path.
So a listener.ora file with a listener listening on port 1511 might look like:
(ADDRESS = (PROTOCOL = TCP)(HOST =
) (PORT = 1511))
The listener must be stopped and started after changing the listener.ora file!
2) The tnsnames.ora needs an entry for the DG4ODBC alias:
The domain of the tns alias can differ from the one used above (de.oracle.com), depending on the parameter in the sqlnet.ora:
NAMES.DEFAULT_DOMAIN = de.oracle.com
The important entry is the (HS=OK) key word. (HS=) is also a valid entry, but DBCA and NetCA will only recognize (HS=OK) entries and remove any (HS=) entries.
After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping
It should come back with a success message.
3) init.ora of the gateway:
There are some restrictions on how to name the SID (described in the Net Administrators Guide in detail). At this place only a short note: don't use dots in the SID and keep it short!
The SID is also relevant for the initialisation file of the gateway. The name of the file is init
.ora. In this example it is called initDG4ODBC.ora.
The file is located at $ORACLE_HOME/hs/admin.
It should contain at least the connect details:
# HS init parameters
HS_FDS_CONNECT_INFO = mssql
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /home/odbc/dd/lib/libodbc.so
# ODBC specific environment variables
# Environment variables required for the non-Oracle system
Short explanation of the parameters:
HS_FDS_CONNECT_INFO points to the ODBC DSN configured in step "0" of this note.
HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager library at
For the Data Direct Technologies odbc driver the generic odbc library on Linux is called libodbc.so. This library checks the ODBC DSN configuration and loads the driver to the foreign database server. The name of this library may differ from odbc vendor to vendor. Please check out the driver documentation to figure out the generic odbc library. Also some ODBC driver vendors do not require an ODBC Driver Manager; so the ODBC driver library itself can be specified here. To determine if an ODBC Driver Manager is required, please contact the ODBC driver vendor.
(As not each ODBC Driver vendor documents its ODBC Driver Manager library and the library name might differ from Driver Manager to Driver Manager. A possible way to figure out the Driver Manager library name could be to check for the existence of SQLAllocConnect ODBC function within this library:
|grep -i sqlalloc
The set DBCINI=/home/odbc/dd/odbc.ini points to the location of an odbc.ini file you want to use with this hsodbc configuration.
4) Configuring the Oracle database
The only thing that must be done here is to create a database link:
connect with the username/password that has sufficient rights to create a database link (i.e. system).
The syntax is:
create [public] database link
identified by using ' ';
In other words, to connect to the MS SQL Server configured in the last steps, the syntax must be:
CREATE DATABASE LINK sqlserver
CONNECT TO "sa" IDENTIFIED BY "sa" USING 'DG4ODBC';
The db link name is sqlserver. Username and password must be in double quotes, because the username and password are case sensitive in SQL Server. 'DG4ODBC' points to the alias in the tnsnames.ora file that calls the HS subsystem.
If everything is configured well, a select of a SQL Server table should be successful:
select * from "systables"@sqlserver;
Side note: The systables table name at the MS SQL Server is in small letters. As the MS SQL Server is case sensitive this table name must be surrounded by double quotes. "@sqlserver" points to the name of the database link to the MS SQL Server.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/123747/viewspace-615467/，如需转载，请注明出处，否则将追究法律责任。