TNS-12516及ORA-12516錯誤解決

在用戶端進行sqlplus連接配接報錯如下：

ORA-12516: TNS: 監聽程式找不到符合協定堆棧要求的可用處理程式
           

在/home/oracle/product/10.2.0/db_1/network/log/listener.log中檢視到錯誤如下：

TNS-12516: TNS:listener could not find available handler with matching protocol stack
           

sqlplus連接配接之後報錯：

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 16 00:06:17 2013

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

Connected.
SQL> show parameter session
ORA-01012: not logged on


SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-00020: maximum number of processes (%s) exceeded
           

alterlog中報錯：

Wed May 15 23:59:26 2013
Process m000 died, see its trace file
Wed May 15 23:59:26 2013
ksvcreate: Process(m000) creation failed
Thu May 16 00:00:26 2013
Process m000 died, see its trace file
Thu May 16 00:00:26 2013
ksvcreate: Process(m000) creation failed
Process m000 died, see its trace file
Thu May 16 00:00:27 2013
ksvcreate: Process(m000) creation failed
Thu May 16 00:01:27 2013
Process m000 died, see its trace file
Thu May 16 00:01:27 2013
ksvcreate: Process(m000) creation failed
Process m000 died, see its trace file
Thu May 16 00:01:27 2013
ksvcreate: Process(m000) creation failed
           

1、無法登陸資料庫的情況下使用以下方法：

a、找到資料庫的關鍵程序，然後殺死，此時資料庫執行個體會自動進行關閉

[[email protected] ~]$ ps -ef|grep dbw
	oracle   16107     1  0 00:04 ?        00:00:00 ora_dbw0_orcl
	oracle   16755 15946  0 00:19 pts/0    00:00:00 grep dbw
	[[email protected] ~]$ kill -9 16107
           

2、公用的方法，當能登陸進資料庫之後

b、sqlplus登入進資料庫，啟動資料庫

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 16 00:20:11 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size		    2019384 bytes
Variable Size		  100667336 bytes
Database Buffers	   71303168 bytes
Redo Buffers		    2170880 bytes
Database mounted.
Database opened.
           

c、檢視系統，發現process的值已經接近上限，修改processes的值

SQL> show parameter process

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes 		     integer	 0
db_writer_processes		     integer	 1
gcs_server_processes		     integer	 0
job_queue_processes		     integer	 10
log_archive_max_processes	     integer	 2
processes			     integer	 20
           

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

System altered.
           

d、重新啟動oracle資料庫

SQL> startup force
ORACLE instance started.

Total System Global Area  176160768 bytes
Fixed Size		    2019384 bytes
Variable Size		  100667336 bytes
Database Buffers	   71303168 bytes
Redo Buffers		    2170880 bytes
Database mounted.
Database opened.
SQL> show parameter process;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes 		     integer	 0
db_writer_processes		     integer	 1
gcs_server_processes		     integer	 0
job_queue_processes		     integer	 10
log_archive_max_processes	     integer	 2
processes			     integer	 150
           

連接配接不上資料庫的時候，可能會是processes值已經達到了上限，進而修改processes的值，加大即可。