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.