解决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查询表

posted @ 2017-02-19 22:14  nicho_c  阅读(104833)  评论(1编辑  收藏