Oracle transparent network substrate (TNS)
The Oracle Transparent Network Substrate
(TNS) allows for simple interdatabase communications. To
implement TNS, Oracle has built a management layer over the
standard network topology.
To implement Oracle*Net, several Oracle
files must be present on the server:
TSNAMES.ORA - This file defines
incoming database requests. It contains all database names
(sid's) running on the processor. When a new database is
added to a box, /etc/tnsnames.ora must be updated. This
file also describes each domain name, with protocol, host,
and port information.
LISTNER.ORA - A list of destinations
for outgoing database connections. When a new destination
database is added to a box, it must be added to
/etc/listener.ora, and the listener must be bounced.
In addition, TNS uses several server
files to resolve host and service names. On UNIX, these files
/etc/HOSTS - lists all of the host
names and their corresponding IP addresses.
/etc/SERVICES - lists the SQL*Net
services and their IP addresses.
Now let’s take a look at how a
connection is made:
Step 1 – Here the request is made in
the SQL with a database link name
Step 2 – The Oracle dictionary takes
the link names and supplies the TNS service name, the USER
ID and the password.
Step 3 – The service name is
looked-up in the tnsnames.ora file, and the host name, port
number and protocol are supplied.
Step 4 – The host name is passed to
the /etc/hosts file where the host name is used to get the
Step 5 – The network packet is
shipped to the remote database using the IP address port
number and protocol
Step 6 – The remote listener
intercepts the request and bequeaths a UNIX process.
Step 7 – The UNIX process connects
to the remote database using the SID, User ID and password.
Application Connection with SQL*Net
Connections to remote databases can be
made by specifying either "service names" or "connect strings".
Connect strings originated with SQL*Net version 1, where the
full connection is specified. In example 2 below, the "t:"
means a TCP/IP connection, "host:" is the name of the remote
processor, and "database:" is the name of the databases on that
1. Connect with a service name:
(SQL*Net version 2 & Oracle*Net)
2. Connect with a server connect
string: (SQL*Net version 1)
These connect strings can be stored in
the Oracle database dictionary for use by distributed SQL. They
are created with the “create database link” command, and are
stored in the Remote DBA_USER-DBLINKS meta table:
create public database link ny_emp