Oracle TNS Names Configuration

Tuesday, July 19, 2005

4:12 PM

 

A question I am frequently asked is how do I set up a PC's TNSNAMES.ORA file so that it contains an entry pointing at a particular oracle database instance.

 

Adding an ODBC DSN is out of the scope of this document, but I will mention the following:

Once this configuration is done you can use the ODBC Administrator to add an Oracle DSN.  In the Oracle ODBC Driver Configuration window, in the TNS Name Service text box, you can enter the net service name as you entered it in the setup below, or you can enter the fully qualified net service name as it appears in the tnsnames.ora file.

 

Ok, here is the configuration:

 

Please note that the following screen shots may slightly vary if this is not the first name service entry you have added.

 

Run the Oracle Net Configuration Assistant from:

 

Start Menu / All Programs / OraHome90 / Configuration and Migration Tools / Net Configuration Assistant.   (OraHome90 may be named something different.)

 

Select "Local Net Service Name configuration", then "Next".

 

 

Select "Add", then "Next".

 

 

Select "Oracle8i or later database or service", then "Next".

 

 

Enter the name of the service.  This will most likely need to be the fully qualified service name of the oracle service instance. (Not necessarily the name of the machine, because more than one oracle database service instance can run on a single machine.)

 

If you do not know the service name, one way to find it is to run the "Oracle Net Manager" on the server and expand "Oracle Net Configuration", then expand "Local", then expand "Service Naming".  This will then contain a list of service names.

 

 

Select "TCP", then "Next".

 

 

Enter the host name of the computer that the oracle database service is running on.  This can be the IP address, the NetBIOS computer name (like "computer1"), or fully qualified domain name of the computer (like "computer1.mydomain.com").

 

 

Select "Yes, perform a test", then "Next".

 

 

Because the first test is done using the "scott" and "tiger" username and password, it will likely fail and you must select "Change Login".

 

 

Change the default username and password to a valid username and password for the database service you entered.  Then click "OK".

 

 

After changing the login, the test will automatically rerun and hopefully you will have success.  If so, select "Next".

 

 

Enter any net service name you like here which will be used as a reference to the configuration you just set up.  Then select "Next".

 

 

 

At this point your configuration has been saved to your TNSNAMES.ORA which will be somewhere like:  C:\Program Files\Oracle\ora90\network\admin\tnsnames.ora

 

And will look something like:

 

# TNSNAMES.ORA Network Configuration File: C:\progra~1\oracle\ora90\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

 

MYSERVICENAME.MYDOMAIN.COM =

(DESCRIPTION =

(ADDRESS LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = computer1)(PORT = 1521))

)

(CONNECT DATA =

(SERVICE NAME = myoracleservice.mydomain.com)

)

)

 

Select "Next".

 

Select "Finish" and the configuration application will close.