DB Instance Crash With ORA-4030
One of our DB instance recently sucked up to issue with memory allocation. This looked like any other memory allocation error, but they usually erred the process out but don't crash the database. This one did so something amiss for sure.
When looked up closely we realized the issue was the process reached the max limit as it could not allocate more memory and it turned out to be fatal process.
here is the road map to trouble shooting...
Error (Alert Log ) -
2014-06-22 20:17:24.592000 -04:00
Errors in file
/u01/app/oracle/diag/rdbms/idrpprd/IDRPPRD/trace/IDRPPRD_ora_10042.trc
(incident=1206454):
ORA-04030: out of process memory
when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)
Incident details in:
/u01/app/oracle/diag/rdbms/idrpprd/IDRPPRD/incident/incdir_1206454/IDRPPRD_ora_10042_i1206454.trc
Use ADRCI or Support Workbench to package
the incident.
Errors in file
/u01/app/oracle/diag/rdbms/idrpprd/IDRPPRD/trace/IDRPPRD_ora_10042.trc
(incident=1206455):
ORA-04030: out of process memory when
trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when
trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)
ORA-04030: out of process memory
when trying to allocate 288 bytes (kkoutlCreatePh,kkojo : kkoiqb)
ORA-06500: PL/SQL: storage error
ORA-04030: out of process memory
when trying to allocate 16328 bytes (koh-kghu sessi,pmuccst: adt/record)
ORA-06512: at "REPORTING.DMD_PEG_BULK_COLLECT",
line 208
ORA-06512: at
"REPORTING.DMD_PEG_BULK_COLLECT", line 1816
ORA-06512: at
"REPORTING.DMD_PEG_RUN_ALG", line 404
ORA-04030: out of process memory when
trying to allocate 16328 bytes (koh-kghu call ,pmuccst: adt/record)
Use ADRCI or Support Workbench to package
the incident.
See Note 411.1 at My Oracle Support for
error and packaging details.
2014-06-23 12:05:06.552000 -04:00
Sweep [inc][1205958]: completed
2014-06-23 13:06:13.075000 -04:00
Suspending MMON slave action kehslave_
for 82800 seconds
Cause -
The Issue was happening when the application was trying to run lengthy PL/SQL program. This was running for some time. The issue was during memory allocation, process is limiting at 4GB while running this program.
The trace file also confirms that the
process is limiting at 4GB
=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
4061 MB
total: <<--- Process max PGA is limiting at 4G
4060 MB commented, 818 KB permanent
150 KB free (0 KB in empty
extents),
3274 MB, 1
heap: "session heap "
785
MB, 3 heaps: "koh-kghu call
" 18 KB
free held
ffffffffff600000-ffffffffffe00000 ---p
00000000 00:00
0 [vdso] << --- Process dump limiting at
4G
These errors usually show up
because of running out of map entries from the OS. There are only 65536 memory
map entries per process with 64K page size.
So mathematically - 4GB memory/ 64K pagesize
(4*1024*1024) K /64 K --> 65536
Solution will be to
either modify the DB or OS Parameters
On our Server the setting is -
oracle@xxxxx> more
/proc/sys/vm/max_map_count
65536
Change the page count at the OS level:
# sysctl -w vm.max_map_count=200000 (or anything higher , usually double is preferred)
Here we increase the total page map count and page size remains the same. so it comes to around 12.20 GB
Total memory available for process - 200000* 64K = 12.20G
OR
Adjust the realfree heap pagesize
within the database by setting the following parameters in the init/spfile and
restart the database.
_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint
= 262144
Total memory available to process will be - 65K page maps * 256K pagesize = 16G
