[20231023]共享伺服器的問題2.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2991952/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20231024]共享伺服器的問題3.txt伺服器
- [20190116]詭異的問題2.txt
- session共享問題？？？Session
- [20230427]bbed sum apply問題2.txtAPP
- [20180423]關於rman備份的問題2.txt
- [20181124]關於降序索引問題2.txt索引
- [20190218]延遲約束問題2.txt
- [20180413]熱備模式相關問題2.txt模式
- [20241118]NLS_LANG設定問題2.txt
- [20221130]最佳化備庫dg遇到的問題2.txt
- [20191113]oracle共享連線模式埠2.txtOracle模式
- [20221111]19c配置Data Guard Broker問題2.txt
- [20191216]共享池繫結變數的值在哪裡2.txt變數
- vue-router元件複用共享$route的問題Vue元件
- [20191204]關於oracle例項是否使用hugepages問題2.txtOracle
- [20210421]分析會話佔用的共享記憶體段2.txt會話記憶體
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- [20240818]測試21c下sqlplus show recyclebin的小問題2.txtSQL
- 伺服器常見的問題伺服器
- Ubuntu共享資料夾訪問許可權問題Ubuntu訪問許可權
- [20191209]降序索引疑問2.txt索引
- 分析伺服器延遲的問題伺服器
- 一個伺服器部署的問題伺服器
- 解決Mac無法共享網路問題Mac
- [20231023]生成bbed的執行指令碼(bash shell).txt指令碼
- nfs伺服器搭建和遇到的問題NFS伺服器
- 遊戲伺服器存在的主要問題遊戲伺服器
- 伺服器問題 排查思路伺服器
- [20240930]關於共享池-表物件在庫快取探究2.txt物件快取
- 華為雲伺服器配置遇到的問題伺服器
- php CURL 伺服器響應慢的問題PHP伺服器
- MQTT伺服器連線不上的問題MQQT伺服器
- 伺服器常見的四大問題伺服器
- Oracle共享伺服器的連線模式Oracle伺服器模式
- tomcat伺服器快取問題Tomcat伺服器快取
- nginx伺服器配置問題心得Nginx伺服器
- Chrome89針對sessionStorage的更新導致資料共享問題ChromeSession
- Debian 12 + KDE 螢幕共享失敗問題解決