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

来源：这里教程网时间：2026-03-03 19:01:45 作者：

[20231023]共享服务器的问题2.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相关进程。 --//今天尝试更正错误. 1.环境: 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 dispatchers PARAMETER_NAME TYPE VALUE ---------------- -------- -------------- dispatchers string (PROTOCOL=TCP) max_dispatchers integer SYS@192.168.100.41:1521/icare> show parameter service PARAMETER_NAME TYPE VALUE -------------- ------ ------ service_names string icare $ lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 23-OCT-2023 10:57:15 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 10-JUL-2021 12:06:42 Uptime 337 days 20 hr. 22 min. 39 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/102/network/admin/listener.ora Listener Log File /opt/oracle/102/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=8888))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=7777))(Presentation=FTP)(Session=RAW)) Services Summary... Service "icare" has 2 instance(s). Instance "icare", status UNKNOWN, has 1 handler(s) for this service... Instance "icare", status READY, has 2 handler(s) for this service... Service "icaredg4" has 1 instance(s). Instance "icare", status READY, has 2 handler(s) for this service... Service "icaredg4_XPT" has 1 instance(s). Instance "icare", status READY, has 2 handler(s) for this service... The command completed successfully --//奇怪竟然没有icareXDB服务. 2.尝试修复错误. SYS@192.168.100.41:1521/icare> 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 2006-05-15 10:26:23 127 NONE N NO SHORT 2 SYS$USERS 3427055676 2006-05-15 10:26:23 127 NONE N NO SHORT 4 icare 3845202787 icare 2006-05-15 10:26:26 578707901 NONE N NO LONG 6 icaredg4 315015500 icaredg4 2021-07-08 18:48:39 1148158569 NONE N NO LONG --//没有icareXDB服务. SYS@192.168.100.41:1521/icare> SELECT comp_id,schema,status,version,comp_name FROM dba_registry ORDER BY 1; COMP_ID SCHEMA STATUS VERSION COMP_NAME ---------- --------- --------- -------------- ------------------------------------ AMD OLAPSYS VALID 10.2.0.4.0 OLAP Catalog APS SYS VALID 10.2.0.4.0 OLAP Analytic Workspace CATALOG SYS VALID 10.2.0.4.0 Oracle Database Catalog Views CATJAVA SYS VALID 10.2.0.4.0 Oracle Database Java Packages CATPROC SYS INVALID 10.2.0.4.0 Oracle Database Packages and Types CONTEXT CTXSYS VALID 10.2.0.4.0 Oracle Text EM SYSMAN VALID 10.2.0.4.0 Oracle Enterprise Manager EXF EXFSYS VALID 10.2.0.4.0 Oracle Expression Filter JAVAVM SYS VALID 10.2.0.4.0 JServer JAVA Virtual Machine ODM DMSYS VALID 10.2.0.4.0 Oracle Data Mining ORDIM ORDSYS VALID 10.2.0.4.0 Oracle interMedia OWM WMSYS VALID 10.2.0.4.3 Oracle Workspace Manager RAC SYS INVALID 10.2.0.4.0 Oracle Real Application Clusters RUL EXFSYS VALID 10.2.0.4.0 Oracle Rule Manager SDO MDSYS VALID 10.2.0.4.0 Spatial XDB XDB VALID 10.2.0.4.0 Oracle XML Database ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ XML SYS VALID 10.2.0.4.0 Oracle XDK XOQ SYS VALID 10.2.0.4.0 Oracle OLAP API 18 rows selected. --//XDB组件是安装的,不管它先加上看看. SYS@192.168.100.41:1521/icare> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=icareXDB)' scope=memory; System altered. $ lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 23-OCT-2023 11:03:18 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 10-JUL-2021 12:06:42 Uptime 337 days 20 hr. 28 min. 42 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/102/network/admin/listener.ora Listener Log File /opt/oracle/102/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=8888))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=7777))(Presentation=FTP)(Session=RAW)) Services Summary... Service "icare" has 2 instance(s). Instance "icare", status UNKNOWN, has 1 handler(s) for this service... Instance "icare", status READY, has 1 handler(s) for this service... Service "icareXDB" has 1 instance(s). Instance "icare", status READY, has 1 handler(s) for this service... ~~~~~~~~~~~~~~~~~~~ Service "icaredg4" has 1 instance(s). Instance "icare", status READY, has 1 handler(s) for this service... Service "icaredg4_XPT" has 1 instance(s). Instance "icare", status READY, has 1 handler(s) for this service... The command completed successfully --//OK已经自动加上. SYS@192.168.100.41:1521/icare> 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 2006-05-15 10:26:23 127 NONE N NO SHORT 2 SYS$USERS 3427055676 2006-05-15 10:26:23 127 NONE N NO SHORT 3 icareXDB 3261664364 icareXDB 2006-05-15 10:26:26 578707901 NONE N NO LONG ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 4 icare 3845202787 icare 2006-05-15 10:26:26 578707901 NONE N NO LONG 6 icaredg4 315015500 icaredg4 2021-07-08 18:48:39 1148158569 NONE N NO LONG --//自动加上icareXDB服务名,很明显这个服务以前是存在的,注意看CREATION_DATE字段的时期. --//可以推测不知道那个运维人员修改了dispatchers参数,我查询alert日志已经无法确定. $ grep dispatchers alert_icare.log | sort |uniq All dispatchers and shared servers shutdown ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP) (SERVICE=icareXDB)' SCOPE=MEMORY; SYS@192.168.100.41:1521/icare> @ v V$SERVICES Show SQL text of views matching "%V$SERVICES%"... no rows selected VIEW_NAME TEXT ------------ ---------------------------------------------------------------------------------------------------- GV$SERVICES select inst_id, kswsastabsi, kswsastabnm, kswsastabnmh, kswsastabnn, kswsastabcd, kswsastabcdh, decode(kswsastabgoal, -1, NULL, 0, 'NONE', 1, 'SERVICE_TIME', 2, 'THROUGHPUT', NULL) kswsastabgoal, decode(bitand(kswsastabpflg, 2), 2, 'Y', 'N') kswsastabpflg, decode(bitand(kswsastabpflg, 4), 4, 'YES', 'NO'), decode(bitand(kswsastabpflg, 8), 8, 'LONG', 'SHORT') from x$kswsastab V$SERVICES select SERVICE_ID, NAME, NAME_HASH, NETWORK_NAME, CREATION_DATE, CREATION_DATE_HASH, GOAL, DTP, AQ_HA_NOTIFICATION, CLB_GOAL from GV$SERVICES where inst_id = USERENV('Instance') SYS@192.168.100.41:1521/icare> column NETWORK_NAME format a40 SYS@192.168.100.41:1521/icare> select SERVICE_ID,NAME,NETWORK_NAME,CREATION_DATE from service$; SERVICE_ID NAME NETWORK_NAME CREATION_DATE ---------- --------------------------- ------------------------------------- ------------------- 1 SYS$BACKGROUND 2006-05-15 10:26:23 2 SYS$USERS 2006-05-15 10:26:23 3 icareXDB icareXDB 2006-05-15 10:26:26 4 icare icare 2006-05-15 10:26:26 5 icare_taf icare_taf 2006-05-15 11:26:30 6 icaredg4 icaredg4 2021-07-08 18:48:39 7 SYS.KUPC$S_2_20080715103012 SYS$SYS.KUPC$S_2_20080715103012.ICARE 2008-07-15 10:30:13 8 SYS.KUPC$C_2_20060925155047 SYS$SYS.KUPC$C_2_20060925155047.ICARE 2006-09-25 15:50:47 9 SYS.KUPC$S_2_20060925155047 SYS$SYS.KUPC$S_2_20060925155047.ICARE 2006-09-25 15:50:48 9 rows selected. 3.测试: $ rlsql -s -l sys/XXXXX_YY@192.168.100.41:1521/icarexdb as sysdba @ spid <<< quit SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---- ---------- ------------ --------- ------ ------- ---------- -------------------------------------------------- 1062 18730 12036 SHARED 15771 15 201 alter system kill session '1062,18730' immediate; $ rlsql -s -l sys/XXXXX_YY@192.168.100.41:1521/icarexdb:DEDICATED as sysdba @ 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 --//服务名icareXDB仅仅支持共享模式。 $ rlsql -s -l sys/XXXXX_YY@192.168.100.41:1521/icare as sysdba @ spid <<< quit SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---- ---------- ------------ --------- ------ ------- ---------- -------------------------------------------------- 1062 18745 12040 DEDICATED 31300 23 162 alter system kill session '1062,18745' immediate; $ rlsql -s -l sys/XXXXX_YY@192.168.100.41:1521/icare:SHARED as sysdba @ 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 --//服务名icare仅仅支持专用模式，这样这个问题彻底解决!! SYS@192.168.100.41:1521/icare> show spparameter disp SP2-0614: Server version too low for this feature SP2-1539: Edition requires Oracle Database 11g or later. --//噢,10g不支持这个命令. $ strings spfileicare.ora | grep -i disp *.dispatchers='(PROTOCOL=TCP)' SYS@192.168.100.41:1521/icare> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=icareXDB)' scope=both; System altered. $ strings spfileicare.ora | grep -i disp *.dispatchers='(PROTOCOL=TCP) (SERVICE=icareXDB)' --//保险起见我kill S00X相关进程,应该以后不会再有类似问题.