今天记起来一个数据库很久没有检查了，打开alert 日志看到大量的warning 信息，意思大概是操作系统最近5分钟是否有剧烈的swap活动了，具体的日志如下：

[oracle@WONHIGHOA trace]$ tail -f alert_WONHIGHOA.log
Wed Aug 20 12:23:09 2014
WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [34.34%] pct of memory swapped out [2.75%].
Please make sure there is no memory pressure and the SGA and PGA
are configured correctly. Look at DBRM trace file for more details.
Wed Aug 20 13:23:11 2014
WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [8.42%] pct of memory swapped out [0.51%].
Please make sure there is no memory pressure and the SGA and PGA
are configured correctly. Look at DBRM trace file for more details.

先说明一下我的库：
oracle      ： 11.2.0.4 64位
系统内存：3G
[oracle@WONHIGHOA ~]$ free -g
                total       used       free     shared    buffers     cached
Mem:              3          3          0          0          0          0
-/+ buffers/cache:          3          0
Swap:             7          3          3

一、查找线索
进数据库查看内存情况（手动管理的）：

点击(此处)折叠或打开

SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1184M
sga_target big integer 1184M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 394M

查看redo文件：

点击(此处)折叠或打开

GROUP# A.BYTES/1024/1024 MEMBERS STATUS MEMBER
---------- ----------------- ---------- ---------- ------------------------------------------------
1 512 2 CURRENT /u01/app/oracle/oradata/WONHIGHOA/redo01.log
1 512 2 CURRENT /u01/app/oracle/oradata/WONHIGHOA/redo11.log
2 512 2 INACTIVE /u01/app/oracle/oradata/WONHIGHOA/redo02.log
2 512 2 INACTIVE /u01/app/oracle/oradata/WONHIGHOA/redo22.log
3 512 2 INACTIVE /u01/app/oracle/oradata/WONHIGHOA/redo03.log
3 512 2 INACTIVE /u01/app/oracle/oradata/WONHIGHOA/redo33.log

日志切换的频率：

点击(此处)折叠或打开

SQL> select sequence#, FIRST_TIME from v$log_history;
SEQUENCE# FIRST_TIME
---------- -------------------
1 2014-07-30 10:47:04
2 2014-07-30 11:37:54
3 2014-07-31 11:00:58
4 2014-08-02 17:20:32
5 2014-08-04 19:16:02
6 2014-08-06 22:09:00
7 2014-08-09 02:48:34
8 2014-08-10 18:07:25
9 2014-08-12 22:03:48
10 2014-08-14 22:12:25
11 2014-08-16 18:02:56
SEQUENCE# FIRST_TIME
---------- -------------------
12 2014-08-18 15:49:38
12 rows selected.

查看回话情况：

点击(此处)折叠或打开

SQL> select sid,serial#,username,program,machine,status from v$session where STATUS=\'ACTIVE\';
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ---------- ------------------------------------ ------------------------------ ----------
2 1 oracle@WONHIGHOA (PMON) WONHIGHOA ACTIVE
3 1 oracle@WONHIGHOA (PSP0) WONHIGHOA ACTIVE
4 1 oracle@WONHIGHOA (VKTM) WONHIGHOA ACTIVE
5 1 oracle@WONHIGHOA (GEN0) WONHIGHOA ACTIVE
6 1 oracle@WONHIGHOA (DIAG) WONHIGHOA ACTIVE
7 1 oracle@WONHIGHOA (DBRM) WONHIGHOA ACTIVE
8 1 oracle@WONHIGHOA (DIA0) WONHIGHOA ACTIVE
9 1 oracle@WONHIGHOA (MMAN) WONHIGHOA ACTIVE
10 1 oracle@WONHIGHOA (DBW0) WONHIGHOA ACTIVE
11 1 oracle@WONHIGHOA (LGWR) WONHIGHOA ACTIVE
12 1 oracle@WONHIGHOA (CKPT) WONHIGHOA ACTIVE
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ---------- ------------------------------------ ------------------------------ ----------
13 1 oracle@WONHIGHOA (SMON) WONHIGHOA ACTIVE
14 1 oracle@WONHIGHOA (RECO) WONHIGHOA ACTIVE
15 1 oracle@WONHIGHOA (MMON) WONHIGHOA ACTIVE
16 1 oracle@WONHIGHOA (MMNL) WONHIGHOA ACTIVE
17 15 oracle@WONHIGHOA (CJQ0) WONHIGHOA ACTIVE
20 3 oracle@WONHIGHOA (QMNC) WONHIGHOA ACTIVE
26 1 oracle@WONHIGHOA (Q000) WONHIGHOA ACTIVE
27 5 oracle@WONHIGHOA (Q001) WONHIGHOA ACTIVE
31 3 oracle@WONHIGHOA (SMCO) WONHIGHOA ACTIVE
33 159 SYSMAN OMS WONHIGHOA ACTIVE
38 83 DBSNMP emagent@WONHIGHOA (TNS V1-V3) WONHIGHOA ACTIVE
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ---------- ------------------------------------ ------------------------------ ----------
61 30281 oracle@WONHIGHOA (J001) WONHIGHOA ACTIVE
81 55755 SYS sqlplus@WONHIGHOA (TNS V1-V3) WONHIGHOA ACTIVE
87 48657 oracle@WONHIGHOA (W000) WONHIGHOA ACTIVE
103 5037 oracle@WONHIGHOA (J000) WONHIGHOA ACTIVE
26 rows selected.

进一步诊断可以观察DBRM后台进程的trace文件：
[oracle@WONHIGHOA ~]$ cat /u01/app/oracle/diag/rdbms/wonhighoa/WONHIGHOA/trace/WONHIGHOA_dbrm_8428.trc
Trace file /u01/app/oracle/diag/rdbms/wonhighoa/WONHIGHOA/trace/WONHIGHOA_dbrm_8428.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: WONHIGHOA
Release: 2.6.18-308.el5
Version: #1 SMP Fri Jan 27 17:17:51 EST 2012
Machine: x86_64
VM name: VMWare Version: 6
Instance name: WONHIGHOA
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 8428, image: oracle@WONHIGHOA (DBRM)

*** 2014-07-30 10:47:23.747
*** SESSION ID:(7.1) 2014-07-30 10:47:23.747
*** CLIENT ID:() 2014-07-30 10:47:23.747
*** SERVICE NAME:() 2014-07-30 10:47:23.747
*** MODULE NAME:() 2014-07-30 10:47:23.747
*** ACTION NAME:() 2014-07-30 10:47:23.747

PQQ: Skipping service checks
kgsksysstop: blocking mode (2) timestamp: 1406688443753400
kgsksysstop: successful; Total stall time = 0 ms.
PQ-critical CG's:
kgsksysresume: successful

*** 2014-07-30 10:48:04.915
PQQ: Active Services changed
PQQ: Old service table
SvcIdx SvcId Active ActDop
PQQ: New service table
SvcIdx SvcId Active ActDop
1 1 1 0
2 2 1 0
5 5 1 0
6 6 1 0
[oracle@WONHIGHOA ~]$

为了进一步问题做了AWR，鉴于篇幅只给出下面的

在网上找了一下，90%的都是一样的，转载别人的，或者复制粘贴别人的，八成是为了提高访问量。
目前百度上找到三个可以列举出来的：
http://www.oracledatabase12g.com/archives/11gr2-dbrm-collect-swap-info.html
http://blog.itpub.net/23135684/viewspace-759732
http://blog.sina.com.cn/s/blog_e560b96a0101ell5.html

二、分析问题：
首先，从数据库内存上看，设置约占系统内存的50%，而且回话连接数很少，数据库非常闲，结果awr报告 sga ,pga设置的都比较合理。
其次，同网上的案例对比一下，有如下区别：
1、他们的库要么宕了，要么hang了，可我的还在跑着。
2、而且我的内存调整情况跟他们的也不同，他们的库是繁忙的，而我的是闲的蛋疼
3、他们的库基本上都是11.2.0.3，我的是11.2.0.4,。
4、系统，官方说AIX上出现这个情况居多，这一点跟网上的相符，但我的是RedHat 。
借用一下官方的：
Description
  This fix is really an enhancement.
  With this fix a warning is printed to the alert log (and DBRM trace)
  if excessive swapping is observed on the system.
  The warning will not be printed more than once per hour.

The same issues could have been happening on 11.2.0.2 but they simply would not have been reported.
There is no cause for concern and we suggest that the databases are just monitored as usual.
If you look in the alert log, you will see that the message does not happen every hour.
If SWAP was causing any serious issue we could also see ORA-4031/ORA-4030 and would then be able to use the DBRM trace in conjunction with obtaining further diagnostics.
5、目前没有出现ORA-4031/ORA-4030
所以从oracle版本和问题的情况来看，初步判断不是是bug引起的，是物理内存不足。

三、解决问题：

我试过他们的方法，但是都没用，所以最后增加一倍的物理内存，同时调大 sga和pga 现在的sga=2G,pga=500m
重启数据库之后就ok了。
期待有其他的解决办法！

ITPub博客

WARNING: Heavy swapping observed on system in last 5 mins

最新文章