今天配置完共享服務器模式之後發現登錄過程中報錯ORA-12523，排查錯誤之後發現是靜態監聽惹的禍。
本機之上有兩個監聽，一個靜態監聽1521端口，一個動態監聽1526端口。
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=jp)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
LSNR2=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=jp)(PORT=1526))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1))))
trace_level_LSNR2=SUPPORT
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=PROD))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(PROGRAM=extproc)))
出錯時的tnsnames配置：
prod_s=
(description =
(address = (protocol = tcp)(host = jp)(port = 1521))
(connect_data =
(server = shared)
(service_name = PROD)
)
)
連接數據庫時報錯：
[oracle@jp admin]$ sqlplus sys/oracle@prod_s as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 8 10:43:54 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection
檢查之後發現prod_s連接串走的是靜態監聽，共享服務器模式需要將調度進程的信息注冊到監聽之中，當有連接連入時，監聽會選擇一個負載最低的調度進程。而靜態監聽沒有調度進程的信息，導致通過shared_server模式連接報錯。
修改tnsnames
prod_s=
(description =
(address = (protocol = tcp)(host = jp)(port = 1526))
(connect_data =
(server = shared)
(service_name = PROD)
)
)
重新通過shared_server連接，成功：
[oracle@jp admin]$ sqlplus sys/oracle@prod_s as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 8 10:42:22 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SYS@prod_s>select server from v$session;
SERVER
---------
SHARED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
SERVER
---------
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
16 rows selected.
