[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相关进程,应该以后不会再有类似问题.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2991952/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
