一、情况描述
oracle数据可以远程登录，但是创建到远程数据库dblink，出现了ORA-12514错误
二、问题原因
oracle服务器缺少本地IP的监听设置，可以通过修改配置文件，也可以通过数据库提供的 Net Manager进行配置。
1、 TNS配置两个地址：一个是 localhost 一个是自己的本地IP
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 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.105.213.133)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORA10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.105.3.30)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)
)
|
2、监听配置地址 一个IPC协议 一个localhost 一个本地IP
listener.ora文件内容：
|
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\HUAWEI\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\HUAWEI\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.105.213.133)(PORT = 1522))
)
)
ADR_BASE_LISTENER = D:\app\HUAWEI
|
三、注意问题（至少本人碰到了这样的问题，如有不对，请加以指正，谢谢）
有时候配置数据库之后，明明设置了 localhost 的tns名称和监听，为什么依然ORA-12541TNS无监听程序，甚至本地都无法连接，查看一下本地 hosts文件是如何配置 。 C:\Windows\System32\drivers\etc 下 hosts文件，若放开了 127.0.0.1 和 localhost 关系，
，上面配置的 tns和监听再用 localhost是不起作用的，依然会报 无监听程序 。把上面的配置中 localhost改为127.0.0.1 ，重启oracle服务，本地可以连接，dblink也可以正常访问！
