Thursday, March 1, 2012

Configuration of LISTENERS & STATUS

Hi Guys,

This article will explain how to configure the listeners and tnsnames as well as how to check the service status. 

Basically listeners registration will happen in two ways.
1. Dynamic listeners
2.Static listeners

While installing the oracle database software  if you choose database creation as a option at that time database will create a default listener, its name is LISTENER and that is dynamic listener. After that when your creating extra listeners all those will be part of static listeners.

The major difference between dynamic listener and static listener:
Whenever we are bringing up the database , the listener automatically bringup its services also. But in static listener we need to manually start the listeners and need to check the its status also.

From oracle 11g Release 2 onwards, oracle given a option that we can create dynamic  listeners which is created for user requirement.


Basically using netca command we can create listeners and tnsnames .  In unix flavour or windows flavour netca command is available in $ORACLE_HOME/bin directory.


PMON will search for tnsnames.ora in the following order:
  • $HOME/.tnsnames.ora
  • $TNS_ADMIN/tnsnames.ora
  • /var/opt/oracle/tnsnames.ora or /etc/tnsnames.ora (depending on platform)
  • $ORACLE_HOME/network/admin/tnsnames.ora


How to create listeners using Putty or SILENT MODE

Step1:  Copy the netca response file[file name is netca_typ.rsp] to some other location.

Login through putty,
copy the file from $ORACLE_HOME/network/install
#su - oracle
$. oraenv
$cd $ORACLE_HOME/network/install
$pwd
/u01/app/oracle/product/11.2.0.3/server/network/install
$ cp netca_typ.rsp /u01/app/oracle

Step2 :  Modify the response file
$cd /u01/app/oracle
$vi netca_typ.rsp

Ex: Below is the file content
[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
[Session]
TOPLEVEL_COMPONENT={"oracle.net.ca","11.2"}
[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""custom""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER_DB11G_1590"}
LISTENER_PROTOCOLS={"TCP;1590"}
LISTENER_START=""LISTENER_DB11G_1590""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}


Based on your requirement change LISTENER_NAMES, LISTENER_PROTOCOLS's port number, LISTENER_START values and save it.


Step 3: Configure the listener
Got to $ORACLE_HOME/bin
$cd $ORACLE_HOME/bin

[oracle@inatechsrv bin]$ ./netca  /silent  /responsefile  /mnt/netca_typ.rsp
Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /mnt/netca_typ.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Configuring Listener:LISTENER_DB11G_1590
Listener configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /u01/app/oracle/product/11.2.0.3/server/bin/lsnrctl start LISTENER_DB11G_1590
    Listener Control complete.
    Listener started successfully.
Profile configuration complete.
Oracle Net Services configuration successful. The exit code is 0

[oracle@inatechsrv bin]$ lsnrctl status LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 13:10:38
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DB11G_1590
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                01-MAR-2012 13:10:24
Uptime                    0 days 0 hr. 0 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1590/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
The listener supports no services
Now listener is create but still its showing NO SUPPORT SERVICEs it means this listener will not accept the new connections.  Now You change as  either static listener or dynamic listener to accept the new connections .

STEP4:

--------------------------------------------
Case 1: STATIC LISTENER

Step1:
If you want static listener , go to listener.ora check current listener entry ,it should be similar to below entry.

Note:  In Oracle 11g Release 2 , by default it will show dynamic listener format.

If you want to enable as static listener , change your LISTENER as similiar as below.  May be some entries will not be there just add it.

Ex:
LISTENER_DB11G_1590=
  (DESCRIPTION=
    (ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590))))
SID_LIST_LISTENER_DB11G_1590=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=DB11G)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/server)
      (PROGRAM=extproc)))
ADR_BASE_LISTENER_DB11G_1590 = /u01/app/oracle

Step2:  Check the listener services
$lsnrctl status LISTENER_DB_1590
it will show

[oracle@inatechsrv admin]$ lsnrctl status LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 13:36:43
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr..com)(PORT=1590)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DB11G_1590
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                22-FEB-2012 11:15:34
Uptime                    8 days 2 hr. 21 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1580/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
  Instance "DB11G", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@inatechsrv admin]$

Now listener is fine, it will accept the incoming connection.  Note: UNKNOWN means its static listener

Case 2: DYNAMIC LISTENER
 Step1:
By default in oracle 11g release 2 onwards  If you are creating any listeners, we will get dynamic listener configuration format.

It should be similar to

ADR_BASE_LISTENER_DB11G_1580 = /u01/app/oracle
LISTENER_DB11G_1590 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590)) )  )

 Step 2:    Check in tnsnames.ora file whether below entry is there or not.

If not just add similar to below

 LISTENER_DB11G_1590 =(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))

Step3:  Add the listener entry into  Initialization parameter file .
If you want dynamic listener , we need to set LOCAL_LISTENER parameter

Note: If database uses spfile then

$sqlplus '/as sysdba'
SQL>alter system set local_listener=LISTENER_DB11G_1590 scope=spfile;
SQL>shut immediate
SQL>startup

Note: If you want you can set multiple listeners as a dynamic listeners
SQL>alter system set local_listener=LISTENER_DB11G_1590,LISTENER_DB11G_1600 scope=spfile;


When database is bringup pmon will check these listeners entry is available or not in tnsnames.ora . If not then database will not UP. I hope now you understand while listener name entry should present in TNSNAMES.ORA file.

If database using pfile, just open the pfile and add the entry, save it. Rebounce the database.

step4: Check the listener status
[oracle@inatechsrv admin]$ lsnrctl status LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 13:52:15
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DB11G_1590
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                22-FEB-2012 11:15:34
Uptime                    8 days 2 hr. 36 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1590/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.com" has 1 instance(s).
  Instance "DB11G", status READY, has 1 handler(s) for this service...
The command completed successfully
Note: Status READY means its dynamic listener.


HOWEVER , For same database we can create multiple listeners, we can configure some listeners as dynamic and some listeners as static.
----------------------------------------------------------

STEP5: Configure the tnsname entry

When you create listeners automatically tnsnames. ora file also creating.
Tnsnames entry required if you want to access the database remotely otherwise no need to worry for this step.

As per oracle 11g Release 2
tnsname.ora entry should be

DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.amk.com)
    )
  )

This service name should be equavalent to database service name.
To find database service name
SQL>show parameter services_names;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      DB11G.amk.com
Note: If these two things not matches means your connection will not establish.

If you have multiple listeners then net service would be
ex:
DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))
     (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1580))
     )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.amk.com)
    )
  )
As per above example same database using two listeners and two ports, it can either static or dynamic or both.


STEP6:  Check the listener services
[oracle@inatechsrv admin]$ lsnrctl service LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 14:47:14
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
  Instance "DB11G", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Here ESTABLISHED IS 0 means no connections till now.

[oracle@inatechsrv admin]$ sqlplus sys@DB11G
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 14:47:22 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> !

[oracle@inatechsrv admin]$ lsnrctl service LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 14:47:39
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
  Instance "DB11G", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
[oracle@inatechsrv admin]$

Here established is 1 means one connection is accepted.  If still shows its zero means some where you missed one step.

Now listener is configured and its accepting new connections from remotely also.


Using the TNSPING Utility to Test Connectivity from the Client

The TNSPING utility determines whether the listener for a service on an Oracle Net network can be reached successfully.

[oracle@inatechsrv admin]$ tnsping DB11G
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 15:04:07
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.3/server/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))) (CONNECT_DATA = (SERVICE_NAME = DB11G.amk.com)))
OK (0 msec)
You have new mail in /var/spool/mail/oracle
[oracle@inatechsrv admin]$

Note: OK means its able to reach within 0 msecs. We can accept upto 20msec. For above 20msecs, we say its connectivity issue it can network or database performance issue.

Using the TRCROUTE Utility to Test Connectivity from the Client

The Trace Route Utility (TRCROUTE), in Linux and UNIX environments, enables administrators to discover the path or route a connection is taking from a client to a server. If TRCROUTE encounters a problem, then it returns an error stack to the client .

[oracle@inatechsrv admin]$ trcroute DB11G
Trace Route Utility for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 15:07:13
Copyright (c) 1995, 2011, Oracle.  All rights reserved.
Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
01-MAR-2012 15:07:13 ADDRESS= PROTOCOL=TCP  HOST=inatechsrv.blr.com  PORT=1590

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
01-MAR-2012 15:07:13




SOME ISSUES

1.  SQL> startup nomount
Issue:
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER

Solution:  Local_listener parameter is not setted in parameter file.

2.
Issue:
 SQL>STARTUP;ORA-00119: invalid specification for system parameter LOCAL_LISTENERORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))
Cause: The listener address specification is not valid.
Action: Make sure that all fields in the listener address (protocol, port, host, key, ...) are correct.


3.
Issue: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

Solution: Service  names not matching.

4.
Issue: The listener support no services.
Solution: PMON not yet registered. Local_listener parameter is not matching with its listener.



Let us know if any doubt on this document. Please contact me murali9231@gmail.com





1 comment:

  1. Nice pants. Can I test the zipper? Hey, i am looking for an online sexual partner ;) Click on my boobs if you are interested (. )( .)

    ReplyDelete

Share your knowledge it really improves, don't show off...