Tag Archives: listener

TNS-00509: Buffer overflow

TNS-00509: Buffer overflow

Error: TNS 509 – Buffer overflow
Cause: Too much data for buffer.
Action: Reexecute with larger receive buffer or smaller send buffer.

Solution:
You can set the larger value of Receive buffer than send buffer in listener.ora file.
For more information you can check HR entries in following example for client(tnsnames.ora) and Server side(listener.ora) files. We increase value of RECV_BUF_SIZE than SEND_BUF_SIZE in listener.ora file(server side) and tnsnames.ora file(client side).

For more details as follows

Configuring I/O Buffer Space
RECV_BUF_SIZE and SEND_BUF_SIZE parameters specify sizes of socket buffers associated with an Oracle Net connection. For best performance, the size of the send and receive buffers should be set large enough to hold all the data that may be sent concurrently on the network connection.
Note: Default values for these parameters are operating system-specific.

Session Data Unit(SDU)
Data transferred in buffer as SDU(session data unit) sqlnet.ora. Configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file, as follows:
DEFAULT_SDU_SIZE=8192
Note: Default SDU for the client and a dedicated server is 8192 bytes. The default SDU for a shared server is 65535 bytes

Configuring I/O Buffer Space on the Client
SEND_BUF_SIZE and RECV_BUF_SIZE parameters are set in the client’s sqlnet.ora file or TNSNAMES.ora for configuring client side.

— TNSNAMES.ORA file:

sales.oracle.com=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)
(SEND_BUF_SIZE=11784)
(RECV_BUF_SIZE=11784))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)
(SEND_BUF_SIZE=11784)
(RECV_BUF_SIZE=11784))
(CONNECT_DATA=
(SERVICE_NAME=sales.oracle.com)))

hr.oracle.com=
(DESCRIPTION=
(SEND_BUF_SIZE=8192)
(RECV_BUF_SIZE=8192)
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=hr.oracle.com)))

Configuring I/O Buffer Size on the Server
In Server side , make changes in listener.ora and sqlnet.ora files

— Listener file:

LISTENER=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)
(SEND_BUF_SIZE=11784)
(RECV_BUF_SIZE=11784))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)
(SEND_BUF_SIZE=11784)
(RECV_BUF_SIZE=11784)))

LISTENER2=
(DESCRIPTION=
(SEND_BUF_SIZE=8192)
(RECV_BUF_SIZE=16384)
(ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521)))

SQLNET file
You can set file following way in both client or Server Side.

RECV_BUF_SIZE=65536
SEND_BUF_SIZE=65536

Advertisements