In this Document
Symptoms
Changes
Cause
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.2 - Release: 10.2 to 11.2Information in this document applies to any platform.
Symptoms
You may see the one or more of the following scenarios:- Alert log shows error ORA-04030 when connecting remotely using SQL*Net:
ORA-04030: out of process memory when trying to allocate 258072 bytes (QERGH
hash-agg,kllcqas:kllsltba)
- Local connection works without any errors and user process is using about 142 MB of PGA for the session.
- Increased the parameter pga_aggregate_target to 5G or more but the error still occurs:
ALTER SYSTEM SET pga_aggregate_target='5000M' SCOPE=SPFILE SID='*';
- A heapdump shows a user process is using about 100M of PGA for the session when connecting via SQL*NET which is less than the memory used during local connection.
event= '4030 trace name heapdump level 536870917;name errorstack level 3'
Changes
The following unix/linux OS limits are set to unlimited:ulimit -aH
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) unlimited
proddm1:DOCTRACK1:/home/oracle/dwl: ulimit -aS
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) unlimited
The following unix/linux OS parameters are also set in the file /etc/security/limits for Oracle user and OS user but problem still occurs:
oracle:
fsize = -1
core = -1
cpu = -1
data = =-1
stack = -1
rss = -1
nofiles = -1
core_hard=-1
cpu_hard=-1
data_hard=-1
fsize_hard=-1
but problems still persist.
Cause
1. The problem is identified as the listener has started up with crs_start or srvctl.Srvctl or crs_start (which is not supported) uses lsnrctl start command to start the listener.
Srvctl is a wrapper script. written to start the listener. Consider it like a shell script.
From 11.2, if you install Grid for ASM and use srvctl to administer the TNS listener
then you might hit this issue.
Please note that this has nothing to do with RAC or Clusterware.
This is not an Oracle memory resource issue since connecting via SQL*Net is using less PGA for the session than a local connection which is successful.
2. If the listener is being started automatically, then we will inherit the limits of the user who started the process (root). If the root user is given the same ulimits as the oracle user, the problem should go away as when the listener autostarts on boot (root user privs), it will start with the correct ulimits.
Solution
As a workaround:1. Stop the listener and start it with lsnrctl
2. Stop and start the listener as the oracle user so it gives the listener the oracle ulimits.
References
NOTE:266219.1 - Ora-04030 While Running a query from a remote clientNOTE:470604.1 - Client Connection Fails with ORA-04030 (QERHJ hash-joi,kllcqas:kllsltba)
NOTE:399497.1 - FAQ: ORA-4030 [Video]
