Using the Information Technology Services TNS Names Service

Overview

This document discusses how to use the Information Technology Services TNS names service, which provides Oracle database connectivity information (TNS, Transparent Network Substrate) over the network. Workstations can be configured to connect to and automatically use the centrally managed ITS database TNS names instead of locally managing the information themselves. Databases TNS entries not listed in the ITS TNS names service can still be used and referenced from a local tnsnames.ora file.

Oracle Database Connectivity – TNS Names

When an Oracle client makes a connection to an Oracle database, it needs to know the database name and various other connection related details such as host names, ports, service names, etc. Most Oracle clients store this information in a TNS names file, called tnsnames.ora. A TNS names entry for a database may look something like this:

Using the ITS TNS Names Service

Unless otherwise configured, this tnsnames.ora file will be stored and managed locally on a workstation.  If a database’s connectivity information changes, or a new database is added, then the tnsnames.ora file will need to be manually updated.  However, if you connect to ITS managed databases, you can avoid having to manage a local tnsnames.ora file and instead use the ITS TNS names service available over the network.  It’s convenient, saves time, and removes the need to manage a local tnsnames.ora file. Using the ITS TNS names service does not preclude you from also using a local tnsnames.ora file.

The ITS TNS names service information is stored in the ITS LDAP (directory) system.  If you need assistance with using the ITS TNS Names Service, please contact the ITS DBA Support Team at dbaadmin@calpoly.edu.

How to Configure Workstations Clients to use the ITS TNS Names Service

The following instructions show how to setup an Oracle client to use the ITS TNS names service. This configuration will have the client reference the ITS TNS names service first, and secondly, any information you may have in your workstation’s local TNS information source file, tnsnames.ora, if it exists. If you’d like to reverse the look-up order, see the notes at the end of this section. The instructions have you modify your ldap.ora and sqlnet.ora configuration files. These configuration files are regular text files that have an .ora file extension instead of .txt.

1. Go to the directory where your workstation’s tnsnames.ora file is stored.*
2. Make backup file copies: In the directory, you may also see several files other than the tnsnames.ora file. Backup all the files to an alternate location first in case you need to revert the changes we’re about to make.
3. If it doesn’t already exist, create an ldap.ora file and put the following data in it:
   
   # ldap.ora

   DIRECTORY_SERVERS= (ldap.calpoly.edu:389:636)

   DEFAULT_ADMIN_CONTEXT = "cn=tnsnames,cn=apps,dc=calpoly,dc=edu"

   DIRECTORY_SERVER_TYPE = OID

4. Create the sqlnet.ora file if it doesn’t already exist. If it does, add the following information**:
   
   # sqlnet.ora

   NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES,EZCONNECT)

5. The configuration is complete. You can test the changes by trying a database connection to a database whose TNS information is available in the ITS TNS names service. If you happen to have an entry for the same database in your local tnsnames.ora file, temporarily remove it from your local file first and re-enter it after testing the ITS TNS names service.

*On a Windows workstation, your tnsnames.ora file might be located at:

C:\oracle\product\<version>\client\NETWORK\ADMIN

If the tnsnames.ora file isn’t there, then check to see if the TNS_ADMIN environment variable is set. To check the variable, open the Windows control panel (or right-click on My Computer and choose “Properties”). Click on the “Advanced” tab in the System Properties window and the click on the “Environmental Variables” button.  If the TNS_ADMIN variable is set, check that directory to locate your tnsnames.ora file.

**The sqlnet.ora example configuration shown above will force your client to reference the ITS TNS names service first, looking for the database TNS entry your client is requesting.  If the TNS name is not found in the ITS TNS names service, or if the service lookup fails, it will then look through your workstation’s local tnsnames.ora file, if it exists. If you’d like your client to look at your local tnsnames.ora file first, then switch the NAMES.DIRECTORY_PATH order so that TNSNAMES is first and LDAP is second.

If after reviewing these instructions you are still having difficulty with LDAP for TNS name resolution, please contact: ITS Database Administrators dbaadmin@calpoly.edu.