I am a technical consultant for IFS Australia. I have encountered the Oracle error ORA-04031: Unable to allocate %s bytes of shared memory. The database is up and running, but I get this error when I open the application. My client is using Oracle version 184.108.40.206.0 on Windows 2000 Server. I ran into this problem after I created a test database and imported the dump file of the production database into Test database. The shared_pool_size is the same as that of production (200M), and we are running just one application (Financials) in their database. I do not have this problem in production database. The java_pool_size and all the other initialization parameters are exactly the same as in Production. I have tried the following recommendations from metalink:
- Used the parameter _db_handles_cached=0
- Used SHARED_POOL_RESERVED_SIZE = 20M (10% of shared_pool_size)
- Used SHARED_POOL_RESERVED_MIN_ALLOC = 5000
- Will it be possible to rollback to previous Oracle version i.e., from 220.127.116.11.4 to 18.104.22.168.0 (I know that sounds stupid, but I just wanted to know)?
- Will increasing the servers memory (RAM) resolve the issue?
- Any problems that I might encounter in upgrading (I didn't have any problems in upgrading my computer)?
- Any other suggestions you might think of to resolve this issue?
Another possible cause for the 4031 error is if your log_buffers parameter is too low. If you take the default setting for log_buffers when you create the database, it'll typically be 32K. This is typically way too small to allow LGWR to write the info in the redo log buffer to the redo log files in a timely enough fashion. So... what happens is that shared memory gets to help with the burden and you get the 4031 error. If you haven't done so already, check your log_buffers parameter and make sure it is set to a significantly larger value than the default (I'd suggest trying 512K to start).
If you've already got your log_buffers set high enough, the only advice I could offer would be the same as you got on Metalink. So.... I'll keep my fingers crossed that this is the culprit! Good luck.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.