ORA-12537: TNS:connection closed错误处理过程

1，同事说Oracle测试换了连接不上了，报错如下

[oracle@pldb236 admin]$ rlwrap sqlplus powerdesk/pd141118@PD236SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 14:16:31 2015Copyright （c） 1982, 2009, Oracle.All rights reserved.ERROR:ORA-12537: TNS:connection closedEnter user-name:

检查监听正常，Oracle服务也是正常启动的，但是登录不进去

[oracle@pldb236 admin]$ tnsping PD236TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-NOV-2015 14:17:22Copyright （c） 1997, 2009, Oracle.All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 192.168.180.236）（PORT = 1521））） （CONNECT_DATA = （SERVICE_NAME = powerdes）））OK （10 msec）[oracle@pldb236 admin]$

2，解决方案

[oracle@pldb236 bin]$ cd $ORACLE_HOME/bin/[oracle@pldb236 bin]$ [oracle@pldb236 bin]$ [oracle@pldb236 bin]$ ll oracle-rwsr-s--x. 1 oracle oinstall 210823844 Jul 31 13:21 oracle[oracle@pldb236 bin]$ [oracle@pldb236 bin]$ chmod 6571 oracle[oracle@pldb236 bin]$ [oracle@pldb236 bin]$ ll oracle-r-srws--x. 1 oracle oinstall 210823844 Jul 31 13:21 oracle[oracle@pldb236 bin]$ [oracle@pldb236 bin]$ rlwrap sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 14:20:09 2015Copyright （c） 1982, 2009, Oracle.All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SQL>

ok可以连接上了，问题初步解决

3，不过3分钟后，又不行了，登录不上去。

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 14:29:17 2015Copyright （c） 1982, 2009, Oracle. All rights reserved.ERROR:
ORA-12537: TNS:connection closedEnter user-name:去查看lsnrctl状态：
[oracle@pldb236 bin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-NOV-2015 14:30:33Copyright （c） 1991, 2009, Oracle.All rights reserved.Connecting to （DESCRIPTION=（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.180.236）（PORT=1521）））STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date23-NOV-2015 14:30:19Uptime0 days 0 hr. 0 min. 13 secTrace Level offSecurityON: Local OS AuthenticationSNMPOFFListener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File /oracle/app/oracle/diag/tnslsnr/pldb236/listener/alert/log.xmlListening Endpoints Summary...（DESCRIPTION=（ADDRESS=（PROTOCOL=tcp）（HOST=192.168.180.236）（PORT=1521）））（DESCRIPTION=（ADDRESS=（PROTOCOL=ipc）（KEY=EXTPROC0）））Services Summary...Service "PLSExtProc" has 1 instance（s）.Instance "PLSExtProc", status UNKNOWN, has 1 handler（s） for this service...Service "powerdes" has 1 instance（s）.Instance "powerdes", status UNKNOWN, has 1 handler（s） for this service...The command completed successfully[oracle@pldb236 bin]$

看到后台alert的日志报错如下：

Mon Nov 23 14:32:00 2015ORA-00020: maximum number of processes 150 exceededORA-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.Mon Nov 23 14:32:47 2015Process m000 submission failed with error = 20Mon Nov 23 14:33:02 2015ORA-00020: maximum number of processes 150 exceededORA-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.Mon Nov 23 14:34:03 2015ORA-00020: maximum number of processes 150 exceededORA-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.

解决方案1：
lsnrctl stop 5分钟后，再lsnrctl start起来，问题解决了，是应用程序一直不停的连接数据库，占满了连接池导致的。解决方案2：
查看oracle的连接数，果然为150

SQL> show parameter processes;NAME TYPEVALUE------------------------------------ ----------- ------------------------------aq_tm_processesinteger 0db_writer_processesinteger 2gcs_server_processes integer 0global_txn_processes integer 1job_queue_processesinteger 1000log_archive_max_processesinteger 4processesinteger 150SQL> SQL> SQL>

分析原因：

SQL> select count（1） from v$session t where t.status="INACTIVE" and t.username="PLAS";COUNT（1）----------88SQL> SQL> SQL> select count（1） from v$session t where t.status="INACTIVE" and t.username="PLAS";COUNT（1）---------- 5SQL>

修改配置文件：

[oracle@pldb236 ~]$ find /oracle -name *init.ora*/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora/oracle/app/oracle/product/11.2.0/dbhome_1/srvm/admin/init.ora/oracle/app/oracle/admin/powerdes/pfile/init.ora.7112015171232[oracle@pldb236 ~]$

改动连接数，并且写入参数文件

alter system set processes=500 scope = spfile;SQL> alter system set processes=500 scope=spfile;System altered.SQL> create pfile from spfile;File created.SQL>

关闭重启oracle实例，启动就可以看到最大连接数已经变成了500，问题解决

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@pldb236 ~]$ rlwrap sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 23:09:00 2015Copyright （c） 1982, 2009, Oracle.All rights reserved.Connected to an idle instance.SQL> startup;ORACLE instance started.Total System Global Area 6680915968 bytesFixed Size2213936 bytesVariable Size4362078160 bytesDatabase Buffers 2281701376 bytesRedo Buffers 34922496 bytesDatabase mounted.Database opened.SQL> SQL> SQL> show parameter processes;NAME TYPEVALUE------------------------------------ ----------- ------------------------------aq_tm_processesinteger 0db_writer_processesinteger 2gcs_server_processes integer 0global_txn_processes integer 1job_queue_processesinteger 1000log_archive_max_processesinteger 4processesinteger 500SQL>

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址