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
  • 15. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    function &DBselect($query, $limit = null, $offset = 0) {
         global $DB;

         $result = false;

         if (!isset($DB['DB']) || empty($DB['DB'])) {
              return $result;
         }

         if ((isset($limit) && ($limit < 0 || !zbx_ctype_digit($limit))) || $offset < 0 || !zbx_ctype_digit($offset)) {
              $moreDetails = isset($limit) ? ' Limit ['.$limit.'] Offset ['.$offset.']' : ' Offset ['.$offset.']';
              error('Incorrect parameters for limit and/or offset. Query ['.$query.']'.$moreDetails);
              return $result;
         }

         $time_start = microtime(true);
         $DB['SELECT_COUNT']++;

         // Process limit and offset
         if (isset($limit)) {
              switch ($DB['TYPE']) {
                   case ZBX_DB_MYSQL:
                   case ZBX_DB_POSTGRESQL:
                   case ZBX_DB_SQLITE3:
                        $query .= ' LIMIT '.intval($limit).' OFFSET '.intval($offset);
                        break;
                   case ZBX_DB_ORACLE:
                   case ZBX_DB_DB2:
                        $till = $offset + $limit;
                        $query = 'SELECT * FROM ('.$query.') WHERE rownum BETWEEN '.intval($offset).' AND '.intval($till);
                        break;
              }
         }

         switch ($DB['TYPE']) {
              case ZBX_DB_ORACLE:
                   if (!$result = OCIParse($DB['DB'], $query)) {
                        $e = @ocierror();
                        error('SQL error ['.$e['message'].'] in ['.$e['sqltext'].']');
                   }
                   elseif (!@OCIExecute($result, ($DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS))) {
                        $e = ocierror($result);
                        error('SQL error ['.$e['message'].'] in ['.$e['sqltext'].']');
                   }
                   break;
         }

         // $result is false only if an error occured
         if ($DB['TRANSACTION_NO_FAILED_SQLS'] && !$result) {
              $DB['TRANSACTION_NO_FAILED_SQLS'] = false;
         }

         CProfiler::getInstance()->profileSql(microtime(true) - $time_start, $query);
         return $result;
    }
  • 16. Re: ORA-12154 when connecting remotely problem.
    stephen miller Newbie
    Currently Being Moderated
    So if I am understanding this code correctly:
    switch ($DB['TYPE']) {
    case ZBX_DB_ORACLE:
    if (!$result = OCIParse($DB['DB'], $query)) {
    $e = @ocierror();
    error('SQL error ['.$e['message'].'] in ['.$e['sqltext'].']');
    }
    elseif (!@OCIExecute($result, ($DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS))) {
    $e = ocierror($result);
    error('SQL error ['.$e['message'].'] in ['.$e['sqltext'].']');
    }
    break;
    }


    It is saying
    if the database type is oracle then
    if the result of parsing the query is an error display the error.
    elseif the result of executing the query is an error display the error.
    end if

    I think the line

    elseif (!@OCIExecute($result, ($DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS))) {

    should be

    if (!@OCIExecute($result, ($DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS))) {

    That way it parses the statement, then executes the statement.

    Stephen
  • 17. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    It also looks like the functions OCIParse OCIExecute are deprecated. I will look more at the php and contact the product's support. Thank you for your help.
  • 18. Re: ORA-12154 when connecting remotely problem.
    cj Employee ACE
    Currently Being Moderated
    The chapter on testing in http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html notes that "ORA-12516: TNS:listener could not find available handler with matching protocol stack" can be seen when the DB is not configured to handle the connection throughput. See page 249. Even with your DNS issue resolved, I guess you might still see ORA-12516.

    Regarding some other comments in this thread: "//dbhostmachine:1521/zabbix" is equivalent to "dbhostmachine:1521/zabbix" since the leading "//" is optional. Also, since the default port is 1521 then the following is also equivalent: "dbhostmachine/zabbix".

    OCIParse OCIExecute are aliases of the re-named functions and should work fine.

    The use of string concatenation to create $query instead of using bind variables will really impact scalability of the system.

    That Oracle branch that emulates LIMIT by using BETWEEN won't work:
    $query = 'SELECT * FROM ('.$query.') WHERE rownum BETWEEN '.intval($offset).' AND '.intval($till);
    See p 153 of the above book for working equivalent of LIMIT in Oracle syntax.

    That "elseif" syntax in DBselect should be fine (although awkward), however maybe the parse is failing and the execute is not being called. In a test program I can get an ORA-24374 if I do an oci_parse() followed by ocifetchinto(), without an oci_execute().

    The best place to post followup questions is the OTN PHP forum at http://www.oracle.com/technetwork/forums/php/index.html
  • 19. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    cj wrote:
    That Oracle branch that emulates LIMIT by using BETWEEN won't work:
    $query = 'SELECT * FROM ('.$query.') WHERE rownum BETWEEN '.intval($offset).' AND '.intval($till);
    I'm not sure I understand why it won't work.
  • 20. Re: ORA-12154 when connecting remotely problem.
    cj Employee ACE
    Currently Being Moderated
    By example:
    SQL> select * from (select city from locations order by city) where rownum between 1 and 5;
    
    CITY
    ------------------------------
    Beijing
    Bern
    Bombay
    Geneva
    Hiroshima
    
    SQL> select * from (select city from locations order by city) where rownum between 3 and 5;
    
    no rows selected
    
    SQL>
    See the doc for ROWNUM at http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#SQLRF00255
    "Conditions testing for ROWNUM values greater than a positive integer are always false"
  • 21. Re: ORA-12154 when connecting remotely problem.
    techshell81 Newbie
    Currently Being Moderated
    Hi,

    first connect your application server and do tnsping.

    if your not able to connect DB open you tns file and check the entry are correct or not in means oracle sid name and your ip address correctly mentioned in tns file.


    Thanks
  • 22. Re: ORA-12154 when connecting remotely problem.
    axlrose Newbie
    Currently Being Moderated
    maybe,there is the firewall between the client and server side.

    I have a case the client and server were located in different vlan, for example client(192.168.2.22) ,db server(192.168.3.223)..the connection establish fine at first time

    ok,serveral minutes later, when u are trying to do some query...u will meet "ORA-12154"....

    what's your network information between the client and db?
  • 23. Re: ORA-12154 when connecting remotely problem.
    axlrose Newbie
    Currently Being Moderated
    try : sqlplus username/pwd@ip:1521/service_name
  • 24. Re: ORA-12154 when connecting remotely problem.
    EdStevens Guru
    Currently Being Moderated
    970022 wrote:
    Sorry, I'm new to Oracle. But, I thought that tnsnames.ora is only a server-side file,
    Exactly the opposite. tnsnames.ora is used only by client processes. tnsnames.ora is the client's 'telephone directory', giving it (the client process) the details on how to reach a specified target database. tnsnames.ora means nothing to a server. It only exists on servers because servers sometimes also run client processes.



    and even if it were on the client I'm using direct-addressing so I don't see how it would affect anything? I have data successfully persisted in the database so my configuration was working at one point.
  • 25. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    DB and client are on same network, but different machines. Connecting locally with SQLPlus works fine.
  • 26. Re: ORA-12154 when connecting remotely problem.
    sb92075 Guru
    Currently Being Moderated
    970022 wrote:
    DB and client are on same network, but different machines.
    above requires basic OS/networking to be functional.
    Connecting locally with SQLPlus works fine.
    connecting to local DB can succeed WITHOUT any listener or network packets so is 100% not relevant!


    ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
    ORA-12154 NEVER involves the listener, the database itself or anything on the DB Server.
    ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
    TNS-03505 is thrown by tnsping & is same error as ORA-12154 thrown by sqlplus or others.
    The lookup operation fails because the name provided can NOT be resolved to any remote DB.
    The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
    The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
    The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
    TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
    http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
  • 27. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    It appears I am having my issue because client PHP is creating too many connections to the database.

    =====
    SQL> select count(*) from v$session where program like '%httpd%';

    COUNT(*)
    \----------
    100

    SQL> select count(*) from v$session where program like '%zabbix%';

    COUNT(*)
    \----------
    23

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

    COUNT(*)
    \----------
    161
    =====

    I adjusted max sessions and processes and PHP parameters oci8.max_persistent and oci8.persistent_timeout and it appears stable, but still a lot of connections as seen above.
  • 28. Re: ORA-12154 when connecting remotely problem.
    sb92075 Guru
    Currently Being Moderated
    970022 wrote:
    It appears I am having my issue because client PHP is creating too many connections to the database.


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

    COUNT(*)
    \----------
    161
    =====

    I adjusted max sessions and processes and PHP parameters oci8.max_persistent and oci8.persistent_timeout and it appears stable, but still a lot of connections as seen above.
    161 is not a lot!
  • 29. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    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.

Legend

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