Quick Steps to Configure Tnsnames.ora and Listener.ora file

Tnsnames.ora and Listener.ora are main files for database connection, So Configure Tnsnames.ora and Listener.ora file has become an important task for Database administrator. Listener.ora file have listener details which listen to new database connections and connect them to database. Any new connection to the database server must go through listener, So if listener on the server is down, new connection to the database are not possible. 

Tnsnames.ora file is use for connection from client to server, this file has all details related to destination database serve like database host server name or IP, the service name or instance name database will connect to. DBA can connect to database using this file from any location.


How to configuring Listener.ora file ?


Listener.ora is the file, which is available at $ORACLE_HOME/network/admin location. If you are using Real Application Cluster environment then listener file in use will be available at location $GRID_HOME/network/admin. Usually you will find one listener configured into datbase server by name listener which is defualt listener.

There are two kind of Listener configuration.


1. Dynamic Registration.
2. Static Registration.


1. Configure Dynamic Registration Listener: Default port for dynamic listener is 1521, Though DBA can also change this port. Suppose you are creating a new database, then by default this database will be registered dynamic listener running on 1521 post. A dynamic Listener service looks like below.

[oracle@database samples]$ cat /etc/oracle/oracle/network/admin/listener.ora

# listener.ora Network Configuration File: /etc/oracle/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = database.example.com)(PORT = 1521))
    )
  )
LOCAL_OS_AUTHENTICATION_LISTENER = OFF 
ADR_BASE_LISTENER = /etc/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

DBA can check listener service status by "lsnrctl status" command for both dynamic and static listener. Dynamic Listener is very good feature to use because it doesn't need any configuration and maintenance. but as per my recommendation this is more prone to vulnerability

So for Database Best Security Practice, I would suggest to use static Listener registration for Listener with post other than 1521. In conclusion, Listener with dynamic Registration is self configured and DBA has noting to do about its configuration.


2. Configure Static Registration Listener: In Configuration of static Listner, Database Administrator has to add entries into Listener.ora file. Any listener service configured other than 1521 post is always Static Listener. Let's register a database with Static Listener. To configure a new listener you can find sample listener file into $ORACLE_HOME/network/admin location. Copy below entry from sample file or from this place and make a listener.ora if not exists or append if exists into $ORACLE_HOME/network/admin.

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_DBNAME=salesdb.mycompany)
         (SID_NAME=sid1)
         (ORACLE_HOME=/private/app/oracle/product/8.0.3)
        (PRESPAWN_MAX=20)
        (PRESPAWN_LIST=
         (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
        )
       )
      )

Here, Database Administrator has to fill following parameters, HOST, PORT, GLOBAL_DBNAME, SID_NAME and ORACLE_HOME.

Let's find all one by one.

HOST is IP address of your host or name of the machine. You can use any one of these two. To find IP address or host name use below steps.

[oracle@database ~]$ ifconfig

eth0      Link encap:Ethernet  HWaddr 08:00:27:C2:5E:3E  
          inet addr:192.168.56.102  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fec2:5e3e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:625 errors:0 dropped:0 overruns:0 frame:0
          TX packets:16581 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:74105 (72.3 KiB)  TX bytes:696618 (680.2 KiB)

[oracle@database ~]$ hostname
database.example.com


Port is port number at which Database Listener service will run, Let's choose 1522 as port for new listener. If you have firewall configured into your network, Ask Network team to open 1522 port into firewall otherwise you won't be able to connect to database.

User below command to check port is open on not.

[oracle@database ~]$ telnet database.example.com 1522

GLOBAL_DBNAME is the database parameter. To find this parameter value. Login to database which you want to register with Listener and user below command

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.EXAMPLE.COM

SID_NAME is database instance name parameter. Since this is a single instance database db name and instance name are same. So use v$instance_name view to find SID_NAME

SQL> select instance_name from v$instance; 

INSTANCE_NAME
----------------
orcl

ORACLE_HOME is the path where DBA has installed Oracle Software. DBA can find ORACLE_HOME by using

[oracle@database ~]$ echo $ORACLE_HOME
/etc/oracle/oracle

I am using listener name as "LISTENER" which is default. Now after doing all these changes. Final Listener.ora file look like below. To run Listener on non default port DBA has to register database with listener and to set LOCAL_LISTENER parameter values as given here.

[oracle@database ~]$ cat /etc/oracle/oracle/network/admin/listener.ora

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=database.example.com)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_DBNAME=ORCL.EXAMPLE.COM)
         (SID_NAME=orcl)
         (ORACLE_HOME=/etc/oracle/oracle)
        (PRESPAWN_MAX=20)
        (PRESPAWN_LIST=
          (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
        )
       )
      )


Login to Database register database with listener, set LOCAL_LISTENER parameter and start the listener. 

SQL> alter system register;

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=database.example.com)(PORT=1522))' scope=both;

[oracle@database ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 22-JUL-2013 18:05:06
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database.example.com)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=database.example.com)(PORT=1522))
STATUS of the LISTENER

------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                22-JUL-2013 18:05:06
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   //etc/oracle/oracle/network/admin/listener.ora
Listener Log File         /etc/oracle/diag/tnslsnr/database/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database.example.com)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
Services Summary...
Service "ORCL.EXAMPLE.COM" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 3 handler(s) for this service...
The command completed successfully
Here Database Admin can observe, Listener is configured at 1522 port and has "orcl" instance registered with it. To know the difference between static and dynamic and Exploring the LOCAL_LISTENER parameter See Here.


How to Configure Tnsnames.ora file ?


The main difference between Listener.ora and Tnsnames.ora is, Listener.ora is a server parameter file while Tnsnames.ora is a client side file. If you want to connect from client to database server then DBA has to configure tnsnames.ora file on client machine and you must also have Oracle Client Software installed on client system.

Here, we will configure a tnsnames.ora file to connect to the database server. A sample tnsnames.ora file is present in "$ORACLE_HOME/network/admin/sample" folder or you can copy from Here.

{addressname}=
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = {hostname})(Port ={port number} ))
  )
 (CONNECT_DATA =
   (SERVICE_NAME = {service name})
 )
)
In this sample file, We will add addressname, hostname, port and SERVICE_NAME to configure it for particular database.

addressname is name given to it, which will work as connection string when we will connect to the database. Let's say addressname is db_orcl. DBA can choose any name for this.

Host is the host name of database server, which we have already seen during Listener configuration. You have to give database server host name here.

Port is again Database server port number at which Listener is running you can find it in database server Listener.ora file. In above Listener configuration we give 1522 as listener port.

Service_name is database service name shown in Lsnrctl status output.

Services Summary...
Service "ORCL.EXAMPLE.COM" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 3 handler(s) for this service...
The command completed successfully

From the above snap shot of Lsnrctl status Service name is "ORCL.EXAMPLE.COM". Use same method to get your database service name. This service has "orcl" instance registered with it.

Here is configured tnsnames.ora file. Copy this into $ORACLE_HOME/network/admin/tnsnames.ora file.

db_orcl =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = database.example.com)(Port = 1522))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORCL.EXAMPLE.COM)
 )
)

Now Database admin can connect to database using this tnsnames.ora file from a client machine to database.

[oracle@database admin]$ sqlplus scott/tiger@db_orcl

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 22 22:59:02 2013
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 

Here, We have configured both Listener.ora and tnsnames.ora for database connection. Please share your view about this post.

4 comments:

  1. So good so simple but very intresting thank you so much for this article.

    ReplyDelete
  2. Really nice article.........Very easy to understand ......!!

    ReplyDelete