一. 问题描述

在配置数据库为共享模式时,发现客户端无法以share方式登陆数据库,报ORA-12523错误,如下:
SQL>conn aidu/aidu@prod_s

ORA-12523: TNS:listener could not find instance appropriate for the client connection

二. 问题分析

1.查看共享服务器配置如下:

SQL> show parameter shared

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
max_shared_servers integer 20
shared_server_sessions integer 30
shared_servers integer 10
SQL> show parameter dispatcher
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
dispatchers string (protocol=tcp)(dispatchers=3)(
sessions=200)
max_dispatchers integer 10

共享服务器的配置没有任何问题.

2.检查连接串定义是否正常
$more $ORACLE_HOME/network/admin/tnsnames.ora
......
prod_s=
(description=
(address=(protocol=tcp)(host=ocmdb)(port=1526)
)
(CONNECT_DATA=
(SERVICE_NAME=PROD)
(SERVER=SHARED)
)
)
tnsnames.ora配置应该也没有任何问题.

3. 检查服务器的local_listener有没有设置
SQL> show parameter local

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener string

发现local_listener没有配置.

在shared server环境下,你可以通过初始化参数DISPATCHERS中的LISTENER属性来注册dispatcher到非缺省的本地listener中.
因为,LOCAL_LISTENER参数和dispatchers参数中的LISTENER属性都可以让pmon进程在listener中注册dispatcher.
所以,如果listener的名称相同,则不需要同时设置LOCAL_LISTENER参数和dispatchers的listener属性.

设置LOCAL_LISTENER 参数,如下:
LOCAL_LISTENER=listener_alias
设置dispatchers的listener属性,如下:
DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener_alias)"
listener_alias通过tnsnames.ora文件进行解释.

举个例子:如果listener配置端口为1421(不是1521),你可以在初始化参数中设置LOCAL_LISTENER为下面的值:
LOCAL_LISTENER=listener1
使用相同的listener例子,你可以设置LISTENER属性
DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener1)"
然后在tnsnames.ora文件解析
listener1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocmdb)(PORT=1421)))
修改LOCAL_LISTENER参数,可以使用下面的命令,是动态参数,可以使用scope=both.

ALTER SYSTEM SET LOCAL_LISTENER='listener_alias' SCOPE=BOTH;

如果要关闭LOCAL_LISTENER的配置,可以如下设置:

ALTER SYSTEM SET LOCAL_LISTENER='' SCOPE=BOTH;

三. 问题解决

1. 在服务器的tnsnames.ora文件中增加本地listener的解析
listener1=(address=(protocol=tcp)(host=ocmdb)(port=1421))

2. 设置local_listener初始化参数
SQL> alter system set local_listener='listener1' scope=both;

System altered.

SQL> show parameter local_li

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
local_listener string listener1

3.定义客户端tnsnames.ora,配置为shared方式访问数据库

PROD_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.208.132)(PORT = 1421))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
(SERVER= SHARED )
)
)

4. 客户端进行登陆测试,结果ok了
C:UsersAdministrator>sqlplus aidu/aidu@prod_s

SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 4月 30 21:24:14 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

访问 PRODUCT_USER_PROFILE 时出错
警告: 未加载产品用户概要文件信息!
您需要将 PUPBLD.SQL 作为 SYSTEM 运行

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

5. 检查服务器端监听器的状态,可以看到已经有session通过dispatcher连接上来了,问题解决.
LSNRCTL> service
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=ocmdb)(PORT=1421))
Services Summary...
Service "prod" has 2 instance(s).
Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "prod", status READY, has 4 handler(s) for this service...
Handler(s):
"D002" established:0 refused:0 current:0 max:200 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ocmdb)(PORT=56929))
"D001" established:0 refused:0 current:0 max:200 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ocmdb)(PORT=32050))
"D000" established:0 refused:0 current:0 max:200 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ocmdb)(PORT=40805))
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER

The command completed successfully