ORA-04031: unable to allocate 32 bytes of shared memory

一、查错误代码含义
[oracle@oracle trace]$ oerr ora 04031
04031, 00000, “unable to allocate %s bytes of shared memory (”%s","%s","%s","%s")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
二、查看alert.log
Thu Jan 16 15:20:36 2020
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j001_138344.trc:
ORA-12012: ִؔ֯ѐطҵ 67 Զխ
ORA-01722: ϞЧ˽ؖ
ORA-06512: ՚ “CCENSE.PKG_MSG_MESSAGE_ZZYW_NEW”, line 4696
ORA-06512: ՚ line 1
Thu Jan 16 15:21:22 2020
Thread 1 cannot allocate new log, sequence 682056
说明：提供了trace文件：/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j001_138344.trc和报错信息
三、查看trace文件
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j002_172848.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production #版本
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/db
System name: Linux
Node name: oracle
Release: 4.1.12-61.1.28.el6uek.x86_64
Version: #2 SMP Thu Feb 23 20:03:53 PST 2017
Machine: x86_64
Instance name: orcl #实例名
Redo thread mounted by this instance: 1
Oracle process number: 57
Unix process pid: 172848, image: oracle@oracle (J002) #进程号
*** 2020-04-16 07:07:24.293
*** SESSION ID:(2555.63481) 2020-04-16 07:07:24.293
*** CLIENT ID:() 2020-04-16 07:07:24.293
*** SERVICE NAME:(SYS$USERS) 2020-04-16 07:07:24.293
*** MODULE NAME:() 2020-04-16 07:07:24.293
*** ACTION NAME:() 2020-04-16 07:07:24.293

AUTO MEM: PGA getfail 9 for 112, 8, 1, 33554432, 10737418240, 0, 0, 10737418240, 1
AUTO MEM: backup fail a, 0, 16121856, 320, 321, 0, 100

*** 2020-04-16 07:07:24.293

Begin 4031 Diagnostic Information

The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora _4031_dump_bitvec = 0
Allocation Request Summary Informaton

Current information setting: 04014fff
SGA Heap Dump Interval=3600 seconds
Dump Interval=300 seconds
Last Dump Time=04/16/2020 07:07:23
Dump Count=1
Allocation request for: kglsim object batch
Heap: 0x60069378, size: 3896

---

HEAP DUMP heap name=“sga heap(3,0)” desc=0x60069378
extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=0
parent=(nil) owner=(nil) nex=(nil) xsz=0x2000000 heap=(nil)
fl2=0x20, nex=(nil), dsxvers=1, dsxflg=0x0
dsx first ext=0xde000000
latch set 3 of 4
durations enabled for this heap
LibraryHandle: Address=0x1b4251338 Hash=ed39041b LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SELECT COUNT (*) FROM T_PUSH_DATA WHERE EID = :B3 AND CUSTOMERID = :B2 AND NAME = :B1
FullHashValue=4ac72a4d956bf51828a5b1dbed39041b Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3979936795 OwnerIdn=84
Statistics: InvalidationCount=0 ExecutionCount=2506 LoadCount=163 ActiveLocks=1 TotalLockCount=83 TotalPinCount=80
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 BucketInUse=11 HandleInUse=11 HandleReferenceCount=0
Concurrency: DependencyMutex=0x1b42513e8(0, 10069, 0, 0) Mutex=0x1b4251468(2555, 41507, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
说明:版本、实例名、进程号、语句
二、查看内存使用情况
[oracle@oracle trace]$ free -m
total used free shared buffers cached
Mem: 15756 15564 192 5606 102 13893
-/+ buffers/cache: 1568 14188
Swap: 16501 4814 11687
说明：现场物理内存：15G，swap：16G

三、查看虚拟内存
[oracle@oracle dbs]$ [oracle@oracle dbs]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 132G 37G 89G 29% /
tmpfs 16G 10G 6.1G 63% /dev/shm
/dev/mapper/vgdata-lvdata
493G 249G 219G 54% /u01
/dev/mapper/vgbackup-lvbackup
148G 13G 128G 9% /backup
四、查配置文件/etc/sysctl.conf
cat /etc/sysctl.conf 参数
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 8388608
kernel.shmmax = 25769803776
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
说明：现场物理内存：15G，swap：16G
kernel.shmmax=RAM80%102410241024=1580%102410241024=12884901888
kernel.shmall=kernel.shmmax/kernel.shmmni=12884901888/4096=3145728
结论：现场配置参数错误，kernel.shmall = 8388608
kernel.shmmax = 25769803776 大于了物理内存
更改正确参数
执行生效sysctl -p（无需重启服务器）