ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","CCUR^d679a41c","kglob")

Applies To:

Oracle Server - Enterprise Edition - Version: 7.3.4.0 to 11.2.0.2 - Release: 7.3.4 to 11.2
Information in this document applies to any platform.
This applies to any platform

Concept and Definition:

An ORA-4031 error is raised when memory is unavailable for use or reuse in the System Global Area (SGA). The error message will indicate the memory pool getting errors and high level information about what kind of allocation failed and how much memory was unavailable. The problem with ORA-4031 analysis is that the error and associated trace is for a “victim” of the problem. The failing code ran into the memory limitation, but in almost all cases it was not part of the root problem. The error might be caused by

* SGA components too small for workload
* Auto-tuning issues
* Fragmentation due to application design
* Bug/leaks in memory allocations

Common Solution:

The ORA-4031 can occur for so many different reasons that is difficult to lump this error into a specific solution or even a handful of solutions. Many cases, fall into configuration tuning. Ensuring that MEMORY_TARGET or SGA_TARGET are large enough to accommodate workload can get around many scenarios.

Oracle 11G

For implementation in Oracle 11g startup database in mount mode:
$ export ORACLE_SID=[instance]
$ sqlplus sys as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.
SQL> alter system set memory_max_target=2000m scope=spfile;
System altered.
SQL> alter database open;
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.
Database opened. 
SQL> alter system set memory_target=1600m;
System altered.

Oracle 10G

 For implementation in Oracle 10g startup database in mount mode:
$ export ORACLE_SID=[instance]
$ sqlplus sys as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.
SQL> alter system set sga_max_size=2000M scope=spfile;
System altered.
SQL> alter database open;
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.
Database opened. 
SQL> alter system set sga_target=1600m scope=spfile;;
System altered.

Check alert log file for same issue again. If you are still not able to solve this error use Oracle Support Provided tool to solve ORA-04031.

No comments:

Post a Comment