1 2 Previous Next 25 Replies Latest reply: Feb 19, 2013 2:22 AM by User353235 Go to original post RSS
      • 15. Re: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
        User353235
        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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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-Oracle
                      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
                        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-Oracle
                          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
                            Hi Klaus,

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

                            Thank You,
                            Mohammed.
                            1 2 Previous Next