数据库11.2.0.3 在ASM instances后台会报 ORA-04031这样的错
ERROR at line 1: ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","unknown object","PCursor","kglob")
2) The shared pool free memory was close to 0 MB on the affected ASM instances:
+ASM1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select bytes/1024/1024 MB
2 from v$sgastat
3 where pool = 'shared pool' and name = 'free memory';
MB
17.8854446
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ASM2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select bytes/1024/1024 MB
2 from v$sgastat
3 where pool = 'shared pool' and name = 'free memory';
MB
22.6575012
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ASM3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select bytes/1024/1024 MB
2 from v$sgastat
3 where pool = 'shared pool' and name = 'free memory';
select bytes/1024/1024 MB
*
ERROR at line 1:
ORA-04031: unable to allocate 256 bytes of shared memory ("shared
pool","unknown object","PCursor","kglob")
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
原因:
In 11.2.0.3, the "PROCESSES" parameter will be default to "available CPU cores * 80 + 40" (in the ASM spfile). As the default value for "MEMORY_TARGET" is based on "PROCESSES", it can be insufficient if there is a large number of CPU cores or large number of diskgroups which could cause issues (i.e. Grid Infrastructure stack fails to stop with ORA-04031 etc) per Bug:13605735 & Bug:12885278, it is recommended to increase the value of MEMORY_MAX_TARGET & MEMORY_TARGET before upgrading/installing to 11.2.0.3 (does not apply to 10g ASM)
解决:
Log in to ASM: SQL> show parameter memory_target If the value is smaller than 1536m, issue the following: SQL> alter system set memory_max_target=4096m scope=spfile; SQL> alter system set memory_target=1536m scope=spfile; The number 1536m has proven to be sufficient for most environment, the change will not be effective until next restart.