当前位置：theitroad>oracle ORA-12154: TNS: 无法解析指定的连接标识符

oracle ORA-12154: TNS: 无法解析指定的连接标识符

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/2285527/
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

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 02:23:57 　来源:igfitidea点击:

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

oracleoracle11glistenertnsnamesora-12514

提问by user275705

I am trying to connect to oracle 11g installed on Linux EL 5 and and getting the following error

我正在尝试连接到安装在 Linux EL 5 上的 oracle 11g 并收到以下错误

SQL> connect sys/password@ud06 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

my listener.ora under network/admin is as follows

我在network/admin下的listener.ora如下

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=ud06)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orcl)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11g)
      (SID_NAME=orcl))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11g)
      (PROGRAM=extproc)))

MY tnsnames.ora is as follows

我的 tnsnames.ora 如下

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

UD06=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ud06)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

MY lsnrctl status shows as follows:

我的 lsnrctl 状态显示如下：

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ud06.us.server.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                17-FEB-2010 16:23:06
Uptime                    0 days 0 hr. 12 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11g/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/product/11g/log/diag/tnslsnr/ud06/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ud06.us.server.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

采纳答案by DCookie

Can you ping ud06 successfully (as ud06, not ud06.us.server.com)?

你能 ping ud06 成功吗（作为 ud06，而不是 ud06.us.server.com）？

What does the command

命令是什么 

lsnrctl services

show?

展示？

EDIT: It sounds to me like maybe the database instance name isn't actually "orcl"? What the lsnrctl services output tells me is that the "orcl" service, although defined in the listener.ora file, is not actually running.

编辑：在我看来，数据库实例名称实际上不是“orcl”？lsnrctl 服务输出告诉我的是，尽管在 listener.ora 文件中定义了“orcl”服务，但实际上并未运行。

Can you log on with a direct connection on the server? If so, what do you use as the ORACLE_SID environment variable value? Log in as the SYS user and issue the command:

您可以使用服务器上的直接连接登录吗？如果是这样，您使用什么作为 ORACLE_SID 环境变量值？以 SYS 用户身份登录并发出命令：

ALTER SYSTEM REGISTER;

更改系统寄存器；

Then issue the lsnrctl services command again and see if an additional instance doesn't show up.

然后再次发出 lsnrctl services 命令，看看是否有其他实例没有出现。

Also, as Alex points out, the tnsping command is reporting a fully qualified service name. Edit the sqlnet.ora file and set the NAMES.DEFAULT_DOMAIN value to NULL if it has a value.

此外，正如 Alex 指出的那样，tnsping 命令正在报告一个完全限定的服务名称。编辑 sqlnet.ora 文件并将 NAMES.DEFAULT_DOMAIN 值设置为 NULL（如果它有值）。

EDIT 2: Does tnsping ud06 on the serverwork? Or is my assumption that client and server are on different systems wrong?

编辑 2：服务器上的tnsping ud06工作吗？或者我假设客户端和服务器在不同的系统上是错误的？

回答by Arun Thundyill Saseendran

The answer to this problem is very simple. Do not worry about the .ora files or any other configuration. Oracle does all these just perfect.

这个问题的答案非常简单。不要担心 .ora 文件或任何其他配置。Oracle 将所有这些都做到了完美。

Only while connecting via command line, it gets confused with passwords that have a @ symbol in them.

只有在通过命令行连接时，它才会与包含 @ 符号的密码混淆。

Therefore while connecting through command line SQL, do not use a password with a '@' in it. Just use the web interface to create an account having a password without an '@' symbol in it.

因此，在通过命令行 SQL 连接时，不要使用包含“@”的密码。只需使用 Web 界面创建一个帐户，该帐户的密码中不包含“@”符号。

That is!! Problem solved. I had been breaking my head for quite a few days, and now my problem is solved!!

那是！！问题解决了。折腾了好几天，现在问题解决了！！

回答by Alex Poole

The SERVICE_NAMEin the tnspingoutput doesn't match the entry in tnsnames.ora; is that file from the Windows box or the Linux box? It looks like you don't have a local (Windows) tnsnames.oraentry for u06and it's guessing what the service name should be expanding it - I think that's what the reference to the hostname adapter means.

将SERVICE_NAME在tnsping输出不匹配的条目tnsnames.ora; 该文件是来自 Windows 盒还是 Linux 盒？看起来您没有本地（Windows）tnsnames.ora条目，u06并且正在猜测服务名称应该扩展它 - 我认为这就是对主机名适配器的引用的含义。

回答by wdk

I also run into ORA-12154: TNS:could not resolve the connect identifier specified, and adding the user trying to connect to the oinstall group of oracle fixed it.

我还遇到了 ORA-12154: TNS:could not resolve the connect identifier specified, 并添加尝试连接到 oracle 的 oinstall 组的用户修复了它。

回答by ezzaam

first try whether the oracle instance is started :

先试试oracle实例是否启动：

for windows:

对于窗户：

start->configuration panel-> administration touls->Services->ORACLESERVICEORCL(my instance by the way)->start

开始->配置面板->管理touls->服务->ORACLESERVICEORCL（顺便说一下我的实例）->开始

回答by user4618214

Provide the connection string like this:

提供这样的连接字符串：

ServerName:port/ServiceName;User Name;Password

服务器名称：端口/服务名称；用户名；密码

Add connection image

添加连接图像

回答by pradeep kumar

I was also facing the same error, try this code:

我也遇到了同样的错误，试试这个代码：

SQL> conn  hr/hr  @pdborcl;

and if you find same error, it means that you have a different pluggable database name. Check the pluggable database name by just writing the following command in sqlplus

如果您发现相同的错误，则意味着您具有不同的可插入数据库名称。只需在其中写入以下命令即可检查可插拔数据库名称sqlplus

sql> SELECT  name,  con_id  FROM  v$pdbs;

回答by aemre

I fixed this problem using this steps.

我使用此步骤解决了这个问题。

First of all, this error occured , if you didn't install same directory or drive.

首先，如果您没有安装相同的目录或驱动器，则会发生此错误。

But the answer is here.

但答案就在这里。

  1. Login windows as a Adminstrator.
  2. Go to Control Panel.
  3. System Properties and click Enviroment

  4. Find the OS variable and change name as a "TNS_ADMIN"

    enter image description here

  5. And change the value as a "tnsnames's directory address" enter image description here

  6. Restart the system.

  7. Congrulations.
  1. 以管理员身份登录窗口。
  2. 进入控制面板。
  3. 系统属性并单击环境

  4. 找到操作系统变量并将名称更改为“TNS_ADMIN”

    在此处输入图片说明

  5. 并将值更改为“tnsnames 的目录地址” 在此处输入图片说明

  6. 重新启动系统。

  7. 恭喜。

相关推荐

oracle 如何执行oracle存储过程？

oracle 如何执行oracle存储过程？

oracle 如何使用pl/sql循环接受用户输入？

oracle 如何使用pl/sql循环接受用户输入？

在 Oracle Forms 中评估 PL/SQL 布尔变量

在 Oracle Forms 中评估 PL/SQL 布尔变量

Oracle SQL Developer 多表视图

Oracle SQL Developer 多表视图

相关推荐
最近更新
标签