Oracle — What TNS Names file am I using?
Sometimes I get Oracle connection problems because I can’t figure out which tnsnames.ora file my database client is using. What’s the best way to figure this out? ++happy for various platform solutions.
11 Answers 11
Oracle provides a utility called tnsping :
R:\>tnsping someconnection TNS Ping Utility for 32-bit Windows: Version 9.0.1.3.1 - Production on 27-AUG-20 08 10:38:07 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: C:\Oracle92\network\ADMIN\sqlnet.ora C:\Oracle92\network\ADMIN\tnsnames.ora TNS-03505: Failed to resolve name R:\> R:\>tnsping entpr01 TNS Ping Utility for 32-bit Windows: Version 9.0.1.3.1 - Production on 27-AUG-20 08 10:39:22 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: C:\Oracle92\network\ADMIN\sqlnet.ora C:\Oracle92\network\ADMIN\tnsnames.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = **) (PROTOCOL = TCP) (Host = ****) (Port = 1521))) (CONNECT_DATA = (SID = ENTPR0 1))) OK (40 msec) R:\> This should show what file you’re using. The utility sits in the Oracle bin directory.
$ strace sqlplus -L scott/tiger@orcl 2>&1| grep -i 'open.*tnsnames.ora' shows something like this:
open("/opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora",O_RDONLY)=7 $ strace sqlplus -L scott/tiger@orcl 2>&1| grep -i 'tnsnames.ora' will show all the file paths that are failing.
There is another place where the TNS location is stored: If you’re using Windows, open regedit and navigate to My HKEY Local Machine/Software/ORACLE/KEY_OraClient10_home1 where KEY_OraClient10_home1 is your Oracle home. If there is a string entry called TNS_ADMIN , then the value of that entry will point to the TNS file that Oracle is using on your computer.
On my development machine I have three different versions of Oracle client software. I manage the tnsnames.ora file in one of them. In the other two, I have entered in the tnsnames.ora file:
ifile=path_to_tnsnames.ora_file/tnsnames.ora This way, if for some reason the wrong tnsnames.ora file is used by a client, it will always end up at the up-to-date version.
Codeslave asks «Shouldn’t it always be «$ORACLE_ HOME/network/admin/tnsnames.ora»? The answer is no, it isn’t. Consider these two invocations of tnsping on the same machine:
C:\Documents and Settings\me>D:\Oracle\10.2.0_DB\BIN\tnsping orcl TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 09-OCT-2 008 14:30:12 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: D:\Oracle\10.2.0_DB\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))) OK (40 msec) C:\Documents and Settings\me>tnsping orcl TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 09-OCT-2 008 14:30:21 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: D:\oracle\10.2.0_Client\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = XXXX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL))) OK (20 msec) C:\Documents and Settings\me> Note the two different parameter file locations, that are dependent on which tnsping executable you’re running (and perhaps where it’s being run from). For tnsnames-based oracle networking, using the TNS_ADMIN variable is the only way to ensure you’re getting a consistent tnsnames.ora file. (NOTE: Windows-centric answer)
Tnsnames ora где лежит linux
The Oracle RAC database installation process creates a tnsnames.ora file on each node. This file acts as a repository of net service names.
Each net service name is associated with a connect identifier. A connect identifier is an identifier that maps a user-defined name to a connect descriptor. A connect descriptor contains the following information:
- The network route to the service, including the location of the listener through a protocol address
- The SERVICE_NAME parameter, with the value set to the name of a database service
Note: The SERVICE_NAME parameter that you use in the tnsnames.ora file is singular, because you can specify only one service name. The SERVICE_NAME parameter is not the same as the service_names database initialization parameter. The service_names database parameter defaults to the global database name, a name comprising the db_name and db_domain parameters in the initialization parameter file. When you add service names using SRVCTL or Oracle Enterprise Manager Cloud Control, it lists additional cluster-managed services for the database.
The tnsnames.ora file is located in both the Grid_home /network/admin and Oracle_home /network/admin directories. By default, the tnsnames.ora file is read from the Grid home when Oracle Grid Infrastructure is installed.
With Oracle Clusterware 11g Release 2 and later, the listener association no longer requires tnsnames.ora file entries. The listener associations are configured as follows:
- DBCA no longer sets the LOCAL_LISTENER parameter. The Oracle Clusterware agent that starts the database sets the LOCAL_LISTENER parameter dynamically, and it sets it to the actual value, not an alias. So listener_ alias entries are no longer needed in the tnsnames.ora file.
- The REMOTE_LISTENER parameter is configured by DBCA to reference the SCAN and SCAN port, without any need for a tnsnames.ora entry. Oracle Clusterware uses the Easy Connect naming method with scanname:scanport , so no listener associations for the REMOTE_LISTENER parameter are needed in the tnsnames.ora file.
For example, after you create the database, to add a second listener, listening on port 2012, use a command similar to the following command to have the database register with both listeners on startup:
SQL> alter system set local_listener='(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.61)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.61)(PORT=2012))))' scope=BOTH SID='OCRL1';Where to find tnsname.ora — Debian
In a Debian machine, I can use tnsping to identify a tnsname. But I can not found any tnsnames.ora in this machine. (nothing in $ORACLE_HOME/network/admin/ ) So, how can I find the tns name config file used by tnsping command ? Is there any command (similar with tnsping) to locate the tnsnames file? UPDATE: tnsping command result: Thanks in advance
2 Answers 2
There is no common location and different applications/drivers apply various search paths with different order and precedences.
Check following locations:
- $ORACLE_HOME/network/admin/
- Current directory of application
- $TNS_ADMIN (where TNS_ADMIN is environment variable settings)
Just for completeness in case of Windows check also
- Registry key HKLM\SOFTWARE\ORACLE\KEY_\TNS_ADMIN (for 64 bit)
- Registry key HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_\TNS_ADMIN (for 32 bit)
- .NET config file (i.e. machine.config and/or web.config )
In ideal case all of them should point to the same location, consider to create symbolic links if needed.
NB, maybe file tnsnames.ora simply does not exist. Create such file in location mentioned above.
Thanks for your answer. But in this machine $TNS_ADMIN is not config. I have to config $ORACLE_HOME and $PATH ; $LD_LIBRARY_PATH to run tnsping.
You tell us the tnsping is working. At my place the tnsping tool gives the exact location of the sqlnet.ora it is using. The tnsnames.ora should be in the same directory.
When your environment is correctly configured the tnsnames should be in the mentioned directory. When it does not exist you could try running the netca command and configure «Naming Methods configuration» and add a destination with «Locale Net Service Name configuration». This utility will create sqlnet.ora en tnsnames.ora in the default location.
[oracle@somedir]$ tnsping test TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 27-JUL-2016 22:00:24 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /home/oracle/app/oracle/product/11.2.3/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST))) OK (0 msec) Simon Krenger
This is some additional paragraph placeholder content.
This is some additional paragraph placeholder content.
Oracle InstantClient: TNSNAMES.ORA location
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?
The Oracle website has the answer:
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.
– Instant Client FAQ
So, for UNIX and Linux systems set the variable TNS_ADMIN like so in your .profile or .bash_profile :
export TNS_ADMIN=/opt/oracle/instantclient_11_2/On Windows systems, set the environment variables via the Advanced System properties:
- Open CMD, enter sysdm.cpl
- In the Advanced tab, select Environment Variables
- Under “System Variables”, click on New… and enter “TNS_ADMIN” as the name and the path where your TNSNAMES.ORA resides as your value.
Now, you can use your TNS names for your InstantClient, for example for SQL*Plus:
For more information on SQL*Plus configuration, please refer to the Oracle documentation for SQL*Plus.
Hello world
My name is Simon Krenger, I am a Technical Account Manager (TAM) at Red Hat. I advise our customers in using Kubernetes, Containers, Linux and Open Source.