Firstly, tnsnames.ora is the file used for name resolution of the client application to the Database.
Its important to know the fundamentals of Network layers, as we would have some files (listener.ora, sqlnet.ora, tnsnames.ora) which would use some concepts of Networking in its operation
Layered Approach Model is a model in which each layer addresses a process. Each process procides required effective communication. It tells what must be done, who wwill do, what order, how to relate.
Application Layer(A) - Provides user interface.
Presentation Layer(P) - Provides data, handles processing such as encryption. It provides coding, compression, encryption.
Sessions Layer(S) - Keeps different applications data separate. NFS, RPC, SQL, Apple Talk session Protocols are protocols used by this layer. Simple, Half-duplex, Full Duplex.
Transport Layer (T) - Provides End-to-End logical connection, reliable and unreliable delivery. Services present in here, segments and reassembles data from upper layer applications unite onto the same data stream. Services present in Transport layer, segments and reassembles data from upper layer applications unite onto same data stream. Establishes a logical connection between host and destination on a internetwork. TCP, UDP are the protocols used.
Network Layer (N) - Provides logical addressing, which routers use for path determination. Transferring packets between devices that are not locally attached. When a packet is received at a Router interface -> checked in Routing Table -> If not found Drop -> If found packet routes till the exit. If the interface is found then it is framed to the local network.
There are 2 types of packets
Data - User data and protocos
Route Update - Update neighbor routers & protocols(RIP, OSPF, EIGRP)
Data Link Layer(D) - combines packets, converts to bytes to frames and vice-versa. Provides access to media using MAC Address, performs error detection.
Physical Layer(L) - moves bits between devices, specifies voltage, wire speed and pin out cables based on Physical Topology.
A - P - S - How applications within end work stations communicate with each other, with users and between hosts
T - N - D - L - How data is transmitted end to end
Advantages of this model
- Complex into simple manageable layers
- Only one layer can be changed
- Define standard interface
Here is a sample of
listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))
)
)
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1525))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/my10g/orcl)
(PROGRAM = extproc)
)
)
In
SQL>show parameters
Note:- db_name, db_domain parameters, together constitute GLOBAL NAME
SQL>SELECT * FROM GLOBAL_NAME;
db_name.db_domain
To recognize a listener on a local network
Here is sample of
tnsnames.ora
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
LISTENER2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
A listener status could more or like look like this. We would observe TCP being the protocol, also IPC is used if you are not working a Development Environment and you have a development instance and you are accessing always locally interactively.
LSNRCTL> status
Connecting to
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
~~~~ (parts are cut)
Listener Parameter File
/u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File
/u01/app/oracle/diag/tnslsnr/SERVER_NAME/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=X.X.X.X)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=X.X.X.X)(PORT=1521)))
