DATABASE CRASH WITH SGA_TARGET (Doc ID 747812.1)

Last updated on DECEMBER 08, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.5 to 10.2.0.3 [Release 10.1 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 28-Dec-2013***

Symptoms

Database can crash many times if Automatic Shared Memory Management (ASMM) is used.

Database crashes many times with the following messages from alert_log:-

Tue Oct 28 13:20:35 2008
Errors in file /ora/product/10.2.0prd/admin/oradwprd/bdump/oradwprd_cjq0_1990.trc:
Tue Oct 28 13:22:03 2008
System State dumped to trace file
Tue Oct 28 13:41:45 2008
MMNL absent for 1310 secs; Foregrounds taking over
Tue Oct 28 13:41:52 2008
MMNL absent for 1256 secs; Foregrounds taking over
MMNL absent for 1256 secs; Foregrounds taking over
MMNL absent for 1256 secs; Foregrounds taking over
Tue Oct 28 14:25:28 2008
...
Tue Oct 28 20:16:50 2008
ksvcreate: Process(m000) creation failed
Tue Oct 28 20:21:32 2008
MMNL absent for 1311 secs; Foregrounds taking over
Tue Oct 28 20:45:05 2008

The AWR report during the time the crash happens shows the following:-

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 1,280M 1,568M Std Block Size: 8K
Shared Pool Size: 720M 432M Log Buffer: 15,148K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,031.15 6,487.12
Logical reads: 15,843.66 99,674.56
Block changes: 3.61 22.70
Physical reads: 1,975.69 12,429.32
Physical writes: 454.90 2,861.83
User calls: 53.04 333.65
Parses: 2.14 13.45
Hard parses: 0.44 2.79
Sorts: 1.91 12.00
Logons: 0.03 0.18
Executes: 3.74 23.55
Transactions: 0.16

% Blocks changed per Read: 0.02 Recursive Call %: 43.94
Rollback per transaction %: 7.99 Rows per Sort: ########

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 97.12 Redo NoWait %: 100.00
Buffer Hit %: 94.80 In-memory Sort %: 99.10
Library Hit %: 84.79 Soft Parse %: 79.23
Execute to Parse %: 42.89 Latch Hit %: 95.30
Parse CPU to Parse Elapsd %: 10.35 % Non-Parse CPU: 62.73

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 14.69 25.80
% SQL with executions>1: 83.47 43.38
% Memory for SQL w/exec>1: 69.04 43.09

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: library cache 125 14,869 ###### 33.3 Concurrenc
latch: shared pool 553 13,177 23828 29.5 Concurrenc
read by other session 1,653,348 8,455 5 18.9 User I/O
CPU time 4,353 9.7
db file sequential read 841,956 3,000 4 6.7 User I/O

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms