Oracle数据库连接错误 ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

在数据库服务器上通过控制台sqlplus 账户/密码登陆成功。

但是在数据库服务器上远程使用别名的方式失败：sqlplus 账户/密码@orcl

报错：

ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

查看监听文件listener.ora如下：

# listener.ora Network Configuration File: D:\Oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.16.30.192)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.16.30.192)(PORT = 1521))
    )
  )

 修改SID_NAME=ORCL后 ，保存关闭。

重启监听服务：

lsnrctl stop
lsnrctl start

 重新访问sqlplus 账户/密码@orcl

报错：

ERROR:
ORA-12523: TNS: 监听程序无法找到适用于客户机连接的例程

看到以上监听 文件有2个，使用Net Configuration Assistant删除LISTENER1，重新配置LISTENER后。

监听文件listener.ora如下：

# listener.ora Network Configuration File: D:\Oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = D:\Oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server2-PC)(PORT = 1521))
    )
  )

默认HOST=机器名，需要改成IP，重新访问，同样错误：

cmd ERROR:
ORA-12523: TNS: 监听程序无法找到适用于客户机连接的例程

此时在我机器上使用PL/SQL Developer远程连接这台数据库一样报错：ORA-28547:连接服务器失败，可能是Oracle Net管理错误

因此先修改NETWORK/ADMIN目录下的sqlnet.ora文件，原文件如下：

# sqlnet.ora Network Configuration File: D:\Oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 将NTS修改为NONE后，自己电脑用PL/SQL Developer工具连接成功。

因此再回来看之前的错误ORA-12523: TNS: 监听程序无法找到适用于客户机连接的例程

此时可以决定该错误不是由监听引起了，因为我自己电脑本地远程连接数据库是正常的。

查看tnsnames.ora文件如下：

# tnsnames.ora Network Configuration File: D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL_33 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.16.30.33)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL_32 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.16.30.32)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL_LXS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.123)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.16.30.192)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

根据网上找的教程，基本上指定是SERVER=SHARED引起，但是都是推荐改为SHARED，而配置文件本身就是SHARED模式。

查看数据库连接模式：

SQL> select server from v$session where sid = (select sid from v$mystat where rownum <2);
SERVER
---------
DEDICATED

 可以看出数据库连接模式是专有服务器，因此将

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.16.30.192)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = orcl)
    )
  )

 改为

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 19.16.30.192)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

 然后在数据库控制台中使用sqlplus 账户/密码@ORCL即可访问成功。