今天,一个朋友的数据库出现问题,连接上去一看,原来又是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.
|
此时数据库可以成功启动.
