Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
System name: Linux
Release: 2.6.18-53.el5
Version: #1 SMP Wed Oct 10 16:34:19 EDT 2007
Machine: x86_64
ACTIVE DATAGUARD WITH OBSERVER. ora-04030 error
Alert log information:
Thu Sep 02 02:00:00 2010
Clearing Resource Manager plan via parameter
Thu Sep 02 10:16:31 2010
Errors in file /u01/app/oracle/diag/rdbms/agbcdb/agbc/trace/agbc_asmb_4312.trc (incident=88030):
ORA-04030: out of process memory when trying to allocate 592 bytes (callheap,kfmditer)
Incident details in: /u01/app/oracle/diag/rdbms/agbcdb/agbc/incident/incdir_88030/agbc_asmb_4312_i88030.trc
Thu Sep 02 10:16:36 2010
Trace dumping is performing id=[cdmp_20100902101636]
Errors in file /u01/app/oracle/diag/rdbms/agbcdb/agbc/trace/agbc_asmb_4312.trc:
ORA-04030: out of process memory when trying to allocate 592 bytes (callheap,kfmditer)
ASMB (ospid: 4312): terminating the instance due to error 4030
Thu Sep 02 10:16:36 2010
ORA-1092 : opidrv aborting process unknown ospid (13456_46912513432672)
Instance terminated by ASMB, pid = 4312
Thu Sep 02 10:16:37 2010
Instance termination got error 27120 from SGA destruction.
Error cleared. Process exiting.
49270,3 Bot
trace file information:
Trace file /u01/app/oracle/diag/rdbms/agbcdb/agbc/trace/agbc_asmb_4312.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db
System name: Linux
Release: 2.6.18-53.el5
Version: #1 SMP Wed Oct 10 16:34:19 EDT 2007
Machine: x86_64
Instance name: agbc
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 4312
*** 2010-09-02 10:16:31.510
*** SESSION ID:(321.1) 2010-09-02 10:16:31.510
*** CLIENT ID:() 2010-09-02 10:16:31.510
*** SERVICE NAME:(SYS$BACKGROUND) 2010-09-02 10:16:31.510
*** MODULE NAME:() 2010-09-02 10:16:31.510
*** ACTION NAME:() 2010-09-02 10:16:31.510
Incident 88030 created, dump file: /u01/app/oracle/diag/rdbms/agbcdb/agbc/incident/incdir_88030/agbc_asmb_4312_i88030.trc
"/u01/app/oracle/diag/rdbms/agbcdb/agbc/trace/agbc_asmb_4312.trc" 31L, 1346C 13,1 Top
ORA-04030: out of process memory when trying to allocate 592 bytes (callheap,kfmditer)
error 4030 detected in background process
ORA-04030: out of process memory when trying to allocate 592 bytes (callheap,kfmditer)
*** 2010-09-02 10:16:36.501
ASMB (ospid: 4312): terminating the instance due to error 4030
skgm error 27120: errno = 12, info = 5, 163842, 0, 0, 46912513437872, 1, 46912513437872
the database failover to STANBY, today morning the standby database instance also terminated with the same error message.
we followed metalink documents to troubleshoot.
SQL> ! ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 139264
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 65536
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
max rt priority (-r) 0
stack size (kbytes, -s) 32768
cpu time (seconds, -t) unlimited
max user processes (-u) 2047
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL>
SQL> select * from v$pgastat;
NAME VALUE UNIT
--------------------------------------------- ---------- ------------
aggregate PGA target parameter 2684354560 bytes
aggregate PGA auto target 167772160 bytes
global memory bound 268431360 bytes
total PGA inuse 4780392448 bytes
total PGA allocated 5534604288 bytes
maximum PGA allocated 5551688704 bytes
total freeable PGA memory 269811712 bytes
process count 180
max processes count 181
PGA memory freed back to OS 681246720 bytes
total PGA used for auto workareas 0 bytes
NAME VALUE UNIT
--------------------------------------------- ---------- ------------
maximum PGA used for auto workareas 2717696 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 2757
bytes processed 4510057472 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 2757
19 rows selected.
SQL>
set linesize 120
col program format a10
col used format 999999.99
col alloc format 999999,99
col max format 9999999.9
select spid,program,trunc(pga_used_mem/1024/1024) USED,
trunc(pga_alloc_mem/1024/1024) ALLOC, trunc(pga_max_mem/1024/1024) MAX
FROM V$PROCESS
ORDER BY PGA_ALLOC_MEM DESC;
SQL> COL PROGRAM FORMAT A40
SQL> RUN
1 select spid,program,trunc(pga_used_mem/1024/1024) USED,
2 trunc(pga_alloc_mem/1024/1024) ALLOC,
3 trunc(pga_max_mem/1024/1024) MAX
4 FROM V$PROCESS
5* ORDER BY PGA_ALLOC_MEM DESC
SPID PROGRAM USED ALLOC MAX
------------------------ ---------------------------------------- ---------- ---------- ----------
*4348 oracle@AG-HO-DBS2.agbc.com (ASMB) 4068.00 40,70 4070.0*
4448 oracle@AG-HO-DBS2.agbc.com (RSM0) 3.00 2,26 446.0
4336 oracle@AG-HO-DBS2.agbc.com (DBW0) 5.00 33 41.0
4429 oracle@AG-HO-DBS2.agbc.com (ARCt) 11.00 28 32.0
SQL>
SQL> SELECT SUM(VALUE)/1024/1024 MB
2 FROM V$SESSTAT S,V$STATNAME N
3 WHERE N.STATISTIC#=S.STATISTIC#
4 AND NAME='session pga memory';
MB
----------
4578.77262
SQL>
SQL> run
1 select sid,name,value
2 from v$statname n,v$sesstat s
3 WHERE N.STATISTIC#=S.STATISTIC#
4 and name like'session%memory%'
5* order by 3 asc
SID NAME VALUE
---------- ------------------------------ ----------
119 session pga memory max 22846056
280 session pga memory max 235067848
321 session pga memory max 4267874920
SID NAME VALUE
---------- ------------------------------ ----------
321 session pga memory 4267874920
716 rows selected.
SQL> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
SQL> show parameter workarea_size_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
our manager raised SR, today morning standby also went down because of same ora-04030 error. we started the standby now and check the pga stats.
SQL> select spid,program,trunc(pga_used_mem/1024/1024) USED,
2 trunc(pga_alloc_mem/1024/1024) ALLOC,
3 trunc(pga_max_mem/1024/1024) MAX
4 FROM V$PROCESS
5 ORDER BY PGA_ALLOC_MEM DESC
6 /
SPID PROGRAM Used (in MB) ALLOC MAX
------------------------ --------------------------------------------- ------------ ---------- -----------
4372 oracle@AG-HO-DBS2.agbc.com (LGWR) 11.00 24.00 24.00
4473 oracle@AG-HO-DBS2.agbc.com (ARCd) 11.00 24.00 24.00
4503 oracle@AG-HO-DBS2.agbc.com (ARCs) 11.00 24.00 24.00
SQL> SELECT SUM(VALUE)/1024/1024 MB
2 FROM V$SESSTAT S,V$STATNAME N
3 WHERE N.STATISTIC#=S.STATISTIC#
4 AND NAME='session pga memory';
MB
----------
453.999092
SQL> run
1 select sid,name,value
2 from v$statname n,v$sesstat s
3 WHERE N.STATISTIC#=S.STATISTIC#
4 and name like'session%memory%'
5* order by 3 asc
309 session pga memory 12753512
326 session pga memory 12810392
326 session pga memory max 12827448
336 rows selected.
SQL>
now the memory used by pga is very less,
my question: 1. why ASMB takes too much memory?
2. after switch over to standby database also the memory used by ASMB process is not released very high nearly 99.99 percent?
Thanks and regards,
Rajeshkumar Govindarajan.