How to change tnsping Used parameter files?

Question

Write in cmd: tnsping orcl

Used parameter files:

~/product\11.2.0\client_1\network\admin\sqlnet.ora

I want to change ref folder. client -> dbhome1

So, if 'Used parameter files' = "~/product\11.2.0\dbhome1\network\admin\sqlnet.ora" play sqlplus.

Answer 1

You have some choices here

1. set the ORACLE_HOME environment variable. By default we will look in ORACLE_HOME/network/admin, or

2. set the TNS_ADMIN environment variable. We will look there if it is set

3. Don't bother with tns entries. You can use //hostname:port/service_name as the string when you connect

Answer 2

sqlnet.ora does not contain a database alias. tnsnames.ora contains database aliases. If you want to change the tnsnames.ora file and take the contents of the file in another directory, then you can add one more method to the above methods. Change the directory with the required tnsnames file.

cd product\11.2.0\dbhome1\network\admin\
tnsping tns_alias
or
sqlplus user/password@tns_alias

For example. i have 2 ORACLE_HOME

 1) C:\Oracle\10.2.0\OAS\NETWORK\ADMIN
    
     Directory of C:\Oracle\10.2.0\OAS\NETWORK\ADMIN
    23.07.2020  19:15    <DIR>          .
    23.07.2020  19:15    <DIR>          ..
    23.07.2020  18:54               526 tnsnames.ora

    2) C:\Oracle\11.2.0\dbhome_1\NETWORK\ADMIN
    
     Directory of C:\Oracle\11.2.0\dbhome_1\NETWORK\ADMIN
    
    23.07.2020  19:14    <DIR>          .
    23.07.2020  19:14    <DIR>          ..
    21.07.2020  13:00               628 listener.ora
    21.07.2020  13:00               450 sqlnet.ora
    21.07.2020  13:06               547 tnsnames.ora

C:\Oracle\11.2.0\dbhome_1\ is default ORACLE_HOME

C:\Oracle\10.2.0\OAS\NETWORK>tnsping nbd11

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 23-JUL-2020 19:23:19

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.32.175)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nbd11)))
OK (20 msec)

tnsping work fine. it takes the database aliases from the file C:\Oracle\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora.

tnsping Failed to resolve name.

C:\Oracle\10.2.0\OAS\NETWORK>tnsping nbd111

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 23-JUL-2020 19:27:51

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\11.2.0\dbhome_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

Change the directory with the required tnsnames file. tnsping work fine. it takes the database aliases from the file tnsnames.ora in the current directory.

C:\Oracle\10.2.0\OAS\NETWORK>cd admin

C:\Oracle\10.2.0\OAS\NETWORK\ADMIN>tnsping nbd111

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 23-JUL-2020 19:29:21

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.26.32.175)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nbd11)))
OK (30 msec)

For example 2.

Using the full address without using the tnsnames file.

C:\Oracle\10.2.0\OAS\NETWORK>tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.26.32.175)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=nbd11)))

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 23-JUL-2020 19:38:39

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.26.32.175)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=nbd11)))
OK (20 msec)