0

Our database servers are configured with the tnsnames.ora and sqlnet.ora files centrally located on a shared network drive. In every ORACLE_HOME/network/admin directory we have a symbolic link pointing to the centrally-located tnsnames.ora and sqlnet.ora files. I want to understand the consequences of these files becoming unavailable in the case of a loss of connectivity to the network share.

  1. Are these files used by any database processes, or only by client programs like SQL*Plus?
  2. If these files became unavailable, would existing client connections be affected, or only new connections?
  3. Would database links using the TNS aliases be broken while the tnsnames.ora file is unavailable?
Improve this question
5
  • 1
    From my own experience: 1. Yes they are accessed (I use SQL). 2. Existing connections will fail and new. 3. Database links will be broken.
    – jstexasdba
    Commented Aug 23, 2017 at 21:13
  • 1
    point 2 above: new connections will fail, NOT existing
    – sandman
    Commented Aug 24, 2017 at 9:16
  • Some parameters in sqlnet.ora file have default values, check them from Parameters for the sqlnet.ora File
    – Wernfried Domscheit
    Commented Aug 24, 2017 at 9:27
  • I'd reconsider doing this if I were you, and use Oracle Names Server instead. Allows for centralised management of TNS
    – Philᵀᴹ
    Commented Aug 24, 2017 at 12:00
  • 3
    @JSapkota gave a very thorough answer, but it boils down to this: tnsnames.ora is, in effect, a telephone directory for client processes. But keep in mind, the database itself is acting as a client when it acesses a db_link. Also, just like a telephone directory, it is used only when looking up a number to call. It does not need to be kept in hand and open while talking on the phone.
    – EdStevens
    Commented Aug 24, 2017 at 13:24

1 Answer 1

Reset to default
3

Are these files used by any database processes, or only by client programs like SQL*Plus?

Yes, these files are used by Oracle processes while connecting to the database, once the connection is established they don't need these files.

For example,

If you have specified the following parameter in sqlnet.ora file on server side.

tcp.validnode_checking = yes
tcp.invited_nodes =<list the valid nodes here>

The Oracle server process will check whether the incoming connection request is valid or not. Once it finds the node is valid it will proceed ahead and it no longer needs the sqlnet.ora file.

Similarly, Oracle client process will look up TNS Name on tnsnames.ora file for the connection string. Once it finds the connection details to make connection it also no longer needs the tnsnames.ora file.

If these files became unavailable, would existing client connections be affected, or only new connections?

Only the new connection will be impacted because existing connections have already used these files during the connection initiation.

Would database links using the TNS aliases be broken while the tnsnames.ora file is unavailable?

If you are middle of the transaction using that database link and the tnsnames.ora is missing then it will not impact the on going transaction. However, next session which uses the TNS Name in database link will fail.

ORA-12154: TNS:could not resolve the connect identifier specified

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.