ORA-04031 [转www.eygle.com]

一个01年的系统的db出现的问题

ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","TRIGGER$","sga heap","state objects")
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4200 bytes

所以Oracle 版本也就是8i了

[@more@]

数据库出现问题,连接上去一看,原来又是ORA-04031:

[oracle@statdata bdump]$ sqlplus "/ as sysdba" SQL*Plus: Release 8.1.7.0.0 - Production on 星期五 6月 23 11:04:31 2006 (c) Copyright 2000 Oracle Corporation. All rights reserved. ERROR: ORA-00604: error occurred at recursive SQL level 2 ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","TRIGGER$","sga heap","state objects")

sql*plus无法连接,想了一下才记起,还有svrmgrl可以用:

[oracle@statdata dbs]$ svrmgrl Oracle Server Manager Release 3.1.7.0.0 - Production Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved. Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SVRMGR> connect internal Connected. SVRMGR> shutdown immediate; ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","DATABASE","sga heap","state objects")

在Oracle8.1.7.0.0中,ORA-04031的问题是由来已久的,使用svrmgrl也不能执行shutdown immediate了.只能通过shutdown abort关闭数据库后重起.

SVRMGR> connect internal Connected. SVRMGR> shutdown abort; ORACLE instance shut down.

进一步检查发现这个数据库处于初始态运行,共享池设置的只有30M,过小的共享池设置也是导致ORA-04031的原因之一:

shared_pool_size = 31457280
db_block_buffers = 2048

对这两个参数进行了放大调整,主机毕竟有4G内存,调整后,ORA-04031错误应该会少很多了.

数据库关闭后,共享内存并未及时释放:

SVRMGR> connect internal Connected. SVRMGR> shutdown abort; ORACLE instance shut down. SVRMGR> exit Server Manager complete. [oracle@statdata dbs]$ ipcs -sa ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 2293760 oracle 640 77824 1 dest 0x00000000 2326529 oracle 640 17825792 1 dest 0x00000000 2359298 oracle 640 17825792 1 dest 0x00000000 2392067 oracle 640 20971520 1 dest 0x00000000 2424836 oracle 640 16961536 1 dest ------ Semaphore Arrays -------- key semid owner perms nsems ------ Message Queues -------- key msqid owner perms used-bytes messages

杀掉残余的Oracle进程后,共享内存释放:

[oracle@statdata dbs]$ ps -ef|grep ora oracle 4159 1 0 May11 ? 00:17:20 /export/home/oracle/product/8.1.7/bin/tnslsnr LISTENER -inherit oracle 7663 7651 0 10:47 ? 00:00:00 [sshd] oracle 7664 7663 0 10:47 pts/1 00:00:00 -bash oracle 7730 7664 0 10:48 pts/1 00:00:00 svrmgrl oracle 7731 7730 0 10:48 ? 00:00:00 oracleora8 (DESCRIPTION=(LOCAL=YES) (ADDRESS=(PROTOCOL=beq))) oracle 8344 8342 0 11:03 ? 00:00:00 [sshd] oracle 8345 8344 0 11:03 pts/2 00:00:00 -bash oracle 9094 1 0 11:19 ? 00:00:00 oracleora8 (LOCAL=NO) oracle 9101 8345 0 11:19 pts/2 00:00:00 ps -ef oracle 9102 8345 0 11:19 pts/2 00:00:00 grep ora [oracle@statdata dbs]$ kill -9 9094 [oracle@statdata dbs]$ ps -ef|grep ora oracle 4159 1 0 May11 ? 00:17:20 /export/home/oracle/product/8.1.7/bin/tnslsnr LISTENER -inherit oracle 7663 7651 0 10:47 ? 00:00:00 [sshd] oracle 7664 7663 0 10:47 pts/1 00:00:00 -bash oracle 7730 7664 0 10:48 pts/1 00:00:00 svrmgrl oracle 8344 8342 0 11:03 ? 00:00:00 [sshd] oracle 8345 8344 0 11:03 pts/2 00:00:00 -bash oracle 9113 8345 0 11:19 pts/2 00:00:00 ps -ef oracle 9114 8345 0 11:19 pts/2 00:00:00 grep ora [oracle@statdata dbs]$ ipcs -sa ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status ------ Semaphore Arrays -------- key semid owner perms nsems ------ Message Queues -------- key msqid owner perms used-bytes messages [oracle@statdata dbs]$ svrmgrl Oracle Server Manager Release 3.1.7.0.0 - Production Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved. Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SVRMGR> connect internal Connected. SVRMGR> startup ORACLE instance started. Total System Global Area 767996064 bytes Fixed Size 73888 bytes Variable Size 243462144 bytes Database Buffers 524288000 bytes Redo Buffers 172032 bytes Database mounted. Database opened.

此时数据库可以成功启动.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/202861/viewspace-915716/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/202861/viewspace-915716/