Databases
StackOverflow2013
Postsdbo
POUsing oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
    primarykey
    data
    text
    <p>Hello I've got a problem accessing Oracle DB from our datacenter through a tunnel.</p> <p>We've got a pretty standard datacenter with one machine being accessible from the outside (I put it's IP in the /etc/hosts file as dc) and the Oracle DB inside. The IP address of our oracle database on internal network is 192.168.1.7</p> <p>To create a tunnel I'm using the command:</p> <pre><code> ssh -L 1521:192.168.1.7:1521 root@dc </code></pre> <p>and of course it works (sometimes I also add some debug -vv to see if anything is passing through).</p> <p>Now the difficult part - connecting to Oracle. I installed instantclient 11.2. and my tnsnames.ora looks like that:</p> <pre><code>testdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dbname) ) ) </code></pre> <p>And when I try to connect using the command:</p> <pre><code>./sqlplus username/pass@testdb </code></pre> <p>It starts connecting through the tunnel (I see it in the ssh debug) but then it fails telling:</p> <pre><code>./sqlplus username/pass@testdb SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 13 20:46:07 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-12541: TNS:no listener Enter user-name: </code></pre> <p>When I'm trying to execute this same command on when I'm on the intranet it works (obviously the only difference is that in the tnsnames.ora HOST we have 192.168.1.7 and not the localhost).</p> <p>I also tried to use the simple command line:</p> <pre><code>./sqlplus username/pass@//localhost:1521/testdb </code></pre> <p>or alternatively</p> <pre><code>./sqlplus username/pass@//localhost:1521/testdb </code></pre> <p>But nothing helped :)</p> <p>I would appreciate any help or suggestions. Am I missing some ssh flag to make it possible?</p> <p>Probably the log file:</p> <pre><code>*********************************************************************** Fatal NI connect error 12541, connecting to: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DBNAME)(CID=(PROGRAM=sqlplus@velvet)(HOST=velvet)(USER=johndoe)))) VERSION INFORMATION: TNS for Linux: Version 11.2.0.1.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production Time: 13-JAN-2010 20:48:42 Tracing not turned on. Tns error struct: ns main err code: 12541 TNS-12541: Message 12541 not found; No message file for product=network, facility=TNS ns secondary err code: 12560 nt main err code: 511 TNS-00511: Message 511 not found; No message file for product=network, facility=TNS nt secondary err code: 111 nt OS err code: 0 </code></pre> <p>where velvet is my local hostname and johndoe is my local username. Why is it sent to the other side?</p> <p><strong>UPDATE:</strong></p> <p>After investigating a little bit more from inside datacenter and it looks like: - the first connection is going to the port 1521 - but then sqlplus is redirected to the port number > 3300, which is different everytime and incrementing by 3 (at least few tries I had) - when we are trying to connect trough a tunnel sqlplus will try to connect to localhost and it will obviously fail</p> <p>So the error "No Listener" comes probably from the fact that we are not redirecting those ports. Is there any way (probably some option in tnsnames.ora file) to force some specific port to be used?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAPC
    1. USJakub Troszok
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUsing oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat is the OS of the database server ? If it is Windows, Google USE_SHARED_SOCKET
      singulars
      1. POUsing oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
      1. USGary Myers
    2. COHitting this as well; did you ever find a solution?
      singulars
      1. POUsing oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
      1. USMartijn Pieters
    3. COTo clarify: I am connecting to a Oracle RAC, and I am not in a position to reconfigure the listener on the other side (such as setting it to use a shared socket). sqlplus does connect once every few connection attempts, my application never manages to connect.
      singulars
      1. POUsing oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
      1. USMartijn Pieters
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload