ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ORA-00020: maximum number of processes (150) exceeded

ORA-00020: maximum number of processes (150) exceeded

Oracle 作者：urgel_babay 时间：2016-02-29 17:27:28 0 删除 编辑
2014.12.17

今天在巡检地区数据仓库的时候发现一个地区的alert.log 出现大量的报错：
Errors in file /u01/app/oracle/diag/rdbms/hbshoes/HBSHOES/trace/HBSHOES_cjq0_24531.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/hbshoes/HBSHOES/trace/HBSHOES_cjq0_24531.trc:
Wed Dec 17 11:46:13 2014
ORA-00020: maximum number of processes (150) 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 J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/hbshoes/HBSHOES/trace/HBSHOES_cjq0_24531.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/hbshoes/HBSHOES/trace/HBSHOES_cjq0_24531.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/hbshoes/HBSHOES/trace/HBSHOES_cjq0_24531.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/hbshoes/HBSHOES/trace/HBSHOES_cjq0_24531.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/hbshoes/HBSHOES/trace/HBSHOES_cjq0_24531.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
Errors in file /u01/app/oracle/diag/rdbms/hbshoes/HBSHOES/trace/HBSHOES_cjq0_24531.trc:
Process J000 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process 
继续查看trc文件：
*** 2014-12-17 11:46:13.304
ORA-00020: maximum number of processes (150) exceeded
ORA-00020: maximum number of processes (150) exceeded
ORA-00020: maximum number of processes (150) exceeded

*** 2014-12-17 11:46:14.306
ORA-00020: maximum number of processes (150) exceeded
ORA-00020: maximum number of processes (150) exceeded
ORA-00020: maximum number of processes (150) exceeded

*** 2014-12-17 11:46:15.308
ORA-00020: maximum number of processes (150) exceeded
ORA-00020: maximum number of processes (150) exceeded
ORA-00020: maximum number of processes (150) exceeded

*** 2014-12-17 11:46:16.310
ORA-00020: maximum number of processes (150) exceeded
ORA-00020: maximum number of processes (150) exceeded
ORA-00020: maximum number of processes (150) exceeded
这个报错一般情况就是processes 设置的太小造成的。所以处理起来就很容易了。
进到数据库里面查看相关的参数设置。
（我第一次sqlplus / as sysdba 的时候，进入去，因为超过process，过了一会才登录进去的）

点击(此处)折叠或打开

  1. [oracle@zhanglin ~]$ sqlplus / as sysdba

  3. SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 17 11:53:50 2014

  5. Copyright (c) 1982, 2011, Oracle. All rights reserved.


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

  12. SQL> show parameter process

  14. NAME TYPE VALUE
  15. ------------------------------------ ----------- ------------------------------
  16. aq_tm_processes integer 1
  17. cell_offload_processing boolean TRUE
  18. db_writer_processes integer 4
  19. gcs_server_processes integer 0
  20. global_txn_processes integer 1
  21. job_queue_processes integer 1000
  22. log_archive_max_processes integer 4
  23. processes integer 150      ---才150肯定满足不了，下面调大到3000
  24. processor_group_name string
  25. SQL> alter system set processes=3000 scope=spfile;

  27. System altered.

  29. SQL> show parameter session

  31. NAME TYPE VALUE
  32. ------------------------------------ ----------- ------------------------------
  33. java_max_sessionspace_size integer 0
  34. java_soft_sessionspace_limit integer 0
  35. license_max_sessions integer 0
  36. license_sessions_warning integer 0
  37. session_cached_cursors integer 50
  38. session_max_open_files integer 10
  39. sessions integer 256       ---同时调整会话连接数
  40. shared_server_sessions integer
  41. SQL> alter system set sessions=2000 scope=spfile;    ---写入到静态参数文件里面，重启后生效

  43. System altered.

  45. SQL> show parameter job

  47. NAME TYPE VALUE
  48. ------------------------------------ ----------- ------------------------------
  49. job_queue_processes integer 1000
  50. SQL> create pfile from spfile;     ---把修改的，写到动态参数文件，以免用pfile启动实例的时还是以前的参数。

  52. File created.

  54. SQL> set linesize 180
  55. SQL> col program format a36
  56. SQL> col username format a10
  57. SQL> col machine format a30
  58. select sid,serial#,username,program,machine,status from v$session;SQL>

  60.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  61. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  62.          4 44545 U_MD_RS oracle@zhanglin (P026) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  63.         11 3563 U_MD_RS oracle@zhanglin (P027) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  64.         17 1 oracle@zhanglin (PMON) zhanglin ACTIVE
  65.         20 3123 U_MD_RS oracle@zhanglin (P028) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  66.         25 1 oracle@zhanglin (PSP0) zhanglin ACTIVE
  67.         28 13049 U_MD_RS oracle@zhanglin (P029) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  68.         33 1 oracle@zhanglin (VKTM) zhanglin ACTIVE
  69.         34 13899 U_MD_RS oracle@zhanglin (P030) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  70.         41 1 oracle@zhanglin (GEN0) zhanglin ACTIVE
  71.         43 12891 U_MD_RS oracle@zhanglin (P031) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  72.         45 551 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE

  74.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  75. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  76.         49 1 oracle@zhanglin (DIAG) zhanglin ACTIVE
  77.         52 7101 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  78.         57 1 oracle@zhanglin (DBRM) zhanglin ACTIVE
  79.         61 23579 SYS sqlplus@zhanglin (TNS V1-V3) zhanglin ACTIVE
  80.         65 1 oracle@zhanglin (DIA0) zhanglin ACTIVE
  81.         68 465 oracle@zhanglin (W000) zhanglin ACTIVE
  82.         69 26109 U_CL_CTL plsqldev.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  83.         73 1 oracle@zhanglin (MMAN) zhanglin ACTIVE
  84.         77 20025 U_CL_CTL plsqldev.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  85.         81 1 oracle@zhanglin (DBW0) zhanglin ACTIVE
  86.         82 28147 U_MD_RS oracle@zhanglin (P010) WORKGROUP\\HBSHOESPORTAL16 ACTIVE

  88.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  89. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  90.         89 1 oracle@zhanglin (DBW1) zhanglin ACTIVE
  91.         91 40343 U_MD_RS oracle@zhanglin (P011) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  92.         97 1 oracle@zhanglin (DBW2) zhanglin ACTIVE
  93.         98 12801 U_MD_RS oracle@zhanglin (P012) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  94.        101 35 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  95.        105 1 oracle@zhanglin (DBW3) zhanglin ACTIVE
  96.        106 8225 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  97.        107 44797 U_MD_RS oracle@zhanglin (P013) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  98.        113 1 oracle@zhanglin (LGWR) zhanglin ACTIVE
  99.        115 24437 U_MD_RS oracle@zhanglin (P014) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  100.        117 2643 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 ACTIVE

  102.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  103. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  104.        121 1 oracle@zhanglin (CKPT) zhanglin ACTIVE
  105.        122 25477 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  106.        123 44059 U_MD_RS oracle@zhanglin (P015) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  107.        129 1 oracle@zhanglin (SMON) zhanglin ACTIVE
  108.        130 20553 oracle@zhanglin (CJQ0) zhanglin ACTIVE
  109.        132 13219 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  110.        137 1 oracle@zhanglin (RECO) zhanglin ACTIVE
  111.        145 1 oracle@zhanglin (MMON) zhanglin ACTIVE
  112.        147 25321 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  113.        153 1 oracle@zhanglin (MMNL) zhanglin ACTIVE
  114.        158 1079 U_CL_CTL plsqldev.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE

  116.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  117. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  118.        177 5 oracle@zhanglin (SMCO) zhanglin ACTIVE
  119.        185 5 oracle@zhanglin (QMNC) zhanglin ACTIVE
  120.        195 14391 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  121.        204 9527 U_MD_RS oracle@zhanglin (P008) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  122.        209 9595 oracle@zhanglin (Q002) zhanglin ACTIVE
  123.        217 18941 U_MD_RS oracle@zhanglin (P009) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  124.        225 40905 U_CL_CTL plsqldev.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  125.        228 25399 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  126.        235 34583 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  127.        244 28143 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  128.        245 329 U_MD_RS oracle@zhanglin (P024) WORKGROUP\\HBSHOESPORTAL16 ACTIVE

  130.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  131. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  132.        249 1 oracle@zhanglin (Q001) zhanglin ACTIVE
  133.        251 20705 U_MD_RS oracle@zhanglin (P025) WORKGROUP\\HBSHOESPORTAL16 ACTIVE

  135. 57 rows selected.

  137. SQL>
       上面的显示当前连接到数据库的会话，其中很多都是INCATIVE ，而且都是由报表服务产生的。暂时还不知情这个报表服务的作用，所以不好做处理。
        如果你的会话连接出现大量的INCATIVE ,同时实例不允许重启的情况下，可以kill 掉这些非活动的连接，前提是要得到你的领导同时，不然出问题了，就不好办了，对吧！让后找个空闲的时段，重启一下实例即可！

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30936525/viewspace-2016679/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle 10g 安装gc:OPMN Process Manager failed to start和OUI - 25031解决方案
下一篇： '+CRS' in '/etc/oracle/ocr.loc' and '/dev/raw/raw1' does not match
请登录后发表评论 登录
全部评论
urgel_babay

注册时间：2016-02-29

  • 博文量
    203
  • 访问量
    339135

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593