一、情况描述
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也可以正常访问!