oracle数据库连接数不足的问题解剖

最新推荐文章于 2024-09-16 21:55:08 发布

上帝的二公子

最新推荐文章于 2024-09-16 21:55:08 发布

阅读量1.4k

点赞数 20

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/gaoda00136/article/details/139725707

版权

数据库不能连接、监控程序报警数据库服务DOWN等信息，究其原因，均为数据库连接数process被用光（或linux/unix环境oracle用户用户ulimit process限制达到上限），针对这类问题，总结历史经验及近期问题的处理过程，汇总如下方法：

1.当听到客户反馈数据库无法连接、监控程序报警数据库服务DOWN等信息等问题的分析排查：
在第一时间需要确认的是主机是否宕机无响应或重启，这些通过uptime/top/vmstat等操作系统相关命令可以很快做出初步的判断。
其次如报数据库无法连接，需要及时查看数据库进程是否异常、数据库日志有无报错以及监听状态和监听日志，以及连接数量（ps -ef|grep LOCAL=NO|wc -l,v$process/v$session等）；
通过这些信息，基本可以在3到5分钟能对数据库的运行情况有一个总体的了解并对客户提出的问题有一个基本的判断；
此处我们分析连接数用满情况下的排查。

2.当发现数据库无法连接的原因为连接数用满，此时对应的错误可能为：

alert_xx.log中显示数据库processes 用光：
Tue Feb 02 16:16:10 2021
ORA-00020: maximum number of processes (5000) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
或操作系统limit process限制达到上限
[root@test111 ~]# su - oracle
-bash: fork: retry: Resource temporarily unavailable
或没有如上两种典型的数据库或主机层报错信息，但是查看ps -ef|grep LOCAL=NO|wc -l及v$process/v$session等基本在process上限值附近。

3.确定原因为processes 用满后，如何定位处理？
a.如果不查原因，简单粗暴的方法就是人工KILL进程。但是存在一个问题就是大部分程序配置了自动重连机制，单纯的KILL进程后可能很快连接数又会被用光。同时KILL的话在数据库中alter system kill通常没有在操作系统层面kill -9 pid直接。
b.进阶一点的方法是查找v$process/v$session,如使用select program,machine,count(*) from v$session……方法来查看哪个主机或业务模块的连接数多等来判断出对应的程序。
c.其它还可能出现的情况是由于数据库异常导致，如进程执行的SQL速度慢(SQL效率问题、常见的TX锁等待、其它类型的进程阻塞等)，导致前面的进程未执行完成退出时，新的进程不断连接并执行SQL，导致进程数量不断增多；
d.还有可能是执行完语句后不退出，这种在一些监控程序里见过类似场景，监控软件定期连接到数据库查询信息，但是查询完没有退出，导致进程数被用光；当监控连接数据库频率很高时，这个进程增加速度会非常快。
e.不太常见的一种情况是程序连数据库的监听相关配置不太合理，导致v$process中查到的进程数量很高，v$session中查到的很少，这种可以通过操作系统层面的ps -ef|grep LOCAL=NO|wc -l来辅助判断到底是不是进程数量用光。
f.如e的情况在数据库层面就不方便查出哪个应用或主机的连接多，此时还可以通过监听日志的分析来辅助判断，找出异常时段数据库监听日志中的连接信息，如判断异常时段和平时的连接次数差异、统计IP对应的连接次数等来辅助判断。

通过如上几个方面的判断，通常可以定位到异常的业务程序；对于这种情况，人工KILL进程没用时，建议是协调应用厂商来关闭异常的业务程序并排查问题(有时候只是重启一下应用程序就恢复正常了）。

如下是几次典型问题的相关日志及处理：
1.操作系统ulimit过小导致(设置过小或数据库启动后才设置导致数据库没有用到正确的shell ulimit参数)

2.监控程序连接没有设置退出导致连接数满

3.程序异常导致连接数满--执行频率或连接信息配置不当等

情景1：
SQL> !ps -ef|grep LOCAL|grep drdb|wc -l
1705

SQL> select count(*) from v$process;

COUNT(*)
----------
1774

SQL> select count(*) from v$session;

COUNT(*)
----------
647
情景2：（并发大执行频率高）

4.SQL执行慢导致连接数用满（并发高、SQL效率低或突然变慢、TX锁等阻塞类等情况）

BASE_RDS 130 JDBC Thin Client jdbcclient 29m8263tmmy1k library cache lock 1546 1
BASE_RDS 145 JDBC Thin Client jdbcclient 29m8263tmmy1k library cache lock 1546 1
BASE_RDS 171 JDBC Thin Client jdbcclient 29m8263tmmy1k library cache lock 1546 1
BASE_RDS 188 JDBC Thin Client jdbcclient 29m8263tmmy1k library cache lock 1546 1
BASE_RDS 190 JDBC Thin Client jdbcclient d47fh1amkdvm4 library cache lock 1546 1
BASE_RDS 2652 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1553 1
BASE_RDS 2653 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1553 1
BASE_RDS 601 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1553 1
BASE_RDS 1621 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1553 1
BASE_RDS 593 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1553 1
BASE_RDS 1277 JDBC Thin Client jdbcclient c9tt136su8dmd enq: TX - row lock contentio 1568 1
n

BASE_RDS 274 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1681 1
BASE_RDS 1968 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1681 1
BASE_RDS 214 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 232 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 390 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 18 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 23 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 26 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 59 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 95 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 102 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 128 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 156 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 163 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749 1
BASE_RDS 175 JDBC Thin Client jdbcclient 86kcmu10zk12g …………
BASE_RDS 858 JDBC Thin Client jdbcclient 86kcmu10zk12g enq: TX - index contention 1749
5.监听日志的分析

按时间分析，发现问题时段的连接数量比平时多
[oracle@mzdb1 trace]$ fgrep "02-FEB-2021 " 1.log |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 }' |sort |uniq -c
1374 02-FEB-2021 00
1378 02-FEB-2021 01
1331 02-FEB-2021 02
1342 02-FEB-2021 03
1317 02-FEB-2021 04
1415 02-FEB-2021 05
1465 02-FEB-2021 06
1646 02-FEB-2021 07
2847 02-FEB-2021 08
2892 02-FEB-2021 09
3039 02-FEB-2021 10
2473 02-FEB-2021 11
1938 02-FEB-2021 12
2215 02-FEB-2021 13
2379 02-FEB-2021 14
20607 02-FEB-2021 15
78522 02-FEB-2021 16

问题时段按IP汇总统计，可以发现某些IP的连接很多
[oracle@mzdb1 trace]$ fgrep "02-FEB-2021 16" listener.log|fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort
166 172.16.0.216
17 172.16.1.108
183 172.16.0.87
19474 172.16.0.74 ====>>>>
21 172.16.1.97
220 172.16.0.55
22 172.16.0.22
24 172.16.0.156
301 172.16.0.157
305 172.16.0.198
3 172.16.0.170
3 172.16.0.84
32 172.16.27.245
326 172.16.1.186
33371 172.16.0.76 ====>>>>
40 172.16.0.79
4237 172.16.0.133
4237 172.16.0.180
4238 172.16.0.19
4238 172.16.0.203
4238 172.16.0.210
4250 172.16.0.36