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

1.login into oracle user
# su - oracle
$ sqlplus /nolog
SQL> conn /as sysdba
Connected.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on 星期五 12月 9 09:43:12 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

2.test the connection
$ sqlplus /nolog
SQL> conn sys/oracle@orcl as sysdba
ERROR:
ORA-12516: TNS:listener could not find available handler with matching protocol
stack

SQL> conn hr/hr@orcl
ERROR:
ORA-12516: TNS:listener could not find available handler with matching protocol
stack

3.Check the listener.ora and tnsnames.ora, they are OK.

$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin
$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PL***tProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    )
  )

$

$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

4.查看一下数据库现有的进程数，是否已经达到参数processes的大小。

   -- select count(*) from v$process;  取得数据库目前的进程数。
   -- select value from v$parameter where name = 'processes'; 取得进程数的上限

(1)查看当前会话数、processes和sessions值，发现session数14和processes值150已经非常接近
SQL> select count(*) from v$session;

  COUNT(*)
----------
       146

SQL> show parameter processes

NAME                                 TYPE                   VALUE
------------------------------------ ------------------------------


aq_tm_processes                      integer                0

db_writer_processes                  integer                2

gcs_server_processes                 integer                0

job_queue_processes                  integer                10

log_archive_max_processes            integer                2

processes                            integer                150

(2) Modify the two parameter(processes,sessions)

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

System altered.

SQL> alter system set sessions=335 scope=spfile;

System altered.

(3) Let's shutdown and startup database in order to let the two parameter taken effect.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup        
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2030456 bytes
Variable Size             989856904 bytes
Database Buffers          603979776 bytes
Redo Buffers               14745600 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
$ sqlplus /nolog

(4) test the result.

SQL*Plus: Release 10.2.0.3.0 - Production on 星期五 12月 9 09:53:13 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn sys/oracle@orcl as sysdba
Connected.
SQL>

--the end.