[20231013]共享服务器的问题.txt

--//前一段时间遇到1台数据库服务器机器无法登陆的问题，出现ora-04030错误。
--//参考链接：[20230809]ora-04030问题分析整理.txt
--//问题在于中间件配置的连接串没有(SERVER = DEDICATED)设置，导致全部连接使用共享模式，而估计10g版本存在某种bug，
--//导致共享服务的s00X进程占用的内存空间.而且还使用了大量的swap内存.

--//主要问题还是dispatchers配置有问题,我当时没有修改这个参数，仅仅探究看看。
SYS@192.168.100.41:1521/icare> @ ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SYS@192.168.100.41:1521/icare> show parameter disp
PARAMETER_NAME   TYPE     VALUE
---------------- -------- ---------------
dispatchers      string   (PROTOCOL=TCP)
max_dispatchers  integer
--//奇怪，dispatchers参数没有任何配置(service=.....)
--//我当时想因为dispatchers参数没有显式写(servie=....),而连接串没有(SERVER = DEDICATED)设置，导致全部连接使用共享模式。
--//我当时的做法就是要同事修改连接串，加入(SERVER = DEDICATED)，杀死全部s00N相关进程。

--//今天例行检查，我发现还是有一些连接使用共享服务模式。我必须测试dispatchers加入(servie=icareXDB)后,是否可以彻底解决问
--//题。测试不能在生产系统做这项工作，在测试环境演示看看。

1.环境：
SYS@book> @ ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> show parameter dispatchers
PARAMETER_NAME   TYPE      VALUE
---------------- --------- --------------------------------
dispatchers      string    (PROTOCOL=TCP) (SERVICE=bookXDB)
max_dispatchers  integer

SYS@book> show parameter service
PARAMETER_NAME TYPE    VALUE
-------------- ------- ---------------
service_names  string  BOOK, BOOKSHARE

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-OCT-2023 10:12:54

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-APR-2023 08:58:39
Uptime                    182 days 1 hr. 14 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 1 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully

$ rlsql -s -l scott/book@127.0.0.1:1521/book  @ spid <<<quit
 SID SERIAL# PROCESS SERVER    SPID       PID  P_SERIAL# C50
---- ------- ------- --------- ------ ------- ---------- --------------------------------------------------
   1    1253 11376   DEDICATED 11381       24        158 alter system kill session '1,1253' immediate;

$ rlsql -s -l   scott/book@127.0.0.1:1521/book:shared @spid<<<quit
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

--//测试开始前使用ezconnect连接,服务名book缺省连接模式是SERVER=DEDICATED，如果指定共享模式shared并不支持.

2.开始测试:
SYS@book> alter system set dispatchers='(PROTOCOL=TCP)' scope=memory;
System altered.

$ rlsql -s -l scott/book@127.0.0.1:1521/book  @ spid <<<quit
 SID SERIAL# PROCESS SERVER    SPID       PID  P_SERIAL# C50
---- ------- ------- --------- ------ ------- ---------- --------------------------------------------------
 341      11 11386   SHARED    51628       20          1 alter system kill session '341,11' immediate;
--//你可以现在使用服务名book采用ezconnect连接,缺省的连接模式是SERVER=SHARED.我甚至没有重启监听.
--//也就是任何服务名(除了bookXDB)支持两者连接模式,如果使用ezconnect连接不指定连接模式,优先尝试SHARED模式,不行才是
--//DEDICATED模式.

--//你可以指定连接模式DEDICATED.
$ rlsql -s -l scott/book@127.0.0.1:1521/book:DEDICATED  @ spid <<<quit
 SID SERIAL# PROCESS SERVER    SPID       PID  P_SERIAL# C50
---- ------- ------- --------- ------ ------- ---------- --------------------------------------------------
   1    1255 11403   DEDICATED 11408       24        159 alter system kill session '1,1255' immediate;

--//这样也就模拟生产系统看到的情况,在dispatchers='(PROTOCOL=TCP)'的情况下，普通的服务名配置连接支持专用连接和共享连接模
--//式。

3.继续测试:

$ rlsql -s -l   scott/book@127.0.0.1:1521/bookshare @spid<<<quit
 SID SERIAL# PROCESS SERVER    SPID       PID  P_SERIAL# C50
---- ------- ------- --------- ------ ------- ---------- --------------------------------------------------
 341      25 11504   SHARED    51628       20          1 alter system kill session '341,25' immediate;
--//这是我写在参数service_names的服务名也支持.

$ rlsql -s -l   scott/book@127.0.0.1:1521/bookXDB @spid<<<quit
ERROR:
ORA-12516: TNS:listener could not find available handler with matching protocol stack
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

$ rlsql -s -l   scott/book@127.0.0.1:1521/bookxdb @spid<<<quit
ERROR:
ORA-12516: TNS:listener could not find available handler with matching protocol stack
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
--//但是不支持bookXDB服务名.

--//尝试重启监听看看。
$ lsnrctl stop ; lsnrctl start
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-OCT-2023 10:36:15
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-OCT-2023 10:36:01
Uptime                    0 days 0 hr. 0 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 2 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 2 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 0 handler(s) for this service...
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The command completed successfully
--//注册的服务名bookXDB是存在,但是无论怎么,都无法通过bookxdb连上服务,可以注意一个细节handler=0.前面是1,也就是没有获得句
--//柄.(我估计如果我重启数据库，这项内容应该不存在，明天补充测试看看!!)

SYS@book> select * from V$SERVICES order by 1;
SERVICE_ID NAME            NAME_HASH NETWORK_NAME CREATION_DATE       CREATION_DATE_HASH GOAL D AQ_ CLB_G
---------- -------------- ---------- ------------ ------------------- ------------------ ---- - --- -----
         1 SYS$BACKGROUND  165959219              2013-08-24 11:37:40                  0 NONE N NO  SHORT
         2 SYS$USERS      3427055676              2013-08-24 11:37:40                  0 NONE N NO  SHORT
         4 BOOKSHARE      3041418781 BOOKSHARE    2023-10-18 08:49:31         1313067792 NONE N NO  LONG
         5 bookXDB        1843642519 bookXDB      2015-11-24 09:11:13         3631320093 NONE N NO  LONG
         6 book           1062280681 book         2015-11-24 09:11:13         3631320093 NONE N NO  LONG

SYS@book> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=bookXDB)' scope=memory;
System altered.

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-OCT-2023 10:44:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-OCT-2023 10:37:31
Uptime                    0 days 0 hr. 6 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.4/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "BOOKSHARE" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
Service "Book" has 2 instance(s).
  Instance "book", status UNKNOWN, has 1 handler(s) for this service...
  Instance "book", status READY, has 1 handler(s) for this service...
Service "bookXDB" has 1 instance(s).
  Instance "book", status READY, has 1 handler(s) for this service...
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The command completed successfully

$ rlsql -s -l   scott/book@127.0.0.1:1521/bookxdb @ spid <<<quit
 SID    SERIAL# PROCESS SERVER    SPID       PID  P_SERIAL# C50
---- ---------- ------- --------- ------ ------- ---------- --------------------------------------------------
 341         37 11754   SHARED    51628       20          1 alter system kill session '341,37' immediate;

$ rlsql -s -l   scott/book@127.0.0.1:1521/bookxdb:DEDICATED @ spid <<<quit
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of　server
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
--//bookxdb 服务无法使用DEDICATED模式连接.

$ rlsql -s -l   scott/book@127.0.0.1:1521/book @ spid <<<quit
 SID    SERIAL# PROCESS SERVER    SPID       PID  P_SERIAL# C50
---- ---------- ------- --------- ------ ------- ---------- --------------------------------------------------
   1       1275 11786   DEDICATED 11791       24        166 alter system kill session '1,1275' immediate;

$ rlsql -s -l   scott/book@127.0.0.1:1521/book:DEDICATED @ spid <<<quit
 SID    SERIAL# PROCESS SERVER    SPID       PID  P_SERIAL# C50
---- ---------- ------- --------- ------ ------- ---------- --------------------------------------------------
   1       1277 11794   DEDICATED 11799       24        167 alter system kill session '1,1277' immediate;

$ rlsql -s -l   scott/book@127.0.0.1:1521/book:shared @spid <<<quit
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

$ rlsql -s -l   scott/book@127.0.0.1:1521/book:SHARED @spid <<<quit
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server
SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
--//可以看出book服务现在仅仅支持DEDICATED连接模式.

4.总结:
--//很简单就是修改dispatchers参数等于'(PROTOCOL=TCP) (SERVICE=bookXDB)'.指定专门的服务供共享连接使用.
--//dispatchers不写(SERVICE=bookXDB)的话,其它服务名支持两种连接模式.
--//再次强调ezconnect如果不指定连接模式的话，优先选择共享连接模式，如果它支持的话。
--//我以前提到尽量避免1个服务名支持两者连接模式的情况,这样非常难以维护.