[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Windows共享的那些問題Windows
- session共享問題？？？Session
- 關於資料共享的問題
- Web--Session共享問題WebSession
- Tomcat 共享session問題TomcatSession
- [20231024]共享伺服器的問題3.txt伺服器
- rdesktop共享剪貼簿的問題
- 共享記憶體分段問題記憶體
- windows xp共享問題解決方法Windows
- ZT:字元長度與共享問題字元
- aix 共享記憶體段問題AI記憶體
- vue-router元件複用共享$route的問題Vue元件
- 關於sql語句的遊標共享問題SQL
- 多個JVM之間，資料共享的問題？JVM
- 儲存目錄的SMB目錄共享問題
- [20230427]bbed sum apply問題2.txtAPP
- [20180413]熱備模式相關問題2.txt模式
- 伺服器常見的問題伺服器
- IM伺服器的問題,急!!!伺服器
- 問一個tomcat伺服器的問題Tomcat伺服器
- wmware共享磁碟redhat 5.8掛載問題Redhat
- 多執行緒中的使用共享變數的問題執行緒變數
- SharePreference原理及跨程式資料共享的問題
- Ubuntu共享資料夾訪問許可權問題Ubuntu訪問許可權
- 解決不能訪問伺服器共享檔案的終極方案伺服器
- DHCP伺服器問題伺服器
- 遊戲伺服器存在的主要問題遊戲伺服器
- 分析伺服器延遲的問題伺服器
- 一個伺服器部署的問題伺服器
- vmware server 解決rac中共享磁碟問題Server
- 解決Mac無法共享網路問題Mac
- [20241118]NLS_LANG設定問題2.txt
- [20221130]最佳化備庫dg遇到的問題2.txt
- 伺服器問題 排查思路伺服器
- [20210421]分析會話佔用的共享記憶體段2.txt會話記憶體
- 華為雲伺服器配置遇到的問題伺服器
- 配置nfs伺服器出現的問題NFS伺服器
- nfs伺服器搭建和遇到的問題NFS伺服器