oracle exp 04031,ORA-04031 故障解决案例详细分析一则

环境：

Oracle 10g

AIX 6.1

HACMP 6

现象：

1.数据库Down。

2.alert日志中频繁报ORA-04031: unable to allocate 4120 bytes of shared memory

("shared pool",...)错误。

3.数据库每晚的逻辑导出报错：

TESTDB:/expbackup#>tail -200 backehr140322.log

EXP-00056: ORACLE error 1034 encountered

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

IBM AIX RISC System/6000 Error: 2: No such file or

directory

EXP-00005: all allowable logon attempts failed

EXP-00000: Export terminated unsuccessfully

即使是手动发起备份脚本，也是报同样的错误，备份无法进行，但是每晚的RMAN定时备份能正常进行，每天的备份集状态正常。

分析：

Oracle的解释：

[oracle@TESTDB ~]$ oerr ORA 4031

04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"

// *Cause:  More shared memory is needed than was allocated in the shared

//          pool or Streams pool.

// *Action: If the shared pool is out of memory, either use the

//          DBMS_SHARED_POOL package to pin large packages,

//          reduce your use of shared memory, or increase the amount of

//          available shared memory by increasing the value of the

//          initialization parameters SHARED_POOL_RESERVED_SIZE and

//          SHARED_POOL_SIZE.

//          If the large pool is out of memory, increase the initialization

//          parameter LARGE_POOL_SIZE.

//          If the error is issued from an Oracle Streams or XStream process,

//          increase the initialization parameter STREAMS_POOL_SIZE or increase

//          the capture or apply parameter MAX_SGA_SIZE.

查阅了一些Oracle的资料，发现ORA-04031错误一般可能由于两个原因引起的：

1.内存中存在大量碎片，导致在分配内存的时候，没有连续的内存可存放，此问题一般是需要在开发的角度上入手，比如增加绑定变量，减少应解析来改善和避免；

2.内存容量不足，需要扩大内存。

Alert Log在数据库Down之前的报了很多ORA-04031错误：

Sat Mar 22 19:00:09 2014

Errors in file /oracle/admin/oratest/bdump/oratest_j001_233886.trc:

ORA-12012: error on auto execute of job 8950

ORA-04031: unable to allocate ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,password,datats#...","Typecheck","kgghteInit")

ORA-06512: at "SYS.DBMS_SPACE", line 3289

ORA-06512: at line 1

ORA-00604: error occurred at recursive SQL level 5

ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,password,datats#...","Typecheck","kgghteInit")

ORA-06512: at "SYS.PRVT_ADVISOR", line 1624

ORA-06512: at "SYS.DBMS_ADVISOR", line 186

ORA-06512: at "SYS.DBMS_SPACE", line 1500

ORA-06512: at "SYS.DBMS_SPACE", line 1566

bytes of shared memory ("","","","")

Sat Mar 22 20:28:28 2014

Errors in file /oracle/admin/oratest/bdump/orahtest_j000_278630.trc:

ORA-12012: error on auto execute of job 42781

ORA-04031: unable to allocate ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select x.inst_id,x.indx+1,ks..."