最近客戶郵件描述無法從客戶端連接到數據庫，其錯誤號為ORA-12537: TNS:connection closed，連接被關閉。直接通過tnsping沒有任何問題。listener日志中出現TNS-12518: TNS:listener could not hand off client connection。即Listener無法分發客戶端連接。下面是具體的錯誤信息、分析與解決的過程。
1、前端錯誤信息
C:\Program Files\VMware\VMware vSphere CLI>sqlplus robinson@sywgmr
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 27 10:49:00 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12537: TNS:connection closed
oracle@SZDB:/users/oracle/OraHome10g/network/log> tail listener_sywgmr.log
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe
27-MAR-2014 11:02:38 * (CONNECT_DATA=(SID=SYWGMR)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=63561)) * establish * SYWGMR * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe
oracle@SZDB:~> oerr ora 12537
12537, 00000, "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has disconnected.
// *Action: None needed; this is an information message.
oracle@SZDB:~> oerr ora 12518
12518, 00000, "TNS:listener could not hand off client connection"
// *Cause: The process of handing off a client connection to another process
// failed.
// *Action: Turn on listener tracing and re-execute the operation. Verify
// that the listener and database instance are properly configured for
// direct handoff. If problem persists, call Oracle Support.
// *Comment: The problem can be worked around by configuring dispatcher(s)
// to specifically handle the desired presentation(s), and connecting
// directly to the dispatcher, bypassing the listener.
#根據上面的2個error no，沒有獲得太多的有用信息
#ora-12518建議作一個trace.其comment說明可以通過配置disaptcher來解決，而當前我們的數據庫使用的是dedicate方式
#關於如何配置監聽器trace，可參考: http://blog.csdn.net/leshami/article/details/8254720
#此處不做trace，筆者嘗試reload以及restart 監聽，故障依舊
#下面查看alert log 日志
#下面的日志裡表明無法創建m000進程，m000進程是SMON進程的奴隸進程
oracle@SZDB:/users/oracle> tail /u02/database/SYWGMR/bdump/alert_SYWGMR.log
Thu Mar 27 11:00:28 2014
ksvcreate: Process(m000) creation failed
Thu Mar 27 11:01:29 2014
Process m000 died, see its trace file
Thu Mar 27 11:01:29 2014
ksvcreate: Process(m000) creation failed
Thu Mar 27 11:02:30 2014
Process m000 died, see its trace file
Thu Mar 27 11:02:30 2014
ksvcreate: Process(m000) creation failed
#在06:19:02 2014時有一個Error，無法衍生job 奴隸進程
Thu Mar 27 06:19:02 2014
Process J000 died, see its trace file
Thu Mar 27 06:19:02 2014
kkjcre1p: unable to spawn jobq slave process
Thu Mar 27 06:19:02 2014
Errors in file /u02/database/SYWGMR/bdump/sywgmr_cjq0_7780.trc:
#從上面的alert log可以看出實例無法創建新的進程，推斷有可能是由於超出了實例設定進程的最大值
oracle@SZDB:~> export ORACLE_SID=SYWGMR
oracle@SZDB:~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 27 10:56:27 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
-- Author : Leshami
-- Blog : http://blog.csdn.net/leshami
SQL> col RESOURCE_NAME for a20
SQL> col LIMIT_VALUE for a20
SQL> select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');
RESOURCE_NAME MAX_UTILIZATION LIMIT_VALUE
-------------------- --------------- --------------------
processes 1000 1000
sessions 1004 1105
#上面的查詢結果果然如此
#找到了引起故障的原因，就好處理了。下面是關於processes的描述
PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.
Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.
The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter.
Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.
#修改實例的processes參數，如下，不支持memory的修改，因此需要加scope
sys> alter system set processes=1500;
alter system set processes=1500
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
sys> alter system set processes=1500 scope=spfile;
System altered.
#下面是修改並重啟後的結果，如果你使用的是pfile參數，則直接修改pfile中processes的值再重啟即可。
sys> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 1500
sys> show parameter session;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 1655
更多參考
有關Oracle RAC請參考
有關Oracle 網絡配置相關基礎以及概念性的問題請參考：
有關基於用戶管理的備份和備份恢復的概念請參考
有關RMAN的備份恢復與管理請參考
有關ORACLE體系結構請參考
