问题背景
Oracle跑在linux虚拟机上,虚拟机用户名:oralce,Windows上安装Oracle客户端,使用PLSQL进行远程连接。
PLSQL登录报错:【PLSQL登录/ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
使用Windows客户端的SQL plus进行登录报错:ORA-12560: TNS: 协议适配器错误
配置信息
项目 | Value |
---|---|
虚拟机用户名 | oralce |
虚拟机IP | 192.168.137.154 |
虚拟机OS | Linux |
物理机IP | 192.168.137.1 |
物理机OS | Windows |
服务名 | trade |
问题解决
看了两天网上的解决方法,归根到底是服务和监听的配置问题,我直接把我成功的配置展示,供大家参考
虚拟机上的配置
tnsnames.ora
TRADE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.154)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trade)
)
)
// A code block
listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.154)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Windows物理机配置
tnsnames.ora
# tnsnames.ora Network Configuration File: D:\oracleapp\Administrator\product\11.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
TRADE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.154)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = trade)
)
)
listener.ora
# listener.ora Network Configuration File: D:\oracleapp\Administrator\product\11.2.0\client_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.154)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = D:\oracleapp\Administrator
总结
多用lsnrctl status命令查看监听状态
lsnrctl status
------------------------
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for Linux: Version 11.2.0.4.0
- Production启动日期 15-4月 -2024 13:37:20
正常运行时间 0 天 0 小时 2 分 21 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件 /u01/app/oracle/product/11.2.0/db/net
work/admin/listener.ora监听程序日志文件 /u01/app/oracle/diag/tnslsnr/jiangyub
o/listener/alert/log.xml监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.154)(PO
RT=1521)))服务摘要..
服务 "trade" 包含 2 个实例。
实例 "trade", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
实例 "trade", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功