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

问题如下:

SQL> conn hr/hr@oracle9ivm;  --在客户端连接

ERROR:

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

 

首先用oerr ora 12523

[oracle@oracle9idemo dbs]$ 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.

 

说明是服务器端listener配置的问题

查看服务器端listener配置:

[oracle@oracle9idemo ~]$ lsnrctl service;

 

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 29-JUN-2015 02:43:06

 

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.88)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

Service "denver" has 1 instance(s).

  Instance "denver", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

The command completed successfully

 

说明在服务器端的listener中并没有将dispatcher注册进去,这是为什么?

 

原因:

上面的Instance "denver", status UNKNOWN, has 1 handler(s) for this service...说明listener采用的是静态注册,UNKNOWN表示静态注册,READY表示动态注册,所以没有DISPATCHERS这个选项。

解决办法:

将服务器器端的listener改成动态注册就行,将$ ORACLE_HOME/network/admin下的listener.ora删除或者移动到另一个地方就行 注意动态注册只要在初始参数文件里面指定sevice_names和instance_name参数文件即可,客户端的tnsnames文件中的service_name文件要与service_names名字一样。

 

ervice_names=global database name = sid.db_domain

查看serveice_names命令:

sql>show parameter service

其中global_names是由sid.db_domain构成的

instance_name=sid

 
[oracle@oracle9idemo admin]$ lsnrctl services


LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 29-JUN-2015 05:36:00


Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.


Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
Services Summary...
Service "denver" has 1 instance(s).
  Instance "denver", status READY, has 5 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
      "D002" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle9idemo)(PORT=33266))
      "D001" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle9idemo)(PORT=33267))
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle9idemo)(PORT=33260))
      "D003" established:1 refused:0 current:1 max:1002 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle9idemo)(PORT=33268))
Service "denverXDB" has 1 instance(s).
  Instance "denver", status READY, has 0 handler(s) for this service...
The command completed successfully

这时候在客户端就可以在连接到数据库了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29840459/viewspace-1716207/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29840459/viewspace-1716207/