ORA-12520 TroubleShooting

同事反饋他連接一個新搭建的測試資料庫時，報“ORA-12520: TNS: 監聽程式無法為請求的伺服器類型找到可用的處理程式”錯誤，在解決他這個問題時，順便分析、總結一下ORA-12520錯誤。下麵重現一下這個場景：

Oracle Client段的tnsnames.ora的配置如下：

MY_TEST=

        (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.5.37)(PORT=49161))

            (CONNECT_DATA=

           (SERVER = SHARED)

                  (SERVICE_NAME = XE)

            )

        )

客戶端SQL*PLUS訪問資料庫報錯：

C:\Users>sqlplus test/test123456@MY_TEST

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 1月 8 23:30:47 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:

ORA-12520: TNS: 監聽程式無法為請求的伺服器類型找到可用的處理程式

請輸入用戶名:

請輸入用戶名:

在伺服器檢查是否開啟了shared server模式（註意，如果配置正確，但是沒有開啟共用伺服器模式，也會報這個錯誤）

SQL> show parameter shared_servers

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_shared_servers                   integer     10

shared_servers                       integer     10

SQL> 

在伺服器檢查SERVICE_NAME的信息：

SQL> show parameter service_name;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

service_names                        string      XE

SQL> !

oracle@3c939f31e44b:~$ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JAN-2019 15:33:45

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

Service "XE" has 1 instance(s).

  Instance "XE", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:102 refused:0 state:ready

         LOCAL SERVER

Service "XEXDB" has 1 instance(s).

  Instance "XE", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:4 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: 3c939f31e44b, pid: 5980>

         (ADDRESS=(PROTOCOL=tcp)(HOST=3c939f31e44b)(PORT=41385))

Service "XE_XPT" has 1 instance(s).

  Instance "XE", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:102 refused:0 state:ready

         LOCAL SERVER

The command completed successfully

我們知道如果共用伺服器模式連接資料庫，是需要通過DISPATCHER的，那麼要看看參數dispatchers是如何配置的。如下所示，dispatchers裡面設置的是SERVIE_NAME為XEXDB，不是XE，難怪會出這個錯誤。

那麼我們修改一下DISPATCHERS參數配置，將SERVICE_NAME改為XE：