代码改变世界

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

2015-01-06 15:55  潇湘隐者  阅读(22770)  评论(0编辑  收藏  举报

    今天使用PL/SQL Developer连接到一台新的测试服务器时,遇到ORA错误:ORA-12523: TNS: 监听程序无法找到适用于客户机连接的例程。对应的监听日志文件里面错误为TNS-12523: TNS:listener could not find instance appropriate for the client connection

 

首先使用oerr命令查看ORA-12523错误提示的详细内容

[oracle@DB-Server admin]$ oerr ora 12523
12523, 00000, "TNS:listener could not find instance appropriate for the client connection"
// *Cause:  The listener could not find any available (database) instances, 
// that are appropriate for the client connection.
// *Action: Run "lsnrctl services" to ensure that the instance(s) are
// registered with the listener, and have status READY.

 

按照提示信息,我首先运行"lsnrctl services" 查看数据库实例是否注册了监听服务,并且监听服务是否处于就绪状态。

[oracle@DB-Server admin]$ lsnrctl services
 
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 06-JAN-2015 14:24:23
 
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.22)(PORT=1521)))
Services Summary...
Service "EPPS" has 1 instance(s).
  Instance "EPPS", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

 

然后检查了一下本地的tnsnames.ora的配置情况,发现其使用共享服务器模式(SHARED)连接到数据库

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = epps)
    )

检查查看测试服务器,确认其配置是否开启了共享服务器(Shared Server)模式,如下所示,数据库为开启共享服务器模式

SQL> show parameter shared_server
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer     40
shared_server_sessions               integer
shared_servers                       integer     1
SQL> 
 
SQL> show parameter dispatchers
 
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
dispatchers                          string                           (protocol=TCP)
max_dispatchers                      integer
SQL> 

但是为什么数据库启用了共享服务器模式,客户端无法以SHARED模式登陆数据库? 那么我先修改客户端的连接方式为专用(DEDICATED)模式

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = epps)
    )
  )

此时以使用PL/SQL Developer登录数据库,发现OK,不会出现ORA-12523错误哦。查看当前会话,你可以通过下面SQL发现使用专用方式连接数据库。

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

当然,你可以用下面SQL语句查看,其中GET253194为本人计算机名称

COL USERNAME FOR A20
COL OSUSER FOR A10
COL MACHINE FOR A20
COL TERMINAL FOR A20;
SELECT SID, USERNAME, OSUSER, MACHINE,TERMINAL, SERVER 
FROM V$SESSION
WHERE TERMINAL='GET253194'; 

 

clip_image001

修改tnsnames.ora,将(SERVER = DEDICATED)删除(如下所示),依然可以连接到数据库,查看其连接方式,发现它会自己选择专用连接方式。也就是说默认为专用模式连接,除非指定为共享服务器模式

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = epps)
    )
  )

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

那么问题出在哪里呢? 很是纳闷,明明服务器为共享服务器模式,应该可以以共享服务连接方式连接数据库的。最后在折腾中发现,这台服务器本来是生产服务器,后来退下来被我当做测试服务器,于是我将IP地址改为了192.168.16.22, 但是当时只是修改了/etc/sysconfig/network-scripts/ifcfg-eth0系统文件. 忘记修改/etc/hosts对应的IP地址。我将/etc/hosts中的IP地址修改过来后,发现ORA-12523错误不见了,问题完满解决。但是本质的原因呢?看来还需要深入了解监听服务原理。多去学习、了解一些相关资料。留待后面补充。