Tuesday, June 3, 2008

central tnsnames.ora

Introduction

For hassle-free management of enterprise wide databases connection information, the connect descriptors can be stored in a centralized repository. There are several solutions to implement a centralized repository ranging from the basic tnsnames.ora that is stored on a NFS to the advanced LDAP complaint directory services implementation through OID (Oracle Internet Directory).

Problem

Several of our Oracle developers and end users rely on 3rd party tools like TOAD, SQL*Navigator to communicate with Oracle databases. We have put the tnsnames.ora containing all the oracle databases connection information for these utilities on a shared network drive in windows. This solves the problem for clients using Windows. However our system administrators do not allow NFS access on Unix servers. Due to this restriction, it has become a hassle to maintain a separate tnsnames.ora in every Unix server that hosts oracle installations(client or server). If we create a new database or change an existing database's connection settings, we have to update the tnsnames.ora on all other Unix servers with this new connection information.

Solution
While discussing in Oracle forums, i stumbled upon a specialized tool tnsManager built by Andrew Barry that acts as a simple LDAP compliant server to serve just TNS information. It is the kind of solution that i was looking for solving the problem. It is quite easy to use and has a very small footprint. The 'HOW TO' guide is here.

Conclusion
We will be implementing Oracle Identity Management (that will contain OID) by the end of the year that provides a much more sophisticated mechanism for organizing and managing centralized directory services like applications login accounts, centrally managing database connection information etc. The tnsManager utility can be a good alternative which will satisfy our basic needs for storing just the database connection information in a central tnsnames.ora until the full blown Identify Management project is implemented. Best of all its just one executable that Andrew supplies and it takes 5 minutes to install and get it going ! As far as a DBA is concerned, all he or she has to do is edit the central tnsnames.ora that is read by the tnsManager utility whenever an existing database connection settings are changed or a new database is added in the enterprise. No extra configuration necessary.

2 comments:

Anonymous said...

Srinivas - I also came accross this tool when I was looking for a solution to a similar problem. Tell me, do you not feel reluctant to download and install these 3rd party applications? How is the product working now a year after your original installation?

Srinivas Ramineni said...

We dont allow access to Production from third party tools. But since we have around 20+ dev systems, we could take the risk. The tool worked fine. We used it only for providing access to dev and test systems.

But in November last year, we migrated to the LDAP mechanism after we went live with the SSO project. So we used the tool for around 5 months last year.

Related Posts Plugin for WordPress, Blogger...