通过 ssh 隧道使用 oracle db。错误“ORA-12541：TNS：无侦听器”
您好，我在通过隧道从我们的数据中心访问 Oracle DB 时遇到问题。
我们有一个非常标准的数据中心，其中一台机器可以从外部访问 （我把它的 IP 作为 dc 放在 /etc/hosts 文件中）和里面的 Oracle DB。我们的oracle数据库在内部网络上的IP地址是192.168.1.7
为了创建一个隧道，我使用以下命令：
ssh -L 1521:192.168.1.7:1521 root@dc
当然它可以工作（有时我还添加一些debug -vv来查看是否有任何东西通过）。
现在是困难的部分 - 连接到 Oracle。我安装了 InstantClient 11.2。我的 tnsnames.ora 看起来像这样：
testdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbname)
)
)
当我尝试使用命令连接时：
./sqlplus username/pass@testdb
它开始通过隧道连接（我在 ssh 调试中看到它），但随后失败 告诉：
./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:
当我尝试在 Intranet 上执行相同的命令时，它可以工作（显然唯一的区别是在 tnsnames.ora HOST 中我们有 192.168.1.7 而不是 localhost）。
我还尝试使用简单的命令行：
./sqlplus username/pass@//localhost:1521/testdb
或者
./sqlplus username/pass@//localhost:1521/testdb
但没有任何帮助:)
我将不胜感激任何帮助或建议。我是否缺少一些 ssh 标志来使其成为可能？
可能是日志文件：
***********************************************************************
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
其中 velvet 是我的本地主机名，johndoe 是我的本地用户名。 为什么会被发送到对方？
更新：
从数据中心内部进行了更多调查后，它看起来像： - 第一个连接将连接到端口 1521 - 但随后 sqlplus 被重定向到端口号 > 3300，每次都不一样并且增加3（至少我尝试过几次） - 当我们尝试通过隧道连接时，sqlplus 将尝试连接到本地主机，但显然会失败
因此错误“无监听器”可能来自于我们没有重定向这些端口。有什么方法（可能是 tnsnames.ora 文件中的某些选项）强制使用某些特定端口？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
查看Metalink ID 361284.1 （编辑：实际上不公开，但请在此处查找信息）
看来 Oracle Connection Manager 将是您的选择。它基本上处理防火墙内的端口重定向。我以前没有使用过它，所以无法给你进一步的建议。
更新：另一种方法是使用 MTS，为调度程序配置某些端口并在防火墙中打开这些端口。您不必为此安装额外的软件，但通过共享服务器连接可能需要增加 LARGE_POOL_SIZE 以及其他考虑因素。因此，您仍然需要 DBA 角色来更改 DISPATCHERS 参数。您还必须退回数据库。
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.
通常这应该有效。我不会使用默认侦听器端口作为 ssh 隧道的条目，但这不应该是问题。我也不会使用 root 帐户来创建 ssh 连接，最好是专用的常规帐户。您使用的是共享服务器还是数据库恰好是具有负载平衡配置的 RAC 数据库？
这里有一个很好的解释 如何通过ssh隧道链（双隧道，服务器在公司网络）连接到ORACLE DB？，稍微复杂一点......
更新
查看 DbVisualizer，它现在集成了 ssh 隧道。我认为至少值得尝试一下。它不是免费的，但是很好。多平台、多数据库，非常灵活。
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.
就我而言，问题是数据库服务器有多个 IP，当我使用 SSH 隧道时，它连接到错误的不同 IP。
因此，请尝试检查目标IP是否与数据库服务器上listener.ora文件中的IP相同。
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.
您能否尝试进行跟踪以确定到底发生了什么：
Can you try to make a trace to determine exactly what is happening:
乔丹！您的隧道仅用于初始 tcp 连接，您自己的 LISTEN 端口未建立隧道，并且可能未实现。防火墙应该允许连接回您，类似于主动 FTP。
Oracle 的所有端口都在“构建 Internet 防火墙”2/E 第 23 章第 670 页开始进行了相当广泛的记录，段落：Oracle SQL*Net 和 Net8。 上查看
您可以在 SafariBooksOnline.com ISBN 1565928718
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
也许你的监听器还没有启动。尝试运行“lsnrctrl start”命令。
Perhaps your listener haven't been started yet. Try run "lsnrctrl start" command.
这里还有一个很好的解释 连接到 Oracle 数据库虽然 SSH 安全 shell 对我有用。
Also a good explanation is here connection to an oracle database though a SSH secure shell which worked for me.