Monthly Archives: February 2013

How to reproduce ORA-04031

You are able to reproduce the ORA-04031 with shared pool depletion by pin dbms_shared_pool procedure by all the cursor on the SGA as shown below, it prohibits the release.

 conn / as sysdba
i number;
j char (30);
sqlstr varchar2 (100);
cursor cur_pin is
select rawtohex (address) | | ',' | | to_char (hash_value)
from sys.v_ $ sql where kept_versions = 0;
rec_pin varchar2 (1000);
i: = 1;
i: = i +1;
exit when i mod 1000 = 0;
sqlstr: = 'select''' | | i | |'' 'from dual';
execute immediate sqlstr into j;
end loop;
open cur_pin;
fetch cur_pin into rec_pin;
exit when cur_pin% notfound;
dbms_shared_pool.keep (rec_pin, 'x');
end loop;
close cur_pin;
end loop;

No buffer space available (maximum connections reached?)

Caused by: No buffer space available (maximum connections reached?): connect

This is most likely due to the number of ephemeral TCP ports being exhausted, the server has too many active client connections due to which the ephemeral TCP ports are all used up and in this case no more ports can be allocated to a new client connection request resulting in the behaviour we see here that no new connections can be setup.

Quoting from Wikipedia entry:

“Ephemeral port is a transport protocol port for Internet Protocol (IP) communications allocated automatically from a predefined range by the TCP/IP Stack Software. It is typically used by the TCP, UDP or SCTP as port for the client end of a client-server communication when the application doesn’t bind the socket to a specific port number, or by a server application to free up service’s well known listening port and establish a service connection to the client host. The allocations are temporary and only valid for the duration of the connection. After completion of the communication session the ports become available for reuse, although most implementations simply increment the last used port number until the ephemeral port range is exhausted.”

This is a known issue on Windows boxes, a hotfix is available from Microsoft: “Kernel sockets leak on a multiprocessor computer that is running Windows Server 2008 R2 or Windows 7” –

HOW TO: Disable DNS caching

For Solaris:
To disable the DNS cache on Solaris
1. Edit the file /etc/nscd.conf as follows:
a. un-comment the line # enable-cache hosts no.
b. add the line enable-cache ipnodes no after the line above.
2. To activate the changes, restart the nscd daemon by typing the command:
$ svcadm restart svc:/system/name-service-cache

For AIX:
To disable the DNS cache on AIX 6.1
DNS caching is managed by the network caching daemon (netcd).
1. stop the network caching daemon : #stopsrc -s netcd
2. prevent the network caching daemon from starting at boot time, edit the file /etc/rc.tcpip:
a. comment the line for the netcd daemon as follows:
# Start up binld daemon
#start /usr/sbin/binld “$src_running”
For more information see IBM Technote T1010925

For Linux:
To disable the DNS cache on Linux
1. Edit the file /etc/nscd.conf as follows:
a. un-comment or update the line to reflect: enable-cache hosts no
2. To activate the changes, restart the nscd daemon by typing the command:
# service nscd restart
3. Make sure the dnsmasq service not running:
a. stop the service : # service dnsmasq stop
b. disable the service from starting at boot time :
# chkconfig dnsmasq off
# chkconfig –list dnsmasq
dnsmasq         0:off   1:off   2:off   3:off   4:off   5:off   6:off