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

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>
客户端连接时,报下如的错误。

00020: No more process state objects available故障一例


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 with error = 20
 Thu May 14 15:59: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
 Process m001 submission failed with error = 20
 Thu May 14 16:00:30 2015
 Process m000 submission failed with error = 20
 Thu May 14 16:01: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 16:03:31 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 16:04:31 2015
 Process m000 submission failed with error = 20
例子中我们可以清楚地了解到是因为数据库在实际运行中出现了processes进程总数达到参数设定上限从而导致问题出现,那么可以合理增加初始化参数processes或者通过修正异常频繁的程序客户端登录来解决该问题。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/dfb4aac34fd97843fd84fae48a1c9b2b.html