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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.