oracle sqlplus怎么连不上？

Question

提问by Ethan

My goal is to connect to an Oracle 9i instance from my OS X machine. I've followed the setup instructions hereand got through them with no errors (eventually). However, I'm finding that sqlplus is unable to connect:

我的目标是从我的 OS X 机器连接到 Oracle 9i 实例。我已经按照此处的设置说明进行操作，并且没有出现错误（最终）。但是，我发现 sqlplus 无法连接：

[ ethan@gir ~ ]$ sqlplus xxx/yyy@zzz

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 17 10:13:08 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Looooong wait...

呜呜呜等...

ERROR:
ORA-12170: TNS:Connect timeout occurred

Enter user-name: xxx
Enter password: 
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

My tnsnames.orafile...

我的tnsnames.ora档案...

zzz =
  (DESCRIPTION = 
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = dbhost)
        (PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVICE_NAME = zzz)
  )
)

Maybe there's an env variable that needs to be set?

也许有一个需要设置的环境变量？

UPDATE

更新

Able to ping DB host machine no problem.

能够ping通数据库主机没问题。

Tried...

试过...

sqlplus xxx/yyy@//dbhost/zzz

Got...

得到了...

ERROR:
ORA-12170: TNS:Connect timeout occurred

Tried using SIDinstead of SERVICE_NAMEin tnsnames.ora. Did not seem to change the result. Reverted back to SERVICE_NAME.

尝试在 tnsnames.ora 中使用SID而不是SERVICE_NAME。似乎没有改变结果。恢复到SERVICE_NAME.

Last couple entries in sqlnet.log...

sqlnet.log 中的最后几个条目...

***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for MacOS X Server: Version 10.2.0.4.0 - Production
    TCP/IP NT Protocol Adapter for MacOS X Server: Version 10.2.0.4.0 - Production
  Time: 17-APR-2009 10:33:06
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: Message 12535 not found; No message file for product=network, facility=TNS
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Message 505 not found; No message file for product=network, facility=TNS
    nt secondary err code: 60
    nt OS err code: 0


***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for MacOS X Server: Version 10.2.0.4.0 - Production
    TCP/IP NT Protocol Adapter for MacOS X Server: Version 10.2.0.4.0 - Production
  Time: 17-APR-2009 11:24:08
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: Message 12535 not found; No message file for product=network, facility=TNS
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Message 505 not found; No message file for product=network, facility=TNS
    nt secondary err code: 60
    nt OS err code: 0

PARTIAL ANSWER

部分答案

Thanks everyone for your answers. They were helpful. I found that there was a DNS issue. I was able to ping by hostname, so thought that should work fine. I also tried I.P. address. Turned out that I needed the internal"10.1.x.x" I.P. address for it to work on this OS X machine (but hostname is fine on Windows).

谢谢大家的回答。他们很有帮助。我发现有一个DNS问题。我能够通过主机名 ping，所以认为应该可以正常工作。我也试过IP地址。原来我需要内部“10.1.xx”IP地址才能在这台OS X机器上工作（但主机名在Windows上很好）。

At this point, I can connect with...

此时，我可以连接...

sqlplus xxx/yyy@//INTERNAL_IP/zzz

However, with those values entered into tnsnames.ora, this still doesn't work...

但是，将这些值输入到 tnsnames.ora 后，这仍然不起作用......

sqlplus xxx/yyy@zzz

...

ORA-12154: TNS:could not resolve the connect identifier specified

I searched for a sample tnsnames.ora file that was close to what I needed and copied the contents into my file. Changed the params and now everything works. Not sure why mine wasn't working.

我搜索了一个接近我需要的样本 tnsnames.ora 文件，并将内容复制到我的文件中。更改了参数，现在一切正常。不知道为什么我的不工作。

Answer 1

回答by Quassnoi

Since you are using a 10gclient, it's advisable to use Easy Connectsyntax instead:

由于您使用的是10g客户端，因此建议改用Easy Connect语法：

export TWO_TASK=//dbhost/zzz
sqlplus xxx/yyy

, or just this:

，或者只是这个：

sqlplus 'xxx/yyy@//dnhost/zzz'

Also check your ORACLE_HOMEpoints to the right folder: tnsnames.orais searched for in $ORACLE_HOME/network/admin/tnsnames.ora

还要检查您ORACLE_HOME指向正确文件夹的点：tnsnames.ora在$ORACLE_HOME/network/admin/tnsnames.ora

Answer 2

回答by aintnoprophet

Your brackets seem correct.

你的括号似乎是正确的。

Try using the SID:

尝试使用 SID：

The following is an example of a tnsnames.ora file:

以下是 tnsnames.ora 文件的示例：

IDENTIFIER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = userid.myhosteddb.net)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = odb))
  )

Read about SID here.

在此处阅读有关 SID 的信息。

Answer 3

回答by Chars

You can use

您可以使用

sqlplus user/password@servicename_host

if you cannot connect you can use

如果您无法连接，您可以使用

sqlplus user/password@(DESCRIPTION=((ADDRESS=(PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME = zzz)))

if you are using linux another *Nix OS you need to use quotes else the () are interpreted by the shell

如果您使用的是 linux 另一个 *Nix 操作系统，则需要使用引号，否则 () 由 shell 解释

e.g

例如

sqlplus user/password@'(DESCRIPTION=((ADDRESS=(PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME = zzz)))'

Answer 4

回答by Alan Thompson

I had a similar problem and it seems that Oracle sqlplus was the problem!

我有一个类似的问题，似乎是 Oracle sqlplus 的问题！

Connecting like either of these works:

像以下任一工作方式连接：

   > sqlplus MyUsername/MyPassword@MyHostname:1521/MyServiceName
   > sqlplus MyUsername/MyPassword@//MyHostname:1521/MyServiceName

(the // is optional before the hostname). However, leaving off password or both user/pass fails like this:

（// 在主机名之前是可选的）。但是，不使用密码或用户/密码都失败，如下所示：

    sqlplus @//MyHostname:1521/MyServiceName

    SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 2 15:59:49 2015

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

    SP2-0310: unable to open file "//MyHostname:1521/MyServiceName.sql"
    Enter user-name: MyUsername
    Enter password: MyPassword
    ERROR: 
    ORA-12162: TNS:net service name is incorrectly specified

So, even though sqlplus asks you for the username/password, it will stupidly fail with a bogus error message if you type them in at the prompt. It only works if you place them (both!) at the beginning of the connection string.

因此，即使 sqlplus 要求您输入用户名/密码，如果您在提示符下输入它们，它也会愚蠢地失败并显示虚假的错误消息。仅当您将它们（两者都！）放在连接字符串的开头时才有效。

Stupid Oracle!!!

愚蠢的甲骨文！！！

Answer 5

回答by Dave Costa

There is probably a sqlnet.log file being generated in your working directory. This may help you or if you post its contents it might give us more information.

您的工作目录中可能正在生成一个 sqlnet.log 文件。这可能会对您有所帮助，或者如果您发布其内容，它可能会为我们提供更多信息。

In your example, you're trying two different things. On the command line you used "xxx/yyy@zzz". It looks like this is finding the "zzz" entry in tnsnames.ora successfully, but the timeout indicates that it is getting no response whatsoever from the server. Can you ping dbhost successfully?

在您的示例中，您正在尝试两种不同的方法。在命令行上，您使用了“xxx/yyy@zzz”。看起来这是在 tnsnames.ora 中成功找到“zzz”条目，但超时表明它没有从服务器获得任何响应。你能ping通dbhost吗？

On your second try you just entered "xxx" for the username; which makes sense if you are no accustomed to SQLPlus, but as you can see there is no point where it prompts you for the database name. So in this case it was trying to connect to "xxx/yyy" without a service name, leading to the second error. This just means you don't have a default service name set up. So this error comes from incomplete input. (You would enter "xxx@zzz" for the username to specify the service name at this prompt. You can actually enter the whole connect string "xxx/yyy@zzz" at the username prompt, if you don't mind the password being visible.)

在您第二次尝试时，您刚刚输入了“xxx”作为用户名；如果您不习惯 SQLPlus，这是有道理的，但正如您所见，它没有提示您输入数据库名称的地方。所以在这种情况下，它试图连接到没有服务名称的“xxx/yyy”，导致第二个错误。这只是意味着您没有设置默认服务名称。所以这个错误来自不完整的输入。（您将在用户名中输入“xxx@zzz”以在此提示下指定服务名称。您实际上可以在用户名提示下输入整个连接字符串“xxx/yyy@zzz”，如果您不介意密码为可见的。）

Answer 6

回答by trent

Have you tried using telnet to get to the open port to make sure a firewall isn't blocking you? may be worth a try telnet port-num host

您是否尝试过使用 telnet 访问开放端口以确保防火墙没有阻止您？可能值得一试 telnet port-num host

Answer 7

回答by Jon Ericson

Did you set up your environment with the oraenvscript?

您是否使用oraenv脚本设置了环境？

Have you tried tnsping?

你试过tnsping吗？

$ tnsping $ORACLE_SID

Perhaps it will help to compare the output on a machine that does connect to the output on a machine that fails to connect. At least that's what I do just before contacting our DBA.

也许将连接到的机器上的输出与连接失败的机器上的输出进行比较会有所帮助。至少这就是我在联系我们的 DBA 之前所做的。

You can get a bit more detail on what the error with the oerrcommand:

您可以获得有关oerr命令错误的更多详细信息：

$ oerr ora 12170
12170, 00000, "TNS:Connect timeout occurred"
// *Cause:  The server shut down because connection establishment or
// communication with a client failed to complete within the allotted time
// interval. This may be a result of network or system delays; or this may
// indicate that a malicious client is trying to cause a Denial of Service
// attack on the server.
// *Action: If the error occurred because of a slow network or system,
// reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,
// SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values.
// If a malicious client is suspected, use the address in sqlnet.log to
// identify the source and restrict access. Note that logged addresses may
// not be reliable as they can be forged (e.g. in TCP/IP).

Answer 8

回答by SAGAR MANOCHA

FOLLOW THISLINK FOR STEP BY STEP INSTALLATION AND POST INSTALLATION METHODS.. BE CAREFULL WITH IT.

按照此链接逐步安装和安装后方法.. 小心使用它。

And to connect with sql plus:

并与 sql plus 连接：

USERNAME WiLL BE system AS PER THE INSTRUCTION GIVEN IN ABOVE LINK
your password MUST NOT BE tiger but the one which you set at the beginning of the installation

用户名将按照上述链接中的说明进行系统设置
您的密码不能是tiger，而是您在安装开始时设置的密码

P.S: dont panic if you find problem (browser incompatibility) connecting Oracle Enterprise Manager while testing installation if your browser is Google Chrome. hit the back page arrow and next page arrow to agree terms and clicking ok.

PS：如果您在测试安装时发现问题（浏览器不兼容）连接 Oracle Enterprise Manager，请不要惊慌，如果您的浏览器是 Google Chrome。点击后页箭头和下一页箭头以同意条款并单击确定。

I resolved this problem on my machine in a day.. but it will be a matter of couple of hors for you.

我在一天之内就在我的机器上解决了这个问题..但这对你来说是件难事。

soruces: i am computer science engineer mostly code in java

来源：我是计算机科学工程师，主要用 Java 编写代码

Answer 9

回答by Mr_Moneybags

I had the same error (ORA-12162: TNS:net service name is incorrectly specified) but a different reason (On Windows 7 Enterprise 64-bit). Hope this helps someone:

我有同样的错误（ORA-12162：TNS:net 服务名称指定不正确）但有不同的原因（在 Windows 7 Enterprise 64 位上）。希望这有助于某人：

I.T. at my work installed 32bit and 64bit Oracle, and based on my PATH variable, the shell looked in the 64-bit path for SQLPLUS.exe compared to the 32-bit path.

我工作中的 IT 安装了 32 位和 64 位 Oracle，并根据我的 PATH 变量，与 32 位路径相比，shell 在 SQLPLUS.exe 的 64 位路径中查找。

The different paths use different TNSNAMES.ora files and I didn't have my connection string in the 64-bit path:

不同的路径使用不同的 TNSNAMES.ora 文件，我在 64 位路径中没有连接字符串：

Oracle\product\11.1.0\client_1_64bit\network\admin\TNSNAMES.ora

I only had the connection string in the 32-bit TNSNAMES.ora: Oracle\product\11.1.0\client_1_32bit\network\admin\TNSNAMES.ora

我在 32 位 TNSNAMES.ora 中只有连接字符串：Oracle\product\11.1.0\client_1_32bit\network\admin\TNSNAMES.ora

Also, since there were multiple ORACLE installations I had to remove the ORACLE_HOME environment variable so that both installs can use different home directories.

此外，由于有多个 ORACLE 安装，我必须删除 ORACLE_HOME 环境变量，以便两个安装都可以使用不同的主目录。

oracle sqlplus怎么连不上？

提问by Ethan

回答by Quassnoi

回答by aintnoprophet

回答by Chars

回答by Alan Thompson

回答by Dave Costa

回答by trent

回答by Jon Ericson

回答by SAGAR MANOCHA

回答by Mr_Moneybags

相关推荐

最近更新

标签

oracle sqlplus怎么连不上？

提问by Ethan

回答by Quassnoi

回答by aintnoprophet

回答by Chars

回答by Alan Thompson

回答by Dave Costa

回答by trent

回答by Jon Ericson

回答by SAGAR MANOCHA

回答by Mr_Moneybags

相关推荐

可以将 oracle 11g 客户端与 10g 服务器一起使用吗？

是否可以在不终止会话的情况下终止 oracle 中的单个查询？

oracle SQL Plus 更改当前目录

如何在 Oracle 数据库的 HQL 查询中使用当前日期？

相关推荐

最近更新

标签