ORA-00020: No more process state objects available故障一例 - 数据库编程

今天一网友咨询数据库宕机了，当时数据库出现用户无法登录的症状，为了了解起因，去查看了网友的告警日志alert.log，发现在晚上10:00左右出现大量的ORA-00020: No more process state objects available 错误，具体错误日志如下:

Fatal NI connect error 12170.

我们可以看到processes的MAX_UTILIZATION最大使用数目曾到过LIMIT_VALUE限定的400，
?sessions达到了640.

从以上V$resource_limit视图的输出来看，极有可能是processes总数达到上限导致了新的后台辅助进程创建失败，其实我们可以很方便地验证这一点:

[oracle@bntjftest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 14 15:28:15 2015

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

SQL> select * from v$version;

BANNER
?--------------------------------------------------------------------------------
?Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
?PL/SQL Release 11.2.0.4.0 - Production
?CORE 11.2.0.4.0 Production
?TNS for Linux: Version 11.2.0.4.0 - Production
?NLSRTL Version 11.2.0.4.0 - Production

SQL> show parameter processes

NAME? ? ? ? TYPE? VALUE
?------------------------------------ ----------- ------------------------------
?aq_tm_processes? ? ? ? integer? 1
?db_writer_processes? ? ? integer? 1
?gcs_server_processes? ? ? integer? 0
?global_txn_processes? ? ? integer? 1
?job_queue_processes? ? ? integer? 1000
?log_archive_max_processes? ? ? integer? 4
?processes? ? ? ? integer? 500
?SQL> alter system set processes=40 scope=spfile;

System altered.

SQL> startup force;
?ORACLE instance started.

Total System Global Area 1987563520 bytes
?Fixed Size? ? ? 2254584 bytes
?Variable Size? 1224739080 bytes
?Database Buffers? 754974720 bytes
?Redo Buffers? ? ? 5595136 bytes
?Database mounted.
?Database opened.
?SQL> select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');

?RESOURCE_NAME? ? ? ? MAX_UTILIZATION LIMIT_VALUE
?------------------------------ --------------- --------------------
?processes? ? ? ? 24? ? ? ? 40
?sessions? ? ? ? 25? ? ? 556

SQL> show parameter proce

NAME? ? ? ? TYPE? VALUE
?------------------------------------ ----------- ------------------------------
?aq_tm_processes? ? ? ? integer? 1
?cell_offload_processing? ? ? boolean? TRUE
?db_writer_processes? ? ? integer? 1
?gcs_server_processes? ? ? integer? 0
?global_txn_processes? ? ? integer? 1
?job_queue_processes? ? ? integer? 1000
?log_archive_max_processes? ? ? integer? 4
?processes? ? ? ? integer? 40
?processor_group_name? ? ? string

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

RESOURCE_NAME? ? ? ? MAX_UTILIZATION LIMIT_VALUE
?------------------------------ --------------- --------------------
?processes? ? ? ? 40? ? ? ? 40
?sessions? ? ? ? 45? ? ? 556

SQL>
客户端连接时，报下如的错误。

alter文件中的预警信息如下：
Thu May 14 15:39:32 2015
?SMCO started with pid=37, OS id=19445
?Thu May 14 15:44:30 2015
?ORA-00020: maximum number of processes (40) 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.
?Process m000 submission failed with error = 20
?Thu May 14 15:49:30 2015
?ORA-00020: maximum number of processes (40) 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.
?Process m000 submission failed wi

ORA-00020: No more process state objects available故障一例(一)