记一次由数据泵导出报ORA-04031导致数据库卡死的解决

1. 准备变更业务进行数据备份。查看Oracle没有开归档，选择expdp进行逻辑备份，结果expdp命令输入后返回信息卡在"Processing object type"不再往下走。
2. 此时查看alter告警日志文件，发现大量报错

Thu Sep 17 11:06:32 2020
Errors in file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_ora_14642.trc  (incident=24724):
ORA-04031: 无法分配 264 字节的共享内存 ("shared pool","TB_S_JZRECORDER_CHECK","CCUR^abb88fbf","kglob")
Incident details in: /oradata/app/oracle/diag/rdbms/oasearch/oasearch/incident/incdir_24724/oasearch_ora_14642_i24724.trc
Errors in file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_dm00_16005.trc  (incident=25044):
ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","select owner#,name,namespace...","CCUR^d679a41c","kglob")
Incident details in: /oradata/app/oracle/diag/rdbms/oasearch/oasearch/incident/incdir_25044/oasearch_dm00_16005_i25044.trc

3. ORA-04031 异常说明
   Oracle在共享池中试图分配大片的连续内存失败时，首先清除池中当前没使用的所有对象，使空闲内存块合并。如果仍然没有足够大单个的大块内存满足请求,就会产生ORA-04031 错误。

4. 立刻查看OS与Oracle的内存设置信息
   OS服务器配了16G内存
   Oracle的memory_max_target=400M
   这么小的内存，可不一下子挂掉了。

5. 业务中断要求恢复。

   – 心中一万只羊驼路过，要求重启DB调整参数。
   （memory_max_target参数调整必须重启DB）

6. 不管他们了，先停止expdp命令（因为已经卡死，没有任何反应，直接Kill -9干掉）

7. 然后等待批示，看日志刷各种报错（擅自重启就地枪决Orz…）

Thu Sep 17 11:06:37 2020
Errors in file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_ora_14742.trc  (incident=24676):
ORA-04031: 无法分配 264 字节的共享内存 ("shared pool","unknown object","KGLH0^ae4fab53","kglob")
ORA-01013: 用户请求取消当前的操作
Thu Sep 17 11:06:39 2020
Errors in file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_ora_16009.trc  (incident=25060):
ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","CCUR^e3a2d601","kglob")
Thu Sep 17 11:06:40 2020
Errors in file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_m000_16034.trc  (incident=25092):
ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","KGLH0^4dc3c65e","kglob")
Sweep [inc][25092]: completed
Sweep [inc][25060]: completed
Sweep [inc][25052]: completed
Sweep [inc][25044]: completed
Sweep [inc][25028]: completed
Sweep [inc][24804]: completed
Sweep [inc][24724]: completed
Sweep [inc][24676]: completed
Sweep [inc2][25052]: completed

Thu Sep 17 11:13:58 2020
System State dumped to trace file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_ora_16156.trc
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7F9499B800C0] [PC:0x1ED0A2D, lxoPadStr()+61] [flags: 0x0, count: 1]
Errors in file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_ora_11037.trc  (incident=25356):
ORA-07445: 出现异常错误: 核心转储 [lxoPadStr()+61] [SIGSEGV] [ADDR:0x7F9499B800C0] [PC:0x1ED0A2D] [Address not mapped to object] []
Incident details in: /oradata/app/oracle/diag/rdbms/oasearch/oasearch/incident/incdir_25356/oasearch_ora_11037_i25356.trc
Thu Sep 17 11:14:04 2020
Trace dumping is performing id=[cdmp_20200917111404]
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7F9499B800C0] [PC:0x1ED0A2D, lxoPadStr()+61] [flags: 0x0, count: 1]
Errors in file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_ora_11037.trc  (incident=25357):
ORA-07445: 出现异常错误: 核心转储 [lxoPadStr()+61] [SIGSEGV] [ADDR:0x7F9499B800C0] [PC:0x1ED0A2D] [Address not mapped to object] []
Incident details in: /oradata/app/oracle/diag/rdbms/oasearch/oasearch/incident/incdir_25357/oasearch_ora_11037_i25357.trc
Trace dumping is performing id=[cdmp_20200917111407]

Thu Sep 17 11:23:59 2020
Errors in file /oradata/app/oracle/diag/rdbms/oasearch/oasearch/trace/oasearch_ora_11019.trc  (incident=25284):
ORA-00600: 内部错误代码, 参数: [729], [1016], [space leak], [], [], [], [], [], [], [], [], []
Incident details in: /oradata/app/oracle/diag/rdbms/oasearch/oasearch/incident/incdir_25284/oasearch_ora_11019_i25284.trc
Thu Sep 17 11:24:01 2020
Trace dumping is performing id=[cdmp_20200917112401]
Thu Sep 17 11:24:04 2020
Sweep [inc][25284]: completed
Sweep [inc2][25284]: completed

8. 得到中午12点可以重启的通知~
9. 解决：

ALTER SYSTEM SET memory_max_target='6525M' SCOPE=SPFILE;
ALTER SYSTEM SET memory_target='6525M' SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size='5525M' SCOPE=SPFILE;
shutdowm immediate;
startup

不到1分钟就解决了……

反思：
运维坑多，特别是这种突然过来的，万事小心得想的周全才行。