oracle network(listener,tnsnames,sqlnet,TNS_ADMIN)

Oracle Network Configuration

In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora and sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration.

Listener.ora

Tnsnames.ora

Sqlnet.ora

The tnsnames.ora and listener.ora are the two most important configuration files when it comes into configuring the listener.

Listener.ora:

The listerner.ora file contains server side network configuration parameters. It can be found in the ‘$ORACLE_HOME/network/admin’ directory on the server. Here is an example of a listener.ora file.

The oracle listener is cause of many issues when attempting to configure it for use. Because the listener is usually configured and then forgotten about it’s sometimes overlooked and only learnt about when there are errors. So I thought it would be good idea to cover some basics along with some error messages that you might come across which involve the listener and configuration of the listener.ora and tnsnames.ora files.

How the listener works:

The listener.ora file is resides in server side which allows you to connect to the server from client side. It listens for client side requests on an IP address that you specify in the listener.ora file on the server, using a default port of 1521. The port is also configurable. Once you make a request to connect to the database from your client machine, the listener will authenticate you with the database and then hand off your connection to the database server itself so that you are now connected directly to the server. At that point it doesn’t matter if the listener is working or not because you no longer pass any information through the listener. In short, the listener just passes off your connection to the database if you give the correct address, username and password and have sufficient privileges to make a connection to the DB.

Sample listener as bellow:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

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

      )

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ORCL.WORLD)

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

      (SID_NAME = ORCL)

    )

   )

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

$ lsnrctl stop

$ lsnrctl start

$ lsnrctl reload

Tnsnames.ora

The "tnsnames.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a "tnsnames.ora" file.

The TNSNames.ora file is used to list alias names, IP addresses or DNS names and protocols to use when connecting to the listener. In simple terms, it’s like an address book for how your client will connect to the database. It has all the information needed to contact the listener.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.128)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

From this entry you can find out quite a lot of information. It shows you that there is an alias called TESTDB which is just the name that you would use in your connection string to connect to the DB.

Now let’s take a look at the listener.ora file and how that is configured.

To test the connection we can use tnsping <service name>

Here service name is name which is used in tnsnames.ora file. The example for the above tnsnames.ora is

$tnsping orcl

The successful test of the connection as follows.

[oracle@localhost ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-FEB-2015 05:14:35

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.128)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))

OK (140 msec)

SQLNET.ora

   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.

    By default, the sqlnet.ora file is located in the $ORACLE_HOME/network/admin directory. The sqlnet.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable.

It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a "sqlnet.ora" file.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

NAMES.DEFAULT_DOMAIN = <domain name>

# the following entry is necessary on Windows if OS authentication is required.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

A common setting to have in this file is the NAMES.DEFAULT_DOMAIN parameter, which controls what is appended to any connection string. It’s easiest to show this with an example so take a look at these connection strings which would usually be executed from a command prompt to connect to the database:

$Sqlplus user/password@orcl

$Sqlplus user/password@orcl.dev.com

The difference between the two is that the 1st one specifies a domain and the 2nd one doesn’t. If you had a SQLNET.ora file which had the entry NAMES.DEFAULT_DOMAIN = DEV.INT.COM in it then both of these connection strings would work. The idea is that by using this parameter you don’t have to specify the full database name and domain name each time you connect.

Testing:

Once the files are present in the correct location and amended as necessary the configuration can be tested using SQL*Plus by attempting to connect to the database using the appropriate username (SCOTT), password (TIGER) and service (ORCL).

$tnsping orcl

Now connect to database with service name.

$ sqlplus scott/tiger@orcl

Changing Location($TNS_ADMIN):

Default Location of those three files $ORACLE_HOME/network/admin if we need  to change this location we need to set TNS_ADMIN

Ex: if we need to place listener and tnsnames in /u01/TNS need to do follow.

export TNS_ADMIN=/u01/TNS

Copy listener,tnsnames and sqlnet.ora to this location. Restart the listener.