再遇ORA-12516

記得以前有過一次，今天用戶再次反應又連不上數據庫了，遇到問題總要先查找原因，後尋找解決辦法，這個過程困難，也給心裡造成壓力。

避免後續再次出現同樣的問題，能夠最快時間順利輕鬆解決，今天就記錄之，平衡心理壓力呵...

問題描述：

[oracle@test dbs]$ oerr ora 12516
12516, 00000, "TNS:listener could not find available handler with matching protocol stack"
// *Cause: None of the known and available service handlers for the given
// SERVICE_NAME support the client's protocol stack: transport, session,
// and presentation protocols.
// *Action: Check to make sure that the service handlers (e.g. dispatchers)
// for the given SERVICE_NAME are registered with the listener, are accepting
// connections, and that they are properly configured to support the desired
// protocols.

診斷過程：

[oracle@test dbs]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 23-MAY-2008 13:59:30

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                23-MAY-2008 13:20:03
Uptime                    0 days 0 hr. 39 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.182.4.65)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "infuse01" has 1 instance(s).
  Instance "infuse01", status UNKNOWN, has 1 handler(s) for this service...
Service "infuse02" has 1 instance(s).
  Instance "infuse02", status UNKNOWN, has 1 handler(s) for this service...
Service "sgwmsdb" has 1 instance(s).
  Instance "sgwmsdb", status READY, has 1 handler(s) for this service...
Service "sgwmsdbXDB" has 1 instance(s).
  Instance "sgwmsdb", status READY, has 1 handler(s) for this service...
Service "sgwmsdb_XPT" has 1 instance(s).
  Instance "sgwmsdb", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test_XPT" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@test dbs]$ ps -ef|grep ora_

oracle    5018     1  0 Mar07 ?        00:00:41 ora_pmon_test
oracle    5020     1  0 Mar07 ?        00:01:45 ora_psp0_test
oracle    5022     1  0 Mar07 ?        00:00:01 ora_mman_test
oracle    5024     1  0 Mar07 ?        00:02:50 ora_dbw0_test
oracle    5026     1  0 Mar07 ?        00:07:52 ora_lgwr_test
oracle    5028     1  0 Mar07 ?        00:03:21 ora_ckpt_test
oracle    5030     1  0 Mar07 ?        00:04:53 ora_smon_test
oracle    5032     1  0 Mar07 ?        00:00:00 ora_reco_test
oracle    5034     1  0 Mar07 ?        00:01:46 ora_cjq0_test
oracle    5036     1  0 Mar07 ?        00:05:06 ora_mmon_test
oracle    5038     1  0 Mar07 ?        00:02:11 ora_mmnl_test
oracle    5040     1  0 Mar07 ?        00:00:00 ora_d000_test
oracle    5042     1  0 Mar07 ?        00:00:00 ora_s000_test
oracle    5052     1  0 Mar07 ?        00:01:12 ora_arc0_test
oracle    5054     1  0 Mar07 ?        00:01:11 ora_arc1_test
oracle    5058     1  0 Mar07 ?        00:00:00 ora_qmnc_test
oracle    5075     1  0 Mar07 ?        00:00:00 ora_q000_test

發現缺少job進程ora_j000_test

[oracle@test dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 13:23:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected.
SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-01012: not logged on

SQL> exit

[oracle@test dbs]$ ps -ef|grep oracle|wc -l
172

而spfile文件中processes = 150,從而判斷，這個值太小導致新用戶無法再連入DB.

數據庫處於not log on 狀態，無法直接修改相應參數，於是就先把相關oracle進程kill掉後，可以正常登錄數據庫.

[oracle@test dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 14:08:52 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system set processes=300 scope=spfile;

SQL> shutdown immediate;

SQL> startup;

以上，用戶正常使用.