ora-12523錯誤分析與解決

一. 問題描述

在配置資料庫為共享模式時,發現客戶端無法以share方式登陸資料庫,報ORA-12523錯誤,如下:
SQL>conn

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

[@more@]

二. 問題分析

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

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