Oracle 数据库内存不足导致的停库问题

Oracle 数据库内存不足导致的停库问题

一、问题描述

用户的两节点 Oracle 11g 集群，配置 Dataguard，近期发现 dg 数据库多次停库，数据也无法同步。启动数据库，并执行以下命令 dg 数据库恢复正常：

alter database recover managed standby database using current logfile disconnect;
1

但 dg 数据库运行几天以后又出现停库。9 月 20 日、9 月 24 日、10 月 4 日连续出现该问题。

10 月 4 日导出 alert 日志，分析 alert 日志发现如下错误信息：

System State dumped to trace file /oracle/app/oracle/diag/rdbms/hisdbdg/hisdb/trace/hisdb_diag_9060_20220921220106.trc
Wed Sep 21 22:01:06 2022
ORA-1092 : opitsk aborting process
Dumping diagnostic data in directory=[cdmp_20220921220106], requested by (instance=1, osid=9050 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 9050
.........


PMON (ospid: 7044): terminating the instance due to error 471
Fri Sep 23 16:10:20 2022
opiodr aborting process unknown ospid (27498) as a result of ORA-1092
Fri Sep 23 16:10:20 2022
opiodr aborting process unknown ospid (27500) as a result of ORA-1092
Fri Sep 23 16:10:20 2022
System state dump requested by (instance=1, osid=7044 (PMON)), summary=[abnormal instance termination].
...........


ORA-1092 : opitsk aborting process
Dumping diagnostic data in directory=[cdmp_20221004230056], requested by (instance=1, osid=8162 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 8162
Thu Oct 06 14:07:23 2022
Starting ORACLE instance (normal)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

执行如下命令查看操作系统日志：

egrep -i -r 'Out of memory' /var/log
1

发现如下错误信息：

查看主机内存的使用情况，发现服务器总内存为 16 GB，空闲内存只有200MB左右，可以确定 Oracle 停库的原因是服务器内存不足造成的。然后要求用户把服务器内存扩充到 32GB。

11 月 8 日，用户反映 oracle dg 数据库服务器又停库，数据同步服务中断。查看服务器信息发现以前的问题再次出现。

主机的内存使用情况如下：

操作系统日志信息如下：

可以发现，系统在 10 月 6 日杀掉了 oracle 进程。查看 dg 中的同步数据发现，最新数据就是 10 月 6 日的数据！说明 10 月 6 日 oracle dg 数据库发生了停库，数据同步服务中断。

然后用户把服务器内存继续扩容到 64 GB。

11 月 9 日上午，用户反馈说服务器内存又快使用完了，截图如下：

二、原因分析

通过问题发生的过程，一味地增加服务器内存看来并不能解决问题。

当 oracle dg 数据库停库时，通过主机内存的使用情况发现，产生了大量的 cache（缓存）。通过测试发现，cached 数据增加得很快。

查看 oracle dg 的 sga 和 pga 发现，参数 sga_max_size 和 pga_aggregate_target 的值设置过大：

SQL> show parameter sga

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			 boolean	 FALSE
sga_max_size			 big integer 64G
sga_target			     big integer 64G
SQL> ^C

SQL> show parameter pga

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target	 big integer 32G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

服务器的物理内存为 64 GB，并且 oracle dg 数据库只用于数据备份，并不提供数据访问服务。

sga_max_size 和 pga_aggregate_target 的值加起来达到 96 GB，比服务器的物理内存还要大（sga_max_size 和 pga_aggregate_target 的和一般不能超过物理内存的 60%），这明显是不合理的。

三、问题的解决思路

1、调整参数 sga_max_size 和 pga_aggregate_target 的值

设置参数 sga_max_size 的值为 32 GB，参数 pga_aggregate_target 的值为 8 GB，命令如下：

alter system set pga_aggregate_target=8g scope=spfile;

alter system set sga_max_size=32g scope=spfile;
alter system set sga_target=32g scope=spfile;
1
2
3
4

修改参数之后重启数据库：

-- 停库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area 3.4206E+10 bytes
Fixed Size		    2270360 bytes
Variable Size		 5167385448 bytes
Database Buffers	 2.8991E+10 bytes
Redo Buffers		   45649920 bytes
Database mounted.
Database opened.

-- 查看 sga 的大小
SQL> show parameter sga

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			 boolean	 FALSE
sga_max_size			 big integer 32G
sga_target			     big integer 32G
SQL> show parameter pga

-- 查看 pga 的大小
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target		     big integer 8G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

2、开启数据同步服务，查看数据的同步情况

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select rownum,fee_date
from ryhis.fin_ipb_itemlist
where fee_date>sysdate-5 and rownum<10
order by fee_date desc;  2    3    4  

    ROWNUM FEE_DATE
---------- -------------------
	 1 2022:11:09 19:51:44
	 2 2022:11:09 19:51:44
	 3 2022:11:09 19:51:44
	 4 2022:11:09 19:51:44
	 5 2022:11:09 19:51:43
	 6 2022:11:09 19:50:44
	 7 2022:11:09 19:50:44
	 8 2022:11:09 19:50:44
	 9 2022:11:09 19:50:44

9 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3、查看服务器的内存使用情况

发现 cached 仍然很大。

执行如下命令清理操作系统的缓存：

# 同步数据
[root@datapp-dg ~]# sync

# 清理缓存
[root@datapp-dg ~]# echo 1 > /proc/sys/vm/drop_caches
[root@datapp-dg ~]# echo 2 > /proc/sys/vm/drop_caches
[root@datapp-dg ~]# echo 3 > /proc/sys/vm/drop_caches
1
2
3
4
5
6
7

查看服务器的内存使用情况如下：

可以考虑创建一个定时任务，定期清理操作系统的缓存（cache）。

问题能不能彻底解决有待观察。

四、关于 Linux 操作系统缓存问题的说明

Linux 服务器运行一段时间后，会将暂时不用的内存转为 cache，这样在程序使用到这一部分数据时，能够很快的取出，从而提高系统的运行效率。在程序真正需要内存空间时，Linux 会将缓存让出给程序使用，达到对内存的充分利用。

因此，真正剩余的内存为：free + buffers + cache

有时大量的缓存占据内存空间也可能使系统变慢，甚至会杀掉一些正常的进程（比如上面的问题，就是杀掉了 oracle 进程导致 oracle dg 服务器停库）。此时，需要手动释放内存。

释放内存时，首先执行命令：sync，将内存中的缓冲区写入磁盘，包括已经修改的文件 inode、延迟的块 I/O 以及读写映射文件，从而确保文件系统的完整性。

都是内存中的实时数据保存在【/proc】目录下。关于内存的管理方式保存在【/proc/sys/vm/drop_chches】文件中。该文件中存放的并不是具体的内存内容，而是 0-3 这几个数字。文件的位置及内容如下：

[oracle@datapp-dg vm]$ pwd
/proc/sys/vm

[oracle@datapp-dg vm]$ ll drop*
-rw-r--r-- 1 root root 0 Nov  9 22:28 drop_caches

[oracle@datapp-dg vm]$ cat drop_caches 
3
1
2
3
4
5
6
7
8

【/proc/sys/vm/drop_chches】文件中的数字含义如下：

0（系统默认值）：默认情况下表示不释放内存，由操作系统自动管理；

1：释放页缓存；

2：释放 dentries 和 inodes；

3：释放所有缓存。

根据上面的说明，分别将 1、2、3 这3个数字重定向到文件 drop_caches 中即可以实现内存的释放，一般都是重定向 3 到文件中，以释放所有的缓存。