Network Configuration Files in Oracle

Oracle uses three files (tnsnames.ora, listener.ora and sqlnet.ora) for network configuration. These are explained below:

1. tnsnames.ora file in oracle

TNS stands for Transparent Network Substrate. The "tnsnames.ora" file contains client side network configuration parameters. tnsnames.ora files contains the information which is used by the system to connect to oracle database.

Location of tnsnames.ora in oracle: By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory on UNIX operating systems and in the ORACLE_HOME\network\admin directory on Windows operating systems.

Format / Syntax of tnsnames.ora:

net_service_name=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = yourHostIPAddress)(PORT = portNumber))
    )
    (CONNECT_DATA =
      (SID = databaseName)
    )
  )

Explanation: Syntax contains:

PROTOCOL: Here TCP is used.
HOST IP ADDRESS: Where oracle is installed
PORTNO: Port number of server where oracle is installed
SID: SID stands for System Identifier. SID is the name of database.

Failover and Load-Balancing in tnsnames.ora

Consider the following example

net_service_name=
 (DESCRIPTION=
  (ADDRESS_LIST=
   (LOAD_BALANCE=on)
   (FAILOVER=off)
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress1)(PORT = portNumber1))
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress2)(PORT = portNumber2)))
  (ADDRESS_LIST=
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress3)(PORT = portNumber3))
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress4)(PORT = portNumber4)))
  (CONNECT_DATA=
   (SID = databaseName)))

In the above example, we specify the list of addresses which will take over another address in case of failure or overload.

Note: Typically you could have two tnsnames.ora files in the system, one that is set for the entire system and is called the system tnsnames.ora file, and a second file that is used by each user locally so that he can override the definitions dictated by the system tnsnames.ora file.

tnsping: tnsping is used to ping the tnsnames.ora file. If there is no error in tsnnames.ora, it will ping it otherwise error will be displayed.

Format of tnsping command: tsnping hostIPAddress

2. listener.ora file in oracle

The listerner.ora file contains server side network configuration parameters. It is found in the $ORACLE_HOME/network/admin" directory on the server.

Example:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = port))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = databaseName.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
      (SID_NAME = databaseName)
    )
  )

After the "listener.ora" file is amended the listener should be restarted or reloaded to allow the new configuation to take effect.

C:> lsnrctl help
C:> lsnrctl status
C:> lsnrctl stop
C:> lsnrctl start
C:> lsnrctl reload

3. sqlnet.ora file in oracle: The "sqlnet.ora" file contains client side network configuration parameters. It is present in the "$ORACLE_HOME/network/admin" It contains following settings:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

Above setting is necessary on Windows if OS authentication is required.

NAMES.DIRECTORY_PATH= (TNSNAMES, ,ONAMES, HOSTNAME)

A list of naming adaptors to be used when resolving a name. These will be used in the order listed. TNSNAMES = tnsnames.ora file, ONAMES = Oracle Names, HOSTNAME = use the hostname