RATE_LIMIT on listener

How To Control the Amount of Connections Handled by the TNS Listener [ID 443744.1]

Solution

Patch set releases 9.2.0.8, 10.1.0.6 and 10.2.0.3 include a new enhancement to limit the amount of connections a TNS listener can handle. This achieved via the parameters CONNECTION_RATE_<listener name> and RATE_LIMIT.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host.sample.com)(PORT = 1521)(RATE_LIMIT=10))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host.sample.com)(PORT = 1522))
    )
  )

In the above example the RATE_LIMIT for port 1521 on the listener is restricted to 10 connections per second. Where as port 1522 has no limit. We can also set RATE_LIMIT globally

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host.sample.com)(PORT = 1521)(RATE_LIMIT=YES))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host.sample.com)(PORT = 1522)(RATE_LIMIT=YES))
    )
  )

CONNECTION_RATE_LISTENER=10 

In this configuration, the total number of new connections through ports 1521 and 1522 is capped at 10 per second.
Rate Limit Example.
Set RATE_LIMIT to 5 in the LISTENER.ORA file. Remember to stop / start the TNS listener for the parameter to be picked up.

LISTENER =
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = host.sample.com)(PORT = 1521)(RATE_LIMIT=5))
  )
 )

kick off more connections than the value of RATE_LIMIT. Here using 10 connections. Run.sh is sqlplus connection. Driver.sh is just calling run.sh 10 times.

> more run.sh
sqlplus scott/tiger@11gtest <
EOF

Running script to call run.sh

> more driver.sh
run.sh&
run.sh&
run.sh&
run.sh&
run.sh&
run.sh&
run.sh&
run.sh&
run.sh&
run.sh&

And the listener log show 5 connections 11:42:24 and 5 connections 11:42:25

15-MAY-2010 11:42:24 * establish * 1158
TNS-01158: Internal connection limit reached, preventing dispatcher from connecting
15-MAY-2010 11:42:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60340)) * establish * S111W6 * 0
15-MAY-2010 11:42:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60341)) * establish * S111W6 * 0
15-MAY-2010 11:42:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60342)) * establish * S111W6 * 0
15-MAY-2010 11:42:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60344)) * establish * S111W6 * 0
15-MAY-2010 11:42:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60343)) * establish * S111W6 * 0
15-MAY-2010 11:42:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60345)) * establish * S111W6 * 0
15-MAY-2010 11:42:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60346)) * establish * S111W6 * 0
15-MAY-2010 11:42:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60347)) * establish * S111W6 * 0
15-MAY-2010 11:42:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60349)) * establish * S111W6 * 0
15-MAY-2010 11:42:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=S111W6)(CID=(PROGRAM=sqlplus)(HOST=sample.com)(USER=grdbms))) * (ADDR
ESS=(PROTOCOL=tcp)(HOST=sample.com)(PORT=60348)) * establish * S111W6 * 0

TNS-01158 is expected as the incoming connection rate was higher than the RATE_LIMIT value

---

Known Problems

Bug 8529537All New connections Are Refused by ORA-12547 After TNS-01158 in Listener.log. Effects releases 10.2 onwards and is fixed release 12. One off fixes are available via Patch 8529537. If no one off is available for your platform / release, please request one via service request.