Oracle - What TNS Names file am I using?

Question

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.

Answer 1

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.

Answer 2

For linux:

$ 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 

Changing to

$ strace sqlplus -L scott/tiger@orcl 2>&1| grep -i 'tnsnames.ora' 

will show all the file paths that are failing.