9 Replies Latest reply: May 5, 2008 8:25 AM by 637337 RSS

    Oracle instant client - missing but undesired tnsnames.ora

    637337
      Hi,
      I'm trying connect to a database using sqlplus instant client and for some reason the easy connection string is not working the same as in any other server I tried. Someway it is looking the entry on a tnsnames.ora file which is not defined and I don't won't/need to use either.

      Is there anyone who has any idea how to resolve this or that can help me? Read below for the details.

      OS = MSWin server 2003
      TNS_ADMIN is not defined

      c:\oracle_instant_client>sqlplus user/pass@//IP:PORT/service

      SQL*Plus: Release 10.1.0.3.0 - Production on Fri May 2 13:54:05 2008

      Copyright (c) 1982, 2004, Oracle. All rights reserved.

      ERROR:
      ORA-12154: TNS:could not resolve the connect identifier specified


      Enter user-name:
        • 1. Re: Oracle instant client - missing but undesired tnsnames.ora
          608334
          check service name in tnsnames .ora file

          and if you have domain names .. then need to check is that setup in sqlnet.ora

          --Girish                                                                                                                                                                                                                                                                               
          • 2. Re: Oracle instant client - missing but undesired tnsnames.ora
            637337
            Hey, thanks for your reply!
            I'm trying to avoid tnsnames usage. That's why I'm using the easy connection string that should work fine without it. Furthermore I'm connecting to the same database with the same connection string from other windows servers. Any clue?
            • 3. Re: Oracle instant client - missing but undesired tnsnames.ora
              Jocelyn Simard
              Make sure your instantclient directory is present in your system path and before any other Oracle client that may already be installed. It should work...

              Regards,
              • 4. Re: Oracle instant client - missing but undesired tnsnames.ora
                637337
                Well, actually that was one of the things I suspected however as you can see in my first post I'm running instant client from the instant client "home" directory itself and I have also tried the following to make sure:

                c:\oracle_instant_client>.\sqlplus user/pass@//IP:PORT/service
                • 5. Re: Oracle instant client - missing but undesired tnsnames.ora
                  orafad
                  Could be that a sqlnet.ora file is picked up somewhere along the paths.
                  Download and use Process Monitor to see what's really going on:
                  http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

                  Version string says 10.1... is that right, why not 10.2 or later?
                  • 6. Re: Oracle instant client - missing but undesired tnsnames.ora
                    Jocelyn Simard
                    Well, you have 3 components to verify:

                    - Attempt to ping the host... maybe you have a connectivity issue?
                    - Double-check that you are using the port you think you are
                    - Double-check that the service name is what you think it is

                    You can use "tnsping service_name" to find out the correct port used by the listener.

                    Also, easy connection string is not specific to the instant client. So, start by testing your connect string on a regular client. Once it works, it should also work right away in instant client.

                    Regards,
                    • 7. Re: Oracle instant client - missing but undesired tnsnames.ora
                      637337
                      Well I think you're right about the sqlnet.ora. That's the closest approach to a feasible explanation to this problem however I'm not sure why this file is picked when I'm specifying a full connection string...

                      I've already did some tests before I read your message that may be confirming that sqlnet.ora is picked and therefore the tnsnames.ora file as it is specified in the sqlnet.ora. I tried to connect to an already defined service in the existing tnsnames file and the connection was successful when I typed:

                      c:\oracle_instant_client>.\sqlplus user/pass@service

                      and unsuccessful when I typed it as

                      c:\oracle_instant_client>.\sqlplus user/pass@//IP:PORT/service.

                      It happens that everything after the "@" is interpreted as a service name so there's no way to find the service in the tnsnames. I'd say that since the ORACLE_HOME is defined in the windows registry the program may be searching sqlnet.ora which it is actually defined within network/admin directory and pointing to use tnsnames. I need to remove this file to test whether this works without the sqlnet.ora but I couldn't give it a try yet since it is a production server and I have to go through a process to do changes :-(

                      Regarding the ping, port and service name I'm completely sure about it. As I previously stated the same connection string is working from other load balanced servers within the same network and since the service is not defined and I don't want to define it either in the tnsnames; tnsping is not helpful. Furthermore I'm completely sure about the correctness of the IP, port number,service name and syntax.

                      Thanks all for your help!!!
                      • 8. Re: Oracle instant client - missing but undesired tnsnames.ora
                        orafad
                        If any of the "other" (i.e. outside instant client directory) sqlnet.ora contain a names.directory.path setting, such as (TNSNAMES), then that might explain what you are seeing.

                        the ORACLE_HOME is defined in the windows registry
                        the program may be searching sqlnet.ora which it is
                        Why do you have a separate Oracle install/home, while trying to use Instant client on the same machine? Can you not just use sqlplus from the existing home?

                        pointing to use tnsnames. I need to remove this file
                        to test whether this works without the sqlnet.ora but
                        To override sqlnet.ora settings you should be able to use your own sqlnet.ora in the Instant client directory, e.g. with a single line with names.directory_path=(ezconnect).

                        Additionally, having an sqlnet.ora file (possibly empty for defaults) in the Instant client dir and setting TNS_ADMIN=<path to instant client dir> (via command line just before you run sqlplus), should override reading of sqlnet.ora file from multiple locations altogether.

                        To override reading of oracle settings from the registry, you could, at least as a temporary workaround, create an empty oracle.key file in the Instant client dir -- then sqlplus will look directly in registry branches HKCU and HKLM instead of under HKxx\software\oracle.

                        It seems as if a default setup of Instant client (on win x86) implies it will find settings from other oracle installations and source .ora files from other oracle home directory paths.
                        • 9. Re: Oracle instant client - missing but undesired tnsnames.ora
                          637337
                          Thanks again for your help!

                          Regading your question about having sqlplus and instant client in the same server the problem is that I'm on a shared environment used by multiple remote applications for different users and one of these applications has one functionality that depends on instant client to connect to the database and it's bundled with it...

                          Thanks again for your comments and different workarounds!

                          FYI I've choosen the approach of placing an sqlnet.ora within instant client home directory and setting ezconnect which worked pretty well.