/build/static/layout/Breadcrumb_cap_w.png

How R U dealing w/ TNSNAMES.ORA?

Aside from its usual removal from the Oracle client to keep it from being overwritten, how is everyone dealing with Oracle's TNSNAMES.ORA?

The options I'm weighing are:

1) Keep it as a separate package, deploy upgrades as necessary.
PROS: developers who need to maintain it themselves can be simply left out of the distribution.
CONS: I'll be constantly producing upgrades for it.

2) Treat it as an INI file or custom action it? (Not quite sure how yet) i.e. search for the required test, add it if its not present.
PROS: Each package can update TNSNAMES.ORA as necessry
CONS: Can become a mess of obsolete entries. No self repair in the TNSNAMES.ORA file.

Does anyone use another solution that I'm not considering yet?

0 Comments   [ + ] Show comments

Answers (20)

Posted by: kthardin 19 years ago
Yellow Belt
2
Generally we use the IFILE parameter in the TNSNAMES.ORA file in order to differentiate between applications and to keep the TNSNAMES file manageable.

The TNSNAMES.ORA file is composed of this line only:

IFILE = "C:\Program Files\Oracle\TNSNAMES\TNSNAMES_MASTER.ORA"

All this does is redirect enquries from the original TNSNAMES file to the new one, TNSNAMES_MASTER.ORA You can call it whatever you wish and place the new ORA file wherever you wish.

In TNSNAMES_MASTER.ORA is where we keep the list of applications in this manner:

IFILE = "C:\Program Files\Oracle\TNSNAMES\Your App Name Here.ORA"

Yes, this another redirector. You can have as many redirectors in the new TNSNAMES_MASTER file that you want. Within the "Your App name Here.ORA" file is where you keep the actual TNS connection information for your application. Depending on your naming convention, that name can be anything you want in order to differentiate between applications.

Yes, we could have placed all the redirectors within the original TNSNAMES.ORA file, but by creating a new 'master' and having that hold all the redirectors in a directory we create, it keeps it manageable from both a programming perspective and a managerial perspective (so those with lesser knowledge can go look in one place for everything and not have to worry about any OTHER files in their way).
Posted by: d_fisher 19 years ago
Senior Yellow Belt
0
Some applications will use a tnsnames.ora that is in the application directory over any other copy on the PC. This gives you an application specific tnsnames.ora file. It has not effect on other applications. I have a few applications that have been deployed extremely successfully using this method.

Now if I can only get our Oracle DBAs to get away from this outdated method.

Doug
Posted by: plangton 19 years ago
Second Degree Blue Belt
0
Hi VikingLoki,

It doesn't have to be a package at all. One place I worked at had the TNSNAMES.ORA in a public share - the clients who required it would copy it if newer, the TNSNAMES.ORA was a global one that had all required settings in it. That setup worked well.

My problem is more the Paradox database configuration files. What a nightmare!

Rgds

Paul
Posted by: VikingLoki 19 years ago
Second Degree Brown Belt
0
Hmmm... I like the TNSNAMES.ORA in the application directory. Is that a specific function of the application or can it be forced? I suppose that isn't in the scope of .LOCAL isolation, right?
Posted by: d_fisher 19 years ago
Senior Yellow Belt
0
Is that a specific function of the application or can it be forced?

I think it is similiar to how the hosts file will override dns.

I happen to stumble across a webpage some time back that mentioned this technique. It has worked for almost every application I have tried it on. To test it out make a bogus tnsnames.ora file and put it in the traditional location. Next create a valid file and put it in the applicaiton directory. If it works you are golden.

Doug
Posted by: VikingLoki 19 years ago
Second Degree Brown Belt
0
If it works you are golden.
I'm skeptical... Falls into the "Too good to be true" catagory. [:)]

I'll try it out though, and damn I hope you're right!!
Posted by: damatz5 19 years ago
Senior Yellow Belt
0
I also worked at a site where we had the tnsnames.ora on a share. The kix script would check if the latest copy was on the users machine everytime they logged on.

The only problem is we had users who did not log off and log on, thats why we did a nightly reboot.
Posted by: VikingLoki 19 years ago
Second Degree Brown Belt
0
Next create a valid file and put it in the applicaiton directory. If it works you are golden.
Nope. No dice. Didn't work. Drat. Can you find that article in case there was something else in it?
Posted by: aogilmor 19 years ago
9th Degree Black Belt
0
Some sort of "centralized" tnsnames is highly desirable if not absolutely necessary in any org with more than 1 database.

Definitely should be separate from the package, and either copied down or somehow replicated. Of course any app that uses its own tnsnames should be good, provided Oracle networking is solid (and it usually is).

Developers sometimes have a hard time grasping the concept of separating the tnsnames for the database they want to work with, from the regular installation - they also tend to want everything installed, including the database utilities, sql*Plus, networking utils and all of the shortcuts. No! just tell me what you need for THIS APPLICATION to run! We're not trying to recreate your development environment for the application users!

Another thing - the oracle plague is having many diffefent installations, when really what DBAs and developers should do is to find the minimun client needed to run all the Oracle applications out there, and stick with that. Otherwise you end up with a mess.

End of rant

[:@]

O
Posted by: MSIMaker 19 years ago
2nd Degree Black Belt
0
We remove tnsnames.ora from tnhe Oracle install and install it as a seperate package called Oracle config. Our users are not allowed to edit the file as this is the DBA's job.

Removing it from the Oracle package allows us to just deploy and redeplot the config package whenever there is a change required and also forces the business to advise us when a new Oracle server instance is created.

We do this for all DSN's as well.....SQL, DB2, etc. Each has its own config package.
Posted by: VikingLoki 19 years ago
Second Degree Brown Belt
0
The Oracle Config package is the way we were leaning... now we find that not even our DBA's know exactly what should be in there. Apparently everyone is quite accustomed to the "Fly by the seat of your pants" approach to the point that they don't even realize that TNSNAMES just shouldn't be this complicated!
Posted by: MSIMaker 19 years ago
2nd Degree Black Belt
0
VikingLoki we had the same problem until we created the config package and sent it out to the DBA's so they could see what we had colledted so far. We are on version 16 of the package....so you see how much it changes as you progress in rolling out to the enterprise.

Imagine uninstalling Oracle 16 times to just add to the tnsnames.ora file.....lol
Posted by: DV150 19 years ago
Yellow Belt
0
Hi,
Not sure if this helps but the solution I used was to remove the TNSNames.ora file from the oracle installs completely. I see it as a shared resource for our environment in terms of applications and as such shouldn't be put down on a per application basis.
I then arranged a central store location on a server that all users had read access to. Our DBAs being the only people with full access rights to this file. They are responsible for maintaining the ora file and as it is no longer included in any of the packages...I don’t have to deal with any ora related queries! Also has the added benefit of keeping everything nice and central for your DBAs - they know exactly which servers your users are pointing to at a glance...so they will appreciate it!

The next stage is to define the TNSNames.ora location. I did this by creating HKCU\Environment\TNS_Admin and setting it to the folder name containing the ora file on the network.
I chose to do this with a logon script associated with the Oracle group. We use AD and I figured it was best to associate the actual TNS setting with Oracle as opposed to any specific application. (i.e. if a user has oracle, they run this script at logon)
Dependant on your environment you will probably want to change this last bit slightly...it doesn't really matter as long as you get the reg key down! Bare in mind you will need to log off and back on again to pick this environment setting up if you are setting it within an MSI.
Posted by: VikingLoki 19 years ago
Second Degree Brown Belt
0
kthardin -

Your solution sounds interesting... IF you can have multiple IFILE entries. Does IFILE work that way?
Posted by: MSIMaker 19 years ago
2nd Degree Black Belt
0
DV150 I like the idea and I might look into it but I don't think it will scale well for me. It might be ok for a small environment but not a big one.
Posted by: jonasm 19 years ago
Blue Belt
0
Thank you kthardin for your information about the IFILE-setting.

In my case I added IFILE = "P:\Oracle\tnsnames.ora" to the local tnsnames.ora, and as long as the user has the network connection P:\ (everyone does), it works great!
Now I only need to change the tnsnames-file at one place, if a databes changes servername or kinda.

Two points for you kthardin!

/Jonas
Posted by: mohd 19 years ago
Senior Yellow Belt
0
I have a method which i can share
1.Place the TNSNAMES.ORA on a distribution server or common share in which users having read access and only DBA will have write access to update the file.
2.Deploy a package to all users machines in which it will add a script which will run every time the user logs in to the machine [The Local Machine RUN Key].
3.The script should compare the date of the file on the local machine with the file on the distribution server, if the file in the local machine is older, then it should replace it with the file from the distribution server. [WiseScript Can Be Used]
Posted by: VikingLoki 19 years ago
Second Degree Brown Belt
0
Well, here's the decision we've come up with for TNSNAMES.ORA.

Placing it on a network share was rejected as it's a single point of failure for all database connectivity. Too risky.

As a rule, TNSNAMES are always referenced via DNS, never direct IP address. Changes to server locations are handled via DNS.

We are including a TNSNAMES.ORA in the working directory of each packaged app, containing only entries for that particular application. This works most of the time and changes to the entries are unlikely to occurr outside of a client update.

For the applications where placing a TNSNAMES.ORA in the working directory doesn't work (which should be few) a default TNSNAMES.ORA file will be packaged as a separate app, version controlled which will be "upgraded" on an as-needed basis.
Posted by: Radia709 19 years ago
Senior Yellow Belt
0
VikingLoki,

Yes you can have multiple IFILE entries in the TNS_MASTER that will point to "your apps.ora" file. The TNS_MASTER will in fact contain all the IFILE entries redirecting them to "your apps.ora" via appeding the entries to the top or the bottom of the master file pending how you manage this file during new application installs.


ORIGINAL: VikingLoki

kthardin -

Your solution sounds interesting... IF you can have multiple IFILE entries. Does IFILE work that way?
Posted by: cazf1 19 years ago
Yellow Belt
0
We created in the registry a new key called TNS_ADMIN under hklm\software\oracle\home0 and the value is a network path (\\server\share\oracle)where you can put your files tnsnames.ora so we can maintain it easy for all our users at the same time.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ