解决问题: 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)
- )
- )
来源: http://www.linuxidc.com/Linux/2019-07/159379.htm