Ошибка tns 12541 tns no listener

[{«Product»:{«code»:»SS9S6B»,»label»:»IBM Cognos Controller»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Controller»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»8.2″,»Edition»:»All Editions»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

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?

when i was trying to connect my remote server as-

sqlplus SYSTEM/oracle@192.168.0.171:1521/kannel

it shows me this error-

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 15 15:36:52 2013
copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

I have checked my listener, it is running

[root@roracle bin]# ./lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-NOV-2013 15:42:24

 Copyright (c) 1991, 2011, Oracle.  All rights reserved.

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Linux Error: 111: Connection refused
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
 TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Linux Error: 111: Connection refused

tnsnames.ora file contain —

KANNEL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = kannel)
     )
 )

listener.ora file contain

SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
   (GLOBAL_DBNAME = kannel)
   (ORACLE_HOME = /opt/u01/app/product/11.2.0/dbhome_1)
   (SID_NAME = kannel)
 )
)

LISTENER=
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   )
  )
 )

sqlnet.ora file contain-

SQLNET.AUTHENTICATION_SERVICES= (ALL)

NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)

Oracle home path —

$ORACLE_HOME=/opt/u01/app/product/11.2.0/dbname_1

Oracle base path —

$ORACLE_BASE=/opt/u01/app

i have set environment variable

[root@roracle bin]# env | sort

}
_=/bin/env
CVS_RSH=ssh
G_BROKEN_FILENAMES=1
HISTCONTROL=ignoredups
HISTSIZE=1000
HOME=/root
HOSTNAME=roracle
LANG=en_IN
LESSOPEN=|/usr/bin/lesspipe.sh %s
LOADEDMODULES=
LOGNAME=root
LS_COLORS=rs=<......something........>:
MAIL=/var/spool/mail/root
module=() {  eval `/usr/bin/modulecmd bash $*`
MODULEPATH=/usr/share/Modules/modulefiles:/etc/modulefiles
MODULESHOME=/usr/share/Modules
OLDPWD=/root
ORACLE_BASE=/opt/u01/app/
ORACLE_HOME=/opt/u01/app/product/11.2.0/dbhome_1
ORACLE_SID=kannel
PATH=/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
PWD=/opt/u01/app/product/11.2.0/dbhome_1/bin
QTDIR=/usr/lib64/qt-3.3
QTINC=/usr/lib64/qt-3.3/include
QTLIB=/usr/lib64/qt-3.3/lib
SHELL=/bin/bash
SHLVL=1
SSH_CLIENT=192.168.0.83 35604 22
SSH_CONNECTION=192.168.0.83 35604 192.168.0.171 22
SSH_TTY=/dev/pts/0
TERM=xterm
USER=root

When i access database as a localhost —

./sqlplus / as sysdba[oracle@roracle bin]$ ./sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 16:04:02 2013

 Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> select v$database;
 select v$database
 *
 ERROR at line 1:
 ORA-01034: ORACLE not available
 Process ID: 0
 Session ID: 0 Serial number: 0

I Don’t understand where i am mistaking…..Can somebody help?