联系:手机/微信(+86 17813235971) QQ(107644445)
标题:large pool太小导致shared server异常
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库出现如下错误
Fri Oct 5 09:33:54 2012 Process S001 started up but failed with error = 20 failed to start shared server 'S001', oer=20 Process S001 started up but failed with error = 20 failed to start shared server 'S001', oer=20 Process S001 started up but failed with error = 20 failed to start shared server 'S001', oer=20 Process S001 started up but failed with error = 20 failed to start shared server 'S001', oer=20 Process S001 started up but failed with error = 20 failed to start shared server 'S001', oer=20 Process S001 started up but failed with error = 20 failed to start shared server 'S001', oer=20 Process S001 started up but failed with error = 20 failed to start shared server 'S001', oer=20 Process S001 started up but failed with error = 20 failed to start shared server 'S001', oer=20
重启后错误提示变为
Successfully onlined Undo Tablespace 1. Fri Oct 5 09:34:41 2012 SMON: enabling tx recovery Fri Oct 5 09:34:41 2012 Database Characterset is AL32UTF8 replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN Fri Oct 5 13:53:50 2012 Errors in file /oracle/admin/ora/bdump/ora_s000_25948.trc: ORA-04031: unable to allocate 72 bytes of shared memory ("large pool","unknown object","session heap","trigger condition node") Fri Oct 5 13:53:50 2012 Errors in file /oracle/admin/ora/bdump/ora_s000_25948.trc: ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], [] ORA-04031: unable to allocate 72 bytes of shared memory ("large pool","unknown object","session heap","trigger condition node") Fri Oct 5 13:54:52 2012 found dead shared server 'S000', pid = (11, 1) Fri Oct 5 17:25:59 2012 Errors in file /oracle/admin/ora/bdump/ora_s000_31081.trc: ORA-04031: unable to allocate 72 bytes of shared memory ("large pool","unknown object","session heap","trigger condition node") Fri Oct 5 17:25:59 2012 Errors in file /oracle/admin/ora/bdump/ora_s000_31081.trc: ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], [] ORA-04031: unable to allocate 72 bytes of shared memory ("large pool","unknown object","session heap","trigger condition node")
通过这里的错误,我们可以看到是large pool不能分配72 bytes的连续内存空间而使得S000进程报错.那这两者有什么联系:我们知道S000是shared server的进程,那shared server为什么导致large pool不足呢?查询官方文档得出,如下三种情况会使用large pool
Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database) I/O server processes Oracle backup and restore operations
主要也就是shared server/parallel query buffers/backup restore这几个操作会使用到large pool.在该案例中很明显的可以看到是因为shared server进程需要分配large pool中一部分空间,而没有连续空间从而出现该错误.数据库相关参数配置
SQL> show parameter mts; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ mts_circuits integer 555 mts_dispatchers string (PROTOCOL=TCP) (SERVICE=oraXDB) mts_listener_address string mts_max_dispatchers integer 5 mts_max_servers integer 20 mts_multiple_listeners boolean FALSE mts_servers integer 1 mts_service string ora mts_sessions integer 550 SQL> show parameter large; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ large_pool_size big integer 16777216
这里可以看出来,数据库明显配置了MTS,因为数据库在启动时候,最少会建立一个shared server进程,而这个时候因为large pool太小(16M),导致该进程无法正常建立,从而出现上述alert中相关错误,临时处理方法增加large pool.后续需要关注业务特点,考虑是否可以采用Oracle Dedicated server模式来处理.