ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-04030 When Clients Connect Across Network Using SQL*Net [ID 753439.1]

ORA-04030 When Clients Connect Across Network Using SQL*Net [ID 753439.1]

原创 Linux操作系统 作者:spider0283 时间:2012-02-14 00:18:26 0 删除 编辑

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.2
Information 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 client
NOTE:470604.1 - Client Connection Fails with ORA-04030 (QERHJ hash-joi,kllcqas:kllsltba)
NOTE:399497.1 - FAQ: ORA-4030 [Video]
返回页首返回页首

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-716197/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    609345