This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Nov 12, 2012 3:52 PM by cj Go to original post RSS
  • 30. Re: ORA-12154 when connecting remotely problem.
    sb92075 Guru
    Currently Being Moderated
    970022 wrote:
    Client is using direct addressing, so it doesn't even have tnsnames.ora. In fact I have already gone through that guide!

    I believe I am having my issue because of max process/session reached on the database because I was able to get ORA-12154 25% of the time when connecting locally with SQLPlus.
    Forgive me, but I don't accept your view of reality.
    A different error would be thrown for too many sessions.
    PHP requires Oracle client software to be present & uses OCI Thick protocol to connect to remote DB.
  • 31. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    Oops I meant ORA-12516. I would get intermittent errors such as :

    ociplogon(): ORA-12516: TNS:listener could not find available handler with matching protocol stack

    After adjusting some settings I am no longer having an issue, but it remains to be seen if the increased limits of Oracle will be reached again.


    EDIT: An example locally:
    SQL> connect xxx/xxx@localhost/zabbix;
    Connected.
    SQL> connect xxx/xxx@localhost/zabbix;
    Connected.
    SQL> connect xxx/xxx@localhost/zabbix;
    ERROR:
    ORA-12516: TNS:listener could not find available handler with matching protocol
    stack

    Edited by: 970022 on Nov 12, 2012 8:07 AM
  • 32. Re: ORA-12154 when connecting remotely problem.
    sb92075 Guru
    Currently Being Moderated
    970022 wrote:
    Oops I meant ORA-12516. I would get intermittent errors such as :

    ociplogon(): ORA-12516: TNS:listener could not find available handler with matching protocol stack

    After adjusting some settings I am no longer having an issue, but it remains to be seen if the increased limits of Oracle will be reached again.


    EDIT: An example locally:
    SQL> connect xxx/xxx@localhost/zabbix;
    Connected.
    SQL> connect xxx/xxx@localhost/zabbix;
    Connected.
    SQL> connect xxx/xxx@localhost/zabbix;
    ERROR:
    ORA-12516: TNS:listener could not find available handler with matching protocol
    stack

    Edited by: 970022 on Nov 12, 2012 8:07 AM
    above can result from the DB "running out of available session/process slots".
    This can result when application starts a new session for every single SQL issued.
    Starting with V10, Oracle attempted to increase the capability of the listener.
    It did this by having the listener query the DB to learn how many sessions the DB has & can handle.
    Then as listener make new connection requests, listener increments a counter showing how many sessions exist within the DB.
    When MAX sessions is reached, listener reports error to any client asking for a connection.
    Once every minute when DB registers itself with the listener, the listener obtains a current session count;
    which results in new connections request succeeding until the MAX session limit is reached again, again.

    If the application used connection pooling, then these errors are avoided.
  • 33. Re: ORA-12154 when connecting remotely problem.
    cj Employee ACE
    Currently Being Moderated
    The best solution for reducing connection load with PHP & Oracle DB is to use "Database Resident Connection Pooling" (DRCP). This is also recommended in general for PHP/Oracle web applications. DRCP is trivial to configure and use. See the comprehensive whitepaper http://www.oracle.com/technetwork/topics/php/whatsnew/php-scalability-ha-twp-128842.pdf The benchmark in that paper is impressive; it effectively had 20,000 active users sharing 100 connections.
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points