8 Replies Latest reply: Jan 30, 2013 12:29 PM by sb92075 RSS

    Connecting to remote DB fails even though the listener service is running

    987650
      Hey All,

      We're getting an odd error while trying to connect to our dbserver which is hosted remotely.

      1. We can telnet into port 1521.
      2. Doing a `sqlplus admin/passwd@//address:port/db_name` returns a:
      ORA-12547: TNS:lost contact

      3. sqlplus invites us to log in at that point:
      Enter user-name:

      3a. If you go ahead with it, you get
      Enter password:
      ERROR:
      ORA-12162: TNS:net service name is incorrectly specified
      the listener service
      This just seems kind of weird. We can connect to the db locally. The permissions on bin/oracle seems to be set correctly, and it's probably not the firewall because we can telnet in. We've ruled out network issues.

      Anyway, if anyone could give me an idea of where to look, it'd be greatly appreciated.
        • 1. Re: Connecting to remote DB fails even though the listener service is running
          damorgan
          Can you ping?

          Can you TNSPING?

          Post the TNSNAMES.ORA file on the local client.
          • 2. Re: Connecting to remote DB fails even though the listener service is running
            987650
            We can certainly ping the machine and telnet into it.

            tnsping is inexplicably not installed with sqlplus.

            What worked was when we connected to it from a machine on the same subnet. It's kind of not cool that we can't access it from outside of that subnet.

            There does not appear to be a tnsnames.ora on the local client. I also don't see anything that looks like a config file in $ORACLE_HOME.
            Here's the tnsnames.ora on the remote machine.

            # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
            # Generated by Oracle configuration tools.

            QUINTILES =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.0.18)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = quintiles)
            )
            )
            • 3. Re: Connecting to remote DB fails even though the listener service is running
              sb92075
              984647 wrote:
              Hey All,

              We're getting an odd error while trying to connect to our dbserver which is hosted remotely.

              1. We can telnet into port 1521.
              2. Doing a `sqlplus admin/passwd@//address:port/db_name` returns a:
              ORA-12547: TNS:lost contact
              [oracle@localhost ~]$ oerr ora 12547
              12547, 00000, "TNS:lost contact"
              // *Cause: Partner has unexpectedly gone away, usually during process
              // startup.
              // *Action: Investigate partner application for abnormal termination. On an
              // Interchange, this can happen if the machine is overloaded.
              >
              3. sqlplus invites us to log in at that point:
              Enter user-name:

              3a. If you go ahead with it, you get
              Enter password:
              ERROR:
              ORA-12162: TNS:net service name is incorrectly specified
              12162, 00000, "TNS:net service name is incorrectly specified"
              // *Cause:  The connect descriptor corresponding to the net service name in
              // TNSNAMES.ORA or in the directory server (Oracle Internet Directory) is
              // incorrectly specified.
              // *Action: If using local naming make sure there are no syntax errors in
              // the corresponding connect descriptor in the TNSNAMES.ORA file. If using
              // directory naming check the information provided through the administration
              // used for directory naming.
              the listener service
              This just seems kind of weird. We can connect to the db locally. The permissions on bin/oracle seems to be set correctly, and it's probably not the firewall because we can telnet in. We've ruled out network issues.

              Anyway, if anyone could give me an idea of where to look, it'd be greatly appreciated.
              SQL*Net depends upon OS for any and all packet exchange.
              Oracle does NOT know or care about details like whether subnets exist or not.

              sqlplus user1/pass1@'dbserver:1521/orcl_sid'

              above is EZ-Connect connect string format where no tnsnames file or entry is required
              • 4. Re: Connecting to remote DB fails even though the listener service is running
                987650
                Hmm... which ports does Oracle need? We opened up 1521, and as I said, we can telnet into 1521.
                • 5. Re: Connecting to remote DB fails even though the listener service is running
                  sb92075
                  984647 wrote:
                  Hmm... which ports does Oracle need? We opened up 1521, and as I said, we can telnet into 1521.
                  1521 is necessary but *NOT sufficient.

                  The Fire Wall must be configured to allow DB Server to establish new connection to any client IP# on any port above 1024.
                  • 6. Re: Connecting to remote DB fails even though the listener service is running
                    Helios-GunesEROL
                    Hi;

                    Please answer damorgan returns?
                    Can you ping?
                    Can you TNSPING?

                    Regard
                    Helios
                    • 7. Re: Connecting to remote DB fails even though the listener service is running
                      987650
                      I'm sorry I wasn't clear earlier. I can ping the machine and I can telnet into 1521. I do not have TNSPing on this machine because I only installed the InstantClient here. Is it possible to just get TNSPing somewhere, much like the way I got SQLPlus with the InstantClient?

                      Anyway, I think the answer by sb92075 is probably it. That machine is pretty locked down. I'll post back when we bring the VPN back up and I can make sure.

                      Thanks again,
                      Arthur

                      Edited by: Arthur (Comprehend Systems) on Jan 30, 2013 10:14 AM
                      • 8. Re: Connecting to remote DB fails even though the listener service is running
                        sb92075
                        below is stand alone Java code that uses JDBC to connect to Oracle DB
                        bcm@bcm-laptop:~$ cat Conn.java
                        import java.sql.*;
                        class Conn {
                          public static void main (String[] args) throws Exception
                          {
                           Class.forName ("oracle.jdbc.OracleDriver");
                        
                           Connection conn = DriverManager.getConnection
                             ("jdbc:oracle:thin:@//localhost:1521/v112", "scott", "tiger");
                                                // @//machineName:port/SID,   userid,  password
                           try {
                             Statement stmt = conn.createStatement();
                             try {
                               ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
                               try {
                                 while (rset.next())
                                   System.out.println (rset.getString(1));   // Print col 1
                               } 
                               finally {
                                  try { rset.close(); } catch (Exception ignore) {}
                               }
                             } 
                             finally {
                               try { stmt.close(); } catch (Exception ignore) {}
                             }
                           } 
                           finally {
                             try { conn.close(); } catch (Exception ignore) {}
                           }
                          }
                        }
                        bcm@bcm-laptop:~$ javac Conn.java
                        bcm@bcm-laptop:~$ export CLASSPATH=/u01/app/oracle/product/11.2.0/dbhome_1/owb/wf/lib/ojdbc14.jar:.
                        bcm@bcm-laptop:~$ java Conn
                        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                        PL/SQL Release 11.2.0.1.0 - Production
                        CORE     11.2.0.1.0     Production
                        TNS for Linux: Version 11.2.0.1.0 - Production
                        NLSRTL Version 11.2.0.1.0 - Production
                        bcm@bcm-laptop:~$
                        It only requires access to single jar file; like ojdbc14.jar above.