ORA-12523 errors caused by static listening in Oracle

After configuring the shared server mode today, we found that the error ORA-12523 in the process of logging in, and found that static monitoring was the bane of the investigation.

There are two listening on the machine, a static listening 1521 port, and a dynamic listening 1526 port.

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 configuration When an error occurs:

prod_s=

(Description =

(address = (protocol = TCP) (host = JP) (port = 1521))

(Connect_data =

(Server = shared)

(service_name = PROD)

)

)

Connection Database times Error:

[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, +, Oracle. All rights reserved.

ERROR:

Ora-12523:tns:listener could not find instance appropriate for the client

Connection

After the inspection found that the prod_s connection string is static listening, the shared server mode needs to register the information of the scheduling process into the monitoring, when there is a connection, listening to select a load of the lowest scheduling process. Static monitoring does not have information about the scheduling process, which causes the error to be connected through Shared_server mode.

Modify TNSNames

prod_s=

(Description =

(address = (protocol = TCP) (host = JP) (port = 1526))

(Connect_data =

(Server = shared)

(service_name = PROD)

)

)

Re-connect through Shared_server, success:

[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, +, 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

Rows selected.

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/