Understanding tnsnames.ora and listener.ora in depth

admin

Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration.Oracle acts as a Client-Server software. That means you have two main ends, the Client who must somehow get to the server, and the server who must accept connection requests from clients.
When the client attempts to connect to the server, you give him a “Service”. The service is mainly 4 info: Host (for the server address), Protocol (the language to speak with the server), Port (exactly at where of the server to hit to get the connection) and SID (name of the instance/database). Those 4 informations are usually and by default locates in a tnsnames.ora file under $ORACLE_HOME/network/admin. This is called: Transparent Network Substrate.

On the server side you must have one or more active server processes (daemons) who are waiting for the client to attempt a connection. Those services are called Listeners. In most cases only one listener is required. This listener is configured via a listener.ora file under $ORACLE_HOME/network/admin. This file includes the listener process configuration, mainly: Host for which it listens, protocol and port, list of SIDs for which the process can establish a connection.
Furthermore, if you are connecting to the database on the server computer, and it is the default database, you do not need to tell where you are going to connect when you are the client. For default Database you do not even need to have a listener when you are the server and the client both.

When you try to connect through SQL Plus(scott/tiger@connectstring) what Oracle does is it reads the tnsnames.ora file which is located in the client machine in $ORACLE_HOME/network/admin/tnsnames.ora.The tnsnames.ora file lists the name of the connect strings where the client has the capability to connect to.When it finds a match it reads the name of the server(where it should connect to),the protocol it should use(TCP) and the port on which a listener is listening (default 1521).When it finds a match it goes to the relevant server and knocks on the port 1521 where the listener is waiting to hear incoming calls. Now the listener checks it own file $ORACLE_HOME/network/admin/listener.ora which is located in the server itself and matches it with the incoming call request. If it matches, you are given an entry for the database to connect and a session is created for you.

So, tnsnames.ora contains the entries that whom and how I will connect as a client; whereas listener.ora is for how I will be connected by the outside clients.

Commands to maintain the listener,

$ lsnrctl stop <listener_name>

$ lsnrctl start <listener_name>

$ lsnrctl reload <listener_name>  —-do not affect the existing connections in the listener; it only registers or deregisters, the newly added or deleted entry in listener.ora file, in the listener server process respectively.

The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server. Profiles are stored and implemented using this file. The database server can be configured with access control parameters in the sqlnet.ora file. These parameters specify whether clients are allowed or denied access based on the protocol.

The sqlnet.ora file is not necessarily created during the Oracle install.  In fact, many Oracle networking products can run without the sqlnet.ora file because they use the default options

tnsnames.ora:

ORCL.WORLD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL.WORLD)

)

)

listener.ora:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))

)

)

)



SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORCL.WORLD)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0)

(SID_NAME = ORCL)

)

)