Lesson 4	The tnsnames.ora file
Objective	Describe the components and functions of the tnsnames.ora file.

Components Functions of tnsnames.ora File

As we haved mentioned, the tnsnames.ora file is used to store all the details about remote databases. Essentially, the tnsnames.ora file pairs a TNS service name with the corresponding host name (or IP address), protocol, and database system ID (SID) name. For example, let us examine a small tnsnames.ora file:

cust=
(DESCRIPTION=
       (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=155.23.244.76)
       (PORT=1521)
       )       
       (CONNECT_DATA=
       (SID=customer)
       )
)
# Description for the order database
order=
(DESCRIPTION=
       (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=dilbert)
       (PORT=1527)
       )       
       (CONNECT_DATA=
       (SID=order)
       )
)

TNS service names

This is a simple tnsnames.ora file with two TNS service names:

cust and
order.

In the entry for the cust service name, you see the port number of the remote host, the host's IP address, the protocol of the network, and the name of the remote database (CUSTOMER).
In the tnsnames.ora file you may specify the IP address of either the host name or the remote server. Most DBAs prefer to use the host name and let Oracle look up the IP address in the hosts file. That way, they don't need to change the tnsnames.ora file if a server relocated to a new IP address.
Note that in the order entry above, the service name and the database name are the same, but this is not required. In some installations, the DBA will make the service name different from the database name. The flow between the client and the remote database can be summarized as follows:

When a SQL statement references a database link (rdb), the database link in the Oracle dictionary has the TNS name for the remote database.
This TNS name is then located in the tnsnames.ora file, and a connection is established to the remote database.

The following series of images illustrates this in greater detail.

The following images illustrate the flow between the client and the remote database in establishing a remote connection.

1) The SQL statement select * from customer@rd specified rdb as a DATABASE LINK — 1) The SQL statement
select * from customer@rdb
specified rdb as a DATABASE LINK

2) The link name rdb is looked up in the Oracle dictionary DBA_DB_LINK table to get the remote USER ID, and the TNS service name — 2) The link name rdb is looked up in the Oracle dictionary DBA_DB_LINK table to get the remote USER ID, PASSWORD, and the TNS service name

3) The TNS service name is used as input to the tnsnames.ora.file. The tnsnames.ora file provides the PROTOCOL, the HOST_NAME, and the ORACLE_SID.

4) The HOST_NAME is used as input to the UNIX hosts file, where the IP_ADDRESS is collected.

5) The IP_ADDRESS, PROTOCOL, ORACLE_SID, USER_ID, PASSWORD are packaged and sent as a network packet to the remote Oracle listener

The SQL statement select * from customer@rd specified rdb as a DATABASE LINK
The link name rdb is looked up in the Oracle dictionary DBA_DB_LINK table to get the remote USER ID, PASSWORD, and the TNS service name
The TNS service name is used as input to the tnsnames.ora.file. The tnsnames.ora file provides the PROTOCOL, the HOST_NAME, and the ORACLE_SID.
The HOST_NAME is used as input to the UNIX hosts file, where the IP_ADDRESS is collected.
The IP_ADDRESS, PROTOCOL, ORACLE_SID, USER_ID, PASSWORD are packaged and sent as a network packet to the remote Oracle listener

For networks that have more than one database with the same name, we need the ability to identify the services uniquely at all remote locations. This is the case with our project where both Raleigh and Colorado Springs share the same order processing schema and the same local database name. In large Oracle shops, a "master" tnsnames.ora file is created for use by all Oracle clients. This tnsnames.ora file is present on all servers and clients, and the DBA maintains the master copy, distributing the file to all clients whenever a new database is added or deleted. In the next lesson, we will take a closer look at the protocol.ora file.