Using oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"

Question

Hello I've got a problem accessing Oracle DB from our datacenter through a tunnel.

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

To create a tunnel I'm using the command:

 ssh -L 1521:192.168.1.7:1521 root@dc

and of course it works (sometimes I also add some debug -vv to see if anything is passing through).

Now the difficult part - connecting to Oracle. I installed instantclient 11.2. and my tnsnames.ora looks like that:

testdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbname)
    )
  )

And when I try to connect using the command:

./sqlplus username/pass@testdb

It starts connecting through the tunnel (I see it in the ssh debug) but then it fails telling:

./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: 

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).

I also tried to use the simple command line:

./sqlplus username/pass@//localhost:1521/testdb

or alternatively

./sqlplus username/pass@//localhost:1521/testdb

But nothing helped :)

I would appreciate any help or suggestions. Am I missing some ssh flag to make it possible?

Probably the log file:

***********************************************************************

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

where velvet is my local hostname and johndoe is my local username. Why is it sent to the other side?

UPDATE:

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

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?

Subtilize

What is the OS of the database server ? If it is Windows, Google USE_SHARED_SOCKET – Kagoshima

Hitting this as well; did you ever find a solution? – Whaler

To 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. – Whaler

My solution so far: re-connect in a loop until it succeeds, because sometimes you do not get redirected. sigh – Whaler

@MartijnPieters - Are you using VM at all? Also, which OS, which version of the database? – Baywood

@Baywood No VM. Connecting from Mac OS X 10.6 to a 10g RAC cluster (4 virtual IP adresses). I did find that simply retrying the connection a couple of times works around the problem. – Whaler

Answer 1

Look into Metalink ID 361284.1 (Edit: effectively not public, but find the info here)

It seems like Oracle Connection Manager would be your option. It basically handles the port redirects inside the firewall. I haven't used it before, so cannot advise you further.

---

Update: Another way to go would be to use MTS, configure dispatchers with certain ports and open these ports in the firewall. You wouldn't have to install additional software for this, but connecting through shared server may require increasing LARGE_POOL_SIZE, among other considerations. So you'd still need the DBA role to change the DISPATCHERS parameter. You'd also have to bounce the DB.

Sliwa

Unfortunately, I do not have the option to add anything on the server side, let alone the firewall. I did read about the Oracle Connection Manager before though, and if you are in a position to set it up, it certainly sounds like a great option to deal with the redirection-on-connect. – Whaler

Answer 2

Normally this should work. I would not use a default listener port as an entry for the ssh tunnel but that should not be the problem. I would also not user the root account to create the ssh connection, preferably a dedicated regular account. Are you using shared servers or does the database happen to be a RAC database with a load balance configuration? A nice explanation is here How can I connect to ORACLE DB through ssh tunnel chain (double tunnel, server in company network) ?, a bit more complicated .....

update checkout DbVisualizer, it now has integrated ssh tunneling. I think it is worth to al least give it a try. It's not free but good. Multi platform and multi database and very flexible.

Domingo

Answer 3

In my case the problem is that the DB server has several IPs and when I used SSH tunnel it was connecting to wrong different one.

So try to check, if the destination IP is the same as the IP in the listener.ora file on the DB server.

Snot

Answer 4

Can you try to make a trace to determine exactly what is happening:

• For server trace, try here (be carefull! all the new request will be traced and the server can be collapsed).
• For client trace, checkout here.

Pregnant

Answer 5

MJ! Your tunnel is only for the initial tcp connect, your own LISTEN port is not tunnelled, and probably unimplemented. Firewall should allow a connect back to you, similar to active FTP. All ports for Oracle are documented quite extensively starting page 670 of "Building Internet Firewalls" 2/E Chapter 23, paragraph: Oracle SQL*Net and Net8. You can view it on SafariBooksOnline.com

ISBN 1565928718

Minette

Answer 6

Perhaps your listener haven't been started yet. Try run "lsnrctrl start" command.

Writhe

No, the listener has definitely started; note the 'sqlplus does connect once every few connection attempts'. – Whaler

Answer 7

Also a good explanation is here connection to an oracle database though a SSH secure shell which worked for me.

1. Open putty and on the session page, enter the name of a server and make sure SSH is checked. The server can be any server that you have a username and password to login with. I use one here called BLUEBIRD as I own it!

2. On the connection->ssh->tunnels page, uncheck both options at the top ("Local ports accept ..." and "Remote ports do the same").

3. Enter 9999 (or any port above 1024 as the Source Port.

4. In the destination, enter the database host and port as per tnsnames. In my case, this is a server called GREENBIRD and a port of

5. Enter this as server:port.

6. As the port being forwarded is on your desktop, check the "Local" option. Leave "Auto" checked as well for the IP version.

7. Click the Add button. You will see L9999 greenbird:1521 (your will differ) in the list of forwarded ports.

8. Go to the session page again, Enter a name for your saved session and click save.

9. Click open. Supply a username and password for the server (BLUEBIRD in my case). You will login a normal ssh session to the server named BLUEBIRD.

Prieto

Off by far. There's no mention of putty anywhere in this question. – Sadfaced