How to I offer network tnsnames.ora redundancy?

Posted on 2009-02-19
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 47

    Accepted Solution

    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

    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 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    I would keep the TNS_ADMIN path, but in the login script of the users copy the network version to the local path ...
    LVL 23

    Expert Comment

    Nice....  it keeps the client updated.

    Author Comment


     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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now