6 Replies Latest reply: Sep 3, 2010 6:22 AM by rajeysh RSS

    ora-04030 error in dataguard environment

    rajeysh
      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.