AI协助处理-罕见的Oracle内存溢出

背景

最近刚在客户的私有云主机部署了一套Oracle DG环境，昨天开发联系我说数据库有问题连上不去了。我上机器排查发现晚上22：00 alert 日志报好多进程超时，因为环境还未投产目前仅是开发测试阶段，又是22点出现的异常，怀疑是不是客户导的表数据太多，数据库自己的维护任务卡住了。排查了一圈都没找到问题原因，没想到AI为我指明了思路。

问题日志排查

alert 日志报错

Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Mon Sep 23 22:07:28 2024
Timed out trying to start process m000.
Mon Sep 23 22:11:19 2024
Errors in file /u01/oracle/diag/rdbms/t_db/t_db/trace/t_db_cjq0_23890.trc  (incident=72225):
ORA-00445: background process "J000" did not start after 120 seconds
Incident details in: /u01/oracle/diag/rdbms/t_db/t_db/incident/incdir_72225/t_db_cjq0_23890_i72225.trc
Mon Sep 23 22:16:13 2024
Errors in file /u01/oracle/diag/rdbms/t_db/t_db/trace/t_db_smco_23938.trc  (incident=72161):
ORA-00445: background process "W002" did not start after 120 seconds
Mon Sep 23 22:17:00 2024
Incident details in: /u01/oracle/diag/rdbms/t_db/t_db/incident/incdir_72161/t_db_smco_23938_i72161.trc
Mon Sep 23 22:18:41 2024
System state dump requested by (instance=1, osid=23799 (DBW1)), summary=[abnormal instance termination].
System State dumped to trace file /u01/oracle/diag/rdbms/t_db/t_db/trace/t_db_diag_23789_20240923221843.trc
Mon Sep 23 22:19:20 2024
DBW1 (ospid: 23799): terminating the instance due to error 472
Mon Sep 23 22:19:23 2024
Dumping diagnostic data in directory=[cdmp_20240923221841], requested by (instance=1, osid=23799 (DBW1)), summary=[abnormal instance termination].
Mon Sep 23 22:19:23 2024
kkjcre1p: unable to spawn jobq slave process, slot 0, error 1092
Instance terminated by DBW1, pid = 23799

trc 文件

cat /u01/oracle/diag/rdbms/t_db/t_db/trace/t_db_cjq0_23890.trc

• ora_j000_t_db 直接超时被杀掉了

  

系统日志：

• 一直在杀Oracle 进程：Out of memory :kill process ****(Oracle)

  

LOAD 消耗情况

• 服务器和库重启后，让客户导入数据，没过多久load 44+ ,Oracle进程全部cpu:100%，更过分的一个监控平时不占资源的进程cpu:2005%,系统已经卡到无法操作了，只能让云厂商后台关机重启。

  

服务器重启，库未启动

• 服务器启动后，没有启任务程序及数据库，内存被吃掉了34G。

  

• 反馈给云厂商排查系统，最后反馈有bash文件损坏了，于是只能重装系统重新部署数据库。

AI 提供解决思路

• 对于云厂商的反馈还是存有疑虑，网上也搜了好多的贴子也没有类似的案例，于是以试试的心态问一下AI：

  

• 其中提到了内存分配策略与HugePages，于是恍然大悟HugePages管理是预分配的方式。
• 内存耗尽看来是Oracle没使用到HugePages，导致剩下的内存不够数据库使用。
• 咨询客户当时正在导入数据，查看导入命令开了7个并发。于是问题找到了，分配了HugePages但数据库并没有使用，然后并发impdp导入使用原本就紧张的内存不够用了，从而引导系统将Oracle进行kill掉。

根据AI提供的思路，排查HugePages配置及使用情况

• 先看数据库SGA设置：35G

  

• HugePages 脚本参数：vm.nr_hugepages = 17796

  

• 查看系统参数：

  

• 查看HugePages使用情况：HugePages_Rsvd 为0，未使用
  grep HugePages /proc/meminfo

  

• 查看资源限制：memlock 未设置
  cat /etc/security/limits.conf

  

• 查看oracle memlock当时默认64（Oracle 在使用）

[oracle@i-localhost~]$ ulimit -a | grep memory
max locked memory       (kbytes, -l) 64 #一个任务锁住的物理内存的最大值为64KB
max memory size         (kbytes, -m) unlimited #一个任务的常驻物理内存的最大值。
virtual memory          (kbytes, -v) unlimited #没有限制进程的最大地址空间。

• 将Oracle 的 locked memory资源限制设置为unlimited
  vim /etc/security/limits.conf

  

• 再次查看Oracle 限制

[oracle@i-localhost~]$ ulimit -a | grep memory
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
virtual memory          (kbytes, -v) unlimited

• alert 日志:Large Pages Information

************************ Large Pages Information *******************
Per process system memlock (soft) limit = 64 KB
 
Total Shared Global Region in Large Pages = 0 KB (0%)
 
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
 
RECOMMENDATION:
  Total System Global Area size is 35 GB. For optimal performance,
  prior to the next instance restart:
  1. Increase the number of unused large pages by 
 at least 17793 (page size 2048 KB, total size 35 GB) system wide to
  get 100% of the System Global Area allocated with large pages
  2. Large pages are automatically locked into physical memory.
 Increase the per process memlock (soft) limit to at least 35 GB to lock
 100% System Global Area's large pages into physical memory
********************************************************************

• 重启库

  

• 查看HugePages使用情况：HugePages_Rsvd 为16526，已使用，在Oracle刚刚启动时，大部分内存应该都是Reserved并且Free的，随着ORACLE SGA的使用，Reserved和Free都会不断的降低

[root@i-localhost~]# grep HugePages  /proc/meminfo 
AnonHugePages:      8192 kB
HugePages_Total:   17797   <---- 分配的页面数目
HugePages_Free:    16603   <---- 未使用的Hugepages数目
HugePages_Rsvd:    16599   <---- 已经被分配预留但是还没有使用的page数目
HugePages_Surp:        0   <---- 此值为0的情况很常见

• alert 日志:Large Pages Information

************************ Large Pages Information *******************
Per process system memlock (soft) limit = UNLIMITED
 
Total Shared Global Region in Large Pages = 35 GB (100%)
 
Large Pages used by this instance: 17793 (35 GB)
Large Pages unused system wide = 4 (8192 KB)
Large Pages configured system wide = 17797 (35 GB)
Large Page size = 2048 KB
********************************************************************

• 开启7个并发重新尝导入,耗时1小时23分钟，其间未发生内存耗尽的情况

 impdp two/two DIRECTORY=dpdir DUMPFILE=two-%u.dmp logfile=two_impdp.log schemas=two parallel=7 
Import: Release 11.2.0.4.0 - Production on Mon Sep 30 01:10:36 2024
... ...
Job "two"."SYS_IMPORT_SCHEMA_02" completed  at Mon Sep 30 02:33:47 2024 elapsed 0 01:23:10

心得感受

• 作为DBA部署Oracle 的熟练度应该像走路一样熟悉，但还是被路上的小石子绊倒了，再熟悉的路也要细心验证每一个安装细节，避免后续排查耗时间和精力；
• 之前听朋友讲述他们在做数据库迁移的时候，他们领导很牛，自己写了一个SQL转换器，将A库的SQL放进去就能按着B库的SQL语法转换为对应的SQL，很大的提高了他们的工作效率。有人就问领导：“是怎么做到的？”，领导：“通过AI生成代码，然后自己再根据需求进行拼接调整就ok了”。
• 上面的交谈和这次的经历，让我感受到了AI为我们的工作带来了效率和便利，以前对AI总感觉很遥远，没想到它就在身边。
• 专业的技术能为我们提供深度，AI突然让我感觉可以为我们提供广度的发展。