oracle 使用 tns 别名连接时出现 ORA-12154
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13325367/
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
ORA-12154 when connecting using tns alias
提问by Ronald Wildenberg
I installed Oracle 11.2 on a Win7x64 laptop and almost everything works fine. One thing I don't understand, however. I can connect to the sample HR schema with:
我在 Win7x64 笔记本电脑上安装了 Oracle 11.2，几乎一切正常。然而有一件事我不明白。我可以通过以下方式连接到示例 HR 模式：
sqlplus hr/password
but not with:
但不是：
sqlplus hr/password@orcl
I get an ORA-12154: TNS:could not resolve the connect identifier specified.
我得到一个ORA-12154: TNS:could not resolve the connect identifier specified.
I found this out by accident, as I was expecting the second option to be correct. Why can I leave out the TNS alias? I'm an Oracle beginner but when I connect to other environments, the TNS alias is required or I can't make a connection.
我偶然发现了这一点，因为我期待第二个选项是正确的。为什么我可以省略 TNS 别名？我是 Oracle 初学者，但是当我连接到其他环境时，需要 TNS 别名，否则我无法建立连接。
I have the following in my tnsnames.ora:
我有以下内容tnsnames.ora：
LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA = (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))
(CONNECT_DATA = (SID = CLRExtProc)(PRESENTATION = RO)))
ORCL, DUMMY = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl)))
I know for sure it's being used because both tnsping orcland tnsping dummyare ok. Can someone explain this to me?
我知道肯定它是被使用，因为两者tnsping orcl并tnsping dummy都ok。谁可以给我解释一下这个？
UPDATE 1: The output of lsnrctl statusis the following:
更新1：输出lsnrctl status如下：
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 11-NOV-2012 00:43:22
Uptime 0 days 0 hr. 15 min. 53 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\Ronald\product.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\ronald\diag\tnslsnr\Ronald-PDC\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
The result of SELECT name FROM v$databaseis one database: ORCL.
结果SELECT name FROM v$database是一个数据库：ORCL.
UPDATE 2: Output of tnsping orcl(formatted for readability).
更新 2：输出tnsping orcl（为可读性而格式化）。
Used parameter files:
C:\app\Ronald\product.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)
采纳答案by DCookie
Did you actually create a database instance named ORCL?
你真的创建了一个名为 ORCL 的数据库实例吗？
Check out the actual name of your database with the command
使用命令查看数据库的实际名称
lsnrctl status
This should tell you what services the listener knows about, which is what you're talking to when connecting with the @ syntax. The reason you can connect without the @ORCL is that in that case you're using interprocess communication (IPC) and bypassing the listener, because the database is on the same computer as your SQL*Plus client session.
这应该会告诉您侦听器知道哪些服务，这就是您在使用 @ 语法连接时正在谈论的内容。您可以在不使用 @ORCL 的情况下进行连接的原因是，在这种情况下，您正在使用进程间通信 (IPC) 并绕过侦听器，因为数据库与 SQL*Plus 客户端会话位于同一台计算机上。
You can also find out the database name when you connect in the first example with this:
在第一个示例中连接时，您还可以找到数据库名称：
SELECT name FROM v$database;
EDIT:
编辑：
Try the EZConnect syntax and see if that works:
尝试 EZConnect 语法，看看是否有效：
sqlplus user/pw@localhost/ORCL
Keep in mind that TNSPINGdoesn't do anything besides contact the listener - it doesn't verify the database can be accessed.
请记住，TNSPING除了联系侦听器之外，它不会做任何事情——它不会验证可以访问数据库。
One more thing: It looks like maybe TCP isn't a configured protocol for the listener? check your listener.ora file (in the same directory as your tnsnames.ora file). The entry for LISTENER should look something like this:
还有一件事：看起来 TCP 可能不是为侦听器配置的协议？检查您的 listener.ora 文件（与您的 tnsnames.ora 文件在同一目录中）。LISTENER 的条目应如下所示：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
My TNSPINGoutput looks a bit different in one line near the start of output:
我的TNSPING输出在输出开始附近的一行中看起来有点不同：
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))
