How to Setup DG4MSQL (Oracle Database Gateway for MS SQL Server) Release 11 on Linux [轉帖]

In this Document
Goal
Solution
How to Setup DG4MSQL (Oracle Database Gateway for MS SQL Server) on LINUX
--------------------------------------------------------------------------------

Applies to:
Oracle Transparent Gateway for Microsoft SQL Server - Version: 11.0.1.0.1
Linux x86[@more@]Goal
Starting with 11g Oracle now provides a Database Gateway for MS SQL Server on Linux.

The setup in 11g slightly differs from previous releases and this note will explain the steps how to configure DG4MSQL on Linux.

Solution
How to Setup DG4MSQL (Oracle Database Gateway for MS SQL Server) on LINUX
The Oracle Database Gateway for MS SQL Server for Linux platforms comes on a separate CD. It can be installed into an existing 11g database Oracle_Home (please pay attention that if the Oracle_Home contains an already patched release of the database; then you MUST apply this patchset again. The reason for this is that the gateway installation might overwrite already patched libraries with the base version as delivered on the CD. To get a proper environment again an already applied patchset needs to be reapplied).

After the installation the following items must be configured:
1) listener
2) tnsnames
3) init.ora of the hs subsystem
4) environment
5) Oracle database
1) The listener needs a new SID entry like the following:
(SID_NAME=dg4msql)
(ORACLE_HOME=/home/oracle/oracle/product/11.1) (ENVS=LD_LIBRARY_PATH=/home/oracle/product/11.1/dg4msql/driver/lib;/home/oracle/product/11.1/lib)
(PROGRAM=dg4msql)

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 full qualified path to the $ORACLE_HOME/lib and $ORACLE_HOME/dg4msql/driver/lib directory. 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:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=dg4msql)
(ORACLE_HOME=/home/oracle/oracle/product/11.1)
(ENVS=LD_LIBRARY_PATH=/home/oracle/product/11.1/dg4msql/driver/lib;/home/oracle/product/11.1/lib)
(PROGRAM=dg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(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 DG4MSQL alias:
dg4msql.de.oracle.com =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1511))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)

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
But 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 .
tnsping dg4msql
should come back with a successfull message.
3) init.ora of the gateway:
There are some restrictions 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 initialiastion file of the gateway. The name of the
file is init.ora. In this example it is called initdg4msql.ora.
The file is located at $ORACLE_HOME/dg4msql/admin.
It should contain at least the connect details:

#
HS_FDS_CONNECT_INFO=://
# alternate connect format is hostname/serverinstance/databasename
Short explanation of the parameter HS_FDS_CONNECT_INFO:
It can be configured to use a SQL Server port# or to work with instances:
HS_FDS_CONNECT_INFO=://
HS_FDS_CONNECT_INFO=//
is the hostname where the SQL Server resides
is the port number of the SQL Server (default is 1433)
is the name of a dedicated instance you want to connect to; leave it blank if your SQL Server setup does not use SQL Server instances
is the name of the database DG4MSQL should connect to; for example Northwind

Example:
To connect to a Northwind database on a SQL Server (w2k3) with IP Address 999.168.0.1 using the default instance you can use:
HS_FDS_CONNECT_INFO=x2kx.de.oracle.com:1433//Northwind
or
HS_FDS_CONNECT_INFO=999.168.0.1:1433//Northwind
To connect to a SQL Server 2k5 named instance msql2k5 on this machine listening on port 4025 either use:
HS_FDS_CONNECT_INFO=x2kx.de.oracle.com:4025//Northwind
or
HS_FDS_CONNECT_INFO=999.168.2.1:4025//Northwind
or
HS_FDS_CONNECT_INFO=x2kx.de.oracle.com/MSQL2k5/Northwind
or
HS_FDS_CONNECT_INFO=999.168.2.1/MSQL2k5/Northwind
SIDE NOTE:
As the named instance connect might cause trouble the option using the port number is a suitable work around.
You can find the port number by accessing SQL Server's Server Network Utility program， choosing the Instance you want to connect to， highlighting tcp/ip under Enabled Protocols， and clicking the Properties button. A window should open with the port number that the instance is listening on.
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
connect to 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 'dg4msql';
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. 'dg4msql' 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.
Keywords
'HS_FDS_CONNECT_INFO' 'DATABASE~LINK' 'SQL~SERVER'
--------------------------------------------------------------------------------