ORA-12514 TNS:listener 当前不知道连接描述符中请求的服务

2021-12-05 00:00:00 oracle11g oracle sqlplus ora-12514 tnsping

我们有一个在本地运行的应用程序,但遇到以下错误:

We have an application running locally where we're experiencing the following error:

ORA-12514: TNS:listener 当前不知道请求的服务在连接描述符中

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

我已经使用 TNSPing 测试了连接,该连接正确解析并且我尝试了 SQLPlus 尝试连接,但失败并出现与上述相同的错误.我在 SQLPlus 中使用了这个语法:

I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try connecting, which failed with the same error as above. I used this syntax for SQLPlus:

sqlplus username/password@addressname[or host name]

我们已经证实:

  • 服务器上的 TNS 侦听器正在运行.
  • 服务器上的 Oracle 本身正在运行.

我们不知道对此环境进行了任何更改.还有什么我们可以测试的吗?

We don't know of any changes that were made to this environment. Anything else we can test?

推荐答案

我遇到了这个问题,解决方法是确保 tnsnames.ora 中的 SERVICE_NAME 是一个数据库中的有效服务名称.要找出有效的服务名称,您可以在 oracle 中使用以下查询:

I had this issue and the fix was to make sure in tnsnames.ora the SERVICE_NAME is a valid service name in your database. To find out valid service names, you can use the following query in oracle:

select value from v$parameter where name='service_names'

一旦我将 tnsnames.ora 更新为:

TEST =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = *<validhost>*)(PORT = *<validport>*))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = *<servicenamefromDB>*)
    )
)

然后我跑了:

sqlplus user@TEST

成功!侦听器基本上是在告诉您,根据数据库,您使用的任何 service_name 都不是有效的服务.

Success! The listener is basically telling you that whatever service_name you are using isn't a valid service according to the DB.

(*我从 Win7 客户端工作站运行 sqlplus 到远程数据库并责怪 DBA ;) *)

(*I was running sqlplus from Win7 client workstation to remote DB and blame the DBAs ;) *)

相关文章