In this Document
|How to Setup DG4MSQL (Oracle Database Gateway for MS SQL Server) on LINUX|
Starting with 11g Oracle now provides a Database Gateway for MS SQL Server on Linux x86 32bit.
The setup in 11g slightly differs from previous releases and this note will explain the steps how to configure DG4MSQL on Linux.
The gateway is certified also for older Oracle releases 22.214.171.124, 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 My Oracle Support by performing a search
under the 'Patches & Updates' tab at the top of the page. Choose the
'Search' tab and enter <> in the block 'Patch name or Number'
and select the appropriate platform. after choosing the 'Platform' tab.
If there is not a patch available for your database version (i.e. 126.96.36.199, 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 <> to your platform.
This gateway compatibility patch is included in all Oracle database releases above 10.2.0.4.
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 patch set 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 patch set needs to be reapplied).
After the installation the following items must be configured:
1) listener (in the ORACLE_HOME where the gateway software is installed)
2) tnsnames (in the ORACLE_HOME where the Oracle database software is installed)
4) Oracle database
1) The listener needs a new SID entry like the following:
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:
The listener must be stopped and started after changing the listener.ora file!
2) The tnsnames.ora needs an entry for the DG4MSQL 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:
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
should come back with a successful 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 initialization file of the gateway. The name of the
file is init
The file is located at $ORACLE_HOME/dg4msql/admin.
It should contain at least the connect details:
# 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:
To connect to a Northwind database on a SQL Server (w2k3) with IP Address 192.168.0.1 using the default instance you can use:
To connect to a SQL Server 2k5 named instance msql2k5 on this machine listening on port 4025 either use:
When using HS_FDS_CONNECT_INFO=
When connections using the named instance connect method continue to fail (even with the "SQL Browser Service" being started option of using the port number is a suitable workaround.
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:
In other words, to connect to the MS SQL Server configured in the last steps,
the syntax must be:
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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38267/viewspace-730106/，如需转载，请注明出处，否则将追究法律责任。