Oracle Diagnostics:又见ORA-04031

« 陌上花开，可缓缓归矣 | Blog首页 | DB2新手上路:DB2命令行连接 »

今天,一个朋友的数据库出现问题,连接上去一看,原来又是ORA-04031:

[oracle@statdata bdump]$ sqlplus "/ as sysdba"

SQL*Plus: Release 8.1.7.0.0 - Production on 星期五 6月 23 11:04:31 2006

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

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

(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

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.

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

历史上的今天...
>> 2011-06-23文章:

诊断案例:PMON failed acquire latch QMNC Holder

>> 2010-06-23文章:

韩国书籍《海量数据库解决方案I》校定交稿

>> 2008-06-23文章:

RAC基本概念：PCM、IDLM、DLM等

By eygle on 2006-06-23 15:53 | Comments (0) | Case | 806 |