早上接到同事电话说数据库连接超时.连到服务器上,查看alert日志,发现有如下的一些提示:
process m000 died
ksvcreate :process(m000) creation failed
数据库版本为10.2.0.4 运行在64位的linux上.在metalink上查找相关的资料很少.之后用sysdba连进去操作的时候,发现已经不能正常操作,在征得同意的情况下,决定重启数据库.正常的shutdown immediate无法关闭,只有采用abort的方式(因为日志都在).在startup的时候报错:
ora-27102: out of memory linux-x86_64 error:12:cannot allocate memory
这个错误很眼熟了,回想了一下以前遇到过的,应该是lock_sga所引起的.在os上查看
[oracle@pmiscs ~]$ ulimit -a | grep memorymax locked memory (kbytes, -l) 64max memory size (kbytes, -m) unlimitedvirtual memory (kbytes, -v) unlimited
发现locked memory有限制,暂时用 ulimit -l unlimited,然后startup数据库能正常打开
此时再去查看lock_sga参数,发现果然是lock_sga=true;
在metalink上,有很多介绍这个的,如[ID 401077.1],[ID 577898.1]等,造成的原因是:
SGA_MAX_TARGET and SGA_TARGET cannot be used when LOCK_SGA is enabled or with huge pages on Linux.
The LOCK_SGA parameter, when set to TRUE, locks the entire SGA into physical memory. Therefore, this parameter cannot be used in conjunction with Automatic Memory Management or Automatic Shared Memory Management.
The "ulimit -l" parameter is not set to allow the amount of memory (sga size) being requested to be locked.
解决方案有2种:一种是将lock_sga改为false;一种是禁用内存自动管理,采取手工管理的方式去设置:
alter system set SGA_TARGET=0 scope=spfile;You must then set values for the various SGA components manually :Database buffer cache (default pool) : DB_CACHE_SIZEShared pool : SHARED_POOL_SIZELarge pool : LARGE_POOL_SIZEJava pool : JAVA_POOL_SIZEStreams pool : STREAMS_POOL_SIZE
ps:查看sga里面的分配信息以及是否可以resize可以查看v$sgainfo这个视图:
SQL> select * from v$sgainfo;NAME BYTES RES-------------------------------- ---------- ---Fixed SGA Size 1266996 NoRedo Buffers 15507456 NoBuffer Cache Size 385875968 YesShared Pool Size 637534208 YesLarge Pool Size 33554432 YesJava Pool Size 16777216 YesStreams Pool Size 0 YesGranule Size 16777216 NoMaximum SGA Size 1090519040 NoStartup overhead in Shared Pool 67108864 NoFree SGA Memory Available 011 rows selected.
而m000进程应该是awr的一个后台进程,估计是lock_sga锁定了内存之后,无法给m000这个分配内存了,所以报process(m000) creation failed.