ITPub博客

首页 > 数据库 > 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

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

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


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

  7. SQL> show parameter process

  8. NAME TYPE VALUE
  9. ------------------------------------ ----------- ------------------------------
  10. aq_tm_processes integer 1
  11. cell_offload_processing boolean TRUE
  12. db_writer_processes integer 4
  13. gcs_server_processes integer 0
  14. global_txn_processes integer 1
  15. job_queue_processes integer 1000
  16. log_archive_max_processes integer 4
  17. processes integer 150      ---才150肯定满足不了,下面调大到3000
  18. processor_group_name string
  19. SQL> alter system set processes=3000 scope=spfile;

  20. System altered.

  21. SQL> show parameter session

  22. NAME TYPE VALUE
  23. ------------------------------------ ----------- ------------------------------
  24. java_max_sessionspace_size integer 0
  25. java_soft_sessionspace_limit integer 0
  26. license_max_sessions integer 0
  27. license_sessions_warning integer 0
  28. session_cached_cursors integer 50
  29. session_max_open_files integer 10
  30. sessions integer 256       ---同时调整会话连接数
  31. shared_server_sessions integer
  32. SQL> alter system set sessions=2000 scope=spfile;    ---写入到静态参数文件里面,重启后生效

  33. System altered.

  34. SQL> show parameter job

  35. NAME TYPE VALUE
  36. ------------------------------------ ----------- ------------------------------
  37. job_queue_processes integer 1000
  38. SQL> create pfile from spfile;     ---把修改的,写到动态参数文件,以免用pfile启动实例的时还是以前的参数。

  39. File created.

  40. SQL> set linesize 180
  41. SQL> col program format a36
  42. SQL> col username format a10
  43. SQL> col machine format a30
  44. select sid,serial#,username,program,machine,status from v$session;SQL>

  45.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  46. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  47.          4 44545 U_MD_RS oracle@zhanglin (P026) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  48.         11 3563 U_MD_RS oracle@zhanglin (P027) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  49.         17 1 oracle@zhanglin (PMON) zhanglin ACTIVE
  50.         20 3123 U_MD_RS oracle@zhanglin (P028) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  51.         25 1 oracle@zhanglin (PSP0) zhanglin ACTIVE
  52.         28 13049 U_MD_RS oracle@zhanglin (P029) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  53.         33 1 oracle@zhanglin (VKTM) zhanglin ACTIVE
  54.         34 13899 U_MD_RS oracle@zhanglin (P030) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  55.         41 1 oracle@zhanglin (GEN0) zhanglin ACTIVE
  56.         43 12891 U_MD_RS oracle@zhanglin (P031) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  57.         45 551 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE

  58.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  59. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  60.         49 1 oracle@zhanglin (DIAG) zhanglin ACTIVE
  61.         52 7101 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  62.         57 1 oracle@zhanglin (DBRM) zhanglin ACTIVE
  63.         61 23579 SYS sqlplus@zhanglin (TNS V1-V3) zhanglin ACTIVE
  64.         65 1 oracle@zhanglin (DIA0) zhanglin ACTIVE
  65.         68 465 oracle@zhanglin (W000) zhanglin ACTIVE
  66.         69 26109 U_CL_CTL plsqldev.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  67.         73 1 oracle@zhanglin (MMAN) zhanglin ACTIVE
  68.         77 20025 U_CL_CTL plsqldev.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  69.         81 1 oracle@zhanglin (DBW0) zhanglin ACTIVE
  70.         82 28147 U_MD_RS oracle@zhanglin (P010) WORKGROUP\\HBSHOESPORTAL16 ACTIVE

  71.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  72. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  73.         89 1 oracle@zhanglin (DBW1) zhanglin ACTIVE
  74.         91 40343 U_MD_RS oracle@zhanglin (P011) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  75.         97 1 oracle@zhanglin (DBW2) zhanglin ACTIVE
  76.         98 12801 U_MD_RS oracle@zhanglin (P012) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  77.        101 35 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  78.        105 1 oracle@zhanglin (DBW3) zhanglin ACTIVE
  79.        106 8225 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  80.        107 44797 U_MD_RS oracle@zhanglin (P013) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  81.        113 1 oracle@zhanglin (LGWR) zhanglin ACTIVE
  82.        115 24437 U_MD_RS oracle@zhanglin (P014) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  83.        117 2643 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 ACTIVE

  84.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  85. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  86.        121 1 oracle@zhanglin (CKPT) zhanglin ACTIVE
  87.        122 25477 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  88.        123 44059 U_MD_RS oracle@zhanglin (P015) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  89.        129 1 oracle@zhanglin (SMON) zhanglin ACTIVE
  90.        130 20553 oracle@zhanglin (CJQ0) zhanglin ACTIVE
  91.        132 13219 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  92.        137 1 oracle@zhanglin (RECO) zhanglin ACTIVE
  93.        145 1 oracle@zhanglin (MMON) zhanglin ACTIVE
  94.        147 25321 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  95.        153 1 oracle@zhanglin (MMNL) zhanglin ACTIVE
  96.        158 1079 U_CL_CTL plsqldev.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE

  97.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  98. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  99.        177 5 oracle@zhanglin (SMCO) zhanglin ACTIVE
  100.        185 5 oracle@zhanglin (QMNC) zhanglin ACTIVE
  101.        195 14391 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  102.        204 9527 U_MD_RS oracle@zhanglin (P008) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  103.        209 9595 oracle@zhanglin (Q002) zhanglin ACTIVE
  104.        217 18941 U_MD_RS oracle@zhanglin (P009) WORKGROUP\\HBSHOESPORTAL16 ACTIVE
  105.        225 40905 U_CL_CTL plsqldev.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  106.        228 25399 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  107.        235 34583 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  108.        244 28143 U_MD_RS ReportingServicesService.exe WORKGROUP\\HBSHOESPORTAL16 INACTIVE
  109.        245 329 U_MD_RS oracle@zhanglin (P024) WORKGROUP\\HBSHOESPORTAL16 ACTIVE

  110.        SID SERIAL# USERNAME PROGRAM MACHINE STATUS
  111. ---------- ---------- ---------- ------------------------------------ ------------------------------ --------
  112.        249 1 oracle@zhanglin (Q001) zhanglin ACTIVE
  113.        251 20705 U_MD_RS oracle@zhanglin (P025) WORKGROUP\\HBSHOESPORTAL16 ACTIVE

  114. 57 rows selected.

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

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

请登录后发表评论 登录
全部评论

注册时间:2016-02-29

  • 博文量
    203
  • 访问量
    215318