oracle数据库连接池连接失败,Oracle数据库连接池频繁出现ORA-02399错误

ORA-02399

具体错误信息为

ORA-02399: exceede maximum connect time, you are being logged off

大概含义为：超过了最大连接时间，你被强行登出

出现原因

首先看一下Oracle CONNECT_TIME参数是多少

SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('IDLE_TIME','CONNECT_TIME');

本案列中的设置值

PROFILE|RESOURCE_NAME |RESOURCE_TYPE|LIMIT |

-------|-------------------------|-------------|-------------------|

DEFAULT|IDLE_TIME |KERNEL |14 |

DEFAULT|CONNECT_TIME |KERNEL |10 |

CONNECT_TIME: Parameter will disconnect a session whose connect time exceed the value for connect_time.The connect_time is expressed in minutes.

CONNECT_TIME: 会断开超过CONNECT_TIME时间的会话，单位是分钟，默认应该是UNLIMITED

IDLE_TIME: Permitted periods of continuous inactive time during a session(minutes).

IDLE_TIME: 在会话期间允许的连续不活动时间，单位也是分钟，默认是10分钟

所以原因很简单，CONNECT_TIME默认为无限制被设置成为10分钟，所以每隔十分钟数据库连接就会被强行登出(不管你有没有在执行SQL)，所以导致连接不可用就会报错ORA-02399

解决

改数据库参数

将两个值改成无限或者长一点

ALTER PROFILE DEFAULT LIMIT IDLE_TIME UNLIMITED;

ALTER PROFILE DEFAULT LIMIT CONNECT_TIME UNLIMITED;

改连接池参数

Hikari连接池max-lifetime

用来设置一个connection在连接池中的存活时间，默认是1800000，即30分钟。如果设置为0，表示存活时间无限大。如果不等于0且小于30秒则会被重置回30分钟。

# 单位毫秒

max-lifetime: 300000

普通数据库连接池设置maxAge参数(比如org.apache.tomcat.jdbc.pool.DataSource)

看下参数的含义

/**

* Time in milliseconds to keep this connection alive even when used.

* When a connection is returned to the pool, the pool will check to see if the

* ((now - time-when-connected) > maxAge) has been reached, and if so,

* it closes the connection rather than returning it to the pool.

* The default value is 0, which implies that connections will be left open and no

* age check will be done upon returning the connection to the pool.

* This is a useful setting for database sessions that leak memory as it ensures that the session

* will have a finite life span.

* @param maxAge the time in milliseconds a connection will be open for when used

*/

public void setMaxAge(long maxAge);

参考