How To Update Tnsnames.ora File Windows

[Venice Sassone]

dbForgeseems to use either the tnsnames.ora file in either location #2 or #3. I know this by systematically renaming all but one of the tns files and then checking to see if the connection names read from the file are available when trying to create a new connection from with the app.

However, TOAD seems to only recognize the tnsnames.ora file in location #3 and it did not recognize the tnsnames.ora file in location 2 at all! (Being that it was a 32 bit program, I did not expect it to recognize the tns file in location 2 and that was the case). TO summarize the TOAD test for the sake of hopeful clarity, TOAD only recognized the tns file in location 3.

However, based on some tests I made with ODP.NET Managed Driver (4.121.2.0) it takes %ORACLE_HOME%\network\admin and TNS_ADMIN Environment variable into account. Locks like the documentation is not 100% correct.

Just based on your paths you have two installed clients as you suspect (Toad and dbforge are tools, not clients so your terminology is a bit off). One 32-bit, the other 64-bit. It appears that Toad is 32-bit based on its installation path, but execute it and go to HelpSupport Bundle. You'll see the top header will be "APPLICATION INFORMATION (32-bit)" or "APPLICATION INFORMATION (64-bit)" just to confirm. Toad 11.6 was the first to introduce a 64-bit version.

Toad will only see the Oracle client that is for the same platform as it. So your 64-bit client is irrelevant for Toad's sake. The C:\Windows\TNS appears to be a folder used for TNS_ADMIN folder given its odd location and the fact that Toad sees it. At command prompt execute SET TNS_ADMIN and see if it reports "TNS_ADMIN=C:\Windows\TNS" If it does, then all Tools should be using that tnsnames.ora. That's a global override if you will that points to the folder containing your net configuration files. If you don't have TNS_ADMIN set as an environment variable then look for it in your Oracle root registry: HKEY_LOCAL_MACHINE\Software\Oracle.

If you use a common set of connections for all of your tools I'd delete all of your tnsnames.ora files. I'd also relocate that C:\Windows\TNS folder to somewhere more appropriate like C:\Oracle\Admin and create your tnsnames.ora, sqlnet.ora, and ldap.ora (if applicable) there. Create a TNS_ADMIN environment variable pointing to that location.

Most Oracle Client installs use a TNSNames.ora file. This allows users to enter a database alias in client programs to connect to an Oracle database. A common problem is when the Oracle Client either can't find the TNSNames.ora file or is using a different one than was intended.

The default location of the TNSNames.ora file is "ORACLE_HOME\network\admin\tnsnames.ora", where ORACLE_HOME is the directory which contains your primary Oracle Home installation. This could be something like "C:\Oracle12\network\admin\tnsnames.ora".

If you use TNSNames.ora it is recommended to use either an environment variable or a registry setting to force the location of the file. This setting is called TNS_ADMIN and is set to the directory/path of the tnsnames.ora file (but should not contain the filename, just the path to it.)

One possible problem (and this comes up surprisingly often!) is that the Oracle Client will first check if the current application directory contains tnsnames.ora. The current application directory can be initially set in the startup icon options for a program, or programs often keep track of the last used document directory. It is a good idea to not keep copies of tnsnames.ora in default program directories or document directories so as not to have the Oracle Client accidentally use it instead of the intended tnsnames.ora file.

Are you saying that you have multiple tnsnames.ora files? If you are using a TNS_ADMIN variable, then point it to a folder that is not part of your existing Oracle clients (maybe something like c:\Oracle\tnsadmin), and put your tnsnames.ora in that folder. The benefit of the TNS_ADMIN variable is so that you don't need a tnsnames.ora for every oracle client.

That said, I just tried pointing my tns_admin variable to the network\admin folder of one of my clients, and it worked fine for all of my installed clients, so I'm not sure why it isn't working for you.

Interesting that this has nothing with Toad. In mine 5 years extensive work with Toad (all Oracle versions except 12c) Toad have never true problem with TNS names.

So for any problem like this, first try with plain SQL*Plus connection. If works then it is really Toad problem.

:-()

@bricot_750

Your last answer (which you mark as verified) was:

I happened to point the TNS_ADMIN back to what I thought it was originally (second paragraph in my post) and it is working now.

I really do like to understand how that was related to Toad. Please spent some additional time to help us understand the problem.

Change the net_service_name, or the name you want to give to the connection. Change the host to the server name or IP address you want to connect to. Finally, change the service_name to the name of the database you want to connect to.

Thank you. I have one query, i need to take tnsnames.ora file from local path instead of TNS_ADMIN environment variable. How can i do that. I need to do this as i am getting access denied message when i take tnsname.ora file from TNS_ADMIn environment path.

Someone will need to grant the Authenticated Users (Windows) read/write access to the network/admin folder. Then you need to grant read/write or full control to the user(s) authorized to change tnsnames.ora.

I am building a box with Windows XP with some applications. One application requires connection to an Oracle database on remote. I have installed OracleXEClient.exe from Oracle download. The installation does install "Oracle Provider for OLE DB" driver.

I think I may miss TNSNAMEC.ora in the box. I can find this file from another box where Oracle connection works fine. I am not sure what package I should install (from Oracle) so that the default TNSNAEMES.ora will be installed with related files and setup path for accessing the TNS file?

I'm assuming it won't find the connection. But it will tell you something like "user parameter files: " with a sqlnet.ora file in a directory ending in network/adminPut the tnsnames.ora file in that directory (as that is where it will look for it by default).

With the traditional Oracle fat client, you need to have a system environment variable for TNS_ADMIN, which points to the folder where tnsnames.ora is located. And you probably need to log off/logon for that variable to be picked up in your environment.

What is the ORACLE_HOME directory for your Oracle Client installation? It will likely be a subfolder of C:\oracle\product\, although I'm not familiar with XEClient installations. In a typical Oracle 10g client install, the home directory is C:\oracle\product\10.2.0\client_1. Assuming this directory, then your TNSNAMES.ORA file is located in C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN. Drop your working TNSNAMES.ORA file in that directory and test again.

When you install the InstantClient binaries, you often want to use your existing TNSNAMES.ORA file containing all your databases. So how do you specify the location of your TNSNAMES.ORA file?

Always set the TNS_ADMIN environment variable or registry to the location of the tnsnames.ora file (full directory path only, do not include the file name). This practice will ensure that you are using the appropriate tnsnames.ora for your application when running with Instant Client.

In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration.

The "listerner.ora" file contains server side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin"directory on the server. Here is an example of a basic "listener.ora" file from Linux. We can see the listener has the default name of "LISTENER" and is listening for TCP connections on port 1521. Notice the reference to the hostname "myserver.example.com". If this is incorrect, the listener will not function correctly.

The listener defined above doesn't have any services defined. These are created when database instances auto-register with it. In some cases you may want to manually configure services, so they are still visible even when the database instance is down. If this is the case, you may use a "listener.ora" file like the following.

The "tnsnames.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are usedon the server itself. Here is an example of a "tnsnames.ora" file.

The alias used at the start of the entry can be whatever you want. It doesn't have to match the name of the instance or service. Notice the PROTOCOL, HOST and PORT match that of the listener. The SERVICE_NAME can be any valid service presented by the listener. You can check the available services by issuing the lsnrctl status or lsnrctl service commands on the database server. Typically there is at least one service matching the ORACLE_SID of the instance, but you can create more.

The "sqlnet.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself, or if some additional server connection configuration is required. Here is an example of an "sqlnet.ora" file.

Once the files are present in the correct location and amended as necessary the configuration can be tested using SQL*Plus by attempting to connect to the database using the appropriate username (SCOTT), password (TIGER) and service (orcl.example.com).

The server must have a correct entry in the "/etc/hosts" file matching the hostname and IP address of the server, as well as the loopback entry for localhost. For example in this case the values are as follows.

3a8082e126