제목: Ora-04031 And Ora-600 Errors With Lots Of Free Memory In Shared Pool Ora-04031
  문서 ID: 406317.1 유형: PROBLEM
  Modified Date: 24-APR-2007 상태: PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References


Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2
This problem can occur on any platform.

Symptoms

Customer has migrated to 10.2 at the end of Octobar..
Starting Nov 17 12:28, alot of ORA-04031 appeared on the alert log file and customer was unabled
to login to the database, after many tries, he managed to login, he flushed the shared pool which
already has almost 600MB of free memory brings it up to almost 850M.. But in vain, the database
remains "semi" hang for 30 min, till the customer restarted it.

Again in Nov 21 14:05:46 2006, ORA-00600 as a result of ORA-4031 for 20 min till database restart.

.
Starting at Fri Nov 17 12:28:23 2006
Errors in file /u01/app/oracle/admin/baanprod/bdump/baanprod_cjq0_1887.trc:
ORA-00604 : error occurred at recursive SQL level 1
ORA-04031 : unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","sql area","tmp")
..
Untill Fri Nov 17 13:00:58 2006
Errors in file /u01/app/oracle/admin/baanprod/bdump/baanprod_smon_1883.trc:
ORA-00604 : error occurred at recursive SQL level 1
ORA-04031 : unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","sql area","tmp")
Fri Nov 17 13:00:58 2006
Shutting down instance (abort) < Customer had to shutdown.


Also Starting
Tue Nov 21 14:02:16 2006
Errors in file /u01/app/oracle/admin/baanprod/udump/baanprod_ora_27141.trc:
ORA-00600: internal error code, arguments: [12333] , [7], [8], [79], [], [], [], []
...
Tue Nov 21 14:23:30 2006
LNS: Standby redo logfile selected for thread 1 sequence 3208 for destination LOG_ARCHIVE_DEST_2
Tue Nov 21 14:32:14 2006
Shutting down instance (abort)

Cause

The auto SGA functionality is the problem.

Customer has not seen the error since sga_target was set to 0 in the init/spfile.

Solution

To implement the solution, please execute the following steps:

1. As a workaround, set sga_target was set to 0 in the init/spfile.

To solve this issue "permanent":
A. Customer to turn back the auto SGA feature and provide the diagnostic info as requested

---------------------------------

1. Set memory management tracing:
sqlplus / as sysdba
SQL> alter system set "_memory_management_tracing"=7;
This will create MMON trace file in the bdump destination.
.
2. Take the SGA heapdump once a day:
sqlplus / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump heapdump 2
SQL> exit
This will generate a trace file in the udump destination. Repeat this for
seven days, so we get seven files.
.
3. Query V$SGA_RESIZE_OPS once a day:
sqlplus / as sysdba
SQL> set pages 100
SQL> col COMPONENT format a24
SQL> col PARAMETER format a24
SQL> select * from V$SGA_RESIZE_OPS;
SQL> exit

Run the above for a week and then upload:
- the resulting MMON trace file;
- seven heapdump trace files;
- query results;

---------------------------------

B. Wait for the resolution of bug 5649839 and see if we can apply the findings there for this case.

References

Bug 5677284 - ORA-04031 AND ORA-600 ERRORS WITH LOTS OF FREE MEMORY IN SHARED POOL
SR 5985810.993

Keywords

START~DATABASE; SHARED~POOL; HANGING; SQLPLUS; V$SGA_RESIZE_OPS;

'IT > Oracle' 카테고리의 다른 글

raw device 에서 file system으로의 dd copy  (0) 2009.04.24
Materialized view  (0) 2009.04.21
DB dictionary  (1) 2009.04.07
오라클 HINT  (0) 2009.04.07
오라클 데이터베이스 생성과 프로세스  (0) 2009.04.07

+ Recent posts