oracle4031,当心ORACLE 12.2 RAC新特性引入的BUG导致ORA-4031

前几日朋友有套数据库升级到oracle 12C r2后出现了ORA-4031, 是一套on Linux x86 64-bit 2节点的RAC数据库，Hard parse并不高，并且没有使用AMM, ASMM。SGA分配80G， shared pool固定在13Gb.

— nodel 1 db alert log file

2018-05-29T13:44:51.107475+08:00

Thread 1 advanced to log sequence 6680 (LGWR switch)

Current log# 14 seq# 6680 mem# 0: +SSDDG1/anbob/ONLINELOG/group_14.260.944852559

2018-05-29T13:57:28.332831+08:00

Errors in file /oracle/app/diag/rdbms/anbob/anbob1/trace/anbob1_m001_38524.trc (incident=1804178):

ORA-04031: unable to allocate 640 bytes of shared memory ("shared pool"," SELECT source, (case...","kkqctdrvTD: co","ckydef:kkqcscpcky")

2018-05-29T13:57:28.332861+08:00

Errors in file /oracle/app/diag/rdbms/anbob/anbob1/trace/anbob1_ora_38534.trc (incident=1804506):

ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select con#,obj#,rcon#,enabl...","SQLA","tmp")

Incident details in: /oracle/app/diag/rdbms/anbob/anbob1/incident/incdir_1804506/anbob1_ora_38534_i1804506.trc

Incident details in: /oracle/app/diag/rdbms/anbob/anbob1/incident/incdir_1804178/anbob1_m001_38524_i1804178.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

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 /oracle/app/diag/rdbms/anbob/anbob1/trace/anbob1_m001_38524.trc (incident=1804179):

ORA-04031: unable to allocate 4152 bytes of shared memory ("shared pool"," SELECT source, (case...","9003.kgght","9003.kgght")

ORA-04031: unable to allocate 640 bytes of shared memory ("shared pool"," SELECT source, (case...","kkqctdrvTD: co","ckydef:kkqcscpcky")

Incident details in: /oracle/app/diag/rdbms/anbob/anbob1/incident/incdir_1804179/anbob1_m001_38524_i1804179.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

2018-05-29T13:57:28.588932+08:00

ORA-04031 heap dump being written to trace file /oracle/app/diag/rdbms/anbob/anbob1/incident/incdir_1804506/anbob1_ora_38534_i1804506.trc

2018-05-29T13:57:28.971700+08:00

Errors in file /oracle/app/diag/rdbms/anbob/anbob1/trace/anbob1_ora_36389.trc (incident=1804658):

ORA-04031: unable to allocate 424 bytes of shared memory ("shared pool","EXEC_OPR_POS","KGLS^62f5cb39","KGLS MEM BLOCK")

Incident details in: /oracle/app/diag/rdbms/anbob/anbob1/incident/incdir_1804658/anbob1_ora_36389_i1804658.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

2018-05-29T13:57:29.086756+08:00

Errors in file /oracle/app/diag/rdbms/anbob/anbob1/trace/anbob1_ora_75557.trc (incident=1800986):

ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select * from (select id,msi...","SQLA","tmp")

Incident details in: /oracle/app/diag/rdbms/anbob/anbob1/incident/incdir_1800986/anbob1_ora_75557_i1800986.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

M001进程在申请”shared pool”遇到ora-4031, 当然问题时间段的AWR没有生成， 当时建议做heapdump如下

SQL> connect / as sysdba

SQL> alter session set events 'immediate trace name heapdump level 536870914';

OR

sqlplus "/ as sysdba"

oradebug setmypid

oradebug unlimit

oradebug dump heapdump 536870914

oradebug tracefile_name

exit

让现场的收集了部分trace file, 并没有什么异常信息，这时可以看问题时间点前最近的AWR，找”SGA breakdown difference”部份，初步判断异常变化。

Note:

“gc index split transaction” 内存区使用超过了5Gb， 这在以前的版本中是很少见的. 并且继续确认了几份AWR，该heap 内存区时一直增长的， 如果并且如果手动从X$KSMSS 确认当前的”gc index split transaction”也是在持续增长。

gc index split transaction

gc index split transaction 是用于Oracle 12cr2 引入的新特性”fast index split wait”， 该特性是在KCL layer实现，用于RAC的Global Cache，使用它表示一个进程index splits等待远程或本地进程， 基本上每个session在连接时都会分配一组buffer. 所以这个现象只存在于RAC环境。并且配置_gc_fast_index_split_wait=0没有什么作用。

似乎是因为使用了kclxidinit() used kghalp (permanent memory) 分配的是perm trunk，而不是kghalf (freeable memory)

解决方案

Mos中查找了一下，比较符合bug 27163928.

重启可以临时解决问题，但是会逐渐再次因为fast index split wait增长导致ora-4031.

or

安装one-off patch 27163928

or

升级到oracle 18.1(if released!)