解决windows7⽆法连接CentOS7系统中oracle问题：ORA-
12514TNS。。。
linux开启后终端按下⾯输⼊(容易忘记，记录下)；
[oracle@localhost ~]$ lsnrctl stop #先关闭监听服务
[oracle@localhost ~]$ lsnrctl start #开启监听服务
[oracle@localhost ~]$ sqlplus / as sysdba #登⼊
SQL> shutdown immediate #⽴即关闭数据库服务（⼀般是关闭状态）
SQL> startup #开启数据库服务（重要，必输）
SQL> exit #退出
SQL> sqlplus scott/tiger@192.168.78.130/orcl #连接scott⽤户
SQL> select * from tab; #test查询表
解决问题：ORA-12514 TNS 监听程序当前⽆法识别连接描述符中请求服务
环境：CentOS-7 + Oracle 11g 64位
相关说明：
数据库服务器：
Oracle11g64位软件的安装位置为data/oracle/product/11.2.0/db_1，名为默认的orcl,
CentOS7虚拟机的IP设置为：192.168.78.130
Windows7客户端：
1、PLSQL安装位置：C:\Program Files\PLSQL Developer
InstantClient存放位置：C:\Program Files\PLSQL Developer\instantclient_11_2，并创建⽂件network，创建⽂件夹admin；
2.在C:\Program Files\PLSQL Developer\instantclient_11_2\network\admin新建a,⽤记事本编辑.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 虚拟机IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
))
3.添加⼀个环境变量，名为TNS_ADMIN，值为a⽂件所在路径C:\Program Files\PLSQL Developer\instantclient_11_2\network\admin，plsql通过这个到orcl连接字符串
4.添加⼀个环境变量NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
5.打开PLSQL，不⽤登陆,⼯具-⾸选项-ORACLE-连接:
勾选检查连接
Oracle主⽬录： C:\Program Files\PLSQL Developer\instantclient_11_2
安装plsqlOCI库：C:\Program Files\PLSQL Developer\instantclient_11_2\network\admin\oci.dll
遇到问题：
使⽤plsql连接数据库服务器：“ORA-12514 TNS 监听程序当前⽆法识别连接描述符中请求服务”
解决办法简述：
1.修改\data\oracle\product\11.
2.0\db_1\network\a中的localhost改为192.168.78.130
2.修改\data\oracle\product\11.2.0\db_1\network\a中的localhost 改为 192.168.78.130
3.修改后，重启oracle，监听，并注册，主win7上的plsql就可以远程连接虚拟机上的数据库了.
具体操作步骤如下：
⼀、修改数据库服务器中a⽂件内容
命令:
[oracle@localhost admin]$ vi /data/oracle/product/11.2.0/db_1/network/a
原始内容:
# lis te ne r.o ra Ne two rk Co nfiguratio n File: /data/o rac le/pro duc t/11.2.0/db_1/ne two rk/admin/lis te ne r.o ra
# Ge ne rate d by Orac le c o nfiguratio n to o ls.
LIS TENER =
(D ES CRIPTION_LIS T =
(D ES CRIPTION =
(AD D RES S = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(AD D RES S = (PROTOCOL = TCP)(HOS T = lo c alho s t)(PORT = 1521))
)
)
AD R_BAS E_LIS TENER = /data/o rac le
修改为：
# a Network Configuration File: /data/oracle/product/11.2.0/db_1/network/a
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.130)(PORT = 1521))) ADR_BASE_LISTENER = /data/oracle
⼆、修改数据库服务器中a⽂件内容
命令:
[oracle@localhost admin]$ vi /data/oracle/product/11.2.0/db_1/network/a
原始内容:
# a Network Configuration File: /data/oracle/product/11.2.0/db_1/network/adm in/a
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
修改后的内容：
# a Network Configuration File: /data/oracle/product/11.2.0/db_1/network/adm in/a
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.130)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
三、在数据库服务器中启动监听并更新注册
1、命令：
[oracle@localhost ~]$ lsnrctl stop #先关闭监听服务
输出：
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 19-FEB-2017 21:19:42
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.130)(PORT=1521))) The com m and com pleted successfully
[oracle@localhost ~]$
2、命令：
[oracle@localhost ~]$ lsnrctl start #开启监听服务
输出:
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 19-FEB-2017 21:24:22
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /data/oracle/product/11.2.0/db_1/bin/tnslsnr:
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System param eter file is /data/oracle/product/11.2.0/db_1/network/adm a
Log m essages written to /data/oracle/diag/tnslsnr/localhost/listener/ l
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.130)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.130)(PORT=1521))) STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 19-FEB-2017 21:24:22
Uptim e 0 days 0 hr. 0 m in. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Param eter File /data/oracle/product/11.2.0/db_1/network/adm a
Listener Log File /data/oracle/diag/tnslsnr/localhost/listener/ l
Listening Endpoints Sum
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.130)(PORT=1521)))
Services Sum
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for
The com m and com pleted successfully
[oracle@localhost ~]$
3、命令：
[oracle@localhost ~]$ sqlplus / as sysdba #登⼊
输出：
SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 19 21:27:40 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
命令：
SQL> shutdown immediate #⽴即关闭数据库服务
输出：
Database closed.
Database dism ounted.
ORACLE instance shut down.
SQL>
命令:
SQL> startup #开启数据库服务
输出：
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2213736 bytes
Variable Size 939526296 bytes
Database Buffers 637534208 bytes
Redo Buffers 7434240 bytes
Database m ounted.
Database opened.
SQL>
命令：
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
[oracle@localhost ~]$
命令：
[oracle@localhost ~]$ lsnrctl status #查看监听状态
输出：
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 19-FEB-2017 21:37:20
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.130)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 19-FEB-2017 21:24:22
Uptim e 0 days 0 hr. 12 m in. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Param eter File /data/oracle/product/11.2.0/db_1/network/adm a
Listener Log File /data/oracle/diag/tnslsnr/localhost/listener/ l
Listening Endpoints Sum
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.130)(PORT=1521)))
Services Sum
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for
The com m and com pleted successfully
[oracle@localhost ~]$
四、Windows7客户端下的测试步骤：使⽤测试，修改
C:\Program Files\PLSQL Developer\instantclient_11_2\network\admin \a
⽂件，新增到虚拟机Oracle的连接内容
# a Network Configuration File: /data/oracle/product/11.2.0/db_1/network/adm in/a # Generated by Oracle configuration tools.
CentosOracle =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.130)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
运⾏plsql，使⽤SYS⽤户登录成功（我这⾥使⽤scott⽤户）
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论