Saturday, December 3, 2011

ORA-03041 shared pool issue throughout a few days on a 4 RAC nodes setup.

This was happening on an Oracle 10G R2 setup. It always the third node that got evicted every few days. One Oracle error stood out in the node#1 alert.log. It was ORA-03041.

Long story short, I spotted the AWR that the shared pool for node3 was set to different size as other nodes.

Hard parses due to an inadequately sized shared pool were consuming
significant database time.
   RECOMMENDATION 1: DB Configuration, 98% benefit (15324 seconds)
      ACTION: Increase the shared pool size by setting the value of parameter
         "shared_pool_size" to 1552 M.
   ADDITIONAL INFORMATION:
      The value of parameter "shared_pool_size" was "1408 M" during the
      analysis period.


Checking the pfile, I found these.

live3.__shared_pool_size=1526726656
live4.__shared_pool_size=1778384896
live1.__shared_pool_size=1778384896
live2.__shared_pool_size=1778384896




After bumping up the shared_pool and bounced the database, node 3 no longer getting evicted. I was not completely sure if bumping node 3 shared pool up fixed the issue. My guess was, some very large transaction filled up node3 easily and evicted.