通过 ssh 隧道使用 oracle db。错误“ORA-12541：TNS：无侦听器”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2059795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using oracle db through ssh tunnel. Error "ORA-12541: TNS:no listener"
提问by Jakub Troszok
Hello I've got a problem accessing Oracle DB from our datacenter through a tunnel.
您好，我在通过隧道从我们的数据中心访问 Oracle DB 时遇到问题。
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
我们有一个非常标准的数据中心，其中一台机器可以从外部访问（我将它的 IP 作为 dc 放在 /etc/hosts 文件中）和内部的 Oracle DB。我们内网oracle数据库的IP地址是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).
当然它可以工作（有时我还会添加一些 debug -vv 以查看是否有任何内容通过）。
Now the difficult part - connecting to Oracle. I installed instantclient 11.2. and my tnsnames.ora looks like that:
现在是困难的部分 - 连接到 Oracle。我安装了 Instantclient 11.2。我的 tnsnames.ora 看起来像这样：
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:
它开始通过隧道连接（我在 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:
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).
当我尝试在 Intranet 上执行相同的命令时，它可以工作（显然唯一的区别是在 tnsnames.ora HOST 中我们有 192.168.1.7 而不是 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?
我将不胜感激任何帮助或建议。我是否缺少一些 ssh 标志使其成为可能？
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?
其中 velvet 是我的本地主机名，johndoe 是我的本地用户名。为什么要发给对方？
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
在从数据中心内部进行了更多调查后，它看起来像： - 第一个连接将连接到端口 1521 - 但随后 sqlplus 被重定向到端口号 > 3300，每次都不同，并以 3 递增（至少尝试几次我有） - 当我们尝试通过隧道连接时，sqlplus 将尝试连接到本地主机，但显然会失败
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?
所以错误“No Listener”可能来自我们没有重定向这些端口的事实。有没有办法（可能是 tnsnames.ora 文件中的某些选项）强制使用某些特定端口？
采纳答案by HAL 9000
Look into Metalink ID 361284.1(Edit: effectively not public, but find the info here)
查看 Metalink ID 361284.1（编辑：实际上不是公开的，但可以在此处找到信息）
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.
看起来 Oracle Connection Manager 将是您的选择。它主要处理防火墙内的端口重定向。我以前没有用过，所以不能给你进一步的建议。
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.
更新：另一种方法是使用 MTS，使用某些端口配置调度程序并在防火墙中打开这些端口。您不必为此安装额外的软件，但通过共享服务器连接可能需要增加 LARGE_POOL_SIZE 以及其他注意事项。因此，您仍然需要 DBA 角色来更改 DISPATCHERS 参数。你还必须反弹数据库。
回答by ik_zelf
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 .....
通常这应该有效。我不会使用默认侦听器端口作为 ssh 隧道的条目，但这应该不是问题。我也不会使用 root 帐户来创建 ssh 连接，最好是专用的常规帐户。您使用的是共享服务器还是数据库恰好是具有负载平衡配置的 RAC 数据库？一个很好的解释在这里如何通过 ssh 隧道链（双隧道，公司网络中的服务器）连接到 ORACLE DB？，有点复杂……
updatecheckout 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.
更新结帐DbVisualizer，它现在集成了 ssh 隧道。我认为至少值得一试。它不是免费的，但很好。多平台和多数据库，非常灵活。
回答by Robert Sibek
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.
就我而言，问题是数据库服务器有多个 IP，当我使用 SSH 隧道时，它连接到了错误的不同 IP。
So try to check, if the destination IP is the same as the IP in the listener.ora file on the DB server.
所以尝试检查目标IP是否与DB服务器上listener.ora文件中的IP相同。
回答by bbaassssiiee
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
MJ！您的隧道仅用于初始 tcp 连接，您自己的 LISTEN 端口未隧道化，并且可能未实现。防火墙应该允许连接回您，类似于活动的 FTP。从“构建 Internet 防火墙”2/E 第 23 章第 670 页的第 670 页开始，Oracle SQL*Net 和 Net8 的所有端口都有相当广泛的记录。您可以在SafariBooksOnline.com上查看
ISBN 1565928718
ISBN 1565928718
回答by FerranB
回答by lepike
Perhaps your listener haven't been started yet. Try run "lsnrctrl start" command.
也许你的听众还没有开始。尝试运行“ lsnrctrl start”命令。
回答by i.van
Also a good explanation is here connection to an oracle database though a SSH secure shellwhich worked for me.
另外一个很好的解释是通过对我有用的 SSH 安全外壳连接到 oracle 数据库。
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!
On the connection->ssh->tunnels page, uncheck both options at the top ("Local ports accept ..." and "Remote ports do the same").
Enter 9999 (or any port above 1024 as the Source Port.
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
Enter this as server:port.
As the port being forwarded is on your desktop, check the "Local" option. Leave "Auto" checked as well for the IP version.
Click the Add button. You will see L9999 greenbird:1521 (your will differ) in the list of forwarded ports.
Go to the session page again, Enter a name for your saved session and click save.
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.
打开 putty 并在会话页面上，输入服务器的名称并确保选中 SSH。服务器可以是您有用户名和密码登录的任何服务器。我在这里使用一个叫做 BLUEBIRD 的，因为我拥有它！
在 connection->ssh->tunnels 页面上，取消选中顶部的两个选项（“本地端口接受...”和“远程端口也一样”）。
输入 9999（或 1024 以上的任何端口作为源端口。
在目标中，根据 tnsnames 输入数据库主机和端口。就我而言，这是一个名为 GREENBIRD 的服务器和一个端口
将其输入为 server:port。
由于要转发的端口在您的桌面上，请选中“本地”选项。对于 IP 版本，也选中“自动”。
单击添加按钮。您将在转发端口列表中看到 L9999 greenbird:1521（您会有所不同）。
再次转到会话页面，为您保存的会话输入一个名称，然后单击保存。
点击打开。提供服务器的用户名和密码（在我的例子中为 BLUEBIRD）。您将登录到名为 BLUEBIRD 的服务器的正常 ssh 会话。
