=============================================
Here is an article I wrote on Hetergeneous Services.
In order to connect to SQL*Server from Oracle, you must do several things:
1. Install ODBC drivers for the SQL*Server database. The drivers are installed on your laptop or on the server that contains the Oracle code tree.
2. Setup the ODBC connection on your laptop 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 in the Oracle database.
5. Configure the Hetergeneous services. This consists of creating an initodbc.ora file within the Oracle Oracle database installation.
7. Modify the Listener.ora file on the database installation to connect to the Oracle instance and ODBC drivers.
8. Modify the Tnsnames.ora file to point to the proper code tree.
9. Reload the listener
10. Create a database link on the Oracle installation.
11. Run a Select statement for the Oracle installation using the database link.
-----------------------------------------------------------------------------------
1.0 Introduction
==============
Heterogeneous Services is an integrated component within the Oracle database server. It allows transparent SQL access from an Oracle client to non-Oracle systems as if the non-Oracle system was an Oracle database. By using the Heterogeneous Services ODBC (HSODBC) agent with Easysoft products, this functionality can be extended on non-Windows platforms to include any ODBC or JDBC compliant database.
Important HSODBC is a normally a 32-bit application even when distributed with a 64-bit version of Oracle. You need to use the 32-bit HSODBC with a 32-bit Easysoft ODBC driver. For 64-bit platforms, choose the 32-bit version of the driver even when there's a 64-bit version available. 32-bit drivers will still function correctly on a 64-bit platform.
To check whether you have a 32-bit or 64-bit version of HSODBC, on the Oracle machine, run:
file $ORACLE_HOME/bin/hsodbc
If the command output contains ELF-Class64 (or something similar such as ELF-64 or ELF 64-bit), you have a 64-bit HSODBC. Otherwise, you have a 32-bit version.
If $ORACLE_HOME/bin/hsodbc is not present, contact your Oracle DBA.
If you have already downloaded, installed and licensed a 64-bit Easysoft ODBC driver, but need to use a 32-bit version, make a backup copy of /usr/local/easysoft/license/licenses and then remove the Easysoft installation directory.
For example:
cp /usr/local/easysoft/license/licenses /tmp
rm -rf /usr/local/easysoft
Download and install the 32-bit version of the driver, and then copy your backup licenses file to /usr/local/easysoft/license.
Note When using hsODBC to access SQL Server you may need to add "DisguiseWide=1" to your DSN as hsODBC does not seem to recognise SQL_WCHAR columns. (See 5.2 No rows returned when connecting Oracle hsODBC to MS SQL Server.)
The scope of this document is to provide a synopsis of what you need to set up to connect from SQL*Plus to an ODBC driver on a Unix based system. It assumes that the Oracle database server is configured and the HSODBC components are already installed on the machine. As HSODBC runs on the Oracle server, you will need to start the Oracle listener. This is not run on some Oracle client/server installations.
The process can be broken down into the following sections:
1. Install and configure the ODBC driver on the Oracle data-server.
=====================================================
2. Configure within Oracle:
====================================================
o Heterogeneous Services (iniths*.ora)
o Database Listener (listener.ora)
o Network Client (tnsnames.ora)
3. Create a database link through SQL*Plus utilising the above.
================================================================
==============================
2.0 Configure the ODBC driver
==============================
Whichever Easysoft product you want Oracle to load data from, they all contain the unixODBC driver manager. It is this that Oracle will load to access the ODBC driver. Essentially we are linking HSODBC to unixODBC and the driver manager is then responsible for the ODBC access. The configuration:
SQL*Plus>Oracle Client>HSODBC instance>UnixODBC>ODBC driver>Database
The Easysoft product that you will be installing has full installation instructions either within the product manual or within a 'Getting Started Guide'. For the purposes of this document it will be assumed that the software is installed, a datasource entered into /etc/odbc.ini and a successful test connection has been made to the database from the isql utility found within /usr/local/easysoft/unixODBC/bin. Once the ODBC driver has been set-up, Oracle can be configured to use this connection.
========================
3.0 Configure Oracle
========================
To allow the Oracle server to use HSODBC, you will need to edit three files and restart the Oracle Listener. These are:
• init file
• tnsnames.ora
• listener.ora
All three files reference either entries made in the other files or the datasource entry made within /etc/odbc.ini. Care needs to be taken when configuring these files as an incorrect entry in any of them can lead to a failure to connect.
3.1 Create an init file
------------------------------
On the Oracle server, login to the Oracle account and cd into $ORACLE_HOME/hs/admin. Every instance using HSODBC needs a separate iniths*.ora file. For this generic document, we created the init file inithsconnect.ora. You can name this file to identify the connection on your machine e.g inithsmssql.ora. This init file gives details of the datasource that is to be connected to and the shared object to be linked by HSODBC to provide the link. To provide this information, append the file with the following information pertinent to your system:
#
# This is a sample agent init file containing the HS parameters that
# are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = ODBC_DSN
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc
HS_FDS_SHAREABLE_NAME =/usr/local/easysoft/unixODBC/lib/libodbc.so
The information that should be entered is as follows:
• HS_FDS_CONNECT_INFO the datasource name as entered within /etc/odbc.ini. Note that as hsODBC uses the SQLDriverConnect ODBC API, you can actually put any valid ODBC connection string here. Here are some examples:
o "mydsn"
The name of a DSN in the odbc.ini file. Here, hsODBC will call SQLDriverConnect with "DSN=mydsn;UID=xxx;PWD=yyy;" where xxx and yyy are the username and password specified when the link is created (see Create a Database Link).
o "DSN=mydsn;UID=aaa;PWD=bbb;"
As above, except the username and password in the created link is overridden with aaa and bbb.
o "DRIVER={Easysoft ODBC-SQL Server};Server=myhost\\myinstance;UID=mydomain\\myuser; PWD=mypassword;Port=1500;"
A DSN-less connection where all the ODBC driver attributes are specified in the connection string. This example is for the Easysoft ODBC-SQL Server Driver.
o "FILEDSN=/tmp/mydsn.dsn"
This example uses the ODBC FILEDSN attribute to instruct the ODBC Driver manager to read the details for this data source from /tmp/mydsn.dsn.
• HS_FDS_SHAREABLE_NAME the location of libodbc.so on your machine. This is the driver manager that hsODBC will load to gain access to your ODBC data source.
• HS_FDS_TRACE_LEVEL is the level of tracing. You should generally leave this commented out with a # character at the start of the line, as tracing slows the operations down substantially. However, if you are having problems HS_FDS_TRACE_LEVEL may be set to a number from 1 to 4 (where 4 is the most verbose) and trace output is written to the file specified with HS_FDS_TRACE_FILE_NAME.
• HS_FDS_TRACE_FILE_NAME is the name of a trace file to us. This is only relevant if HS_FDS_TRACE_LEVEL is set to a number between 1 and 4. If HS_FDS_TRACE_LEVEL is set but HS_FDS_TRACE_FILE_NAME is not, the trace file will be created in the $ORACLE_HOME/hs/trace directory.
Once this information has been entered into the file, save into $ORACLE_HOME/hs/admin.
3.2 Edit listener.ora
-------------------------
Edit the listener.ora. This creates the instance that the tnsnames.ora references on connection through SQL*Plus. It creates a SID_NAME and specifies the Oracle executable to run on connection. The listener.ora is found within $ORACLE_HOME/network/admin. Configuring HSODBC needs the creation of a new SID_DESC within SID_LIST in the SID_LIST_LISTENER. An example looks like:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /space/oracle/OraHome10db)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=hsconnect)
(ORACLE_HOME=/space/oracle/OraHome10db)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
/usr/local/easysoft/lib)
))
The information that should be entered:
• SID_NAME enter the name of the init file without the init. If you have named the init file inithsconnect.ora, enter hsconnect as the SID_NAME.
• ORACLE_HOME enter the location of your Oracle home directory.
• PROGRAM enter hsodbc as this is the Oracle program used by this SID
• ENVS=LD_LIBRARY_PATH enter the Load Library Path here. You may have to add other paths (colon separated) e.g. for our 32 bit ODBC-Oracle driver, $ORACLE_HOME/lib32 is required.
Once entered save the file.
3.3 Edit tnsnames.ora
------------------------
The final Oracle file to edit is the tnanames.ora, found in $ORACLE_HOME/network/admin. This entry identifies the Oracle server to be attached to and the SID_NAME to be used. A new Oracle definition needs to be created:
hsconnectid=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521))
(CONNECT_DATA= (SID=hsconnect))
(HS=OK)
)
The information that should be entered:
• hsconnectid This the name given to the server. This is the name used when connecting using SQL*Plus. Again, try to link your entries together in logical progression so that all entries can be attributed to each other.
• ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521) This is the entry for your Oracle server.
• CONNECT_DATA=(SID=hsconnect) This must match the entry created as SID_NAME within listener.ora.
• HS=OK Any tnsnames entry that is connecting to a SID that runs HSODBC must have this entry.
Once added save the file.
3.4 Start/Restart the listener
-----------------------------
Once all the entries have been added to the above files, you will need to restart the listener so that the entries that you have made with the listener.ora are used. Note You may not have been running the Oracle listener service previously but you need it to use hsODBC. To do this:
cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start
When the listener is restarted, you may get information messages similar to:
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
service...
Service "hsconnect" has 1 instance(s).
Instance "hsconnect", status UNKNOWN, has 1 handler(s) for this
service...
The status unknown is returned by hsodbc services, as the service is not created until used.
You can also run lsnrctl and enter services to get a list and the status of the services.
After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping
# tnsping hsconnectid
TNS Ping Utility for Solaris: Version 10.1.0.2.0 -
Production on 27-OCT-2004 14:43:57
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
/space/oracle/OraHome10db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = hermod)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = hsconnectid)))
OK (10 msec)
which should come back with a successful message (as shown above).Once this has been performed, a database instance can be created and connection attempted.
===============================
4.0 Create a Database Link
===============================
Connect to SQL*Plus as if connecting to your regular database. Once the prompt is loaded, you need to create a new database link as follows:
CREATE PUBLIC DATABASE LINK hslink CONNECT TO "dbuser"
IDENTIFIED BY "dbpassword" using 'hsconnectid';
In the above command, hslink is the name of the oracle database link that is created using the hsconnect oracle entry within the tnsnames.ora. Note that when creating the database link, the database user and password are entered. These are not the operating system user and passwords. Also if the 'hsconnectid' is not within single quotes, errors are returned:
ERROR at line 1:
ORA-02010: missing host connect string
Once the database link has been created, try passing in an SQL statement:
Select * from dual@hslink;
where "dual" is a table in your "hslink" linked database.
When passing in SQL to the newly created instance, the syntax is always:
table@linkname
You can even join tables in ODBC to those in your Oracle database:
select * from table@hslink,dual
=========================
5.0 Known Problems
=======================
5.1 unixODBC on AIX
-------------------------
When you configure and build unixODBC for AIX, you end up with the driver manager shared object inserted into libodbc.a as libodbc.so.1. i.e.
$ ar -tv libodbc.a
rwxr-xr-x 201/1 636475 Aug 20 09:11 2004 libodbc.so.1
libtool creates the driver manager like this on AIX.
Unfortunately, some applications (e.g. sqlplus) are built and linked against libodbc.so (no version). You can rectify this as follows:
1. cd to the directory where unixODBC's libraries are installed e.g. /usr/local/easysoft/unixODBC/lib.
2. extract the shared object from the archive with:
3. ar -xv libodbc.a
x - libodbc.so.1
4. Rename libodbc.so.1 to libodbc.so.
mv libodbc.so.1 libodbc.so
5. Point HSODBC directly at the new shared object by amending the HS_FDS_SHAREABLE_NAME in your init[name].ora file to be:
HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so
6. Restart your Oracle listener. Your link should now work.
5.2 No rows returned when connecting Oracle hsODBC to MS SQL Server
We have discovered that some versions of hsODBC do not seem to recognise SQL_WCHAR, SQL_WVARCHAR and SQL_WLONGVARCHAR columns. If a table contains these column types, hsODBC will just error producing no rows. A workaround for this is to add "DisguiseWide=1" to the DSN in your odbc.ini file.
5.3 Invalid user and or password specified
If you get an error similar to:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:
[Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2052): ;
[unixODBC][][unixODBC][Easysoft SQI-ISAM Driver]
Invalid user and or password specified.
(SQL State: 28000; SQL Code: 702)
ORA-02063: preceding 2 lines from MYLINK
or basically any error indicating the database username and password is incorrect, you have probably specified them incorrectly when creating the database link above. Although you can view links in the dba_db_links table you cannot see the username and password.
If you set HS_FDS_TRACE_LEVEL to 4, set HS_FDS_TRACE_FILE_NAME to a file in you init*.ora file and try again, you will get a trace output file. If you examine the trace file and locate the SQLDriverConnect call you can see the username and password being used. If this verifies the database username and/or password is incorrect then you need to delete your link and recreate it or change the HS_FDS_CONNECT_INFO in your init*.ora file to specify the UID and PWD (see above). You can delete a link using the syntax:
drop public database link MYLINK
No comments:
Post a Comment