database 测试数据库链接 - ORA-12154: TNS: 无法解析指定的连接标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8532757/
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
Testing DB Link - ORA-12154: TNS:could not resolve the connect identifier specified
提问by m.edmondson
I know there is a load of times this question has been asked but I can't quite figure out how to apply this information to my situation.
我知道这个问题被问到了很多次，但我无法弄清楚如何将这些信息应用于我的情况。
I have two databases of which I'm trying to connect with a DB LINK. These are:
我有两个数据库，我正试图用 DB LINK 连接其中的两个数据库。这些是：
- BBEGMTD1
- OPEGMTP1.WORLD
- BBEGMTD1
- OPEGMTP1.WORLD
A DB link has already been defined by someone, with the name PE_DBLINK.WORLD.
数据库链接已由某人定义，名为 PE_DBLINK.WORLD。
I can access bothof these databases from my machine, so I can't see how tnsnames.ora would be affected.
我可以从我的机器访问这两个数据库，所以我看不到 tnsnames.ora 会受到什么影响。
However when I test the DB LINK I get the famous:
但是，当我测试 DB LINK 时，我得到了著名的：
Link : "PE_DBLINK.WORLD" Error : ORA-12154: TNS:could not resolve the connect identifier specified
链接：“PE_DBLINK.WORLD”错误：ORA-12154：TNS：无法解析指定的连接标识符
Could you please point me in the right direction, does the tnsnames.ora need to be modified on the BBEGMTD1 server?
能否请您指出正确的方向，是否需要在 BBEGMTD1 服务器上修改 tnsnames.ora？
As per @Chance comment, select * from ALL_DB_LINKSreturns:
根据@Chance 评论，select * from ALL_DB_LINKS返回：
PUBLIC
PE_DBLINK.WORLD
PRICING
OPEGMTP1.WORLD
03-NOV-11
PUBLIC
EBPROJ.WORLD
EBPROJ
MIDGMTP1
17-JUN-09
MYOPEGMTP1WORLD.WORLD
PRICING
(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_D
ATA= (SERVER=dedicated) (SERVICE_NAME=OPEGMTT1) ) ) )
16-DEC-11
This is the TNSNames Editor in TOAD to show the details are being shown up:
这是 TOAD 中的 TNSNames 编辑器，用于显示正在显示的详细信息：
回答by chance
Run this to check the host of the DBLink PE_DBLINK.WORLD:
select * from ALL_DB_LINKS
Check your tnsnames.ora fro the HOST (i.e. OPEGMTP1.WORLD ) of that DBLink. Which is located at (your locale machine)
%ORACLE_HOME%\network\admin\tnsnames.ora
If not found in your tnsnames.ora, then add it like this:
OPEGMTP1.WORLD = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=OPEGMTT1) ) )
Or create your own DBLink:
CREATE DATABASE LINK MYOPEGMTP1.WORLD CONNECT TO user IDENTIFIED BY password USING '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=OPEGMTT1) ) ) )'
运行此命令以检查 DBLink PE_DBLINK.WORLD 的主机：
从 ALL_DB_LINKS 中选择 *
从该 DBLink 的 HOST（即 OPEGMTP1.WORLD）检查您的 tnsnames.ora。哪个位于（您的语言环境机器）
%ORACLE_HOME%\network\admin\tnsnames.ora
如果在你的 tnsnames.ora 中没有找到，那么添加它：
OPEGMTP1.WORLD = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=OPEGMTT1) ) )
或者创建自己的 DBLink：
CREATE DATABASE LINK MYOPEGMTP1.WORLD CONNECT TO user IDENTIFIED BY password USING '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME)PEG) ) )'
回答by Karl
Check on the source DB host for valid TNS entries. The DB Link is created from RDBMS Host to RDBMS Host, your PC is irrelevant.
在源数据库主机上检查有效的 TNS 条目。DB Link 是从 RDBMS 主机到 RDBMS 主机创建的，与您的 PC 无关。
回答by Davi
It worked fine for me without any additional changes to tnsnames.ora file:
它对我来说很好，没有对 tnsnames.ora 文件进行任何额外的更改：
CREATE DATABASE LINK <link> CONNECT TO <user> IDENTIFIED BY <password> USING
'(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = <port>))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <service_name>)
)
)';
回答by JUSTIN RAJU
If you face any issue related to ORA-12514.
如果您遇到任何与 ORA-12514 相关的问题。
Scenario: customer is trying to connect to server A from server B using DB link then he gets ORA-12514 error. if the server A doesn't not have the SID_LIST_LISTENER entry in the listener of server A ( this generally happens after upgrade) then he might get the error.
场景：客户尝试使用 DB 链接从服务器 B 连接到服务器 A，然后他收到 ORA-12514 错误。如果服务器 A 在服务器 A 的侦听器中没有 SID_LIST_LISTENER 条目（这通常发生在升级后），那么他可能会收到错误消息。
Error Code : ORA-12514: TNS: listener does not currently know of service requested in connect descriptor
错误代码：ORA-12514：TNS：侦听器当前不知道连接描述符中请求的服务
Resolution : Add the SID_LIST_LISTENER entry in target Listener ( of server A)
解决方案：在目标侦听器（服务器 A）中添加 SID_LIST_LISTENER 条目
Note # most of you must be aware.
注意#你们中的大多数人必须知道。
回答by user1617237
If it's a private DB link, you need to login as the DB Link Owner to test.
如果是私有数据库链接，则需要以数据库链接所有者身份登录进行测试。
回答by user5582933
db_link are from db server to db server, not from client to server; so please make sure your both database servers have correct tns entries for service.
db_link 是从数据库服务器到数据库服务器，而不是从客户端到服务器；所以请确保您的两个数据库服务器都有正确的 tns 服务条目。
tnsnames.ora on Database server should have tns entries for service.
数据库服务器上的 tnsnames.ora 应该有服务的 tns 条目。
