共计 3559 个字符，预计需要花费 9 分钟才能阅读完成。
解决问题：ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务
测试环境：RedHat7.4 + Oracle 11g 64 位
相关说明：
数据库服务器：
Oracle11g 64 位软件的安装位置为 /u01/app/oracle/product/11.2.0/dbhome_1，数据库名为默认的 orcl,
RedHat7.4 虚拟机的 IP 设置为：192.168.8.13
Windows10 客户端：
1、PLSQL 安装位置：E:\Program Files\PLSQL Developer
2、Oracle 客户端位置：E:\app
打开 E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora 文件
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 虚拟机 IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
))
3. 添加一个环境变量，名为 TNS_ADMIN，值为 tnsnames.ora 文件所在路径 E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN，plsql 通过这个找到 orcl 连接字符串
4. 添加一个环境变量 NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
遇到问题：
使用 plsql 连接数据库服务器：“ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务”
解决办法简述：
1. 修改 E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora 中的 localhost 改为 192.168.8.13；
2. 修改 E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora 中的 localhost 改为 192.168.8.13；
3. 修改后，重启 oracle，监听，并注册，主 win10 上的 plsql 就可以远程连接虚拟机上的数据库了。
具体操作步骤如下：
一、修改数据库服务器中 listener.ora 文件内容
命令:
# vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
原始内容:
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
修改后的内容如下：
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
或者 LISTENER 这里改的简单一些，改用 IP 地址也行
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.13)(PORT = 1521)))
二、修改数据库服务器中 tnsnames.ora 文件内容
命令:
# vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
原始内容:
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
修改后的内容：
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.13)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
三、在数据库服务器中启动监听并更新注册
最后重启一下监听与数据库并更新注册
$ lsnrctl stop #先关闭监听服务
$ lsnrctl start #开启监听服务
$ sqlplus / as sysdba #登入
SQL> shutdown immediate #立即关闭数据库服务
SQL> startup #开启数据库服务
命令：SQL> alter system register; #注册
System altered.
SQL>
SQL> quit #登出
输出：
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
四、Windows10 客户端下的测试步骤：使用 plsqldev.exe 测试，
修改 E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora 文件，新增到虚拟机 Oracle 的连接内容:
# tnsnames.ora Network Configuration File:E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.13)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
NH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.19)(PORT = 1525))
)
(CONNECT_DATA =
(SID = nh)
)
)
更多 Oracle 相关信息见Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12
：
