ORA-04030: out of process memory when trying to allocate 16328 bytes (PLSQL Opt Pool,pdziM01_Create: New Set)

ORA-04030: out of process memory when trying to allocate 16328 bytes (PLSQL Opt Pool,pdziM01_Create: New Set)
OS:RHEL 6.5
DB:11.2.0.4 SINGLE or RAC
This error can be reproduced

alert log file informations:


Fri Mar 31 13:43:53 2017
Errors in file /u01/app/oracle/diag/rdbms/hpaydb/hpaydb1/trace/hpaydb1_ora_122940.trc  (incident=245297):
ORA-04030: out of process memory when trying to allocate 16328 bytes (PLSQL Opt Pool,pdziM01_Create: New Set)
Incident details in: /u01/app/oracle/diag/rdbms/hpaydb/hpaydb1/incident/incdir_245297/hpaydb1_ora_122940_i245297.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/hpaydb/hpaydb1/trace/hpaydb1_ora_122940.trc  (incident=245298):
ORA-04030: out of process memory when trying to allocate 8168 bytes (kxs-heap-c,kdbmal allocation)
ORA-04030: out of process memory when trying to allocate 16328 bytes (PLSQL Opt Pool,pdziM01_Create: New Set)
Incident details in: /u01/app/oracle/diag/rdbms/hpaydb/hpaydb1/incident/incdir_245298/hpaydb1_ora_122940_i245298.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Mar 31 13:43:59 2017
Dumping diagnostic data in directory=[cdmp_20170331134359], requested by (instance=1, osid=122940), summary=[incident=245298].
Fri Mar 31 13:44:01 2017
Sweep [inc][245298]: completed
Sweep [inc][245297]: completed
Sweep [inc2][245298]: completed
Sweep [inc2][245297]: completed


trace file  any informations:


Dump continued from file: /u01/app/oracle/diag/rdbms/hpaydb/hpaydb1/trace/hpaydb1_ora_122940.trc
ORA-04030: out of process memory when trying to allocate 16328 bytes (PLSQL Opt Pool,pdziM01_Create: New Set)

========= Dump for incident 245297 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
35% 1413 MB, 90764 chunks: "pdziM01_Create: New Set   "  SQL
         PLSQL Opt Pool  ds=0x7ff68bc3d440  dsprt=0x7ff6aed8f950
25%  988 MB, 356315 chunks: "permanent memory          "  
         PL/SQL DIANA    ds=0x7ff6aed8fea8  dsprt=0x7ff6aed8ff48
18%  729 MB, 64768 chunks: "free memory               "  
         top call heap   ds=0xc0d1f20  dsprt=(nil)
 8%  331 MB, 50717 chunks: "permanent memory          "  SQL
         kxs-heap-c      ds=0x7ff6aed8fac8  dsprt=0xc0d1f20
 2%   79 MB, 20499 chunks: "pdzdM80_Allocate_Block    "  SQL
         PLS CGA hp      ds=0x7ff6aed8f950  dsprt=0x7ff6aed8fac8
 2%   76 MB, 75955 chunks: "phdcsql_init_string       "  SQL
         PLS CGA hp      ds=0x7ff6aed8f950  dsprt=0x7ff6aed8fac8
 2%   72 MB, 75955 chunks: "QCI kxsc                  "  
         PL/SQL DIANA    ds=0x7ff6aed8fea8  dsprt=0x7ff6aed8ff48
 1%   59 MB, 15192 chunks: "pdzgM60_Make              "  SQL
         PLS CGA hp      ds=0x7ff6aed8f950  dsprt=0x7ff6aed8fac8
 1%   57 MB, 14677 chunks: "pdz8Mz2_Make              "  SQL
         PLS CGA hp      ds=0x7ff6aed8f950  dsprt=0x7ff6aed8fac8
 1%   45 MB, 11564 chunks: "pdz7M87_Make              "  SQL
         PLS CGA hp      ds=0x7ff6aed8f950  dsprt=0x7ff6aed8fac8
 
=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
4074 MB total:                                                                <----- here we see the 4G limit being hit
  3344 MB commented, 421 KB permanent
   729 MB free (0 KB in empty extents),
    2940 MB,   1 heap:    "kxs-heap-c     "            718 MB free held
    1113 MB,   1 heap:    "callheap       "            11 MB free held
------------------------------------------------------
Summary of subheaps at depth 1
3320 MB total:
  2984 MB commented, 331 MB permanent
  4853 KB free (0 KB in empty extents),
    1885 MB,   1 heap:    "PLS CGA hp     "            4551 KB free held
    1093 MB,   1 heap:    "TCHK^e468089c  "           
------------------------------------------------------
Summary of subheaps at depth 2
2966 MB total:
  2920 MB commented, 10 MB permanent
    35 MB free (0 KB in empty extents),
    1420 MB,   1 heap:    "PLSQL Opt Pool "           
    1084 MB,   1 heap:    "PL/SQL DIANA   "           
     111 MB, 75955 chunks:  "phdcsql_init_string       " 35 MB free held
      79 MB, 20499 chunks:  "pdzdM80_Allocate_Block    "


Sql statement of the session information(the Anonymous stored procedure  A consists of 75955 single sqls):

*** 2017-03-31 13:43:54.541
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=2kmqk6rk6h24w) -----
declare
begin
update t_supplier_device_temp t set t.active_status=1,t.active_type='02', t.active_time= '20170421' where t.csn='407F236B197025D30100';
update t_supplier_device_temp t set t.active_status=1,t.active_type='02', t.active_time= '20170425' where t.csn='7F0B6B20D94562DA0100';
.......
update t_supplier_device_temp t set t.active_status=1,t.active_type='02', t.active_time= '20170422' where t.csn='982423A18268F1590100';
update t_supplier_device_temp t set t.active_status=1,t.active_type='02', t.active_time= '20170411' where t.csn='64C67E976DA46E540100';
update t_supplier_device_temp t set t.active_status=1,t.active_type='02', t.active_time= '20170423' where t.csn='401EE5AF024AAF2B0100';
end;
                                                                                                
                                                   
----- Process Map Dump -----
******************* Dumping process map ****************
00400000-0bcd4000 r-xp 00000000 fc:19 3024801                            /u01/app/oracle/product/11.2.0/db_1/bin/oracle
0bed4000-0c0cd000 rw-p 0b8d4000 fc:19 3024801                            /u01/app/oracle/product/11.2.0/db_1/bin/oracle
0c0cd000-0c124000 rw-p 00000000 00:00 0 
0d0cc000-0d15c000 rw-p 00000000 00:00 0                                  [heap]
60000000-60001000 r--s 00000000 00:10 27536592                           /dev/shm/ora_hpaydb1_917511_0
60001000-80000000 rw-s 00001000 00:10 27536592                           /dev/shm/ora_hpaydb1_917511_0
........
7ff6b0eeb000-7ff6b0eec000 rw-p 00001000 fc:19 2888649                    /u01/app/oracle/product/11.2.0/db_1/lib/libodmd11.so
7ff6b0eec000-7ff6b0eed000 rw-p 00000000 00:00 0 
7fff4dcaa000-7fff4dce6000 rw-p 00000000 00:00 0                          [stack]
7fff4ddfe000-7fff4de00000 r-xp 00000000 00:00 0                          [vdso]
ffffffffff600000-ffffffffff601000 r-xp 00000000 00:00 0                  [vsyscall]    <---End of the map dump at (Line 65532 of the Process Map Dump)
******************* End of process map dump *      

The default realfree allocator pagesize is 64 kB (65536), so 64K entries take up 4GB. With 256kB (262144) pages, the limit goes to 16GB. 

CAUSE:
These errors usually show up because of running out of map entries from the OS. There are only 65184 memory map entries per process.

OTHER:
The ORA-4030 can occur for a variety of reasons. Some common causes are:
1.OS Memory limit reached such as physical memory and/or swap/virtual paging. 
For instance, IBM AIX can experience ORA-4030 issues related to swap scenarios.
2.OS limits reached (kernel or user shell limits) that limit overall, user level or process level memory
3.OS limit on PGA memory size due to SGA attach address. Re
4.Oracle internal limit on functionality like PL/SQL varrays or bulk collections.
ORA-4030 errors will include arguments like "pl/sql vc2" "pmucalm coll" "pmuccst: adt/re". 
5.Application design causing limits to be reached
6.Bug – space leaks, heap leaks

ORA-07445 [opiaba()+639]  ORA-00600 [17147] instance crash.

ORA-07445 [opiaba()+639]  ORA-00600 [17147] instance crash.
DATABASE VERSIONS:11.2.0.4
OS:RHEL6.5
CLUSTER:RAC

alert log file deatails:

Tue Feb 21 20:00:47 2017
Archived Log entry 23144 added for thread 2 sequence 9451 ID 0x44f2486d dest
1:
Tue Feb 21 20:40:20 2017
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0]
[PC:0x186016B, opiaba()+639] [flags: 0x0, count: 1]
Errors in file
/u01/app/oracle/diag/rdbms/hpaydb/hpaydb2/trace/hpaydb2_ora_185347.trc
(incident=245089):
ORA-07445: 出现异常错误: 核心转储 [opiaba()+639] [SIGSEGV] [ADDR:0x0]
[PC:0x186016B] [SI_KERNEL(general_protection)] []
Incident details in:
/u01/app/oracle/diag/rdbms/hpaydb/hpaydb2/incident/incdir_245089/hpaydb2_ora_185347_i245089.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Feb 21 20:41:53 2017
Dumping diagnostic data in directory=[cdmp_20170221204153], requested by
(instance=2, osid=185347), summary=[incident=245089].
Tue Feb 21 20:41:54 2017
Sweep [inc][245089]: completed
Sweep [inc2][245089]: completed
Tue Feb 21 20:42:32 2017
Errors in file
/u01/app/oracle/diag/rdbms/hpaydb/hpaydb2/trace/hpaydb2_pmon_115454.trc
(incident=240017):
ORA-00600: internal error code, arguments: [17147], [0x6C1999FD0], [], [], [],
[], [], [], [], [], [], []
Incident details in:
/u01/app/oracle/diag/rdbms/hpaydb/hpaydb2/incident/incdir_240017/hpaydb2_pmon_115454_i240017.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Feb 21 20:42:33 2017
Dumping diagnostic data in directory=[cdmp_20170221204233], requested by
(instance=2, osid=115454 (PMON)), summary=[incident=240017].
Errors in file
/u01/app/oracle/diag/rdbms/hpaydb/hpaydb2/trace/hpaydb2_pmon_115454.trc:
ORA-00600: internal error code, arguments: [17147], [0x6C1999FD0], [], [], [],
[], [], [], [], [], [], []
PMON (ospid: 115454): terminating the instance due to error 472
Tue Feb 21 20:42:33 2017
opiodr aborting process unknown ospid (22236) as a result of ORA-1092

/u01/app/oracle/diag/rdbms/hpaydb/hpaydb2/incident/incdir_245089/hpaydb2_ora_185347_i245089.trc

STACK TRACE:
dbkedDefDump()+2746  call     ksedst()                                                                                                             
ksedmp()+41          call     dbkedDefDump()                                                                                                         
ssexhd()+2462        call     ksedmp()                                                                                                               
__sighandler()       call     ssexhd()                                                                                                               
opiaba()+639         signal   __sighandler()                                                                                                         
opiprs()+307         call     opiaba()                                                                                                               
kksParseChildCursor()+572  call     opiprs()                                                                                                       
rpiswu2()+1776       call     kksParseChildCursor()                                                                   
kksLoadChild()+5221  call     rpiswu2()                                                                                                              
kxsGetRuntimeLock()+2204  call     kksLoadChild()                                                                                                    
kksfbc()+15161       call     kxsGetRuntimeLock()
kkspsc0()+1171       call     kksfbc()           
kksParseCursor()+116  call     kkspsc0()          
opiosq0()+2027       call     kksParseCursor()   
kpooprx()+274        call     opiosq0()          
kpoal8()+842         call     kpooprx()          
opiodr()+917         call     kpoal8()           
ttcpip()+2183        call     opiodr()           
opitsk()+1710        call     ttcpip()           
opiino()+969         call     opitsk()           
opiodr()+917         call     opiino()           
opidrv()+570         call     opiodr()           
sou2o()+103          call     opidrv()           
opimai_real()+133    call     sou2o()            
ssthrdmain()+265     call     opimai_real()      
main()+201           call     ssthrdmain()       
__libc_start_main()+253  call     main()             
_start()+41          call     __libc_start_main() 

sql infromations:

/:65536
...skipping                   
                    TRANS_TYPE = :65536,
                    TRANS_CHANNEL = :65537,
                    PAYMENT_CHANNEL =
                    :65538,
                    TRANS_AMT = :65539,   
                    TRANS_CHARGE = :65540,           
                    CURRENCY_ID = :65541,
                    CHANNEL_SEQ_NO = :65542,
                    SEND_ORG_SEQ_NO = :65543,
                    SUB_AMT = :65544,
                    SUB_TRANS_NUM = :65545,
                    BANK_IN_ID = :65546,

core informations:
                                                  
[root@hpaydb2:/u01/app/oracle/diag/rdbms/hpaydb/hpaydb2/cdump/core_185347]#strings
core.6676 | more
  v3
0 v3
CORE
CORE
oracle
oraclehpaydb2 (LOCAL=NO) 
IGISCORE
CORE
ELIFCORE
  v3
  v3
0 v3
0 v3
@ v3
/u01/app/oracle/product/11.2.0/db_1/bin/oracle
/u01/app/oracle/product/11.2.0/db_1/bin/oracle
/lib64/ld-2.12.so
/lib64/ld-2.12.so
/lib64/ld-2.12.so
/lib64/libaio.so.1.0.1
/lib64/libaio.so.1.0.1
/lib64/libaio.so.1.0.1
/lib64/libc-2.12.so
/lib64/libc-2.12.so
/lib64/libc-2.12.so
/lib64/libc-2.12.so
/lib64/libpthread-2.12.so
/lib64/libpthread-2.12.so
/lib64/libpthread-2.12.so
/lib64/libpthread-2.12.so
/lib64/libdl-2.12.so
/lib64/libdl-2.12.so
/lib64/libdl-2.12.so
/lib64/libdl-2.12.so
/lib64/librt-2.12.so
/lib64/librt-2.12.so
/lib64/librt-2.12.so
/lib64/librt-2.12.so
/usr/lib64/libnuma.so.1
/usr/lib64/libnuma.so.1
/usr/lib64/libnuma.so.1
/lib64/libnsl-2.12.so
/lib64/libnsl-2.12.so
/lib64/libnsl-2.12.so
/lib64/libnsl-2.12.so
/dev/zero
/u01/app/oracle/product/11.2.0/db_1/bin/oracle
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero
/dev/zero

DESCRIPTION:
ORA-00600 [17147]        
Oracle has detected that the magic number in a memory chunk header has 
been overwritten. This is a heap (in memory) corruption and there is no
underlying data  corruption.
The error may occur in the one of the process specific heaps (the Call heap,
PGA heap, or session heap) or in the shared heap (SGA).

ORA-07445 [opiaba()+639]
This bug is only relevant when using PL/SQL
When using more than 65535 bind variables from PL/SQL we could
get a crash under opiaba(). This issue can also cause a memory corruption
and a subsequent ORA-600 and/or instance failure.

Bug 12578873 : INSTANCE CRASH WITH CORE DUMP IN OPIABA WHEN USING MORE THAN
65535 BINDS
 
SOLUTION:
Reduce the number of bind variables
 

11G DataGuard  ORA-01111,ORA-01110 – Recover standby  rename to dbs/UNNAMEDxxxxx

11G DataGuard  ORA-01111,ORA-01110 – Recover standby  rename to dbs/UNNAMEDxxxxx

    Today,check log file of dg.I find the mrp0 process was termial.
Because the disk is not enough space and create the new file on the primary ,that not rebuild on the standy database.
OS version:RHEL 6.5
DB version:11.2.0.4

1.the standby's alert log file

Successfully added datafile 129 to media recovery
Datafile #129: '/u02/oradata/hpaypre3/hpay36.dbf'
Mon Jan 23 09:10:53 2017
RFS[222]: Assigned to RFS process 27211
RFS[222]: Possible network disconnect with primary database
Mon Jan 23 09:12:12 2017
Archived Log entry 19108 added for thread 1 sequence 9737 ID 0x44f2486d dest 1:
Mon Jan 23 09:15:35 2017
Errors in file /u01/app/oracle/diag/rdbms/hpaypre3/hpaypre3/trace/hpaypre3_pr00_19866.trc:
ORA-19502: write error on file "/u02/oradata/hpaypre3/users06.dbf", block number 3325952 (block size=8192)
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 28: No space left on device
Additional information: -1
Additional information: 1048576
File #130 added to control file as 'UNNAMED00130'.
Originally created as:
'+DATA/hpaydb/users06.dbf'
Recovery was unable to create the file as:
'/u02/oradata/hpaypre3/users06.dbf'
Errors with log /u02/arch/hpaypre3/2_8462_909679324.dbf
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/hpaypre3/hpaypre3/trace/hpaypre3_pr00_19866.trc:
ORA-01274: cannot add datafile '+DATA/hpaydb/users06.dbf' - file could not be created
Recovery interrupted!
Mon Jan 23 09:15:49 2017
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 19186758978 but controlfile could be ahead of datafiles.
Mon Jan 23 09:15:49 2017
MRP0: Background Media Recovery process shutdown (hpaypre3)
Mon Jan 23 09:17:43 2017

......................

solution

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 24 01:53:06 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set standby_file_management=manual;
System altered.

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00130' as '/u02/oradata/hpaypre3/users06.dbf';
Database altered.

SQL> alter system set standby_file_management=auto;
System altered.

SQL>  alter database recover managed standby database disconnect from session;
Database altered.

we will found mrp process sucessful.

alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (hpaypre3)
Tue Jan 24 02:10:54 2017
MRP0 started with pid=21, OS id=31885 
MRP0: Background Managed Standby Recovery process started (hpaypre3)
 started logmerger process
Tue Jan 24 02:11:00 2017
Managed Standby Recovery not using Real Time Apply
Tue Jan 24 02:11:08 2017
Parallel Media Recovery started with 8 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Tue Jan 24 02:11:08 2017
Completed:  alter database recover managed standby database disconnect from session
Media Recovery Log /u02/arch/hpaypre3/1_9645_909679324.dbf
Media Recovery Log /u02/arch/hpaypre3/2_8462_909679324.dbf

ORACLE 11gR2 occur ORA-27300 ORA-00603 ORA-27300 ORA-27504

Database Versions:Oracle Database – Enterprise Edition – Version 11.2.0.4
OS versions:Linux OS – Version Oracle Linux 6.5

[oracle@trsen2:/home/oracle]$uname -a
Linux trsen2 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013 x86_64 x86_64 x86_64 GNU/Linux

alert log file informations at RAC's node2:
Fri Nov 11 10:11:40 2016
Archived Log entry 41145 added for thread 2 sequence 6213 ID 0x43f83f07 dest 1:
Fri Nov 11 10:17:57 2016
skgxpvfynet: mtype: 61 process 22473 failed because of a resource problem in the OS. The OS has most likely run out of buffers (rval: 4)
Errors in file /u01/app/oracle/diag/rdbms/trsenpr/trsenpr2/trace/trsenpr2_ora_22473.trc  (incident=1120001):
ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2
Incident details in: /u01/app/oracle/diag/rdbms/trsenpr/trsenpr2/incident/incdir_1120001/trsenpr2_ora_22473_i1120001.trc

Network communication issue and High Memory consumption would be observed during this time

Memory informations from em:
2016-11-11 10:04:28    99.38     
2016-11-11 10:09:28    99.35     
2016-11-11 10:14:28    99.36     
2016-11-11 10:19:28    99.38     
2016-11-11 10:24:28    99.55     
2016-11-11 10:29:28    99.55     
2016-11-11 10:34:28    99.48     
2016-11-11 10:39:28    99.46     
2016-11-11 10:44:28    65.23     
2016-11-11 10:49:28    65.20     
2016-11-11 10:54:28    65.13

CAUSE:
becuase having the database backup using RMAN, then high memory consumption .
High value of MTU for loop back adapter on UEK3 causes the issue and network  adapter MTU is default value(1500)

SOLUTION:
1.We can modify the value of MTU.
In UEK3, the MTU value should be modified as below
#ifconfig lo mtu 16436

To make the change persistent over reboot add the following line in the file /etc/sysconfig/network-scripts/ifcfg-lo :
MTU=16436
Save the file and restart the network service to load the changes
#service network restart

2.reduce High physical Memory,for example:
tunning sql
increate physical memory

3.The case ,we will change time of rman backup database.