案例分享|Oracle 11g RAC 数据库连接数过高处理办法

作者 | JiekeXu

来源 | JiekeXu之路（ID: JiekeXu_IT）

转载请联系授权 | (微信ID：xxq1426321293)

大家好，我是 JiekeXu,很高兴又和大家见面了，今天分享 Oracle 11g RAC 数据库连接数过高处理办法。本文发布于微信公众号【JiekeXu之路】，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！

前   言

近期有一套数据库总是出现如下告警 “严重告警：XXX Oracle 服务器:10.10.X.X 数据库的侦听器 LISTENER 状态为 Inactive ”.这样的告警我们已经屡见不鲜，要么就是数据库宕机，要么就是监听异常关闭，因为是 RAC 环境，又不是什么很大问题，便不慌不忙的去处理此问题。

一 问题过程

当登陆到数据库检查数据库状态无异常，查看监听也是正常，那么集群资源状态也是正常。查看数据库后台日志时发现了错误 "ORA-00020：maximum number of processes(2000) exceeded" 连接数达到最大值 2000.查看当前数据库最大连接数设置为 2000，查看数据库除后台进程外两节点数均为 1940 多，外加 50 多个已经超出 2000 了。

排查数据库最大连接数和当前连接数

SQL> show parameter process 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     8
gcs_server_processes                 integer     4
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     2000
processor_group_name                 string

SQL> select inst_id,status,count(*) from gv$session where type <> 'BACKGROUNND' group by inst_id,status order by 3;

从当前连接以及后台日志查看，INACTIVE 非活跃会话 1940 之多，但数据库 CPU 内存等资源均正常，也没有异常等待事件，不过下午已经出现过连接数过高的问题，根据经验猜测应用系统的中间件连接池以及初始连接大小设置有问题，果不其然后面联系应用方确认没有设置连接超时。

由于此系统不是核心系统，活跃会话也只有三四个更没有大事物，简单查询后便决定先杀掉连接恢复告警，但当时想要通过数据库杀掉非活跃会话连接，可是通过 SID 和 SERIAL# 查杀时很多会话已经不存在了。那么当时采取的办法就是通过操作系统 kill 查杀了，又因为活跃会话很少无事务，便使用如下命令全部查杀了。

注意：生产系统中谨慎操作，尤其是有大事物时不能直接查杀。

ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' | wc -l
ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' | xargs kill -9

查杀后连接数下降数据库告警恢复，算是告一段落了，没有深入问题根源。

二 问题复现

第二天早上十点多，还在查看另一系统的性能问题时，有人告知此系统又有问题，无疑又是连接数问题，登陆到系统后查看果不其然。两个节点的连接数已达 1800 多，通过操作系统 kill -9 紧急杀掉会话后数据库连接数下降，但是出现问题时还没达到阈值，肯定还有其他没有来得及的问题存在，这个便要后续排查了。

三 问题排查

发现此数据库内存管理是自动管理的，SGA、PGA 设置的值不合理，当出现大量连接时，PGA  设置不合理，新的会话连接则会出现问题应用方反馈出性能问题；另外大量非活跃会话未释放也没有从数据库端限制，未设置超时连接 SQLNET.EXPIRE_TIME 参数。

Dead Connection Detection（DCD）

SQLNET.EXPIRE_TIME： 设置 DCD 检测时间为 1 分钟。指定时间间隔（以分钟为单位），以发送探测以验证客户端/服务端连接处于活动状态。设置一个大于 0 的值可确保不会由于客户端异常终止而无限期地断开连接。

Dead Connection Detection (DCD)与Inactive Sessions

https://blog.csdn.net/leshami/article/details/9188379

Dead Connection Detection (DCD)与Inactive Sessions

Dead connections:
   These are previously valid connections with the database but the connection between the client and server processes has
   terminated abnormally.
   Examples of a dead connection:
   - A user reboots/turns-off their machine without logging off or disconnecting from the database.
   - A network problem prevents communication between the client and the server.

   In these cases, the shadow process running on the server and the session in the database may not terminate.

   Implemented by
         * adding SQLNET.EXPIRE_TIME = <MINUTES> to the sqlnet.ora file

   With DCD is enabled, the Server-side process sends a small 10-byte packet to the client process after the duration of
   the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter.

   If the client side connection is still connected and responsive, the client sends a response packet back to the database
   server, resetting the timer..and another packet will be sent when next interval expires (assuming no other activity on
   the connection).

   If the client fails to respond to the DCD probe packet
        * the Server side process is marked as a dead connection and
        * PMON performs the clean up of the database processes / resources
        * The client OS processes are terminated

   NOTE: SQLNET.RECV_TIMEOUT can be set on the SERVER side sqlnet.ora file. This will set a timeout for the server process
         to wait for data from the client process.

Inactive Sessions:
   These are sessions that remain connected to the database with a status in v$session of INACTIVE.
   Example of an INACTIVE session:
   - A user starts a program/session, then leaves it running and idle for an extended period of time.

于是乎则在两个节点中均设置 SQLNET.EXPIRE_TIME=1，这个参数在 RAC 中则需要设置到 Oracle 用户下 $ORACLE_HOME/network/admin/sqlnet.ora 文件中，。还有个问题就是不确定这个参数到底是设置到哪个用户下的话，可以Oracle、grid 两个用户都设置了总有一个会生效。

[oracle@JiekeXu ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@JiekeXu admin]$ ll
total 16K
-rw-r--r-- 1 oracle oinstall  378 May 18  2019 listener.ora
drwxr-xr-x 2 oracle oinstall 4.0K Oct 27  2017 samples
-rw-r--r-- 1 oracle oinstall  835 Feb 18  2019 shrept.lst
-rw-r----- 1 oracle oinstall  332 May 18  2019 tnsnames.ora
[oracle@JiekeXu admin]$ 
[oracle@JiekeXu admin]$ vi sqlnet.ora
[oracle@JiekeXu admin]$ more sqlnet.ora 
SQLNET.EXPIRE_TIME=1
DIAG_ADR_ENABLED=OFF

设置完后我这边又通过数据库杀掉了一些连接，通过拼接 SQL 杀掉一个小时前的非活跃会话及非后台进程会话。但是发现数据库中出现 KILLED 会话达 594 个。会话没有得到释放，节点二便采用系统层面的 kill -9 杀掉会话。

select 'alter system kill session '''||sid||','||serial#||'''; 'from v$session s where s.STATUS='INACTIVE' and type <> 'BACKGROUND' and LOGON_TIME<=(sysdate-1/24);
alter system kill session '59,19341';
select inst_id,status, count(*) from gv$session where type <> 'BACKGROUND' group by inst_id,status order by 3;

14:03:19 SQL> alter system kill session '1938,3645';

System altered.
……………………………省略部分……………………………
14:03:19 SQL> alter system kill session '1943,3215';

System altered.

14:03:24 SQL> select 'alter system kill session '''||sid||','||serial#||'''; 'from v$session s where s.STATUS='INACTIVE' and type <> 'BACKGROUND' and LOGON_TIME<=(sysdate-1/24);

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '59,19341';
alter system kill session '458,3637';
……………………………省略部分……………………………
alter system kill session '1952,5867';
alter system kill session '1971,2023';
14:06:34 SQL> select inst_id,count(*) from gv$session group by inst_id;

INST_ID COUNT(*)
---------- ----------
1 1197
2 1194

14:06:40 SQL> select inst_id,status, count(*) from gv$session where type <> 'BACKGROUND' group by inst_id,status order by 3;

INST_ID STATUS COUNT(*)
---------- -------- ----------
2 ACTIVE 1
1 ACTIVE 4
1 INACTIVE 543
1 KILLED 594
2 INACTIVE 1138

节点 2 这里又通过 kill -9 杀掉会话，节点 2 的非活跃会话得到释放，但是节点 1 的 KILLED 的会话还是没明显的下降 。

SQL> select inst_id,status, count(*) from gv$session where type <> 'BACKGROUND' group by inst_id,status order by 3;

INST_ID STATUS COUNT(*)
---------- -------- ----------
1 ACTIVE 3
2 ACTIVE 4
1 INACTIVE 397
1 KILLED 592
2 INACTIVE 671

后面应用方根据建议设置了中间件 Weblogic 连接池超时为 180s 最小连接数为 1 后，节点一 KILLED 也得到了释放，数据库的连接也下降到 400 左右了，算是一个正常范围值。

内存调整

前面说过内存管理为自动管理，这里需要将其调整为手动管理，由于是生产环境不能够随时重启则需要停机窗口，这里先将需要调整的内存参数以及合理的值列出来。

SQL> show parameter target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 39168M
memory_target big integer 39168M
parallel_servers_target integer 1024
pga_aggregate_target big integer 0
sga_target big integer 0
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 39168M
sga_target big integer 0
SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL>
SQL> select * from v$sgastat where name = 'free memory' and pool = 'shared pool';

POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 856007376

--以下为调整步骤
sqlplus / as sysdba 
create pfile='/tmp/pfile1028.ora' from spfile;
alter system set shared_pool_size=5G scope=spfile sid='*';
alter system set db_cache_size=15G scope=spfile sid='*';
alter system set large_pool_size=1G scope=spfile sid='*'
alter system set java_pool_size=128M scope=spfile sid='*';
alter system set streams_pool_size=256M scope=spfile sid='*';

alter system set memory_max_target=0 scope=spfile sid='*';
alter system set memory_target=0 scope=spfile sid='*';

alter system set sga_max_size=30g scope=spfile sid='*';
alter system set sga_target=30G scope=spfile sid='*';
alter system set pga_aggregate_target=14G scope=spfile sid='*';

AIX 操作系统内存 64G，数据库内存 38.25G，将其调整为 30G，PGA 调整为 14G，shared_pool 5G，db_cache 15G，large_pool 1G，java_pool 128G，streams_pool 256M。这些值都是一个近似的值，一个较为合理的值，并不保证 100% 完美正确，在 AWR 报告中 Advisory Statistics 部分，也会有相关的建议值，如下便是一个较为合理的 shared_pool 的值。设置完这些值后重启数据库系统，将会有一个很大的提升。

最后说一下利用 Profile 超时设置，这个没有使用过，网上有方法便晚上回来测试一番 。

首先查询数据库是否开启 resource limit 限制，如果没有开启，则开启这个参数。

SYS@JiekeXu>col name format a15
SYS@JiekeXu>col value format a10
SYS@JiekeXu>SELECT name, value FROM gv$parameter WHERE name = 'resource_limit';

NAME            VALUE
--------------- ----------
resource_limit  FALSE
SYS@JiekeXu>set time on
00:44:34 SYS@JiekeXu>
00:44:35 SYS@JiekeXu>ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

System altered.

00:44:40 SYS@JiekeXu>SELECT name, value FROM gv$parameter WHERE name = 'resource_limit';

NAME            VALUE
--------------- ----------
resource_limit  TRUE

--然后创建空闲 1 分钟中止空闲例程的 Profile
00:44:49 SYS@JiekeXu>CREATE PROFILE app_user LIMIT IDLE_TIME 1;

Profile created.
--设置 scott 用户的 Profile
00:46:27 SYS@JiekeXu>alter user scott profile app_user;

User altered.
--当然也可以使用 默认的 Profile 
00:46:58 SYS@JiekeXu>ALTER PROFILE DEFAULT LIMIT IDLE_TIME  1;

Profile altered.

然后使用客户端工具 SQLPlus 远程连接，查询业务数据等待 1 分钟后在继续查询则会报错 ORA-02396。

[oracle@JiekeXu ~]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JiekeXu =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = JiekeXu)
    )
  )

[oracle@JiekeXu ~]$ sqlplus scott/scott@JiekeXu

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 28 00:56:29 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@JiekeXu>set time on
00:56:35 SCOTT@JiekeXu>select sysdate from dual;

SYSDATE
---------
28-OCT-20

00:56:45 SCOTT@JiekeXu>
00:56:45 SCOTT@JiekeXu>select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again


01:12:48 SCOTT@JiekeXu>

今天的分享就到这里了，如果本文对您有一点儿帮助，请多支持“在看”与转发，不求小费了哪怕是一个小小的赞，您的鼓励都将是我最大的动力，让我有一直写下去的动力，最后一起加油,奥利给！

一键三连“分享、在看与点赞”，给我充点儿电吧~