解决windows7无法连接CentOS7系统中oracle问题：ORA-12514 TNS 监听程序当前无法识别
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新建tnsnames.ora,用记事本编辑.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 虚拟机IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
))
3.添加一个环境变量，名为TNS_ADMIN，值为tnsnames.ora文件所在路径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
OCI库：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\admin\listener.ora中的localhost改为192.168.78.130
2.修改\data\oracle\product\11.2.0\db_1\network\admin\tnsnames.ora中的localhost 改为 192.168.78.130
3.修改后，重启oracle，监听，并注册，主win7上的plsql就可以远程连接虚拟机上的数据库了.
具体操作步骤如下：
一、修改数据库服务器中listener.ora文件内容
命令:
[oracle@localhost admin]$ vi /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
原始内容:
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /data/oracle
修改为：
# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# 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
二、修改数据库服务器中tnsnames.ora文件内容
命令:
[oracle@localhost admin]$ vi /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
原始内容:
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
修改后的内容：
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# 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 command completed 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: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /data/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
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
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /data/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.130)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed 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 dismounted.
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 mounted.
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
Uptime 0 days 0 hr. 12 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /data/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.130)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$
四、Windows7客户端下的测试步骤：使用plsqldev.exe测试，修改
C:\Program Files\PLSQL Developer\instantclient_11_2\network\admin \tnsnames.ora
文件，新增到虚拟机Oracle的连接内容
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# 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用户）
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查询表
