This discussion is archived
8 Replies Latest reply: Jan 30, 2013 10:29 AM by sb92075 RSS

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

987650 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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