How to Configure Listener and tnsnames.ora Network Files in Oracle

December 15, 2018

After Oracle installation you need to configure listener.ora and tnsnames.ora network files. In Oracle three most important network files are : listener.ora, tnsnames.ora and sqlnet.ora files. You can also modify these files if there is any changes in network configurations like IP address, hostname or any other database level changes like port number service name alias etc.

listener.ora nad tnsnames.ora file configurations

How to configure listener.ora file

The “listerner.ora” file located under
“$ORACLE_HOME/network/admin”  directory is a server side network configuration file. It has the default name “LISTENER” which listen for TCP connections on the default port 1521. Below are the sample example of listener.ora file. Please make the necessary changes before using this file for your environment.  

NOTE: If listener and tnsnames.ora file doesn’t exist in above location then please create it with below name based on the OS you are using like Linux and Windows.

1: tnsnames.ora

2: listener.ora 

[oracle@orahow admin]$ more listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host-ip-address)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = extproc)
      (ORACLE_HOME = /data/oracle/app/product/11.2.0.4/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = db11g)
      (ORACLE_HOME = /data/oracle/app/product/11.2.0.4/dbhome_1)
      (SID_NAME = db11g)
    )
  )
INBOUND_CONNECT_TIMEOUT_LISTENER =0

After making necessary changes in the listener file you need to restart or reload the listener to allow new configurations to take place. 

Command to Start, Stop and Reload the Listener 
$ lsnrctl start
$ lsnrctl stop
$ lsnrctl reload

How to configure tnsnames.ora file

The “tnsnames.ora” file located under
“$ORACLE_HOME/network/admin”  directory is a server side network configuration file. Here is an example of a “tnsnames.ora” file.

DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-Server-ip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db11g)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
    )
    (CONNECT_DATA =
      (SID = extproc)
      (PRESENTATION = RO)
    )
  )

The alias name “DB11G” used at the start of the entry can be any name you want. It doesn’t have to match the name of the instance or service. You can use tnsping utility to check the tns configuration for the particular sid.

Must Read: