Config listener.ora file
* listener.ora is located in
– the directory defined by TNS_ADMIN env
– or $ORACLE_HOME/network/admin if TNS_ADMIN is not defined.
listener.ora defines
* listener name
* supported protocol
* listening port number
* supported service names and sid names
* dead connection detection
* prespawned dedicated server processes
* advanced configurations
Advantages using multiple listeners
* Listener failovers
* Separate listeners for separate databases
Syntax
listener_name =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = address_data) # ADDRESS is same as in tnsnames.ora
(PROTOCOL_STACK = protocol_data) # See below
(DESCRIPTION = # Use additional DESCRIPTION parameter
... # to support more listening ports or protocols
)PROTOCOL_STACK
* Default PROTOCOL_STACK
(PROTOCOL_STACK = (PRESENTATION = TTC) (SESSION = NS))
* CORBA PROTOCOL_STACK
(PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW))
Listen on multiple NICs
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
)
)
)SID_LIST Parameter
* Not needed for Oracle 8i and above
* Specifies what SIDs/service_names a listener should handle.
Specify SID_LIST for a listener
* Specify listener by appending listener name to ‘SID_LIST_’ string
SID_LIST_listener_name =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db_service_name)
(ORACLE_HOME = oracle_home_directory)
(SID_NAME = name)
)
(SID_DESC = # Additional SID_DESC
...
)Prespawned Dedicated Server Processes
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.world)
(ORACLE_HOME = /opt/oracle/product/8.1.5)
(SID_NAME = orcl)
(PRESPAWN_MAX = 30)
(PRESPAWN_LIST =
(PRESPAWN_DESC =
(PROTOCOL = TCP)
(POOL_SIZE = 10)
(TIMEOUT = 1)
)
(PRESPAWN_DESC =
(PROTOCOL = SPX)
(POOL_SIZE = 10)
(TIMEOUT = 1)
)
)
)
)External Procedure Call
* Allow external procedure calls from PL/SQL code.
* listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\prog\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)* tnsnames.ora
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)Global Parameters
CONNECT_TIMEOUT_LISTENER = 20 TRACE_LEVEL_LISTENER = OFF CONNECT_TIMEOUT_DEV_LISTENER = 60 TRACE_LEVEL_DEV_LISTENER = SUPPORT
Start/Stop Listener
From lsnrctl utility command interface
* Start lsnrctl utility
lsnrctl LSNRCTL>
* Start/stop a listener named LISTENER (default)
LSNRCTL> start LSNRCTL> stop
* Start/stop a listener named my_listener
LSNRCTL> start my_listener LSNRCTL> stop my_listener
Run a listener command script
lsnrctl @my_listener_script
How to Pair up listener.ora and tnsnames.ora
listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = mykey)) # Listen on IPC protocol
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db.my.com)(PORT = 1521)) # Listen on TCP protocol
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
)Above two ADDRESS entries can be abbreviated as:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = mykey)) # Listen on IPC protocol
(ADDRESS = (PROTOCOL = TCP)(HOST = db.my.com)(PORT = 1521)) # Listen on TCP protocol
)
)tnsnames.ora
# Ues IPC protocol
ORCL-IPC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
# Use TCP protocol
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db.my.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)References
* Oracle Net8 Configuration and Troubleshooting By: Hugo Toledo; Jonathan Gennick