oracle 最大连接数的报错

usharkp

1.设置最大连接数

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

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     25

[oracle@T10 ~]$ ps -ef|grep ora_
oracle    4678     1  0 11:02 ?        00:00:00 ora_pmon_Peng
oracle    4680     1  0 11:02 ?        00:00:00 ora_psp0_Peng
oracle    4682     1  0 11:02 ?        00:00:00 ora_mman_Peng
oracle    4684     1  0 11:02 ?        00:00:00 ora_dbw0_Peng
oracle    4686     1  0 11:02 ?        00:00:00 ora_lgwr_Peng
oracle    4688     1  0 11:02 ?        00:00:00 ora_ckpt_Peng
oracle    4690     1  0 11:02 ?        00:00:00 ora_smon_Peng
oracle    4692     1  0 11:02 ?        00:00:00 ora_reco_Peng
oracle    4694     1  0 11:02 ?        00:00:00 ora_cjq0_Peng
oracle    4696     1  0 11:02 ?        00:00:00 ora_mmon_Peng
oracle    4698     1  0 11:02 ?        00:00:00 ora_mmnl_Peng
oracle    4700     1  0 11:02 ?        00:00:00 ora_d000_Peng
oracle    4702     1  0 11:02 ?        00:00:00 ora_s000_Peng
oracle    4719     1  0 11:02 ?        00:00:00 ora_arc0_Peng
oracle    4721     1  0 11:02 ?        00:00:00 ora_arc1_Peng
oracle    4725     1  0 11:02 ?        00:00:00 ora_qmnc_Peng
oracle    4731     1  0 11:02 ?        00:00:00 ora_q000_Peng
oracle    5050     1  0 11:03 ?        00:00:00 ora_q002_Peng
oracle    5501  5372  0 11:13 pts/9    00:00:00 grep ora_
[oracle@T10 ~]$ ps -ef|grep ora_|grep -v grep|wc
     19     152    1178
后台进程的连接数为19，processes的值一定要大于后台进程的连接数，不然启动不了数据库。

SQL> startup
ORA-03113: end-of-file on communication channel

[oracle@T10 ~]$ ps -ef|grep oraclePeng
oracle    4717  4605  0 11:02 ?        00:00:01 oraclePeng (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5096  5095  0 11:04 ?        00:00:00 oraclePeng (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5098  5097  0 11:04 ?        00:00:00 oraclePeng (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5124  5122  0 11:04 ?        00:00:00 oraclePeng (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5435  5080  0 11:11 ?        00:00:00 oraclePeng (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5562  5561  0 11:15 ?        00:00:00 oraclePeng (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6368  5372  0 11:41 pts/9    00:00:00 grep oraclePeng
[oracle@T10 ~]$ ps -ef|grep oraclePeng|grep -v grep|wc
      6      54     654
此时刚好25个连接。

如果再次连接，则报错：

[oracle@T10 ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 23 11:42:46 2011

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

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

Enter user-name:

查看警告日志：

[root@T10 bdump]# tail alert_Peng.log
Fri Sep 23 11:40:44 2011
ksvcreate: Process(q002) creation failed
Fri Sep 23 11:41:35 2011
Process q002 died, see its trace file
Fri Sep 23 11:41:35 2011
ksvcreate: Process(q002) creation failed
Fri Sep 23 11:43:18 2011
Process q002 died, see its trace file
Fri Sep 23 11:43:18 2011
ksvcreate: Process(q002) creation failed

通过查看警告日志，知道oracle此时有些非核心进程要创建，但由于processes的限制，所以会提示

Process(q002) creation failed。

此时我们需要将连接数增大：

alter system set processes = 300 scope = spfile;

在重新启动数据库。