✦

Technology Geek

18+ YEARS AMONG THE CLOUDS

✦ TRANSMISSION

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 responses to “ORA-12516: TNS:listener could not find available handler”

  1. Anonymous

    Database should be restarted after the modification of processes parameter.

    Like

    Reply
  2. Osama Mustafa

    Depend on the Scope Memory or Spfile

    Like

    Reply
  3. Anonymous

    alter system set Session=300 scope=spfile;”sessions” not session

    Like

    Reply
  4. Chetan

    what is the significance of local_listener in this case ? plz explain Thanks in advance. 🙂

    Like

    Reply
  5. Osama Mustafa

    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

    Like

    Reply
  6. Anonymous

    it really worked well, thanks

    Like

    Reply
  7. محمد النعيمي

    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

    Like

    Reply
  8. Osama Mustafa

    define “أحيانا”

    Like

    Reply
  9. Anonymous

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

    Like

    Reply
  10. Anonymous

    Using oracle 10g express edition

    Like

    Reply
  11. waleed khalid

    thank you mr.osama

    Like

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.