ORA-12537:TNS:connectionclosed错误处理过程

转载

mob604756e78484 2018-01-08 13:17:00

文章标签 oracle sql sed 解决方案 linux 文章分类 代码人生

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

1 2 3 4 5 6 7 8 9 10 11	`<code class=``" hljs ruby"``>[oracle@pldb236 admin]$ rlwrap sqlplus powerdesk/pd141118@PD236` `SQL*Plus: Release 11.2.0.1.0 Production` `on` `Mon Nov 23 14:16:31 2015` `Copyright (c) 1982, 2009, Oracle.` `All` `rights reserved.` `ERROR:` `ORA-12537: TNS:``connection` `closed` `Enter` `user``-``name``: </code>`

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

1 2 3 4 5 6 7 8 9 10 11 12 13	`[oracle@pldb236 admin]$ tnsping PD236` `TNS Ping Utility` `for` `Linux: Version 11.2.0.1.0 - Production` `on` `23-NOV-2015 14:17:22` `Copyright (c) 1997, 2009, Oracle.` `All` `rights reserved.` `Used parameter files:` `Used TNSNAMES adapter` `to` `resolve the alias` `Attempting` `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，解决方案

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27	`[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` `sysdba` `SQL*Plus: Release 11.2.0.1.0 Production` `on` `Mon Nov 23 14:20:09 2015` `Copyright (c) 1982, 2009, Oracle.` `All` `rights reserved.` `Connected` `to``:` `Oracle` `Database` `11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production` `With` `the Partitioning, OLAP, Data Mining` `and` `Real` `Application Testing options` `SQL>` `SQL>`

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

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

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 14:29:17 2015

ERROR:

ORA-12537: TNS:connection closed

Enter user-name:

去查看lsnrctl状态：

[oracle@pldb236 bin]$ lsnrctl status

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26	`LSNRCTL` `for` `Linux: Version 11.2.0.1.0 - Production` `on` `23-NOV-2015 14:30:33` `Copyright (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 LISTENER` `Version TNSLSNR` `for` `Linux: Version 11.2.0.1.0 - Production` `Start` `Date` `23-NOV-2015 14:30:19` `Uptime 0 days 0 hr. 0` `min``. 13 sec` `Trace` `Level` `off` `Security` `ON``:` `Local` `OS Authentication` `SNMP` `OFF` `Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora` `Listener Log File /oracle/app/oracle/diag/tnslsnr/pldb236/listener/alert/log.xml` `Listening 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的日志报错如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17	`<code class=``" hljs livecodeserver"``> Mon Nov 23 14:32:00 2015` `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.` `Mon Nov 23 14:32:47 2015` `Process m000 submission failed` `with` `error = 20` `Mon Nov 23 14:33:02 2015` `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.` `Mon Nov 23 14:34:03 2015` `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.</code>`

解决方案1：

lsnrctl stop 5分钟后，再lsnrctl start起来，问题解决了，是应用程序一直不停的连接数据库，占满了连接池导致的。

解决方案2：

查看oracle的连接数，果然为150

1 2 3 4 5 6 7 8 9 10 11 12 13 14	`<code class=``" hljs vhdl"``>SQL> show parameter processes;` `NAME` `TYPE VALUE` `------------------------------------ ----------- ------------------------------` `aq_tm_processes` `integer` `0` `db_writer_processes` `integer` `2` `gcs_server_processes` `integer` `0` `global_txn_processes` `integer` `1` `job_queue_processes` `integer` `1000` `log_archive_max_processes` `integer` `4` `processes` `integer` `150` `SQL>` `SQL>` `SQL> </code>`

分析原因：

1 2 3 4 5 6 7 8 9 10 11 12 13	`<code class=``" hljs asciidoc"``>SQL>` `select` `count``(1)` `from` `v$session t` `where` `t.status=``'INACTIVE'` `and` `t.username=``'PLAS'``;` `COUNT``(1)` `----------` `88` `SQL>` `SQL>` `SQL>` `select` `count``(1)` `from` `v$session t` `where` `t.status=``'INACTIVE'` `and` `t.username=``'PLAS'``;` `COUNT``(1)` `----------` `5` `SQL> </code>`

修改配置文件：

1 2 3 4 5	`<code class=``" hljs ruby"``>[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 ~]$ </code>`

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

1 2 3 4 5 6 7 8 9 10	`<code class=``" hljs oxygene"``>``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> </code>`

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39	`<code class=``" hljs vhdl"``>SQL> shutdown immediate;` `Database` `closed.` `Database` `dismounted.` `ORACLE instance shut down.` `SQL> exit` `Disconnected` `from` `Oracle` `Database` `11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production` `With` `the Partitioning, OLAP, Data Mining` `and` `Real` `Application Testing options` `[oracle@pldb236 ~]$ rlwrap sqlplus /` `as` `sysdba` `SQL*Plus: Release 11.2.0.1.0 Production` `on` `Mon Nov 23 23:09:00 2015` `Copyright (c) 1982, 2009, Oracle.` `All` `rights reserved.` `Connected` `to` `an idle instance.` `SQL> startup;` `ORACLE instance started.` `Total System` `Global` `Area 6680915968 bytes` `Fixed` `Size` `2213936 bytes` `Variable` `Size` `4362078160 bytes` `Database` `Buffers 2281701376 bytes` `Redo Buffers 34922496 bytes` `Database` `mounted.` `Database` `opened.` `SQL>` `SQL>` `SQL> show parameter processes;` `NAME` `TYPE VALUE` `------------------------------------ ----------- ------------------------------` `aq_tm_processes` `integer` `0` `db_writer_processes` `integer` `2` `gcs_server_processes` `integer` `0` `global_txn_processes` `integer` `1` `job_queue_processes` `integer` `1000` `log_archive_max_processes` `integer` `4` `processes` `integer` `500` `SQL> </code>`