Saturday, October 15, 2005

Setting up Multiple Listeners

Somebody had a problem in one of the forums with connections timing out. I suggested the error might be due to the listener being really busy and the poster could setup multiple listeners and load balance between the two. The setup was complex enough that I wanted enough room to explain the whole thing.

Setting up two listeners on the same box is done via the listener.ora file. The listener.ora file is usually located in your $ORACLE_HOME/network/admin directory. I have setup two listeners on my test box and the listener.ora file looks like this:

LSNR1666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
)
)
)

LSNR2666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 2666))
)
)
)

SID_LIST_LSNR1666 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = lx10r2.us)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
(SID_NAME = lx10r2)
)
)

SID_LIST_LSNR2666 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = lx10r2.us)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
(SID_NAME = lx10r2)
)
)

This part of the listener.ora tells me my listener name is LSNR1666 and that it is listening on port 1666 of hostname "jake" and I'll be using the TCP protocol.

LSNR1666 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
)
)
)

This part of the listener.ora tells me that LSNR1666 will be listening for the DB named lx10r2.us whose ORACLE_HOME is /u01/app/oracle/product/10.2.0.1 and whose ORACLE_SID is lx10r2. (This could have been specified with a SERVICE_NAME also, but I chose to do it this way because it is simpler).

SID_LIST_LSNR1666 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = lx10r2.us)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
(SID_NAME = lx10r2)
)
)

That's it, now you just start the listeners by name:


[oracle@jake admin]$ lsnrctl start lsnr1666

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-OCT-2005 08:52:23

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/lsnr1666.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=1666)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jake)(PORT=1666)))
STATUS of the LISTENER
------------------------
Alias lsnr1666
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 15-OCT-2005 08:52:23
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0.1/network/log/lsnr1666.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=1666)))
Services Summary...
Service "lx10r2.us" has 1 instance(s).
Instance "lx10r2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@jake admin]$ lsnrctl start lsnr2666

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 15-OCT-2005 08:52:30

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/lsnr2666.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=2666)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jake)(PORT=2666)))
STATUS of the LISTENER
------------------------
Alias lsnr2666
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 15-OCT-2005 08:52:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0.1/network/log/lsnr2666.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=2666)))
Services Summary...
Service "lx10r2.us" has 1 instance(s).
Instance "lx10r2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


We can see that the listeners are started and waiting for connections:

[oracle@jake admin]$ tail $ORACLE_HOME/network/log/lsnr1666.log
Trace information written to /u01/app/oracle/product/10.2.0.1/network/trace/lsnr1666.trc
Trace level is currently 0

Started with pid=3201
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=1666)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
15-OCT-2005 08:52:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=jake)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=lsnr1666)(VERSION=169869568)) * status * 0
[oracle@jake admin]$ tail $ORACLE_HOME/network/log/lsnr2666.log
Trace information written to /u01/app/oracle/product/10.2.0.1/network/trace/lsnr2666.trc
Trace level is currently 0

Started with pid=3211
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jake)(PORT=2666)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
15-OCT-2005 08:52:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=jake)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=lsnr2666)(VERSION=169869568)) * status * 0

Next, we configure the client. The client naming is usually controlled by a file called tnsnames.ora in the $ORACLE_HOME/network/admin directory of the client. My tnsnames.ora file looks like this:

D:\oracle\product\10.2.0\NETWORK\ADMIN>type tnsnames.ora
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LX10R2.US =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE=on)
(ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 1666))
(ADDRESS = (PROTOCOL = TCP)(HOST = jake)(PORT = 2666))
)
(CONNECT_DATA = (SID = lx10r2))
)

Here, the database alias is "lx10r2.us". Normally, when you specify multiple address lines for an alias, Oracle will attempt the first and if that fails will attempt the second. This might actually work for the poster, but would still pound port 1666 until it couldn't handle any connections and THEN try port 2666. The key to having a somewhat even distribution over the two listeners is by using the (LOAD_BALANCE=ON) parameter.

Just to test the theory, we create a small batch file on the client machine that connects and disconnects in an infinite loop. The batch file looks like this:

C:\temp>more infinite.bat
:start
sqlplus jeffh/jeffh@lx10r2.us @disconnect
goto :start

C:\temp>more disconnect.sql
exit

Then, we open up multiple command prompt windows and start pounding away. You can see the listener.log file keeps growing and both listeners are accepting approximately the same number of connections:

[oracle@jake log]$ wc -l *.log; sleep 120; wc -l *.log
206 lsnr1666.log
199 lsnr2666.log
405 total
528 lsnr1666.log
582 lsnr2666.log
1110 total

Nice. About three hundred connections per minute. Not bad for an 802.11b connection to a 733Mhz Linux box connected to a 100Mbps router. Will this solve the poster's problem? I don't know. I do know this is how you can setup multiple listeners, though.

9 comments:

Anonymous said...

Great tip, Thanks. Does the DB need a reboot? What I want to know is can we do this with the DB running and conenctions are still being made. Will 2 listeners be always enough? How many listeners can we add extra in a high transaction OLTP system. Is there a theoretical and practical limit?

What is the range of port numbers that can be used?

"ns main err code: 12535
TNS-12535: TNSperation timed out"

What else would cause that error?

Anonymous said...

Great thought Jeff, Funny I never thought about it. Multiple listeners=load balance.

Jeff Hunter said...

Will 2 listeners be always enough?
Always is one of those words that I don't like. Two listeners will be a lot for most situations, but not all. I can't think of too many situations where you would need more than 4.

What is the range of port numbers that can be used?
as long as it's not already used, you can use it.

Anonymous said...

Ooer. One thing I would never recommend is two listeners on the same server (unless it was to deal with different version issues, such as an 8.0 one and a 10g one).

The commonest cause of listener contention issues is that there is extremely poor application design (so that, for example, practically every mouse click causes a new logon request to be made. Stateless HTML... love it!)

Having multiple listeners doesn't help resolve the fundamental problem here. It might distribute the problem enough so you don't notice it, but you still have poor session management any way you look at it. And that's not good.

Another cause for listener contention issues is CPU throttling... and throwing an extra listener onto the server will make that worse, not better.

All in all, a demo of how to set up a tnsnames.ora to achieve load balancing is a fine and dandy thing, but doing it on the one server isn't.

Anonymous said...

Hi Jeff,
I have just configured my second listener on my 8i database following your advice on http://marist89.blogspot.com/2005/10/setting-up-multiple-listeners.html .
But how ever hard I try I cannot get use the second listener. The second listener has started up fine and all changes have been made. I have been testing for 9 straight hours on this but have failed (not given up) on my attempts so far. Even when I shutdown the first listener the second does not pick up the calls I am making through a shell script.


#!/bin/ksh
export ORACLE_SID=EPT4
export ORACLE_HOME=/orasftware/app/oracle/product/8.1.6
count=0
while [ $count -lt 200 ]
do
sqlplus 'system/b1cycle@ept4' @d.sql > /dev/null
count=`expr $count + 1`
echo $count
Done


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = nzlsfn32.zeus.ghsewn.com)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

LISTENEREMR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = nzlsfn32.zeus.ghsewn.com)(PORT = 1526))
)
)
)
CONNECT_TIMEOUT_LISTENER = 10

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /orasftware/app/oracle/product/8.1.7)
(PROGRAM = extproc)
)

(SID_DESC =
(SID_NAME = EPT4)
(ORACLE_HOME = /orasftware/app/oracle/product/8.1.6)
)
(SID_DESC =
(GLOBAL_DBNAME = DES6)
(ORACLE_HOME = /orasftware/app/oracle/product/8.1.6)
(SID_NAME = DES6)
)
)

SID_LIST_LISTENEREMR =
(SID_LIST =
(SID_DESC =
(SID_NAME = EPT4)
(ORACLE_HOME = /orasftware/app/oracle/product/8.1.6)
)
)

TRACE_LEVEL_LISTENER = OFF
#LOG_FILE_LISTENER = listener
TRACE_DIRECTORY_LISTENER = /orasftware/app/oracle/product/8.1.7/network/admin
TRACE_FILE_LISTENER = listener.trc
STARTUP_WAIT_TIME_LISTENER = 0
LOG_DIRECTORY_LISTENER = /orasftware/app/oracle/product/8.1.7/network/admin



MY tnsnames has the following entry.


EPT4.HO.NZDAIRY.CO.NZ =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE=on)
(ADDRESS = (PROTOCOL = TCP)(HOST = empac-qa.datacentre.nzmp.co.nz)(PORT = 1526))
(ADDRESS = (PROTOCOL = TCP)(HOST = empac-qa.datacentre.nzmp.co.nz)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = empac-qa.datacentre.nzmp.co.nz)(PORT = 1526))
)
(CONNECT_DATA =
(SID = EPT4)
)
)




The listeneremr.log is logging all shutdown and startup activity of the listeneremr but that's it.

Thanks for your informative article and your help. Regards,
RD.

My email address is -rahuldixit_nz@yahoo.co.nz

Anonymous said...

I recall in 8i there was a listener queue size parameter that could be increased if there were short bursts on connection requests. I agree with an earlier poster that regular time-outs are an indication of frequent short connections that should be pooled. Creating (and deleting) an Oracle connection is a relatively expensive process and should be avoided for short frequent queries.

Anonymous said...

These comments have been invaluable to me as is this whole site. I thank you for your comment.

Vishal Gupta said...

You can use queue size to handle connection timeout.

QUEUESIZE

Purpose

Use the parameter QUEUESIZE to specify the number of concurrent connection requests that the listener can accept on a TCP/IP or IPC listening endpoint (protocol address).

Embed this parameter at the end of the protocol address with its value set to the expected number of concurrent connection requests.

Default

The default number of concurrent connection requests is operating system specific. Following are the defaults for the Solaris Operating System and Windows:

* Solaris Operating System: 5
* Windows NT 4.0 Workstation: 5
* Windows NT 4.0 Server: 50

Usage Notes

See Also:
Oracle Net Services Administrator's Guide for information about configuring this parameter

Example

listener_name=
(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521)(QUEUESIZE=20)))



See here on Oracle Documentation Link

Anonymous said...

Great tip! Thanks very much.