小白最近又重回oracle的世界,happy~今天又遇到一ora问题,激动~~自己想了一通后,忙活一会后,阵亡,在网上一搜,才活过来~
1. 问题现象:
数据库监听,实例已开启,sqlplus / as sysdba登陆正常,但是远程登录工具PL/SQL登陆报错ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
2. 问题分析:
远程登录问题只可能涉及到两个监听文件:listener.ora 和 tnsname.ora,其中listener.ora文件内容如下:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = activate.adobe.com)(PORT = 1521))
)
)
3. 问题解决:
在listener.ora中添加如下标红处:
# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = activate.adobe.com)(PORT = 1521))
)
)
4. 问题原因:
监听连接时无法识别SID_NAME对应网络服务命名(即实例名),需要对sid_name进行补充说明。