oracle长连接超时时间,用户连接时间超出限制时长(ORA-00604 ORA-02399)

背景

一个入库频繁的应用同时报了 (ORA-00604 + ORA-02399)错误，后续数据再也不能入库。

错误日志

?

ORA-00604: error occurred at recursive SQL level 1

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

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

?

Oracle官方文档

ORA-00604 error occurred at recursive SQL level num

Cause: An error occurred while processing a recursive SQL statement. A recursive SQL statement is one that applies to internal dictionary tables.

Action: If the situation described in the next message on the stack can be corrected, do so; otherwise, contact customer support.

ORA-02399 exceeded maximum connect time, logging off

Cause: A user has exceeded the maximum time allowed to be connected to the database.

Action: The user must reconnect to the database.

分析

不是很能理解ORA-00604的错误，着重看了ORA-02399，大致意思如下：

原因：用户连接数据库时间超出了所允许的最大时长

办法：该用户需要重连数据库

考虑到出问题的应用确实是一刻也不停的操作数据库，而且建立的长连接也未曾释放，很可能超出了Oracle数据库限定的最大连接时长。

在网上很幸运的查询到Andrew Reid的一篇文章，里面专门测试了连接时长的问题。

实验中设置了Profile Connect_time时长为1分钟，而在超过1分钟后当前session在做sql操作也报了如下错误：

?

SQL> select to_char(sysdate,‘hh24:mi:ss‘)

2 time_now from dual

3 /

select to_char(sysdate,‘hh24:mi:ss‘)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02399: exceeded maximum connect time, you are

being logged off

ORA-02399: exceeded maximum connect time, you are

being logged off

SQL>

?

惊奇的发现，ORA-00604与ORA-02399确实连着一起来了。

至此可以确定，由于该用户连接数据库的最大时间受限制，导致了上述问题的发生。

解决

简单的有两个解决方法：

应用处超时重连；

数据库对该用户取消连接时长限制；

本例简单的使用方法2来说明问题。

1. 查询用户所属Profile

Oracle Profile用来限定对用户访问数据库做一定限制。

?

SQL> set lines 100

SQL> col profile for a15

SQL> col resource_name for a30

SQL> col limit for a14

SQL> select username,profile from dba_users where username=‘CRXJ_ALARM‘;

USERNAME PROFILE

------------------------------ ------------------------------

CRXJ_ALARM DEFAULT

?

可以看到当前使用用户CRXJ_ALARM所属DEFAULT这个默认的PROFILE。

2. 查看DEFAULT内容

?

SQL> select * from dba_profiles where profile = ‘DEFAULT‘;

Warning: connection was lost and re-established

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

------------------------------ -------------------------------- ------------- ----------------------------------------

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

DEFAULT CPU_PER_CALL KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

DEFAULT IDLE_TIME KERNEL 15

DEFAULT CONNECT_TIME KERNEL 180

DEFAULT PRIVATE_SGA KERNEL UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected

?

可以发现在CONNECT_TIME 设置为180分钟。由于大部分数据库用户都使用默认profile因此还是新建一个专属示例用户的profile来的保险。

3. 新建用户Profile

创建profile

CREATE PROFILE CRXJ_ALARM_PROFILE LIMIT

COMPOSITE_LIMIT UNLIMITED

SESSIONS_PER_USER UNLIMITED

CPU_PER_SESSION UNLIMITED

CPU_PER_CALL UNLIMITED

LOGICAL_READS_PER_SESSION UNLIMITED

LOGICAL_READS_PER_CALL UNLIMITED

IDLE_TIME 15

CONNECT_TIME UNLIMITED

PRIVATE_SGA UNLIMITED

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_LOCK_TIME 1

PASSWORD_GRACE_TIME 7;

?

2 验证是否创建成功

?

SQL> select * from dba_profiles where profile = ‘CRXJ_ALARM_PROFILE‘;

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

------------------------------ -------------------------------- ------------- ----------------------------------------

CRXJ_ALARM_PROFILE COMPOSITE_LIMIT KERNEL UNLIMITED

CRXJ_ALARM_PROFILE SESSIONS_PER_USER KERNEL UNLIMITED

CRXJ_ALARM_PROFILE CPU_PER_SESSION KERNEL UNLIMITED

CRXJ_ALARM_PROFILE CPU_PER_CALL KERNEL UNLIMITED

CRXJ_ALARM_PROFILE LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

CRXJ_ALARM_PROFILE LOGICAL_READS_PER_CALL KERNEL UNLIMITED

CRXJ_ALARM_PROFILE IDLE_TIME KERNEL 15

CRXJ_ALARM_PROFILE CONNECT_TIME KERNEL UNLIMITED

CRXJ_ALARM_PROFILE PRIVATE_SGA KERNEL UNLIMITED

CRXJ_ALARM_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 10

CRXJ_ALARM_PROFILE PASSWORD_LIFE_TIME PASSWORD UNLIMITED

CRXJ_ALARM_PROFILE PASSWORD_REUSE_TIME PASSWORD UNLIMITED

CRXJ_ALARM_PROFILE PASSWORD_REUSE_MAX PASSWORD UNLIMITED

CRXJ_ALARM_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT

CRXJ_ALARM_PROFILE PASSWORD_LOCK_TIME PASSWORD 1

CRXJ_ALARM_PROFILE PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected

?

3.?修改示例用户的Profile

SQL> alter user CRXJ_ALARM profile CRXJ_ALARM_PROFILE;

User altered.

?

4.?查看示例用户Profile

SQL> select username,profile from dba_users where username=‘CRXJ_ALARM‘;

USERNAME PROFILE

------------------------------------------------------------ ---------------

CRXJ_ALARM CRXJ_ALARM_PROFILE

?

至此完成修改。

引用文章

?

转载请标明作者和原文链接

ifuteng#gmail.com 2014/10/30

?

原文：http://futeng.iteye.com/blog/2149654