ODBC - Troubleshooting Oracle Connections

• ORA-28509: unable to establish a connection to non-Oracle system
    
  • 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-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.
    
  • Set HS_FDS_CONNECT_INFO in the hs{sid}init.ora file to the data source name.
    Example: HS_FDS_CONNECT_INFO = <ODBC DataSource Name>
  • Make sure the hs{sid}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{sid}init.ora file would be named ORACLE_HOME/hs/admin/inithsodbc.ora

    

• ORA-02019: connection description for remote database not found
     
  • Verify that the database link you are using exists.
      
    SQL> select db_link,host,created from user_db_links;

DB_LINK
------------------------------------------------------------------
HOST
------------------------------------------------------------------
CREATED
---------------
A.MINISOFT.LOCAL
mscard
22-MAY-03
  
  • If no records are found, confirm that the database link is accessible.
SQL> select db_link,host,created from all_db_links;
SQL> select db_link,host,created from dba_db_links;
note: You may need to use PUBLIC or SHARED

CREATE [SHARED][PUBLIC] DATABASE LINK link_name
[CONNECT TO CURRENT_USER]
[USING 'connect_string']

CREATE [SHARED][PUBLIC] DATABASE LINK link_name
[CONNECT TO user IDENTIFIED BY password]
[AUTHENTICATED BY user IDENTIFIED BY password]
[USING 'connect_string']

• ORA-02068:  following severe error from HS
     
  • Verify the structure of the tnsnames.ora entry for hsodbc.
    
    MSCARD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oak)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mscard)
(SRVR = DEDICATED)
)
(HS = OK)
)
 
  •  

• ORA-12154: TNS:could not resolve service name
  
  • The name as tried by oracle could not be located in the TNSNAMES.ORA file. This may be do to a missing entry or incomplete resolution do to a problem in SQLNET.ORA and the default domain.
  • Invalid entry in tnsnames.ora, missing right parentheses.

    

• ORA-28545: NCRO Failed to make RSLV connection
  
  • Verify the hostname in the tnsnames.ora file references your Oracle server.  This should not reference the HP3000.

    

• ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    
  [Generic Connectivity Using ODBC][MiniSoft] [3kodbc.dll] In (NetConnect), error 0 Unknown Error - Connect(): Cannot resolve address () (SQL State: 08004; SQL Code: 0)
  
  This indicates that the driver was unable to find a server entry in the DSN or was unable to locate the DSN.
    
  • Check that the odbc.ini file referenced by init{sidname}.ora exists and is readable by the Oracle listener process.

    

• If the init{sidname}.ora file cannot be found, the following message will be seen:
  
  SQL> select * from customer@a;
  select * from customer@a
                        *
  ERROR at line 1:
  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
  [Generic Connectivity Using ODBC][H006] The init parameter
  is not set. Please set it in init.ora file.
  ORA-02063: preceding 2 lines from A      
  
  

• Execution of the Oracle application "hsodbc" should return a banner. If you receive an error message, that the application cannot be run or has a problem with a required library, review your Oracle installation to ensure all components are correctly installed.
  
  $ hsodbc
  
  
  Oracle Corporation --- FRIDAY    SEP 26 2003 08:26:47.268
  
  Heterogeneous Agent based on the following module(s):
     - External Procedure Module
     - Transaction Module
     - SQL/PLSQL Module
  
  If a library is missing you may see the following
  
  SQL> select * from customer@a;
  select * from customer@a
                        *
  ERROR at line 1:
  ORA-28509: unable to establish a connection to non-Oracle system
  ORA-02063: preceding line from A
  
  Your listener.log file will show:
  
  26-SEP-2003 08:31:13 * (CONNECT_DATA=(SID=mscard)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.23.118)(PORT=49349)) * establish * mscard * 12500
  TNS-12500: TNS:listener failed to start a dedicated server process
  TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
  HPUX Error: 32: Broken pipe