Oracle中process超出限制的解决

1、客户端不能连接数据库，alert报错部分内容如下

Sun Jan  4 08:01:08 2015
Process m001 died, see its trace file
Sun Jan  4 08:01:08 2015
ksvcreate: Process(m001) creation failed
Sun Jan  4 08:03:12 2015
Process J001 died, see its trace file
Sun Jan  4 08:03:12 2015
kkjcre1p: unable to spawn jobq slave process

数据库版本如下

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

2、接下来查询下面两个视图

SQL> select * from v$resource_limit;

查询这个视图主要看看process和session是否达到最大限制，通常情况下都是process达到最大限制导致的。

此时可以使用alter system set process=XX scope=spfile语句增加最大限制，然后重启数据库。但是有时是因为过多的连接未释放造成的process达到上限。

SQL> select count(*) from v$session where status='INACTIVE';

使用这个查询发现系统中有大量的状态为INACTIVE的session。

3、关于INACTIVE状态的session

session的active状态官方解释如下：

Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.

session的inactive状态是指语句已经执行完成，处于等待状态的session。通常是应用程序中没有释放的连接引起的。

4、释放资源“坏资源”

在 sqlnet.ora文件中设置expire_time 参数。

可以使用EXPIRE_TIME参数间歇检查异常session并释放process。下面的官方说明：

SQLNET.EXPIRE_TIME

Purpose

Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

Limitations on using this terminated connection detection feature are:

    It is not allowed on bequeathed connections.

    Though very small, a probe packet generates additional traffic that may downgrade network performance.

    Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

Default

0

Minimum Value

0

Recommended Value

10

Example

SQLNET.EXPIRE_TIME=10

注意这里设置是10分钟，每10分钟ORACLE会确认所有session客户端连接是否正常，对于不正常的session，oracle会清理process。

另外，有时能看到session数目明显小于process数目，这是由于“坏的”process的存在：

select * from v$process
where addr not in
(select paddr from v$session )

5、在11g版本中的情况

[oracle@rman-auxiliary ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 4 13:56:42 2015

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


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

SQL> select * from v$resource_limit where resource_name in ('sessions','processes');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
------------------------------ ------------------- --------------- ----------
LIMIT_VALU
----------
processes                                       32              40        150
       150

sessions                                        41              50        248
       248


SQL> alter system set processes=35 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2217832 bytes
Variable Size            1996490904 bytes
Database Buffers         1275068416 bytes
Redo Buffers               16568320 bytes
Database mounted.
Database opened.
SQL> select * from v$resource_limit where resource_name in ('sessions','processes');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
------------------------------ ------------------- --------------- ----------
LIMIT_VALU
----------
processes                                       31              32         35
        35

sessions                                        31              32         76
        76
        
继续增加连接到终于连接不上了：

[oracle@rman-auxiliary ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 4 14:11:31 2015

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

ERROR:
ORA-00020: maximum number of processes (35) exceeded

查看alert，错误日志如下：

......
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_cjq0_16335.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_cjq0_16335.trc:
Sun Jan 04 14:13:23 2015
ORA-00020: No more process state objects available
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.
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_cjq0_16335.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_cjq0_16335.trc:
......

在11g中给出了确切的错误 ORA-00020。

6、管理员也无法连接时

当process满的时候即使在服务器上使用操作系统认证方式也无法登陆，这时候可以使用下面的语句杀掉操作系统进程，以可以创建新的process：

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

然后再执行上面命令得到的kill -9语句。

windows中杀进程：orakill instance_name pid