This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Nov 12, 2012 3:52 PM by cj RSS

ORA-12154 when connecting remotely problem.

973025 Newbie
Currently Being Moderated
I am running Oracle 11g on Windows Server 2008R2. I have a client application(Zabbix) running elsewhere that is trying to connect to it.

The client machine has:

* oracle-instantclient11.2-basic-11.2.0.3.0-1.i386.rpm
* oracle-instantclient11.2-devel-11.2.0.3.0-1.i386.rpm

And as such, it does not have sqlnet.ora or tnsnames.ora files. It is attempting to establish a connection with direct addressing. With a print statement in the php, I see that it is calling
h4. ociplogon(user,password,$connect);

with connect being set to //dbhostmachine:1521/zabbix. So everything looks good there.

#####################
C:\Users\mdobrini> lsnrctl services

LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 02-NOV-2012 13:00:55

Copyright (c) 1991, 2008, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhostmachine.dddd.cccc.com)(POR
Services Summary...
Service "zabbix" has 1 instance(s).
Instance "zabbix", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:578 refused:0 state:ready
LOCAL SERVER
Service "zabbixXDB" has 1 instance(s).
Instance "zabbix", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: dbhostmachine, pid: 6384>
(ADDRESS=(PROTOCOL=tcp)(HOST=dbhostmachine.dddd.cccc.com)(PORT=61396))
Service "zabbix_XPT" has 1 instance(s).
Instance "zabbix", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:578 refused:0 state:ready
LOCAL SERVER
The command completed successfully
#######################

Attempting to connect locally works fine:

#########################
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Nov 2 13:06:31 2012

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Enter user-name: SYSTEM
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> connect uuu/ppp@localhost/zabbix
Connected.
SQL>
#########################

However, remotely I get the popular error:

ociplogon(): ORA-12154: TNS:could not resolve the connect identifier specified

The strange thing is that it was working intermittently before. I would get an error about every 10 seconds in my application:

30923:20121101:142237.595 [Z3001] connection to database '//dbhostmachine:1521/zabbix' failed: [-1] ORA-12516: TNS:listener could not find available handler with matching protocol stack
30923:20121101:142237.596 watchdog: database is down

but there was definitely data being sent across the network, and persisted in the db. After restarting application and server I am no longer able to get a connection at all and am stuck on ORA-12154. Any help would be appreciated.
  • 1. Re: ORA-12154 when connecting remotely problem.
    sb92075 Guru
    Currently Being Moderated
    970022 wrote:
    I am running Oracle 11g on Windows Server 2008R2. I have a client application(Zabbix) running elsewhere that is trying to connect to it.

    The client machine has:

    * oracle-instantclient11.2-basic-11.2.0.3.0-1.i386.rpm
    * oracle-instantclient11.2-devel-11.2.0.3.0-1.i386.rpm

    And as such, it does not have sqlnet.ora or tnsnames.ora files. It is attempting to establish a connection with direct addressing. With a print statement in the php, I see that it is calling
    h4. ociplogon(user,password,$connect);

    with connect being set to //dbhostmachine:1521/zabbix. So everything looks good there.

    #####################
    C:\Users\mdobrini> lsnrctl services

    LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 02-NOV-2012 13:00:55

    Copyright (c) 1991, 2008, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhostmachine.dddd.cccc.com)(POR
    Services Summary...
    Service "zabbix" has 1 instance(s).
    Instance "zabbix", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:578 refused:0 state:ready
    LOCAL SERVER
    Service "zabbixXDB" has 1 instance(s).
    Instance "zabbix", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER <machine: dbhostmachine, pid: 6384>
    (ADDRESS=(PROTOCOL=tcp)(HOST=dbhostmachine.dddd.cccc.com)(PORT=61396))
    Service "zabbix_XPT" has 1 instance(s).
    Instance "zabbix", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:578 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully
    #######################

    Attempting to connect locally works fine:

    #########################
    SQL*Plus: Release 11.1.0.7.0 - Production on Fri Nov 2 13:06:31 2012

    Copyright (c) 1982, 2008, Oracle. All rights reserved.

    Enter user-name: SYSTEM
    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

    SQL> connect uuu/ppp@localhost/zabbix
    Connected.
    SQL>
    #########################

    However, remotely I get the popular error:

    ociplogon(): ORA-12154: TNS:could not resolve the connect identifier specified

    The strange thing is that it was working intermittently before. I would get an error about every 10 seconds in my application:

    30923:20121101:142237.595 [Z3001] connection to database '//dbhostmachine:1521/zabbix' failed: [-1]
    not as above but as below
    'dbhostmachine:1521/zabbix'

    ORA-12516: TNS:listener could not find available handler with matching protocol stack
    30923:20121101:142237.596 watchdog: database is down

    but there was definitely data being sent across the network, and persisted in the db. After restarting application and server I am no longer able to get a connection at all and am stuck on ORA-12154. Any help would be appreciated.
  • 2. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    Sorry, I don't understand what that means.
  • 3. Re: ORA-12154 when connecting remotely problem.
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    THAT MEAN CONNECTING TO YOUR database without using tnsnames.ora
  • 4. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    Sorry, I'm new to Oracle. But, I thought that tnsnames.ora is only a server-side file, 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.
  • 5. Re: ORA-12154 when connecting remotely problem.
    stephen miller Newbie
    Currently Being Moderated
    I think what sb92075 is trying to say is: Can you change the connection information from: //dbhostmachine:1521/zabbix to dbhostmachine:1521/zabbix.

    Stephen
  • 6. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    No luck, I am still getting ORA-12154 if I don't have '//'. Connecting locally works with // though:

    ########
    PS C:\Users\mdobrini> sqlplus uuu/ppp@//dbhostmachine:1521/zabbix

    SQL*Plus: Release 11.1.0.7.0 - Production on Wed Nov 7 13:40:39 2012

    Copyright (c) 1982, 2008, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    ########
  • 7. Re: ORA-12154 when connecting remotely problem.
    stephen miller Newbie
    Currently Being Moderated
    What is the result of ping dbhostmachine from the client machine?

    Stephen
  • 8. Re: ORA-12154 when connecting remotely problem.
    stephen miller Newbie
    Currently Being Moderated
    Additionally, I noticed in the connect information you have port 1521, but the listener has a dispatcher listening on a different port. If you try that port does it work? If not check that the port the listener is on is the same port the client is using in the connect.

    Stephen
  • 9. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    Hmm,

    It appears to be a dns issue, because I can't ping the name from the client. However, providing an IP instead I get another error:

    ocifetchinto():ORA-24374L define not done before fetch or execute and fetch
  • 10. Re: ORA-12154 when connecting remotely problem.
    stephen miller Newbie
    Currently Being Moderated
    And this same code used to work before?

    This new issue looks like you are trying to fetch some values from a select statment and have not used the OCIDefine functions to define the local variables to fetch into.

    Stephen
  • 11. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    Stephen,

    Yes, This is the same code. I am getting the error on the login screen of the client application!
  • 12. Re: ORA-12154 when connecting remotely problem.
    stephen miller Newbie
    Currently Being Moderated
    Can you post the code that follows the ociplogon?

    Stephen
  • 13. Re: ORA-12154 when connecting remotely problem.
    973025 Newbie
    Currently Being Moderated
    It looks like this is the call trace:

    #index.php:
    h3. $config = select_config();
    V
    V
    V

    function select_config($cache = true, $nodeid = null) {
         global $page, $ZBX_LOCALNODEID;
         static $config;

         if ($cache && isset($config)) {
              return $config;
         }
         if (is_null($nodeid)) {
              $nodeid = $ZBX_LOCALNODEID;
         }
    h4.      $db_config = DBfetch(DBselect('SELECT c.* FROM config c WHERE '.DBin_node('c.configid', $nodeid)));
         if (!empty($db_config)) {
              $config = $db_config;
              return $db_config;
         }
         elseif (isset($page['title']) && $page['title'] != _('Installation')) {
              error(_('Unable to select configuration.'));
         }
         return $db_config;
    }


    V
    V
    V

    function DBfetch(&$cursor) {
         global $DB;

         $result = false;

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

         switch ($DB['TYPE']) {

              case ZBX_DB_ORACLE:
    h4.                if (ocifetchinto($cursor, $row, (OCI_ASSOC+OCI_RETURN_NULLS))) {
                        $result = array();
                        foreach ($row as $key => $value) {
                             $field_type = zbx_strtolower(oci_field_type($cursor, $key));
                             $value = (str_in_array($field_type, array('varchar', 'varchar2', 'blob', 'clob')) && is_null($value)) ? '' : $value;

                             if (is_object($value) && (zbx_stristr($field_type, 'lob') !== false)) {
                                  $value = $value->load();
                             }
                             $result[zbx_strtolower($key)] = $value;
                        }
                   }
                   break;
         if ($result) {
              foreach ($result as $key => $val) {
                   if (is_null($val)) {
                        $result[$key] = '0';
                   }
              }
         }
         return $result;
    }

    Edited by: 970022 on Nov 7, 2012 1:37 PM

    Edited by: 970022 on Nov 7, 2012 1:44 PM
  • 14. Re: ORA-12154 when connecting remotely problem.
    stephen miller Newbie
    Currently Being Moderated
    What is the code for the DBselect function?

    Stephen
1 2 3 Previous Next

Legend

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