Oracle HS (Heterogeneous Services)(其他文档)

******************************************************************************************
这是一篇在两台机器上部署的异构数据库的访问：
******************************************************************************************
ORACLE HS的前提是：
If you want to use your 10.2.0. database to connect with DG4ODBC you have to be at patchset level 10.2.0.4.0
在mealink上也提到了。
Notes how to configure DG4ODBC on Linux can be found on Metalink:
Note.561033.1 How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) RDBMS.HS-1110 :
Note.466228.1 How to Setup DG4ODBC on Linux x86 32bit

Installing and configuring Oracle Heterogeneous Services for SQLServer

Par Nixman le dimanche 18 mai 2008, 16:59 - Oracle - Lien permanent

• English
• Oracle

All databases share a common set of normalized SQL, which, in theory, allows them to interoperate directly using database links.
However, reality is not so simple, as those who've tried to connect DB2 with SQLServer might have realized.

Luckily, with Oracle, there are at least two ways to achieve direct SQL*NET connectivity to foreign databases: Oracle Heterogeneous Services ODBC (HSODBC) and Oracle Transparent Gateways.

Here, we will achieve a simple database link between an Oracle database on a UNIX server and an SQLServer database residing on a Windows Server 2003 machine through the simplest of the two methods: Oracle Heterogeneous services. Bluntly, it consists in installing an Oracle pseudo-listener on the target non-Oracle database server.

As Microsoft doesn't provide any sort of UNIX client for SQL Server, all this interoperability is achieved thanks to work done by Oracle coders. Kudos to them, and  the opposite to the other guys.
 
X = Windows Server 2003 with SQLServer 2005 + Oracle 8iR3 with Oracle HS
Y = Solaris 8 server with Oracle 8iR3 + Heterogeneous Services installed.
 
 
On X:

Step 0) On X: Install Oracle Server 8iR3 software or later with Heterogenous Database connectivity (Check that ODBC DRIVERS have really been installed). I won't detail the installation of Oracle on Windows here.
 
Step 1) On X: Configure DSN:
Go to: Settings -> Control Panel. Double-click on ODBC icon.
Then click on the System DSN tab and Add button. Add SQL Server, as local server. Name it, for example, MSQL (we will be using "MSQL" in our example configuration files from now on).
Test it. The default database is the on we're targetting.
 
Step 2) On X :  Copy the file inithsodbc.ora into initMSQL.ora in the  $ORACLE_HOME\hs\admin directory (If you'd named the DSN "ZOZO" in the previous step, you would have named the file initZOZO.ora, of course).
 
Step 3) On X: Modify  the initMSQL.ora file in the following manner (HS_FDS_CONNECT_INFO must have the same name as the DSN):

###########
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSQL
HS_FDS_TRACE_LEVEL = NO
###########
 
Step 4) On X:  Modify the listener.ora file in $ORACLE_HOME\network\admin directory in the following manner (you're modifying the  SID_LIST_LISTENER paragraph. SID_NAME must be the same as the DSN) :
 
###########
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSQL)
         (ORACLE_HOME = c:\Orant)
         (PROGRAM=hsodbc)
       )
      )
 ###########

Another solution would be to add an altogether new listener, that you've called MSQL, like this (here, we've set it to listen on port 1522):

###########
MSQL =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
 
SID_LIST_MSQL=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSQL)
         (ORACLE_HOME = c:\Orant)
         (PROGRAM=hsodbc)
       )
      )
###########

In that case, you would have to start this listener specifically by issuing a lsnrctl start MSQL
 
Step 5) On X: restart the listener by issuing a  lsnrctl reload.
 
 
On Y:
 
Step 1) On Y: set GLOBAL_NAMES=FALSE  in your database's init.ora  file or in the initialization parameters.
 
Step 2) On Y: Add an entry pointing at server X listener by adding the following lines to the tnsnames.ora file:
 
###########
testMSQL  =
  (DESCRIPTION=
    (ADDRESS=
        (PROTOCOL=tcp)
        (HOST= SERVER_X_IP_ADDRESS)
        (PORT= SERVER_X_LISTENER_PORT)
     )
     (CONNECT_DATA=
        (SID=MSQL)
     )
     (HS=OK)
  )
###########
 
Step 3) On Y: Test connectivity by issuing a tnsping testMSQL
 
Step 4) On Y: Create the database link between your Oracle database and testMSQL by issuing the following SQL command:
SQL> create public database link testingMSQL connect to USER identified by PASSWORD using 'testMSQL';
 
Step 5) On Y: Do some selects on Server X's tables:
SQL> select * from TABLE_NAME@testingMSQL;

You're done!

Beware that you're restricted to normalized SQL. between the two databases. Old-timers will find themselves back in Oracle 6 days: You won't be able to use INSERT SELECT statements or other Oracle enhancements, but will have to go through a cursor, etc... However, you will be able to issue simple SELECT, INSERT and UPDATE commands. Which is what you wanted in the first place.

Happy computing.

Drop me a comment if this post has been useful to you, or if you see any reason for add-on or modification.



******************************************************************************************
MEALINK上的文章：
******************************************************************************************

How to Setup DG4ODBC on Linux x86 32bit
	文档 ID:	466228.1	类型:	HOWTO
	Modified Date:	19-JUN-2008	状态:	PUBLISHED

In this Document
  Goal
  Solution
     How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on LINUX x86 32bit
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6
Linux x86

Goal

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 9.2.0.8, 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. 9.2.0.8, 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.

Solution

How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on LINUX x86 32bit

The 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 reapplied).

After the installation the following items must be configured:

0) ODBC driver
1) listener
2) tnsnames
3) init.ora of the hs subsystem
4) environment
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:
<http://www.datadirect.com>

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]

[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

[mssql]
Driver=/home/odbc/dd/lib/ivmsss23.so
Description=DataDirect 5.3 SQL Server Wire Protocol
Database=
LogonID=
Password=
Address=,
QuotedId=YES
AnsiNPW=YES

[ODBC]
IANAAppCodePage=4
InstallDir=/home/odbc/dd
Trace=0
TraceDll=/home/odbc/dd/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0
UseCursorLib=0

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
export LD_LIBRARY_PATH=$ODBC_HOME/lib:$LD_LIBRARY_PATH
b) ODBCINI
export DBCINI=$ODBC_HOME/odbc.ini
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 -pwd
like
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
ubq1'.
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:

(SID_NAME=DG4ODBC)
(ORACLE_HOME=/home/oracle/oracle/product/11.1)
(ENVS=LD_LIBRARY_PATH=//lib/:/lib:/home/oracle/product/11.1/lib)
(PROGRAM=dg4odbc)

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:

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
       (SID_NAME=DG4ODBC)
      (ORACLE_HOME=/home/oracle/oracle/product/11.1)
      (ENVS=LD_LIBRARY_PATH=/home/odbc/dd/lib/:/home/oracle/product/11.1/lib)
      (PROGRAM=DG4ODBC)
      )
   )

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 DG4ODBC alias:

DG4ODBC.de.oracle.com =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1511))
      (CONNECT_DATA=(SID=DG4ODBC))
      (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

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 DG4ODBC
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
#
set DBCINI=/home/odbc/dd/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set =

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
$ODBC_HOME/lib/.
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:
strings |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
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 '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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/123747/viewspace-615467/