ORA-12537: TNS:connection closed连接关闭，Oracle报错问题解决
程序员文章站
2022-03-10 19:25:44
1. 报错信息ORA-12537: TNS:connection closed连接关闭
[oracle@pldb236 admin]$ rlwrap sqlplus powe...
1. 报错信息ORA-12537: TNS:connection closed连接关闭
[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:
检查监听正常，oracle服务也是正常启动的，但是登录不进去。
[oracle@jcapp ~]$ tnsping PD236 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-DEC-2017 16:57:47 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora TNS-03505: Failed to resolve name [oracle@jcapp ~]$
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 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分钟后，又不行了，登录不上去。
去查看lsnrctl状态：
[oracle@pldb236 bin]$ lsnrctl status
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的日志报错如下：
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.
解决方案1：
lsnrctl stop 5分钟后，再lsnrctl start起来，问题解决了，是应用程序一直不停的连接数据库，占满了连接池导致的。
解决方案2：
查看oracle的连接数，果然为150
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>
分析原因：
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>
修改配置文件：
[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> 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>
问题解决。