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
      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
          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
            Sorry, I don't understand what that means.
            • 3. Re: ORA-12154 when connecting remotely problem.
              Osama_Mustafa
              THAT MEAN CONNECTING TO YOUR database without using tnsnames.ora
              • 4. Re: ORA-12154 when connecting remotely problem.
                973025
                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.
                  millest99
                  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
                    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.
                      millest99
                      What is the result of ping dbhostmachine from the client machine?

                      Stephen
                      • 8. Re: ORA-12154 when connecting remotely problem.
                        millest99
                        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
                          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.
                            millest99
                            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
                              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.
                                millest99
                                Can you post the code that follows the ociplogon?

                                Stephen
                                • 13. Re: ORA-12154 when connecting remotely problem.
                                  973025
                                  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.
                                    millest99
                                    What is the code for the DBselect function?

                                    Stephen
                                    1 2 3 Previous Next