问题：
1.重建完DATAPUMP后，后台出现大量ORA-04031报错,业务无法连接。
该报错为SHARED POOL大小不足导致的。
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
该报错为SHARED POOL大小不足导致的。
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
原因：
是由于streams pool无法释放的原因导致SHARED POOL无法增长。
解决办法：
2.查看SHARED POOL大小以及SGA大小
发现streams pool占用较大，该池为导数使用的池。
SQL> select sum(bytes/1024/1024) mb from v$sgastat where pool='shared pool'
2 ;
MB
----------
2905.55228
SQL> show sga
Total System Global Area 1.6106E+10 bytes
Fixed Size 2080240 bytes
Variable Size 7499416080 bytes
Database Buffers 8589934592 bytes
Redo Buffers 14696448 bytes
SQL> select pool,sum(bytes/1024/1024) from v$sgastat group by pool;
POOL SUM(BYTES/1024/1024)
------------ --------------------
8207.9995
shared pool 2905.55228
streams pool 7099.38935
large pool 80
java pool 32
3.尝试加大shared pool
没有足够的内存给shared pool使用。
SQL> alter system set shared_pool_size=3000m;
alter system set shared_pool_size=3000m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
4.收缩BUFFER POOL
alter system set db_cache_size=7g scope=both;
收缩后自动分配给SHARED POOL，业务恢复连接。
发现streams pool占用较大，该池为导数使用的池。
SQL> select sum(bytes/1024/1024) mb from v$sgastat where pool='shared pool'
2 ;
MB
----------
2905.55228
SQL> show sga
Total System Global Area 1.6106E+10 bytes
Fixed Size 2080240 bytes
Variable Size 7499416080 bytes
Database Buffers 8589934592 bytes
Redo Buffers 14696448 bytes
SQL> select pool,sum(bytes/1024/1024) from v$sgastat group by pool;
POOL SUM(BYTES/1024/1024)
------------ --------------------
8207.9995
shared pool 2905.55228
streams pool 7099.38935
large pool 80
java pool 32
3.尝试加大shared pool
没有足够的内存给shared pool使用。
SQL> alter system set shared_pool_size=3000m;
alter system set shared_pool_size=3000m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
4.收缩BUFFER POOL
alter system set db_cache_size=7g scope=both;
收缩后自动分配给SHARED POOL，业务恢复连接。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29123031/viewspace-2122527/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
如何将shell脚本进行加密？
请登录后发表评论
登录
全部评论
