Dear Tom,

as you have listed few out of many issues that can cause ora-04030 error, can we calculate memory useage of each session and adding all of this can we arrive at approx memory need on the DB server. I understand from you point that on Windows box (32 bit) we may hit windows limit if too many sessions are active. But if we get rough estimate of the memory required then it will help to say that how much more we need.

If you can please share how to get memory useage of each session, here I do understand that it's poosible that one of the stored procedure executed may ask much more memory than other procedures/sessions, but if you can please share your thoughts on this.

Thanking you in advance for your inputs on this.

how do i solve error ORA 12560 on oracle..?????

$ oerr ora 12560
12560, 00000, "TNS:protocol adapter error"
// *Cause: A generic protocol adapter error occurred.
// *Action: Check addresses used for proper protocol specification. Before
// reporting this error, look at the error stack and check for lower level
// transport errors. For further details, turn on tracing and reexecute the
// operation. Turn off tracing when the operation is complete.

I have experienced the same ORA-04030 error on our Oracle install of Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production, With the Real Application Clusters option on Enterprise Linux Server release 5.1 (Carthage). The stored procedure which gave me the memory outage error has been run on Oracle 8.x, 9.X and 10g for several years now without this problem. In fact I tried the same job on a WIN32 Oracle 11g install which had 1G of RAM and got the error even though our production Oracle install is a cluster RAC configuration running dual quads with 8G of RAM on each machine (2 machines in cluster).

We've looked at the Linux config settings which seem fine and played with the PGA only at this stage but still get the error. I believe 11g must have some kind of new default setting which is restricting/governing how much memory a process can consume. I have also noticed that long running jobs which took 10 hours on prior versions of Oracle now take a week or more on 11g.

Any more thoughts/ideas would be great thanks.

SQL> exec loadvgo_web_dli(0);
BEGIN loadvgo_web_dli(0); END;

*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 8176 bytes
(kxs-heap-w,qesaQBInit:buffer)
ORA-06512: at "DLIMGR.LOADVGO_WEB_DLI", line 222
ORA-06512: at "DLIMGR.LOADVGO_WEB_DLI", line 348
ORA-06512: at line 1

AND

SQL> exec step_five;
BEGIN step_five; END;

*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 8224 bytes (qesca subheap,qerjo hash table)
ORA-06512: at "DLIMGR.PKGPARCELCONVERTER_DLI", line 1289
ORA-06512: at "DLIMGR.STEP_FIVE", line 9
ORA-06512: at line 1

Hi Tom,

We are getting the foll. error from a view. Database is 10.2.0.4.

select *
from vw_c_block_abk_1
;

ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 572 bytes (Typecheck,kkecs : voppuvs)


This view is a join of 3 other views and has a scalar query that gets empl_name from remote_db
select ...
, nvl ((select dv_employee_fml_name
from dm_employee.dnr_employee@remote_db
where dv_employee_id = cb.cruiser_id
and rownum = 1), '-') as cruiser_name
.....
from .....
;

If this field is commented out, the query runs fine.

There are other views that reference ths same remote table and they work fine.


I tried a trace but the trace did not show the error.

1.

ulimit is set to unlimited
2.

tkprof:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.01 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.01 0 0 0 2

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 7.41 14.38
single-task message 1 0.04 0.04
SQL*Net message to dblink 52 0.00 0.00
SQL*Net message from dblink 52 0.00 0.02
SQL*Net break/reset to client 2 0.00 0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4376 0.10 0.08 0 0 1 0
Execute 4376 0.52 0.53 0 0 0 0
Fetch 4376 0.12 0.12 440 8799 0 4376
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13128 0.75 0.74 440 8799 1 4376

Misses in library cache during parse: 2
Misses in library cache during execute: 2

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 440 0.00 0.00

3 user SQL statements in session.
4376 internal SQL statements in session.
4379 SQL statements in session.

3.
system parameters:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1536M
sga_max_size big integer 1536M
sga_target big integer 1536M
hash_area_size integer 1048576
sort_area_size integer 524288

Previous pga_aggregate_target
pga_aggregate_target big integer 801M

4.
Statspack 1:


STATSPACK MESSAGE WHEN pga_target was 801M:
-------------------------------------------


PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 20 0

Warning: pga_aggregate_target was set too low for current workload, as this
value was exceeded during this interval. Use the PGA Advisory view
to help identify a different value for pga_aggregate_target.

%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 801 295 500.8 0.0 .0 .0 .0 102,400
E 801 703 46.9 0.0 .0 .0 .0 102,400
-------------------------------------------------------------

PGA Aggr Target Histogram DB/Inst: TSMMNDNR/tsmmndnr Snaps: 26-27
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 293 293 0 0
64K 128K 6 6 0 0
512K 1024K 24 24 0 0
1M 2M 2 2 0 0
-------------------------------------------------------------

PGA Memory Advisory DB/Inst: TSMMNDNR/tsmmndnr End Snap: 27
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0

Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
100 0.1 2,810.0 4.3 100.0 5
200 0.3 2,810.0 4.3 100.0 5
401 0.5 2,810.0 4.3 100.0 5
601 0.8 2,810.0 0.0 100.0 2
801 1.0 2,810.0 0.0 100.0 2
961 1.2 2,810.0 0.0 100.0 1
1,121 1.4 2,810.0 0.0 100.0 0
1,282 1.6 2,810.0 0.0 100.0 0
1,442 1.8 2,810.0 0.0 100.0 0
1,602 2.0 2,810.0 0.0 100.0 0
2,403 3.0 2,810.0 0.0 100.0 0
3,204 4.0 2,810.0 0.0 100.0 0
4,806 6.0 2,810.0 0.0 100.0 0
6,408 8.0 2,810.0 0.0 100.0 0
-------------------------------------------------------------

______________________________________________________________________________


5.
Statspack 2:

Due to the Warning "pga_aggregate_target was set too low for current workload", we increased the pga_aggregate_target


STATSPACK MESSAGE WHEN pga_target was increased to 1536M
--------------------------------------------------------



PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 19 0

%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 1,536 1,366 45.9 0.0 .0 .0 .0 114,646
E 1,536 1,365 46.3 0.0 .0 .0 .0 114,646
-------------------------------------------------------------

PGA Aggr Target Histogram DB/Inst: TSMMNDNR/tsmmndnr Snaps: 28-29
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 55 55 0 0
64K 128K 6 6 0 0
512K 1024K 22 22 0 0
1M 2M 2 2 0 0
-------------------------------------------------------------

PGA Memory Advisory DB/Inst: TSMMNDNR/tsmmndnr End Snap: 29
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0

Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
192 0.1 30.2 0.0 100.0 0
384 0.3 30.2 0.0 100.0 0
768 0.5 30.2 0.0 100.0 0
1,152 0.8 30.2 0.0 100.0 0
1,536 1.0 30.2 0.0 100.0 0
1,843 1.2 30.2 0.0 100.0 0
2,150 1.4 30.2 0.0 100.0 0
2,458 1.6 30.2 0.0 100.0 0
2,765 1.8 30.2 0.0 100.0 0
3,072 2.0 30.2 0.0 100.0 0
4,608 3.0 30.2 0.0 100.0 0
6,144 4.0 30.2 0.0 100.0 0
9,216 6.0 30.2 0.0 100.0 0
12,288 8.0 30.2 0.0 100.0 0
-------------------------------------------------------------



6. While running : select * from vw_c_block_abk_1

select name, value
from v$pgastat
;

NAME VALUE
---------------------------------------------------------------- ----------
aggregate PGA target parameter 1610612736
aggregate PGA auto target 1434461184
global memory bound 117397504
total PGA inuse 16762880
total PGA allocated 40198144
maximum PGA allocated 1047208960
total freeable PGA memory 6356992
process count 16
max processes count 20
PGA memory freed back to OS 1.2068E+10
total PGA used for auto workareas 0
maximum PGA used for auto workareas 8470528
total PGA used for manual workareas 0
maximum PGA used for manual workareas 536576
over allocation count 7
bytes processed 3052453888
extra bytes read/written 0
cache hit percentage 100
recompute count (total) 100140

19 rows selected.


7.

select name, value
from v$statname n, v$sesstat t
where n.statistic# = t.statistic#
and t.sid = ( select sid from v$mystat where rownum = 1 )
and n.name in ( 'session pga memory', 'session pga memory max',
'session uga memory', 'session uga memory max')
/
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 222092
session uga memory max 607508
session pga memory 781908
session pga memory max 1175124


Could you please help out on how to resolve with this error? (As a temporary fix, I created a Materialized view that selects from remote_db, and then pointed the View source to the MV insted of direct dblink)

Thanks and Appreciate your help.

Hi Tom,

I missed mentioning the Memory and Swap size :

Mem: 4082748k
Swap: 4192924k

Thanks so much!

Thanks Tom. Yes we already filed an SR yesterday - no updates yet.

Thanks!

Tom,

I am analyzing ORA-4030 (really lots of them, daily) on the following server:

Oracle 11.2.02. on AIX server with 68 GB RAM
memory_target set to 3 GB
pga_aggregate_area set to 500 M

no other application running on the server -it is dedicated db server , pls don't ask why they assigned only 3 GB of 68 RAM to the DB.

My question - how it is possible to get ORA-4030 in such configuration? I have an experience with ORA-4030/4031 in 32-bit windows world when OS can manage less memory (due to defragmentations and OS limits) than db requires, but in this case? Only about 5 % of RAM is assigned to db...
Or my understanding of ORA-4030 is incorrect? Here is your explanation of it: >> ... Whatever you were doing hit the limit on OS memory that could be allocated to you ...<<

So normally I should reduce the db settings, but not under 5 % of RAM?

Thanks in advance

I've experienced this....my money is on someone getting cute in a plsql program.

Recently we upgraded our database from 11.1.0.7 to 11.2.0.3 and the clusterware also got upgraded to 11.2.0.3. there are no changes in application and nothing drastic about the volume of data in the tables. however, the application team started seeing ora-4030 error. The memory on the server (which has 128GB of RAM) showing 80% or more consumed.

I check the AWR report for the database and the PGA consumption is around 500MB for the entire 24 hour period (not much).

Where to check the process that is sucking up the hardware resources? Are there any difference in the way oracle background process work in 11.2.0.3 or even the way clusterware?

Thanks,

ulimit is set to unlimited

g1u0527c:oracle/stage $ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 3023196
stack(kbytes) 392192
memory(kbytes) unlimited
coredump(blocks) 4194303
nofiles(descriptors) 63488

At this particular time:

Physical memory = 33487575 127.7g 100%
Free memory = 9515970 36.3g 28%

Some of the top processes are:


virtual physical swap
pid ppid pages / bytes pages / bytes pages / bytes command
26454 1 163126 637.2m 108079 422.2m 116661 455.7m crsd.bin
7074 1 2414643 9.2g 86521 338.0m 128966 503.8m oracle
15675 1 2389849 9.1g 85617 334.4m 104158 406.9m oracle
1872 1 2376096 9.1g 83665 326.8m 90331 352.9m oracle
10023 1 2382526 9.1g 83374 325.7m 96777 378.0m oracle
28809 1 2378142 9.1g 82109 320.7m 92397 360.9m oracle
20813 1 2377380 9.1g 81485 318.3m 91621 357.9m oracle

The above information was given by our sysadmin. I am still trying to interpret this.

Thanks,

ORA-04030: out of process memory when trying to allocate 512 bytes (callheap,temporary memory)
Oracle Version:11gR1 64bit
OS:SUSE Linux Enterprise Server 10 (i586)
Shared server/ dedicated :shared

error info:
<txt>Errors in file /opt/oracle/diag/rdbms/suseora/suseora/trace/suseora_pmon_27118.trc:
ORA-04030: out of process memory when trying to allocate 512 bytes (callheap,temporary memory)
</txt>

2012/08/29 09:40:04 VCS WARNING V-16-20002-207
Oracle:db_oracle:monitor:Open for ora_pmon failed, setting cookie to null

Hi Tom,

How come Oracle cannot or did not decide to write large collections to disk when they start getting too large?

Dear Tom,
Our running environment is below:
Oracle Version:11gR1 64bit
OS:SUSE Linux Enterprise Server 10 (i586)
Now,the quantitiy of business visit is very short,but after a period,the oracle will throw ora-04030 error.The detail information:
<msg time='2011-11-20T21:28:56.386+02:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
module='' pid='6323'>
<txt>Errors in file /opt/oracle/diag/rdbms/suseora/suseora/trace/suseora_pmon_6323.trc:
ORA-04030: out of process memory when trying to allocate 512 bytes (callheap,temporary memory)
</txt>
</msg>

Pl suggest how to solve it.
Thx
Peter

Stopping background process MMNL
Stopping background process MMON
Shutting down instance (immediate)
License high water mark = 5
ALTER DATABASE CLOSE NORMAL
Mon Nov 21 02:35:16 2011
SMON: disabling tx recovery
SMON: disabling cache recovery
Mon Nov 21 02:35:16 2011
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 27
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Mon Nov 21 02:35:17 2011
Stopping background process VKTM:
Mon Nov 21 02:35:20 2011
Instance shutdown complete
Mon Nov 21 02:38:14 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/oracle/product/11gR1/db/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =36
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side pfile /opt/oracle/product/11gR1/db/dbs/initsuseora.ora
System parameters with non-default values:
processes = 300
spfile = "/dev/datavg/spfile"
sga_target = 512M
control_files = "/dev/datavg/control01"
control_files = "/dev/datavg/control02"
control_files = "/dev/datavg/control03"
db_block_size = 8192
compatible = "11.1.0.0.0"
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
audit_file_dest = "/opt/oracle/admin/suseora/adump"
audit_trail

Oracle recommended the stack be unlimited and we had it limited to 4GB.

if user A using jdbc connection to access procdure owned by user B. whose ulimit is are used user A or user B ?


when a procedure is running under oracle, does oracle ulimit is used in memory allocation ?

Hi Tom,

I have just rewritten our RMAN Scripts and on every machine except one (our biggest!) it works. On the last machine (an M5000 with 256GB RAM) it Fails with the following error:

RMAN-03009: failure of backup command on ORA_DISK_7 channel at 03/04/2015 12:22:29
RMAN-10015: error compiling PL/SQL program
RMAN-10033: error during compilation of job step 7: ORA-00603: ORACLE server session terminated by fatal error
ORA-04030: out of process memory when trying to allocate 272 bytes (PL/SQL DIANA,PTG: Node)

Loooking through this threadI found the following code and ran it on the Server:

select name, value
from v$statname n, v$sesstat t
where n.statistic# = t.statistic#
and t.sid = ( select sid from v$mystat where rownum = 1 )
and n.name in ( 'session pga memory', 'session pga memory max','session uga memory', 'session uga memory max')


The result:

NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 225432
session uga memory max 225432
session pga memory 900248
session pga memory max 900248


In this thread I read that reducing the size of the PGA could resolve the Problem but when I have interpreted this Output correctly I Need to increase the size of the PGA.

That I have never had this Problem on this Server in the past is a curiosity for me.

Any hints or tips that can help?

Regards,
Kev

We had ORA-04030 errors in a 12c db when running a rather heavy statement. It worked in 11.2/Linux. It worked on 12c/AIX when runníng from the server. But, it ended with an ORA-04030 error where running through sql*net. We tried changing the Oracle memory parameters, wew tried changing the OS parameters but nothing helped. It was the sql*net/listener configuration.

We encountered an ora004030 today in our 11.2.0.4 database.
After some digging we found the culprit was a bitmap index.
We tried rebuilding it with no success.
After dropping and recreating it, the problem went away.

Ken

The error can be result of recursive procedure call,
when required stack size exceeds available pga memory.
This is guaranteed if recursion is erroneous:

create or replace procedure p(p_msg varchar2)
as
begin
p(p_msg);
end p;
/
exec p(test);