|
PURPOSE
------- A step by step guide to setting up and troubleshooting Heterogeneous Services using Generic Connectivity on a Unix platform. SCOPE & APPLICATION ------------------- This example setup is from a Sun Solaris Platform running Oracle 8.1.6 to a target Microsoft SQL Server database on Windows NT using the Openlink Generic ODBC driver. 1. Install Heterogeneous Services with the Oracle Installer. If it's already installed, you'll see an "hs" directory under $ORACLE_HOME 2. Install the data dictionary tables and views for Heterogeneous Services. Log in to the Oracle database as sys and run the "caths.sql" script. This script is located in $ORACLE_HOME/rdbms/admin. The data dictionary tables and views may already be installed on the server. You can query the data dictionary to check for their existence. select table_name from dba_tables where table_name like 'HS%'; select view_name from dba_views where view_name like 'HS%'; If they are not, run the script as follows: cd $ORACLE_HOME sqlplus (provide authentication) @rdbms/admin/caths.sql 3. Install your ODBC driver and configure an ODBC DSN. Ensure that connections work by testing via the "odbctest" sample application and/or the iODBC HTTP Administrator. 4. Make sure the following entries are added to the "network/admin/tnsnames.ora" and "network/admin/listener.ora" files (change the "host" / ORACLE_HOME values according to your system): Tnsnames.ora ------------ hsodbc= (description= (address=(protocol=tcp)(host=solaris_server)(port=1521)) (connect_data=(sid=hsodbc)) (hs=ok) ) Listener.ora ------------ sid_list_listener= (sid_list= (sid_desc= (sid_name=hsodbc) (ORACLE_HOME = /dbs/oracle8i/64-bit/8.1.6) (program= hsodbc) ) ) 5. Start the Oracle listener: lsnrctl start ** You should now have a service handler for hsodbc ** 6. Make sure the following noted entries are in the inithsodbc.ora located in $ORACLE_HOME/hs/admin (sample values -- your paths may vary): # This is a sample agent init file that contains the HS parameters that are # needed for an ODBC Agent. # # HS init parameters # HS_FDS_CONNECT_INFO = <Your ODBC DSN Name> HS_FDS_TRACE_LEVEL = 4 HS_FDS_TRACE_FILE_NAME = hs.log # *** Full path to ODBC Driver Manager *** HS_FDS_SHAREABLE_NAME = /dbs/openlink/32bit/v42/lib/libiodbc.so # # ODBC specific environment variables # set ODBCINI=/dbs/openlink/32bit/v42/bin/odbc.ini set ODBCINSTINI=/dbs/openlink/32bit/v42/bin/odbcinst.ini # *** The following variables are extra requirements of the OpenLink ODBC drivers *** set OPENLINKINI=/dbs/openlink/32bit/v42/bin/openlink.ini set PATH=$PATH:/dbs/openlink/32bit/v42/bin set LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/dbs/openlink/32bit/v42/lib 7. Connect to the Oracle database and create a database link to access the target database. Be sure to use the appropriate quotes as noted below. SQL> create database link hsodbc SQL> connect to "user" identified by "password" <= valid user/pwd on target DB SQL> using 'hsodbc'; Once this is done, you should be able to select from a remote table in your DSN: SQL> SELECT * FROM authors@hsodbc; ---------------------------------------------------------------- Troubleshooting / Error messages (from Oracle Knowledgebase) ---------------------------------------------------------------- =========================================================================== Errors and Solutions Associated with the HS Gateway (UNIX) =========================================================================== /********************************************************************/ ORA-28509: unable to establish a connection to non-Oracle system ORA-02063: preceding line from HS /********************************************************************/ Cause: This indicates a problem with the Oracle configuration files. Action: Make sure the HOST parameter in the tnsnames.ora file is correct. Make sure the PORT number is correct. Make sure the SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA /********************************************************************/ ORA-02068: following severe error from HS ORA-03114: not connected to ORACLE /********************************************************************/ Cause: This indicates the required syntax for the TNSNAMES.ORA file is not present. Action: (HS=OK) has to be added to the tnsnames.ora file in the DESCRIPTION section. /********************************************************************/ ORA-02068: following severe error from HS ORA-28511: lost RPC connection to heterogeneous remote agent using %tns_address% /********************************************************************/ Cause: The listener is unable to spawn the HS agent or the agent cannot find the ODBC lib directory. Action: The PROGRAM line in the listener.ora file is incorrect or not specified. Make sure LD_LIBRARY_PATH includes the $ODBC_HOME/lib directory. If not, set LD_LIBRARY_PATH and restart the listener. /********************************************************************/ ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for ODBC][H001] The environment variable <HS_FDS_CONNECT_INFO> is not set. ORA-02063: preceding 2 lines from HS /********************************************************************/ Cause: Incorrect parameter settings in the HS init.ora file. Action: Set HS_FDS_CONNECT_INFO in the HS init.ora file to the data source name located in the odbc.ini file. Example: HS_FDS_CONNECT_INFO = MS_SQLServer7 Make sure the HS init.ora file exists in the $ORACLE_HOME/hs/admin directory and has the same name as the SID in the LISTENER.ORA. Example: If SID=hsodbc in the listener.ora file, then the HS init.ora file would be named $ORACLE_HOME/hs/admin/inithsodbc.ora /********************************************************************/ ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for ODBC][H001] The environment variable <HS_FDS_SHAREABLE_NAME> is not set. ORA-02063: preceding 2 lines from HS /********************************************************************/ Cause: Incorrect parameter settings in the HS init.ora file. Action: Set HS_FDS_SHAREABLE_NAME to the full path plus filename to the libodbc.so file. Example: HS_FDS_SHAREABLE_NAME=/u01/intersolv/odbc/ msql/lib/libodbc.so /********************************************************************/ ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for ODBC]DRV_InitTdp: (SQL State: 01000; SQL Code: 0) ORA-02063: preceding 2 lines from HS /********************************************************************/ Cause: The HS agent cannot find the odbc.ini file. Action: The ODBCINI variable is not set in the HS init.ora file and needs to be set. Example: set ODBCINI=/u01/intersolv/odbc/msql/odbc.ini /********************************************************************/ ORA-00942: table or view does not exist [Transparent gateway for ODBC]DRV_OpenTable: [MERANT][ODBC SQL Server Driver][SQL Server]Invalid object name '%table%'. (SQL State: S0002; SQL Code: 208) ORA-02063: preceding 2 lines from HS /********************************************************************/ Cause: The data source in the odbc.ini file has incorrect database information. Action: Consult your odbc user guide on how to set the parameters for your datasource. /********************************************************************/ ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for ODBC]DRV_InitTdp: [MERANT][ODBC SQL Server Driver][libssclient15]General network error. Check your network documentation. (SQL State: 08001; SQL Code: 11) ORA-02063: preceding 2 lines from HS /********************************************************************/ Cause: There is a problem at the network layer communicating with the foreign data source. Action: Make sure the destination host or ip address and port number are correct for the data source in the odbc.ini file. /********************************************************************/ ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for ODBC]DRV_InitTdp: [MERANT][ODBC SQL Server Driver][SQL Server] Login failed (SQL State: 28000; SQL Code: 4002) ORA-02063: preceding 3 lines from HSTEST /********************************************************************/ Cause: The Oracle database link created for the foreign datasource has either no credentials or incorrect credentials. Action: Recreate the Oracle database link with the propper username and password. Also, username and password must be in double quotes. Example: SQL> create database link ODBC connect to "sa" identified by "pencil" using 'hsodbc'; Other resources: --------------- http://metalink.oracle.com http://technet.oracle.com |
| Evidence |
| Creating a Unix ODBC Trace |
| DBMS Version |