This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Feb 19, 2013 12:22 AM by User353235 Go to original post RSS
  • 15. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    User353235 Newbie
    Currently Being Moderated
    I have tried with another session, but the error is same.

    The initDB4ODBC.ora file has below entries:
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO = mssql
    HS_FDS_TRACE_LEVEL = 255
    HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
    HS_FDS_SUPPORT_STATISTICS=FALSE
    HS_NLS_NCHAR=UCS2
    #
    # ODBC specific environment variables
    #
    set ODBCINI=/home/oracle/odbc.ini
    #

    The current odbc.ini appears as below:
    [ODBC Data Sources]
    mssql=MS SQL Server

    [mssql]
    Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
    Database=FAKIH_ToTestInt
    LogonID=OracleUser
    Password=0r@cle999
    Server=SJSQLV01.fakiehgroup.com
    QuotedId=YES
    AnsiNPW=YES
    [ODBC]
    IANAAppCodePage=4
    Trace=0
    UseCursorLib=0
    UseCursorLib=0
  • 16. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    kgronau Guru
    Currently Being Moderated
    I assume initDB4ODBC.ora is just a typo and and you meant DG4ODBC so that it matches the previous gateway trace which states: ORACLE_SID is "DG4ODBC"

    Please delete all trace files in the hs/log directory, then make sure tracing is enabled and select from your table in a new SQL*Plus session.
    When you got the error, can you please "grep" in the new gateway trace for HS_NLS_NCHAR ("grep NCHAR *.log") and post just this output?
  • 17. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    User353235 Newbie
    Currently Being Moderated
    Sorry, it was type, it is initDG4ODBC.ora.

    Removed all logs, the new created log has below message

    [oracle@testdb log]$ grep NCHAR DG4ODBC_agt_9421.trc
    setting HS_NLS_NCHAR to default of "AL32UTF8"
    setting HS_FDS_MAP_NCHAR to default of "TRUE"
    HOCXU_DRV_NCHAR=873
    HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR)
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR)
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR)
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR)
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR)
  • 18. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    kgronau Guru
    Currently Being Moderated
    Not sure why it is not reading the init file.
    Could you please check if you have any dg4odbc process remaining in the memory (ps- ef|grep dg4odbc) and kill it (kill -9 <pid>).
    Then check again by opening a new SQL*Plus session and executing the select. Grep again for NCHAR in the trace and post the output.

    thx,
    Klaus
  • 19. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    User353235 Newbie
    Currently Being Moderated
    Hi Klaus,

    There are no memory processes for dg4odbc running.

    [oracle@testdb log]$ ps -ef |grep dg4odbc
    oracle 11027 10813 0 16:18 pts/1 00:00:00 grep --color dg4odbc



    [oracle@testdb log]$ grep NCHAR DG4ODBC_agt_10995.trc
    setting HS_NLS_NCHAR to default of "AL32UTF8"
    setting HS_FDS_MAP_NCHAR to default of "TRUE"
    HOCXU_DRV_NCHAR=873
    HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR)
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR)
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR)


    Thank you,
    Mohammed.
  • 20. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    kgronau Guru
    Currently Being Moderated
    Just installed an 11.1.0.7 gateway and it looks like it does not accept HS_NLS_NCHAR settings. I don't remember anymore if for 11.1 a patch was needed to get it working.
    Do you see a chance to use the 11.2.0.3 gateway instead of 11.1.0.7? You can download the 11.2.0.3 gateway from My Oracle Support - look for Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

    and then make sure you have chosen Linux x86-64bit.
    The gateway software is part of the CD: p10404530_112030_platform_5of7.zip

    Download the ZIP file, unzip it on the Linux box and install the gateway inot its own Oracle_home. Define its own listener and in your database location change the tnsnames.ora entry pointing to the newly created listener. then copy the gateway init file from the 11.1 location to the 11.2 OH and test it.
  • 21. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    User353235 Newbie
    Currently Being Moderated
    Hi Kgronau,

    I have configured the same on on 2nd node of our production (2 node RAC) server. When i am querying the sql database directly from oracle database servers (both nodes), its working fine. But when connecting from any other machine from any of the tools, its giving error.
    ORA-12154: TNS:could not resolve the connect identifier specified

    The listener status is ok.

    [oracle@proddb2 admin]$ lsnrctl status listen_dg4odbc

    LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 30-JAN-2013 10:25:24

    Copyright (c) 1991, 2008, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=proddb2.fakiehgroup.com)(PORT=1511)))
    STATUS of the LISTENER
    ------------------------
    Alias listen_dg4odbc
    Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
    Start Date 20-JAN-2013 16:36:33
    Uptime 9 days 17 hr. 48 min. 51 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/oracle/fakieh/db/tech_st/11.1.0/network/admin/fakieh2_proddb2/listener.ora
    Listener Log File /u01/oracle/fakieh/db/tech_st/11.1.0/log/diag/tnslsnr/proddb2/listen_dg4odbc/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=proddb2.fakiehgroup.com)(PORT=1511)))
    Services Summary...
    Service "DG4ODBC" has 1 instance(s).
    Instance "DG4ODBC", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    Please suggest.

    Thank You,
    Mohammed.
  • 22. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    kgronau Guru
    Currently Being Moderated
    Mohammed,
    I'm not sure if I understand your last update correctly stating:"When i am querying the sql database directly from oracle database servers (both nodes), its working fine. But when connecting from any other machine from any of the tools, its giving error.
    ORA-12154: TNS:could not resolve the connect identifier specified".

    The gateway can only be used with an Oracle database using the database link. The database link is stored in the Oracle database so when the link works in SQL*Plus, it commonly also works with applications that access the Oracle database and use this database link.
    But the gateway can't be used directly with for example SQL*Plus to connect directly to the foreign database.

    - Klaus
  • 23. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    User353235 Newbie
    Currently Being Moderated
    Hi Klaus,

    Hear i mean that when i try to query from the same sql statement from machines other than database machines, it is giving me error. For example, if i am executing below select statement from database server itself, it retrieves the rows, while if it gives error from any other machines (eg: My PC, or the machine with application server).
    when query form db server:
    SQL> select count(*) from "Areas"@sqlserver;

    COUNT(*)
    ----------
    10714

    When query from any other machine:
    SQL> select count(*) from "Areas"@sqlserver;
    select count(*) from "Areas"@sqlserver
    *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve the connect identifier specified


    Thank You,
    Mohammed.
  • 24. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    kgronau Guru
    Currently Being Moderated
    Are you connected to the same Oracle database as the same user that's used in the working connection?

    Just as a test, create a second database link sqlserver2 this time include the tns connect string to the gateway in the database link:

    create database link sqlserver2 connect to "<SQL Server username>" identified by "<SQL Server password>" using '
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST= testdb.domain.com )(PORT=1511))
    (CONNECT_DATA=(SID=DG4ODBC))
    (HS = OK))';



    Please make sure to replace theusername/password place holders between with the correct values matching your env.
    - Klaus
  • 25. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
    User353235 Newbie
    Currently Being Moderated
    Hi Klaus,

    It started working after i restart the database. Many thanks for you support.

    Thank You,
    Mohammed.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points