This discussion is archived
11 Replies Latest reply: Jul 27, 2013 7:48 AM by aefb3eb8-b393-4133-8d46-6337253a7c34 RSS

Oracle ODBC Connection - ora-12154

984717 Newbie
Currently Being Moderated
Hello all,

i have one DB-Server and one Application-Server. From the App-Server i want to access the db via odbc.

Even if i create the odbc connection in windows odbc manager and click "test connection", i get the error "Ora-12154 TNS: Angegebener Connect Identifier konnte nicht aufgelöst werden"

DB-Server:
Oracle DB 11.0.2g 64 bit

App-Server:
windows 2008 R2 64Bit, login as network administrator
Oracle Instantclient_11_2 with installed ODBC drivers
folder of instantclient: "C:\ora\instantclient_11_2_new"
folder of tnsnamesora: "C:\ora\net"

environment variables:
-ORACLE_HOME: "C:\ora\instantclient_11_2_new"
-Path: "C:\ora\instantclient_11_2_new"
-TNS_Admin: "C:\ora\net"

tnsnames.ora:
bns =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.99)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = bns)
)
)

Anybody has an idea what the issue is ?
Unfortunately this is the first odbc connection to the oracle db.

Thanks for your help
  • 1. Re: Oracle ODBC Connection - ora-12154
    Tridus Journeyer
    Currently Being Moderated
    Well, that error primarily comes up if it can't find your tnsnames.ora file. It could be not picking up the TNS_ADMIN environment variable, so you could try setting it in the registry instead.
  • 2. Re: Oracle ODBC Connection - ora-12154
    624295 Newbie
    Currently Being Moderated
    Tridus,

    I have the exact same problem - my system details are

    OS: Windows 7 Enterprise 64-bit
    Oracle: 11g R2 (Client) [Oracle home C:\ora11g\product\11.2.0\client_1]

    Other +ve details
    - Oracle home defined in Registry path "Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1\ORACLE_HOME with value C:\ora11g\product\11.2.0\client_1
    - ORACLE_HOME (manually) defined at the OS level as "C:\ora11g\product\11.2.0\client_1"
    - TNS ping working properly and sqlplus connection working appropriately.

    Could anyone tell me what could be the issue?

    Thanks,
  • 3. Re: Oracle ODBC Connection - ora-12154
    624295 Newbie
    Currently Being Moderated
    What should that be set to - the path where the TNSNAMES.ora file is residing?

    [Another fact for my system - the TNSNAMES.ora file contains a single line pointing to ifile (on a network device) --> "ifile=p:\oracle\ADMIN\tnsnames.ora". I guess it would still be fine to put the path of the local TNSNAMES.ORA file (C:\ora11g\product\11.2.0\client_1\network\admin). correct?]
  • 4. Re: Oracle ODBC Connection - ora-12154
    992588 Newbie
    Currently Being Moderated
    I have the same issue on Windows 7 64 bit machine (Oracle 10.2.0 Client) - basic test just use the odbc 32 bit admin (C:\Windows\SysWOW64\odbc32.exe)
    to connect to Oracle - fails with a ora-12154. I have read other posts regarding the PATH statement and they say
    you cannot have ( ) in it as in the installed directory for software on 64 bit, for example: "Program files (x86)"

    I don't have that in my path statement - I am not yet trying to use MS Access via the oracle driver - I am only trying to do the 'test' function in ODBC 32 client.
    Any suggestions - TNS names is located in ADMIN folder as it usually is for Oracle 10.2.0 Client

    I am reading that Oracle 10.2.0 client is 32 bit and won't work with network layers on 64 bit windows - This makes some sense to me since the Oracle 10.2.0 drivers are not available or showing in the OS supplied 64 bit version of ODBC. I will search for a compatibility statement regarding this client and the 64 bit OS but any suggestions are helpful as well.

    thanks!
  • 5. Re: Oracle ODBC Connection - ora-12154
    Tridus Journeyer
    Currently Being Moderated
    user621292 wrote:
    Tridus,

    I have the exact same problem - my system details are

    OS: Windows 7 Enterprise 64-bit
    Oracle: 11g R2 (Client) [Oracle home C:\ora11g\product\11.2.0\client_1]

    Other +ve details
    - Oracle home defined in Registry path "Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1\ORACLE_HOME with value C:\ora11g\product\11.2.0\client_1
    - ORACLE_HOME (manually) defined at the OS level as "C:\ora11g\product\11.2.0\client_1"
    - TNS ping working properly and sqlplus connection working appropriately.

    Could anyone tell me what could be the issue?
    If SQLPlus is working, you do have a working Oracle client. Is that 11g R2 client the only Oracle software on the system? If so, ODBC should be able to connect to anything that SQLPlus can, provided you're using the right ODBC connection manager (the control panel one for 64 bit Oracle clients, and c:\windows\syswow64\odbcad32.exe for 32 bit Oracle clients).
  • 6. Re: Oracle ODBC Connection - ora-12154
    Tridus Journeyer
    Currently Being Moderated
    user621292 wrote:
    What should that be set to - the path where the TNSNAMES.ora file is residing?

    [Another fact for my system - the TNSNAMES.ora file contains a single line pointing to ifile (on a network device) --> "ifile=p:\oracle\ADMIN\tnsnames.ora". I guess it would still be fine to put the path of the local TNSNAMES.ORA file (C:\ora11g\product\11.2.0\client_1\network\admin). correct?]
    My oracle client is installed in c:\oracle\client\11gR2, and my path looks like this:

    C:\oracle\client\11gR2;C:\oracle\client\11gR2\bin; (and a bunch of non Oracle stuff)

    So no, the path should point to your Oracle client folder and it's \bin subfolder, not to the network\admin subfolder directly.

    I don't know anything about setting tnsnames to point to another tnsnames file, so maybe someone else can help with that part. :)
  • 7. Re: Oracle ODBC Connection - ora-12154
    Tridus Journeyer
    Currently Being Moderated
    989585 wrote:
    I have the same issue on Windows 7 64 bit machine (Oracle 10.2.0 Client) - basic test just use the odbc 32 bit admin (C:\Windows\SysWOW64\odbc32.exe)
    to connect to Oracle - fails with a ora-12154. I have read other posts regarding the PATH statement and they say
    you cannot have ( ) in it as in the installed directory for software on 64 bit, for example: "Program files (x86)"

    I don't have that in my path statement - I am not yet trying to use MS Access via the oracle driver - I am only trying to do the 'test' function in ODBC 32 client.
    Any suggestions - TNS names is located in ADMIN folder as it usually is for Oracle 10.2.0 Client

    I am reading that Oracle 10.2.0 client is 32 bit and won't work with network layers on 64 bit windows - This makes some sense to me since the Oracle 10.2.0 drivers are not available or showing in the OS supplied 64 bit version of ODBC. I will search for a compatibility statement regarding this client and the 64 bit OS but any suggestions are helpful as well.

    thanks!
    It's possible that Oracle 10.2 client doesn't work properly on Windows 7 x64, as it was never released for that OS. That said it did work in Vista x64. The simplest thing to test might be to try installing an 11g client instead and see if that works. You can use a 32 bit client on an x64 OS, provided it's 32 bit applications that are using it.

    For now my advice would be to try it with an 11 client, like this one: http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html
  • 8. Re: Oracle ODBC Connection - ora-12154
    819732 Newbie
    Currently Being Moderated
    Hi all,
    I need help from you to resolve tns no listerner error.

    My server and clint are located in difference locations and connecting via NAT.

    no issue in tnsping

    when test connection in ODBC, its gives this error ORA 12541: TNS: no listener.

    I am stragle with this issue for last 1 month. Can any one help for me.

    Thanks for helping

    Swami
  • 9. Re: Oracle ODBC Connection - ora-12154
    Tridus Journeyer
    Currently Being Moderated
    What happens if you use SQL Plus or SQL Developer to connect to that database instead of ODBC?
  • 10. Re: Oracle ODBC Connection - ora-12154
    624295 Newbie
    Currently Being Moderated
    Well, apparently, it was just the test that was failing (I don't know whether it's just my machine or the ODBC admin utility on Windows 7 64-bit that behaves that way).

    When I connect to DB using ODBC (through MS Excel - 2010 or through R) it works fine and I can fetch data.

    Anyone else for whom ODBC behaves (or does not behave) the same way?

    Thanks,
  • 11. Re: Oracle ODBC Connection - ora-12154
    aefb3eb8-b393-4133-8d46-6337253a7c34 Newbie
    Currently Being Moderated

    Found one common reason for that: especially on Windows 7 64 bit Environments.

    The problem is that the Oracle Client for some reason reacts badly if the full path (path + filename) contains round brackets.

     

    This is usually the case, if you install a 32 bit application, because the Default Directory contains round brackets:

    On Windows 7 it is for example: c:\program files (x86)\...

     

    Solution: Install the executable of your application in a different path without round brackets. Ensure that the filename of your executable does not contain round brackets.

Legend

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