oracle中12523,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

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