一、问题的表现
有时候，客户端连接数据库服务器的时候可能遇到这样的错误：
[oracle@ora12c-1 admin]$ sqlplus system@enmy SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 30 15:30:43 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: ERROR: ORA-12537: TNS:connection closed Enter user-name:
造成该错误的原因可能有两个：
1. $ORACLE_HOME/bin/oracle的权限不正确？（很少是这种情况）
2. 数据库服务端的process进程数满（比较可能是这个问题）
在我的环境中，模拟的该错误发生的时候，服务器的alert日志会如下呈现：
Thu Mar 30 15:33:41 2017 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
当前的参数的设置：
SYS@enmy --> col name for a12
SYS@enmy --> col value for a8
SYS@enmy --> select name,type,value from v$parameter where name in ('processes','sessions');
NAME TYPE VALUE
------------ ---------- --------
processes 3 40
sessions 3 82
SYS@enmy -->
当前的会话情况：
set linesize 400
set pagesize 300
col program for a30
col username for a10
col osuser for a14
col machine for a14
select
a.program,a.osuser,a.type,a.sid,a.serial#,a.paddr,a.username,a.machine,a.command,a.status,a.schema#,a.schemaname,
b.TEMPORARY_TABLESPACE,b.username
from
v$session a,dba_users b
where
a.schemaname = b.username;
SYS@enmy --> select 2 a.program,a.osuser,a.type,a.sid,a.serial#,a.paddr,a.username,a.machine,a.command,a.status,a.schema#,a.schemaname, 3 b.TEMPORARY_TABLESPACE,b.username 4 from 5 v$session a,dba_users b 6 where 7 a.schemaname = b.username; PROGRAM OSUSER TYPE SID SERIAL# PADDR USERNAME MACHINE COMMAND STATUS SCHEMA# SCHEMANAME TEMPORARY_TABLESPACE USERNAME ------------------------------ -------------- ---------- ---------- ---------- ---------------- ---------- -------------- ---------- -------- ---------- ------------------------------ ------------------------------ ---------- oracle@ora11gdg1 (W000) oracle BACKGROUND 62 19 00000000F52FBF98 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (SMCO) oracle BACKGROUND 60 31 00000000F5308838 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (Q001) oracle BACKGROUND 57 1 00000000F5304558 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (QMNC) oracle BACKGROUND 54 9 00000000F53023E8 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (ARC2) oracle BACKGROUND 51 1 00000000F5300278 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (MMON) oracle BACKGROUND 49 1 00000000F52F5B48 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (SMON) oracle BACKGROUND 48 1 00000000F52F39D8 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (LGWR) oracle BACKGROUND 47 1 00000000F52F1868 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (MMAN) oracle BACKGROUND 46 1 00000000F52EF6F8 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (DBRM) oracle BACKGROUND 45 1 00000000F52ED588 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (GEN0) oracle BACKGROUND 44 1 00000000F52EB418 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (PSP0) oracle BACKGROUND 43 1 00000000F52E92A8 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (ARC0) oracle BACKGROUND 42 5 00000000F52FE108 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (Q000) oracle BACKGROUND 17 1 00000000F53034A0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (ARC3) oracle BACKGROUND 14 1 00000000F5301330 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (ARC1) oracle BACKGROUND 12 3 00000000F52FF1C0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (NSS2) oracle BACKGROUND 11 1 00000000F52FAEE0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (CJQ0) oracle BACKGROUND 10 13 00000000F5307780 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS sqlplus@ora11gdg1 (TNS V1-V3) oracle USER 9 7 00000000F52F8D70 SYS ora11gdg1 3 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (MMNL) oracle BACKGROUND 8 1 00000000F52F6C00 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (RECO) oracle BACKGROUND 7 1 00000000F52F4A90 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (CKPT) oracle BACKGROUND 6 1 00000000F52F2920 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (DBW0) oracle BACKGROUND 5 1 00000000F52F07B0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (DIA0) oracle BACKGROUND 4 1 00000000F52EE640 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (DIAG) oracle BACKGROUND 3 1 00000000F52EC4D0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (VKTM) oracle BACKGROUND 2 1 00000000F52EA360 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (PMON) oracle BACKGROUND 1 1 00000000F52E81F0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 65 1 00000000F530EC88 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 64 17 00000000F530CB18 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 61 11 00000000F53066C8 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 59 5 00000000F530A9A8 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 24 7 00000000F530DBD0 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 23 37 00000000F52FD050 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 22 19 00000000F5305610 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 20 19 00000000F53098F0 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 19 17 00000000F530BA60 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM 36 rows selected. SYS@enmy -->
上面的会话都是我在其他的机器上模拟出来的。
现在断掉其中的一个的远程连接，看看这个数字会不会降下来：
[oracle@ora11gdg2 ~]$ sqlplus system@enmy SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 30 15:13:29 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: 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 SYSTEM@enmy --> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ora11gdg2 ~]$
服务端再次查看：
SYS@enmy --> / PROGRAM OSUSER TYPE SID SERIAL# PADDR USERNAME MACHINE COMMAND STATUS SCHEMA# SCHEMANAME TEMPORARY_TABLESPACE USERNAME ------------------------------ -------------- ---------- ---------- ---------- ---------------- ---------- -------------- ---------- -------- ---------- ------------------------------ ------------------------------ ---------- oracle@ora11gdg1 (W000) oracle BACKGROUND 62 19 00000000F52FBF98 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (SMCO) oracle BACKGROUND 60 31 00000000F5308838 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (Q001) oracle BACKGROUND 57 1 00000000F5304558 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (QMNC) oracle BACKGROUND 54 9 00000000F53023E8 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (ARC2) oracle BACKGROUND 51 1 00000000F5300278 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (MMON) oracle BACKGROUND 49 1 00000000F52F5B48 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (SMON) oracle BACKGROUND 48 1 00000000F52F39D8 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (LGWR) oracle BACKGROUND 47 1 00000000F52F1868 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (MMAN) oracle BACKGROUND 46 1 00000000F52EF6F8 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (DBRM) oracle BACKGROUND 45 1 00000000F52ED588 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (GEN0) oracle BACKGROUND 44 1 00000000F52EB418 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (PSP0) oracle BACKGROUND 43 1 00000000F52E92A8 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (ARC0) oracle BACKGROUND 42 5 00000000F52FE108 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (Q000) oracle BACKGROUND 17 1 00000000F53034A0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (ARC3) oracle BACKGROUND 14 1 00000000F5301330 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (ARC1) oracle BACKGROUND 12 3 00000000F52FF1C0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (NSS2) oracle BACKGROUND 11 1 00000000F52FAEE0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (CJQ0) oracle BACKGROUND 10 13 00000000F5307780 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS sqlplus@ora11gdg1 (TNS V1-V3) oracle USER 9 7 00000000F52F8D70 SYS ora11gdg1 3 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (MMNL) oracle BACKGROUND 8 1 00000000F52F6C00 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (RECO) oracle BACKGROUND 7 1 00000000F52F4A90 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (CKPT) oracle BACKGROUND 6 1 00000000F52F2920 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (DBW0) oracle BACKGROUND 5 1 00000000F52F07B0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (DIA0) oracle BACKGROUND 4 1 00000000F52EE640 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (DIAG) oracle BACKGROUND 3 1 00000000F52EC4D0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (VKTM) oracle BACKGROUND 2 1 00000000F52EA360 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS oracle@ora11gdg1 (PMON) oracle BACKGROUND 1 1 00000000F52E81F0 ora11gdg1 0 ACTIVE 0 SYS TEMP SYS sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 65 1 00000000F530EC88 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 64 17 00000000F530CB18 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 61 11 00000000F53066C8 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 59 5 00000000F530A9A8 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 24 7 00000000F530DBD0 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 23 37 00000000F52FD050 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 22 19 00000000F5305610 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM sqlplus@ora11gdg2 (TNS V1-V3) oracle USER 19 17 00000000F530BA60 SYSTEM ora11gdg2 0 INACTIVE 5 SYSTEM TEMP SYSTEM 35 rows selected. SYS@enmy -->
确实降了一个。
这时候，再连就没有问题了：
[oracle@ora12c-1 ~]$ sqlplus system@enmy SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 30 15:34:32 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: ERROR: ORA-12537: TNS:connection closed Enter user-name: ^C [oracle@ora12c-1 ~]$ [oracle@ora12c-1 ~]$ sqlplus system@enmy SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 30 15:44:32 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: 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>
二、问题的解决：
增加参数processes的值
参数【processes】是初始化参数，修改该参数需要重启数据库服务
修改:
SYS@enmy --> select name,type,value from v$parameter where name in ('processes','sessions');
NAME TYPE VALUE
------------ ---------- --------
processes 3 40
sessions 3 82
SYS@enmy -->
SYS@enmy --> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileenmy.ora
SYS@enmy -->
SYS@enmy --> alter system set processes=190 scope=spfile;
System altered.
SYS@enmy -->
重启数据库服务：
SYS@enmy --> startup force ORACLE instance started. Total System Global Area 2505338880 bytes Fixed Size 2255832 bytes Variable Size 620758056 bytes Database Buffers 1862270976 bytes Redo Buffers 20054016 bytes Database mounted. Database opened. SYS@enmy --> SYS@enmy -->
这里，我实验环境，使用【force】的方式重启数据库。
如果是正式环境，还是按照正常步骤一步步的来，比较好。
再次查看参数的配置：
SYS@enmy --> select name,type,value from v$parameter where name in ('processes','sessions');
NAME TYPE VALUE
------------ ---------- --------
processes 3 190
sessions 3 308
SYS@enmy -->可以看到，参数processes已经被修改了。
——————————————
Done。