今天使用PL/SQL Developer連接到一台新的測試服務器時，遇到ORA錯誤：ORA-12523: TNS: 監聽程序無法找到適用於客戶機連接的例程。對應的監聽日志文件裡面錯誤為TNS-12523: TNS:listener could not find instance appropriate for the client connection
首先使用oerr命令查看ORA-12523錯誤提示的詳細內容
[oracle@DB-Server admin]$ oerr ora 12523
12523, 00000, "TNS:listener could not find instance appropriate for the client connection"
// *Cause: The listener could not find any available (database) instances,
// that are appropriate for the client connection.
// *Action: Run "lsnrctl services" to ensure that the instance(s) are
// registered with the listener, and have status READY.
按照提示信息，我首先運行"lsnrctl services" 查看數據庫實例是否注冊了監聽服務，並且監聽服務是否處於就緒狀態。
[oracle@DB-Server admin]$ lsnrctl services
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 06-JAN-2015 14:24:23
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.22)(PORT=1521)))
Services Summary...
Service "EPPS" has 1 instance(s).
Instance "EPPS", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
然後檢查了一下本地的tnsnames.ora的配置情況,發現其使用共享服務器模式(SHARED)連接到數據庫
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = epps)
)
檢查查看測試服務器，確認其配置是否開啟了共享服務器(Shared Server)模式,如下所示，數據庫為開啟共享服務器模式
SQL> show parameter shared_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer 40
shared_server_sessions integer
shared_servers integer 1
SQL>
SQL> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
dispatchers string (protocol=TCP)
max_dispatchers integer
SQL>
但是為什麼數據庫啟用了共享服務器模式,客戶端無法以SHARED模式登陸數據庫？ 那麼我先修改客戶端的連接方式為專用(DEDICATED)模式
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = epps)
)
)
此時以使用PL/SQL Developer登錄數據庫，發現OK，不會出現ORA-12523錯誤哦。查看當前會話，你可以通過下面SQL發現使用專用方式連接數據庫。
SQL> select server from v$session where sid = (select sid from v$mystat where rownum < 2);
SERVER
---------
DEDICATED
當然，你可以用下面SQL語句查看，其中GET253194為本人計算機名稱
COL USERNAME FOR A20
COL OSUSER FOR A10
COL MACHINE FOR A20
COL TERMINAL FOR A20;
SELECT SID, USERNAME, OSUSER, MACHINE,TERMINAL, SERVER
FROM V$SESSION
WHERE TERMINAL='GET253194';
修改tnsnames.ora，將(SERVER = DEDICATED)刪除（如下所示），依然可以連接到數據庫，查看其連接方式，發現它會自己選擇專用連接方式。也就是說默認為專用模式連接，除非指定為共享服務器模式
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.22)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = epps)
)
)
SQL> select server from v$session where sid = (select sid from v$mystat where rownum < 2);
SERVER
---------
DEDICATED
那麼問題出在哪裡呢？ 很是納悶，明明服務器為共享服務器模式，應該可以以共享服務連接方式連接數據庫的。最後在折騰中發現，這台服務器本來是生產服務器，後來退下來被我當做測試服務器，於是我將IP地址改為了192.168.16.22， 但是當時只是修改了/etc/sysconfig/network-scripts/ifcfg-eth0系統文件. 忘記修改/etc/hosts對應的IP地址。我將/etc/hosts中的IP地址修改過來後，發現ORA-12523錯誤不見了，問題完滿解決。但是本質的原因呢？看來還需要深入了解監聽服務原理。多去學習、了解一些相關資料。留待後面補充。
