Thursday, July 10, 2014

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

The default realfree allocator pagesize is 64 kB (65536), so 64K entries take up 4GB. With 256kB (262144) pagesize, the limit goes to 16GB.

Total memory available to process will be - 65K page maps * 256K pagesize = 16G