用户连接时间超出限制时长(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

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

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确实连着一起来了。

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

解决

简单的有两个解决方法：

1. 应用处超时重连；
2. 数据库对该用户取消连接时长限制；

本例简单的使用方法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

1. 创建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

 

至此完成修改。

引用文章

ORA-02396: exceeded maximum idle time
ORACLE user profile配置/管理/维护
Server Messages ORA-00000 to ORA-25699
Oracle Enterprise Manager (Example 1)
CONNECT_TIME

 

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

ifuteng#gmail.com 2014/10/30