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

[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个服务名支持两者连接模式的情况,这样非常难以维护.