Description
Tnsnames.ora is a file which is used by oracle client to connect to oracle server. As we know when if we want to connect to oracle database from remote machine we have to start sqlplus using connect identifier as shown below:
C:\>sqlplus
SQL*Plus: Release 10.2.0.1 - Production on Wed Mar 03 15:28:03 2011
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: scott/tiger@ora
Where scott is the schema/user name,
tiger is the password
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
This connect identifier i.e ora in above example is resolved by sqlplus client by looking at tnsnames.ora file which is placed at the following location:
Location
$ORACLE_HOME/network/admin
However it can be placed at any given location and it’s path can be set in $TNS_ADMIN environment variable for UNIX systems. The tnsnames.ora provides the network resolution for connect identifier used by sqlplus clients and application servers.
TNSNAMES.ora contains entries as shown
TNSNAMES.ora contains entries as shown
TNSNAMES.ORA:
=============
=============
V805.US.ORACLE .COM =
(DESCRI PTION =
(ADDRE SS_LIST =
(ADDRE SS =
(PROTOCOL = TCP)
(HOST = DAN.US.ORACLE.COM)
(PORT = 1521)
)
(
CONNECT_DATA = (SID=ORCL))
)
(DE
(AD
(AD
(PROTOCOL = TCP)
(HOST = DAN.US.ORACLE.COM)
(PORT = 1521)
)
(
CONNECT_DATA = (SID=ORCL))
)
LISTENER.ORA:
=============
=============
LISTENER=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL= TCP)
(HOST = DAN.US.ORACLE.COM)
(PORT= 1521)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora.oracle.com)
(SID_NAME = ORA)
)
)
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL= TCP)
(HOST = DAN.US.ORACLE.COM)
(PORT= 1521)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora.oracle.com)
(SID_NAME = ORA)
)
)
As shown above the entries in TNSNAMES correspond to listener entries at the server side. Address_list and Address in tnsnames.ora specifies the address of the listener which listens to incoming client requests for the given database. Also the connect_data specifies the service name i.e Sid of the database specified at the server.
