ORA-04031 [转www.eygle.com]

解决ORA-04031错误

最新推荐文章于 2021-04-15 01:47:56 发布

转载最新推荐文章于 2021-04-15 01:47:56 发布 · 111 阅读

本文介绍了解决Oracle数据库中ORA-04031错误的方法，包括使用svrmgrl进行数据库关闭及调整shared_pool_size参数。

一个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

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.

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

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

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