Hi all,
11.2.0.3.11
aix6
Today our critical 24x7 freeze/hang hitting ORA-4031. When I checked the alert log I saw :
ORA-22303: type "SYS"."AQ$_HISTORY" not found
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 704 bytes of shared memory ("shared pool","unknown object","KGLH0^b23236d","kkscs")
Our RMAN backup at 12 midnight also aborted showing similar error, as the log shows:
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^ae4fab53","kglHeapInitialize:temp")
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^592bf3ba","kglHeapInitialize:temp")
As usual this this is a negative impact for my performance as dba, as not able to prevent or monitor PROACTIVELY the issue.
I searched for possible reason of this error in google, and I found this:
Common Causes
- SGA components too small for workload
- Auto-tuning issues
- Fragmentation due to application design
- Bug/leaks in memory allocations
Since our app been running this for months and have just encountered this error once, or maybe twice or once in a blue moon, which of the above causes is more likely the real cause? and how can I prevent it?
Can I conclude that the allocated memory is small? Do I need to increase memory allocation? My boss ask me to create a script that will alert email the tech support group once the memory usage reached 90%. Which v$history views related to memory can I query for my script?
Please help.
Thanks,
mk