3 Replies Latest reply: Apr 29, 2011 10:54 AM by Christian.Shay -Oracle RSS

    Which tnsnames.ora will be used?

    696513
      I have 2 tnsnames.ora files
      - the XE server install in C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
      - the ODT install in C:\Oracle\product\11.2.0\ODT_client1\Network\Admin

      When I was stepping through the ODT 2 day tutorial, I added the ORCL entry to the one in C:\Oracle\pro....\, but was getting errors. Then I found the one in C:\OracleExe\... and that appears to be the one to use

      Ok, just as I posted this, I found something interesting. When I run the SQL Developer application, it uses the one in C:\OracleExe\.... and when I try to conect via ODT/VS.NET, it tries to use the other one. If I go into the references and try to add the Oracle.DataAccess from the C:\OracleExe folder - it complains.

      My questions was: when developing in VS.NET and I create a connection, how do I know which tnsNames file (when there are more than one on the local disk) will be used and when? (ANSWER) - this appears to be dictated by where I get my Oracle.DataAccess.

      So if the tnsnames.ora entry in the ODT folder is not working right, but SQL Developer is, then copy the entry from the XE tnsnames.ora file to the ODT tnsnames.ora file and it appears to work.

      I also found that while I can use any name for the entry in the ODT tnsnames.ora file, the SERVICE_NAME entry had to match a valid entry in the XE tnsnames.ora file. So entry that worked for me (for the ORCL example) is:

      ORCL =
           (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.yyyy.zzzzzz.com)(PORT = 1521))
                (CONNECT_DATA =
                     (SERVER = DEDICATED)
                     (SERVICE_NAME = XE)
                )
           )

      If I made the SERVICE_NAME=ORCL, it wouldn't work. XE is the entry in the tnsnames.ora file used by XE and SQL Developer.

      Thanks,

      JustMe

      Edited by: justme on Apr 26, 2011 2:26 PM
        • 1. Re: Which tnsnames.ora will be used?
          Christian.Shay -Oracle
          Justme,

          The tnsnames.ora that is used is dictated by the Oracle home the software you are using is affiliated with.

          For example, if ODT is installed in /oraclehome1, then it will look in /oraclehome1/network/admin

          I am by no means a SQLNet expert, but there is an environment variable called TNS_ADMIN that you can look at. This forces all apps to use the same TNSNAMES.ORA file.

          Finally if you are using XE and ODT on the same machine, you probably need no alias at all if it is just a local connection.
          • 2. Re: Which tnsnames.ora will be used?
            696513
            I checked for the TNS_ADMIN environment variable, but it doesn't exist on my machine (I tried this early from some of the other messages I saw.)

            The interesting thing is that you are right, I don't even need to use the tnsnames.ora file when working with the XE locally (I wonder why that tutorial has me add the ORCL key...) Anyway, if I just use the host reference, it works just fine. Thanks for the tip.

            So that leaves me thinking that I need to find another forum to post where I can find out which folder belongs to which install when using the ODBC Administrator.... Not a big deal at this time though.

            Thanks again for the help.

            JustMe
            • 3. Re: Which tnsnames.ora will be used?
              Christian.Shay -Oracle
              justme wrote:
              I checked for the TNS_ADMIN environment variable, but it doesn't exist on my machine (I tried this early from some of the other messages I saw.)

              The interesting thing is that you are right, I don't even need to use the tnsnames.ora file when working with the XE locally (I wonder why that tutorial has me add the ORCL key...) Anyway, if I just use the host reference, it works just fine. Thanks for the tip.

              So that leaves me thinking that I need to find another forum to post where I can find out which folder belongs to which install when using the ODBC Administrator.... Not a big deal at this time though.

              Thanks again for the help.

              JustMe
              TNS_ADMIN is one of the optional environment variables so it won't be set by default. If you decided to add it, you could put it in the system environment variables.

              Normally it is used in deployment scenarios where the target environment already has a functioning TNSNAMES.ORA and you just want to use it. In that case you would put it in the environment of your application.