[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@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]$
PS：原blog地址：http://blog.csdn.net/mchdba/article/category/3254519，未經過原csdn的博主mchdba允許，謝絕轉載
[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可以連接上了，問題初步解決
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 23 14:29:17 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name:
去查看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> 
