Monday, June 11, 2012

ORA-12516: TNS:listener could not find available handler

after the increasing the Number processes in the Oracle database, it solved the problem.

But Before this , i am gonna show you some steps to check the process via OS and Database LETS BEGIN :

  • check if the number of connections reaches the database’s  process parameter using following Unix command:
ps -ef | grep oracleSID | grep -v grep | wc -l
or
ps aux | grep oracleSID | grep -v grep | wc -l
  •  check if the number of connections reaches the database’s  process parameter using following Database Command :
SQL> show parameter processes

NAME                TYPE        VALUE
------------------ ----------- -------
processes           integer     150

SQL> select count(*) from v$process;

  COUNT(*)
----------
       149

SQL> show parameter sessions

NAME                TYPE        VALUE
------------------ ----------- -------
Session              integer     150

SQL> select count(*) from v$session;

  COUNT(*)
----------
       149

Now We need To Increase the Both Parameter By :

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

System altered.
 
SQL> alter system set Session=300 scope=spfile;

System altered. 


If this Solution Not Work For you , Try this One :

SQL> alter system set local_listener=
"(ADDRESS=(PROTOCOL=tcp)(HOST=10.122.28.12)(PORT=1521))" sid='ORCL1';

System altered.

SQL> show parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=tcp)(HOST=1
                                                 0.122.28.12)(PORT=1521))
Thank you 
Osama Mustafa

11 comments:

  1. Database should be restarted after the modification of processes parameter.

    ReplyDelete
  2. alter system set Session=300 scope=spfile;
    "sessions" not session

    ReplyDelete
  3. what is the significance of local_listener in this case ? plz explain

    Thanks in advance. :)

    ReplyDelete
    Replies
    1. local listener to help manage connections across all database instances, If you set the parameter to null then the default local address of TCP/IP, port 1521 is assumed But in our we used this way or you set Listener Name

      Delete
  4. it really worked well, thanks

    ReplyDelete
  5. ORA-12516: TNS:listener could not find available handler with matching protocol stack ;

    connString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.108)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));user id=db_fc1;password=db_fc1;"

    أحيانا يحدث الخطأ أعلاه مع العلم أنني أستخدم في جهازي
    oracle10g
    والاصدار المحمل على السرفر هو
    oracle 11g

    ReplyDelete
  6. How I am supposed to run these commands ? please urgent help needed....

    ReplyDelete
    Replies
    1. Using oracle 10g express edition

      Delete