• Article Type: General
• Product: Aleph
• Product Version: 20, 21, 22, 23

Description:
We get the message:
  ORA-04031: unable to allocate nnn bytes of shared memory
Stopping/restarting oracle does not help.

All day today we've had intermittent trouble with our OPAC:
When someone logs in the have been receiving the message "PASSWORD NOT VERIFIED ON CONNECTABLE HOSTS".  Of course they're using their own normally functional logins, so it's not that.
If we restart the web and pc servers the problem is alleviated for an hour or two but then comes back. We're thinking that the Web OPAC is holding on to network connection for far too long.

At one point when doing ">>netstat -n |wc" we had over 800 open sockets. It's now down to 450 but still it's a lot.
Checking for active processes we got over 400, though now it's down to 261. I can't think of any major process we're running on production OPAC right now, just fyi.

Later, they started seeing these messages in the www_server log:
Oracle error: handle_connection
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate nnn bytes of shared memory

Resolution:
Rebooting the server cleared up these problems. (It had been 170 days since the server had been rebooted.)
Though routine rebooting of the physical server should not be necessary, if such problems continue, you may want to do it every month or two.

It's possible that this command might also clear it up:
SVRMGR> alter system flush shared_pool;

In a second case, with ALEPH 17, Oracle 9, with at database of 6 million bibs, where the problem kept occurring: increasing the SHARED_POOL_SIZE=268435456 (256Meg) to 512Meg corrected the problem. SGA_MAX_SIZE should also be checked.

In a third case, with Aleph 22, where the "top" command showed 1.9GB memory, with 490Meg free, increasing the amount of memory to 8GB corrected the problem.

Also, from Don Wilkerson, at juniper, in regard to Aleph 14.2 / Oracle 8:
"The ORA-4031 error is related to running out of memory in the shared pool. Usually due to bad SQL code, which I know exlibris would not do. But it can also be due to a bug with other third party software such as monitoring tools like patrol or TNG unicenter. We saw a case where there was a bug with patrol software that would take up large chunks of the shared pool and it would never release it, causing ORA-4031 errors. Albeit rebooting the box is a good thing to do every so often I wouldn't necessarily rely on that as the fix for ORA-4031. It would be advisable to do some routine checking on the shared pool and look for huge SQL that may be causing problems. Checking for fragmentation on the shared pool thru some of the X$ tables would be helpful as well. Also the version of Oracle that is used may have shared pool related bugs. I believe this is the case with lower patch levels of 8.1.7."

 

• Article last edited: 10/8/2013