1 Oracle Listener

Listen the incoming network request from client and forward to the Oracle instance.

PMON would register itself to the listener. Generally  take ~ 1 min for PMON to connect to Listener. Until the PMON is connect to Listener, user can not connect to the Database from remote and would get ‘ORA-12514: TNS:listener does not currently know of service requested in connect descriptor’.

We can use alter system register to force the PMON to register the listener

Find the Listener processes

ps -ef |grep tns

Default Listener

By default, We do not need to configure which Listener Oracle Database would connect to. It would connect to default listener ‘LISTENER’. 

• Default Listener  is listen to default port 1521.
• Default listener does not need the listener.ora configure file.
• lsnrctl <command> without the listener name would work for default listener. EX: lsnrctl status

Listener command

• lsnrctl start
• lsnrctl stop
• lsnrctl status
• lsnrctl service

lsnrctl show what database is register to the listener.

If there is no listener , user can not connect from remote and would get “ORA-12541 TNS: no listener ”

Configure Database use particular listener

listener.ora

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

# Define listener A...

LISTENER_PODB= (DESCRIPTION_LIST =

                (DESCRIPTION =

                        (ADDRESS_LIST =

                                (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 7003) )

                        )

                )

             )

SID_LIST_LISTENER_B =

        (SID_LIST =

                (SID_DESC =

                        (SID_NAME = PODB)

                        (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2)

                )

        )

ADR_BASE_LISTENER = /home/oracle/app/oracle

---- Below section are optional , but if you do set the LOCAL_LISTENER in the parameter file, the tnsname.ora must have the correspond entry. However, if there is no SID_LIST defined in the listener.ora, the LOCAL_LISTENER and TNSNAMES.ora must configure. otherwise the SID does not know which listener to go to.

parameter file: set the LOCAL_LISTENER

DB_NAME=PODB

DB_BLOCK_SIZE= 8192

SGA_TARGET  = 300M

CONTROL_FILES=/home/oracle/app/oracle/oradata/podb/controlfile/PODB_controlfile.ctl

UNDO_MANAGEMENT = AUTO

LOCAL_LISTENER=LISTENER_PODB

tnsnames.ora

LISTENER_PODB =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 7003))

Listener configure file:$TNS_ADMIN/listener.ora

one listener can be share by multiple database instance (SID). we can also create dedicate listener for the each SID. Below screenshot shows, the SID orcl is register with LISTENER_A. SID PODB use LISTENER_PODB.

 

Why the SID status is ‘UNKNOWN’ in lsnrctl ?

When we specifically tell the listener about a instance in the SID_LIST section, listener just assumes it is there and creates a listening point for it. It doesn't check the status, and so the status is UNKNOWN. It does not affect the database connection.

 

Reference

• http://www.datadisk.co.uk/html_docs/oracle/connectivity.htm
• http://www.orafaq.com/wiki/SQL*Net_FAQ
• http://docs.oracle.com/cd/B10500_01/network.920/a96580/architec.htm#1044716
• http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/