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连接之后报错：

[oracle@kel ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Thu May 16 00:06:17 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected.SQL> show parameter sessionORA-01012: not logged onSQL> shutdown immediateORA-24324: service handle not initializedORA-24323: value not allowedORA-00020: maximum number of processes (%s) exceeded

alterlog中报错：

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

1、无法登陆数据库的情况下使用以下方法：

a、找到数据库的关键进程，然后杀死，此时数据库实例会自动进行关闭

[oracle@kel ~]$ ps -ef|grep dbworacle   16107     1  0 00:04 ?        00:00:00 ora_dbw0_orcloracle   16755 15946  0 00:19 pts/0    00:00:00 grep dbw[oracle@kel ~]$ kill -9 16107

2、公用的方法，当能登陆进数据库之后

b、sqlplus登录进数据库，启动数据库

[oracle@kel ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Thu May 16 00:20:11 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  176160768 bytesFixed Size    2019384 bytesVariable Size  100667336 bytesDatabase Buffers   71303168 bytesRedo Buffers    2170880 bytesDatabase mounted.Database opened.

c、查看系统，发现process的值已经接近上限，修改processes的值

SQL> show parameter processNAME     TYPE VALUE------------------------------------ ----------- ------------------------------aq_tm_processes      integer 0db_writer_processes     integer 1gcs_server_processes     integer 0job_queue_processes     integer 10log_archive_max_processes     integer 2processes     integer 20

SQL> alter system set processes=1150 scope=spfile;System altered.

d、重新启动oracle数据库

SQL> startup forceORACLE instance started.Total System Global Area  176160768 bytesFixed Size    2019384 bytesVariable Size  100667336 bytesDatabase Buffers   71303168 bytesRedo Buffers    2170880 bytesDatabase mounted.Database opened.SQL> show parameter process;NAME     TYPE VALUE------------------------------------ ----------- ------------------------------aq_tm_processes      integer 0db_writer_processes     integer 1gcs_server_processes     integer 0job_queue_processes     integer 10log_archive_max_processes     integer 2processes     integer 150

连接不上数据库的时候，可能会是processes值已经达到了上限，从而修改processes的值，加大即可。