【Oracle】静态监听导致的ORA-12523错误

宜家博客
宜家博客
宜家博客
47420
文章
0
评论
2020年7月1日12:20:32 评论 2 1753字阅读5分50秒

今天配置完共享服务器模式之后发现登录过程中报错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

 

继续阅读
weinxin
欢迎加入中国站长博客之家
本站的所有资源都会上传分享到博客之家,希望大家互相学习交流进步。
宜家博客
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: