Why Oracle RAC Queries Fail with ORA-04030 on AIX: The Hidden Shell Limits Issue

Background

On October 20, system architect Jing Yong presented a case study about a production‑grade Oracle RAC 11g (11.2.0.4) deployment on AIX 6.1. The database runs on an IBM P720 server with 64 GB RAM, four 4‑core CPUs, and ASM, supporting a municipal public security household‑registration system that exchanges data with a social‑security card system.

Problem Statement

During a specific module’s execution, users intermittently encountered the error

ORA-04030: unable to allocate 123416 bytes (QERHJ hash‑joi,kllcqas:kllsltba) – out of process memory

while other modules ran normally. The error appeared only when the query processed larger data sets.

Initial Diagnosis

Physical memory and paging were examined first. System reports showed abundant free memory and no swapping, ruling out a hardware‑resource shortage.

Memory parameters displayed:

sga_target = 16 GB

sga_max_size = 16 GB

pga_aggregate_target = 8 GB

memory_target and memory_max_target were unset

Thus the database was using less than half of the available 64 GB.

PGA Configuration Details

The hidden parameter _pga_max_size defaults to 200 MB (range 10 MB – 4 TB‑1). The workarea_size_policy was set to AUTO, which makes the SQL work area size controlled by _smm_max_size (128 MB in this case).

Attempted Fixes

Increasing _smm_max_size to 500 MB and re‑executing the query still produced ORA‑04030.

Switching to manual PGA management ( workarea_size_policy=MANUAL) and setting hash_area_size and sort_area_size to 500 MB also failed.

Trace Analysis

Enabling a heap‑dump trace with 4030 trace name heapdump level 536870917 generated a trace file. The trace revealed a private SQL area of 111 MB and detailed PGA block allocation:

Hash area allocated 86 MB

Number of 262 144 blocks of 512 bytes each → 128 MB total PGA per session

The overall instance PGA was configured at 8 GB, but the session could only obtain 128 MB because of operating‑system limits.

Root Cause: Shell Limits

Investigation of user limits showed that only the oracle user had explicit ulimit values; the grid user (which runs the RAC clusterware and listener) relied on defaults. On AIX, defaults allow a maximum of 262 144 data blocks (128 MB) for a process.

Since the listener and the server processes it spawns run under the grid account, the missing shell limits capped the PGA allocation, leading to the ORA‑04030 error when a query required more memory.

Solution

Set appropriate shell limits for the grid user (e.g., increase data and related parameters) and restart the listener. After the change, queries that previously failed executed successfully.

Additional Q&A

Which HOME does the sqlnet.ora whitelist belong to? Answer: CRS_HOME.

What does the 4030 trace name heapdump level 536870917 event do? Answer: It triggers a heap dump useful for diagnosing memory‑allocation failures; search the error number for details.

Why does the grid user’s limit affect server processes? Answer: In RAC, the clusterware (managed by the grid user) starts the instance listener; the listener inherits the grid user’s shell limits, which propagate to the server processes it launches.

Takeaway

Always verify that both oracle and grid users have appropriate shell limits on AIX installations. Ignoring this step can silently cap PGA memory and cause intermittent ORA‑04030 failures even when system resources appear sufficient.