tnsnames.ora

A tnsnames.ora file maps net service names to connect descriptors.

In simple words, tnsnames.ora file provides the ability to reference oracle databases by a simple alias.

It can be found in the ORACLE_HOME/network/admin or ORACLE_HOME/net80/admin directory on the client. This file will also be present on the server if client style connections are used on the server itself.

The net service name thus becomes a (most likely shorter and more readable) alias for the somewhat cumbersome net service name.

In the following example, the «text» after the equal sign is the connect descriptor while the text before the equal sign (net_service_name) is the net service name.

net_service_name=

 (DESCRIPTION=

   (ADDRESS=(protocol_address_information))

   (CONNECT_DATA=

     (SERVICE_NAME=service_name)))

For example:

ORA10 =

  (DESCRIPTION = (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME=ORA10)

    )

  )

sqlnet.ora

The sqlnet.ora file contains client side network configuration parameters.

The sqlnet.ora file enables you to:

• Specify the client domain to append to unqualified names
• Prioritize naming methods
• Enable logging and tracing features
• Route connections through specific processes
• Configure parameters for external naming
• Configure Oracle Advanced Security
• Use protocol-specific parameters to restrict access to the database

It can be found in the ORACLE_HOME/network/admin or ORACLE_HOME/net80/admin directory on the client.

Here is an example of an sqlnet.ora file : 

# SQLNET.ORA Network Configuration File 

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

NAMES.DEFAULT_DOMAIN = WORLD

listener.ora

Listener.ora file consists of the listener configurations like,

• Listener's name
• Protocol addresses that it is accepting connection requests on
• Services it is listening
• Control parameters

By default, the listener.ora file is located in the $ORACLE_HOME/network/admin

A Sample Listener.ora file

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      )

      (ADDRESS_LIST =

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

      )

    )

  )

Example:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:Oracle)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = ORCL.WORLD)

      (ORACLE_HOME = C:Oracle)

      (SID_NAME = ORCL)

    )

  )