ORA-12537: TNS: connection closed, Oracle troubleshooting, ora-12537tns

ORA-12537: TNS: connection closed, Oracle troubleshooting, ora-12537tns

1. error message 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:   

Check that the listener is normal, and the oracle service is started normally, but the logon fails.

[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. Solution

[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, you can connect to the server. The problem is preliminarily solved.

3, but 3 minutes later, the login failed.

Check the lsnrctl status:

[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 Date                23-NOV-2015 14:30:19Uptime                    0 days 0 hr. 0 min. 13 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener 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]$ 

The following error is reported in the alert Log in the background:

        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.

Solution 1:

Lsnrctl stop for 5 minutes and then start lsnrctl. The problem is solved because the application keeps connecting to the database and the connection pool is full.

Solution 2:

Check the number of oracle connections, which is indeed 150

SQL> show parameter processes;NAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------aq_tm_processes              integer     0db_writer_processes          integer     2gcs_server_processes             integer     0global_txn_processes             integer     1job_queue_processes          integer     1000log_archive_max_processes        integer     4processes                integer     150SQL> SQL> SQL> 

Cause analysis:

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> 

Modify the configuration file:

[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 ~]$ 

Change the number of connections and write the parameter file

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> 

Shut down and restart the oracle instance. After the instance is started, you can see that the maximum number of connections has changed to 500. The problem is solved.

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 Size          2213936 bytesVariable Size        4362078160 bytesDatabase Buffers     2281701376 bytesRedo Buffers           34922496 bytesDatabase mounted.Database opened.SQL> SQL> SQL> show parameter processes;NAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------aq_tm_processes              integer     0db_writer_processes          integer     2gcs_server_processes             integer     0global_txn_processes             integer     1job_queue_processes          integer     1000log_archive_max_processes        integer     4processes                integer     500SQL> 

Solve the problem.