How to I offer network tnsnames.ora redundancy?

Posted on 2009-02-19
Medium Priority
Last Modified: 2013-11-16
Our users have a system variable set for tns_admin to point to a tnsnames.ora file on a network drive.  If we lose that server, or if the file is corrupt, removed, etc, resolution to oracle databases fails.  Is there a way that I can set up redundancy/failover so that in the event either the server is gone, or the file is corrupt or does not have the proper database resolution included, to force the client to look to a second location/file?
Question by:scheck_c
LVL 48

Accepted Solution

schwertner earned 672 total points
ID: 23691082
As per
On Windows NT/2000, the precedence in which Oracle Net
Configuration files are resolved is:

1. Oracle Net files in present working directory (PWD/CWD)
2. TNS_ADMIN defined sessionally or by user-defined script
3. TNS_ADMIN defined as a global environment variable
4. TNS_ADMIN defined in the registry
5. Oracle Net files in %ORACLE_HOME/network|net80\admin
(Oracle default location)

So you can also put TNS_ADMIN in the Registry
LVL 23

Assisted Solution

David earned 672 total points
ID: 23691572
In this scenerio, then, your users would have to a) set TNS_ADMIN to an alternate string for each session, or b) reset the client's global variable and reboot; resetting the variable once the problem was resolved.
Other options include allowing EZCONNECT strings, and to use the Oracle Internet Directory (OID).
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 672 total points
ID: 23692114
I would keep the TNS_ADMIN path, but in the login script of the users copy the network version to the local path ...
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 23

Expert Comment

ID: 23692279
Nice....  it keeps the client updated.

Author Comment

ID: 23726925

 a) set TNS_ADMIN to an alternate string for each session

What do you mean by this exactly?  I'm looking for a way to have the client look at multiple network locations for automatic failover should one tns_admin location be unavaialble.
LVL 23

Expert Comment

ID: 23727095
I am thinking that in your context {23691572}, if the global tnsname was not available, your login script or shell might ask the session user to specifiy an alternate.  Something along the logic of: if TNS_ADMIN is null then set TNS_ADMIN=x.  However, schwertner's comment is elegant and along that line.  In the login, attempt to copy from your primary source to somewhere on the PC.  The PC TNS_ADMIN remains set to the local folder.  That way, the PC copy is always either current or no more than one change off.
I'm not sure that's any clearer, but my carpool is leaving very soon.  Let me know Wednesday what you think of the copy approach.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question