## 1、字元集概述 - Oracle語言環境的描述包括三部分：language、territory、characterset(語言、地域、字元集) - language：主要指定伺服器消息的語言，提示信息顯示中文還是英文 - territory：主要指定伺服器的數字和日期的格式 - charact ...
[20230809]ora-04030問題分析整理.txt
--//生產系統同事使用toad連接經常出現ora-04030錯誤。
ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
--//仔細看joi確實不是join,開始猜測可能某個程式的sql語句選擇hash-join,導致pga消耗太大。
--//同事給了我alert日誌的截圖，提示都是icare_s001_20087.trc的進程,難道全部使用共用連接模式報錯!!
--//icare_s001_20087.trc的進程報如下錯誤,對應進程裡面應該能看到sql語句。
ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
--//原始的分析版本寫的太亂,事後重新做一下整理,主要還是想記錄一下當時的分析過程,估計還是很亂!!
1.環境:
# cat /etc/redhat-release
redhat 4
#Red Hat Enterprise Linux Server release 6.0 (Santiago)
# free -m
total used free shared buffers cached
Mem: 15953 15775 178 0 4 5003
-/+ buffers/cache: 10767 5186
Swap: 15999 15999 0
--//昏,swap記憶體都全部使用了.
[email protected]: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
--//哇!很古老的版本.
[email protected]:1521/icare:DEDICATED> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- --------------- ----------------- ------------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- ---
1 icare DZBL_DB_101.132 10.2.0.4.0 2021-08-24 08:24:17 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
--//出現問題後沒有重啟過機器!!順便說明一下,這台機器很少訪問,基本作為歷史資料庫,偶爾有應用連上就是查詢數據.
2.分析:
[email protected]:1521/icare> show sga
Total System Global Area 6442450944 bytes
Fixed Size 2093584 bytes
Variable Size 2181041648 bytes
Database Buffers 4244635648 bytes
Redo Buffers 14680064 bytes
[email protected]:1521/icare> show parameter pga
PARAMETER_NAME TYPE VALUE
-------------------- ----------- -------
pga_aggregate_target big integer 3G
[email protected]:1521/icare> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------ --------- ------ ------- ---------- --------------------------------------------------
855 22839 31462 SHARED 1811 18 237 alter system kill session '855,22839' immediate;
--//噢!以前管理的資料庫不合理,選擇的連接模式是SHARED模式,spid=1811.
--//這樣可能更好理解為什麼alert日誌都是記錄都是S001的進程.
$ grep -B1 "ORA-04030" alert_icare.log | grep Error | sort | uniq -c
1 Errors in file /opt/oracle/admin/icare/bdump/icare_s000_18301.trc:
1 Errors in file /opt/oracle/admin/icare/bdump/icare_s000_26348.trc:
1968 Errors in file /opt/oracle/admin/icare/bdump/icare_s001_20087.trc:
--//很明顯問題問題出在共用連接的進程,也就是可以估計應用段的進程都是以共用模式連接的.
[email protected]:1521/icare> show parameter disp
PARAMETER_NAME TYPE VALUE
---------------- -------- ---------------
dispatchers string (PROTOCOL=TCP)
max_dispatchers integer
--//奇怪沒有任何配置.難道同事建立的配置連接串沒有(SERVER = DEDICATED)設置.預設共用模式優先.
$ lsnrctl service
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 10-AUG-2023 08:25:53
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "icare" has 2 instance(s).
Instance "icare", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1419 refused:0
LOCAL SERVER
Instance "icare", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:1458 refused:0 state:ready
LOCAL SERVER
"D000" established:7472195 refused:0 current:109 max:1022 state:ready
DISPATCHER <machine: DZBL_DB_101.132, pid: 26346>
(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=45587))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Service "icaredg4" has 1 instance(s).
Instance "icare", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:1458 refused:0 state:ready
LOCAL SERVER
"D000" established:7472195 refused:0 current:109 max:1022 state:ready
DISPATCHER <machine: DZBL_DB_101.132, pid: 26346>
(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=45587))
Service "icaredg4_XPT" has 1 instance(s).
Instance "icare", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:1458 refused:0 state:ready
LOCAL SERVER
"D000" established:7472195 refused:0 current:109 max:1022 state:ready
DISPATCHER <machine: DZBL_DB_101.132, pid: 26346>
(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=45587))
The command completed successfully
--//icare服務存在兩者模式,預設連接串沒有配置,優先選擇共用模式連接.
[email protected]:1521/icare> column Program format a30
[email protected]:1521/icare> @pga 10
NAME VALUE_MB
------------------------------ ----------
aggregate PGA target parameter 3072
total PGA inuse 17655.4385
total PGA allocated 19551.1279
over allocation count 18030955
PROFILE CNT PERCENTAGE
------------------------------- ---------- ----------
workarea executions - optimal 74202449 100
workarea executions - onepass 89372 0
workarea executions - multipass 3 0
SPID SID SERIAL# MACHINE CLIENT_INFO Program PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------ ---------- ---------- ---------------- -------------------- ------------------------------ ------------ ------------- -----------
2290 1014 34111 H3C-APM 192.168.101.212 JDBC Thin Client 6862056397 7554874813 7555005885
1811 855 22839 gxqyydg4 192.168.100.78 sqlplus@gxqyydg4 (TNS V1-V3) 2603811029 2875538877 2881830333
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
15102 977 9825 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (LNS1) 38893197 79486477 79486477
26437 1089 3 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (ARC0) 22414453 47438477 48683661
26332 1101 1 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (LGWR) 10946301 28613773 33987725
26439 1086 3 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (ARC1) 10814885 24238733 25483917
26342 1096 1 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (MMON) 1770773 3662333 3793405
26346 833 21702 192.168.100.51 w3wp.exe 933309 933309 1761861
26346 835 18802 192.168.100.51 w3wp.exe 933309 933309 1761861
26346 839 27183 192.168.100.51 w3wp.exe 933309 933309 1761861
10 rows selected.
--//看看那個進程消耗很大的PGA。
--//註意看下劃線是我診斷的連接進程.我不可能怎麼沒做就使用這麼多PGA,一定是連接上共用模式的進程導致的問題.
--//Program=w3wp.exe的spid=26346,說明也是共用模式連接的進程.
--//後記:我這裡分析有錯!!實際上spid=2290進程占用的pga記憶體可能是多個回話共用的,因為當前僅僅1個連上,其他用戶估計很難登陸上.
--//如果多個用戶使用共用模式連上該進程,後面的PGA_USED_MEM都都等於6862056397.看看spid=26346,就很容易明白了.
--//也就是我寫pga.sql腳本查詢涉及共用模式使用pga的情況有點問題.
# ps -fp 2290,1811,26346
UID PID PPID C STIME TTY TIME CMD
oracle 1811 1 0 2022 ? 08:41:28 ora_s002_icare
oracle 2290 1 0 2022 ? 16:15:39 ora_s000_icare
oracle 26346 1 54 2021 ? 391-21:33:26 ora_d000_icare
--//都是共用模式相關的進程.
--//後記:這裡出現一個ora_d000_icare進程,我當時並沒有多想,實際上應用程式的執行卡在這裡,還停留在ora_d000_icare進程,正常成
--//功的化,應該出現的是ora_s00N_icare的進程.當時沒有考慮周全,經驗不足.
--//如果安裝smem軟體包，可以執行如下：
# smem -tk -U oracle -P "ora_|oracleicare"
--//檢查內核參數/proc/sys/vm/max_map_count：
# cat /proc/sys/vm/max_map_count
65530
--//來自鏈接 https://www.josip-pojatina.com/en/bulk-collect-and-memory-limits/
To increase a number of the map entries from 4 GB (65530 x 64 / 1024 to get the amount in GB) into the 16 GB, you need
to execute the following command as a root user:
--//65530*64/1024 = 4095.62500000000000000000
--//2^16 = 65536
--//如何知道每個64K?
# sysctl -w vm.max_map_count=262144
vm.max_map_count = 262144
--//這個我沒有執行,目前不是這方面的問題.
--//順便查看ash的情況:
[email protected]:1521/icare:DEDICATED> @ tpt/ash/ashtop10 sql_id 1=1 &day
Total
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
791 .0 37% | 2023-08-09 08:58:00 2023-08-10 08:52:13
200 .0 9% | 8szmwam7fysa3 2023-08-09 22:00:06 2023-08-09 22:03:26
154 .0 7% | 93jgxvdzsx4y1 2023-08-09 08:57:58 2023-08-10 08:27:37
93 .0 4% | 5r14h528vkacs 2023-08-09 08:58:03 2023-08-10 08:27:41
47 .0 2% | b0nsh8xz40nhr 2023-08-09 08:58:01 2023-08-10 08:27:38
42 .0 2% | 19nrxkxw2b8j1 2023-08-09 08:58:07 2023-08-10 08:27:44
30 .0 1% | dqxfcr2c8265u 2023-08-09 09:06:36 2023-08-10 08:14:24
30 .0 1% | g97w6fq8yw5br 2023-08-09 08:58:11 2023-08-10 08:27:48
29 .0 1% | gy2ncu3j1x25j 2023-08-09 09:56:59 2023-08-10 08:44:33
25 .0 1% | 6q6tgwwt6bku9 2023-08-09 09:58:02 2023-08-10 08:27:40
23 .0 1% | 7dsgq5k08yymk 2023-08-09 12:28:06 2023-08-10 08:27:47
22 .0 1% | 6wcywxctbbvs8 2023-08-09 11:17:26 2023-08-10 06:43:46
21 .0 1% | 27m1sf1nknfz2 2023-08-09 08:58:10 2023-08-10 06:27:48
21 .0 1% | 39nd965zn01yq 2023-08-09 09:26:37 2023-08-10 07:44:18
19 .0 1% | 3vsh63tqr0sr3 2023-08-09 08:54:35 2023-08-10 07:41:41
15 rows selected.
[email protected]:1521/icare:DEDICATED> @ tpt/ash/ashtop10 event 1=1 &day
Total
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN
--------- ------- ------- ----------------------------- ------------------- -------------------
1053 .0 50% | 2023-08-09 08:56:29 2023-08-10 08:52:13
412 .0 19% | db file sequential read 2023-08-09 08:57:59 2023-08-10 08:48:57
245 .0 12% | log file parallel write 2023-08-09 08:58:00 2023-08-10 08:52:09
139 .0 7% | control file parallel write 2023-08-09 09:14:32 2023-08-10 08:51:24
93 .0 4% | log file sync 2023-08-09 08:58:25 2023-08-10 08:52:09
86 .0 4% | control file sequential read 2023-08-09 08:58:04 2023-08-10 08:14:24
26 .0 1% | null event 2023-08-09 09:30:51 2023-08-10 08:41:22
23 .0 1% | LNS wait on SENDREQ 2023-08-09 09:13:47 2023-08-10 08:41:36
14 .0 1% | log file sequential read 2023-08-09 10:05:24 2023-08-10 08:42:45
11 .0 1% | os thread startup 2023-08-09 09:00:13 2023-08-10 00:00:49
10 .0 0% | db file scattered read 2023-08-09 22:00:19 2023-08-09 22:00:59
3 .0 0% | db file parallel read 2023-08-09 16:31:31 2023-08-10 08:52:09
3 .0 0% | log file switch completion 2023-08-09 22:00:05 2023-08-10 08:42:42
2 .0 0% | Log archive I/O 2023-08-09 09:15:04 2023-08-09 12:30:26
1 .0 0% | latch free 2023-08-10 07:03:11 2023-08-10 07:03:11
15 rows selected.
--//資料庫性能基本沒有怎麼問題.
3.臨時解決方法:
--//先停止192.168.101.212 上的應用.修改配置連接參數加上
(SERVER = DEDICATED)
--//192.168.100.51 類似.其他地址類似.
--//剩下的是如何回收記憶體的工作.
[email protected]:1521/icare:DEDICATED> alter system kill session '1014,34113' immediate;
System altered.
# free -m
total used free shared buffers cached
Mem: 15953 15781 172 0 3 5002
-/+ buffers/cache: 10775 5178
Swap: 15999 15999 0
--//前面的執行情況,為了對比方便我複製下來:
# free -m
total used free shared buffers cached
Mem: 15953 15775 178 0 4 5003
-/+ buffers/cache: 10767 5186
Swap: 15999 15999 0
--//昏基本沒有任何改變.
[email protected]:1521/icare:DEDICATED> @ pga 10
NAME VALUE_MB
------------------------------ ----------
aggregate PGA target parameter 3072
total PGA inuse 17656.9668
total PGA allocated 19554.3701
over allocation count 18031763
PROFILE CNT PERCENTAGE
-------------------------------- ---------- ----------
workarea executions - optimal 74204980 100
workarea executions - onepass 89374 0
workarea executions - multipass 3 0
SPID SID SERIAL# MACHINE CLIENT_INFO Program PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------ ---------- ---------- --------------- -------------------- ------------------------------ ------------ ------------- -----------
15102 977 9825 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (LNS1) 38893197 79486477 79486477
26437 1089 3 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (ARC0) 22414453 47438477 48683661
26332 1101 1 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (LGWR) 10946301 28613773 33987725
26439 1086 3 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (ARC1) 10814885 24238733 25483917
26342 1096 1 DZBL_DB_101.132 oracle@DZBL_DB_101.132 (MMON) 1770773 3662333 3793405
11755 855 22871 gxqyydg4 192.168.100.78 sqlplus@gxqyydg4 (TNS V1-V3) 1617021 2220613 5497413
26346 833 21702 192.168.100.51 w3wp.exe 933309 933309 1761861
26346 835 18802 192.168.100.51 w3wp.exe 933309 933309 1761861
26346 839 27183 192.168.100.51 w3wp.exe 933309 933309 1761861
26346 840 28987 192.168.100.51 w3wp.exe 933309 933309 1761861
10 rows selected.
--//後記:這是我才反應過來前面alter system kill session '1014,34113' immediate;不可能完全回收s00N占用的記憶體的.我必須
--//kill對應的進程.這裡也說明一點,如果這個時候使用pag.sql腳本查詢,很難判斷問題在那裡.
--//先看看那些進程占用記憶體.
[root@DZBL_DB_101 app]# top
top - 09:30:22 up 762 days, 13:58, 5 users, load average: 1.08, 1.14, 1.10
Tasks: 271 total, 2 running, 269 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.9%us, 4.4%sy, 0.0%ni, 92.4%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16336568k total, 16169608k used, 166960k free, 4940k buffers
Swap: 16383992k total, 16383992k used, 0k free, 5137576k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26346 oracle 20 0 6292m 313m 311m R 100.0 2.0 564370:57 oracle
2792 root 20 0 154m 27m 1816 S 5.9 0.2 25322:45 Xorg
10132 root 20 0 4470m 1.1g 2568 S 2.0 6.8 30280:47 gnome-system-mo
1 root 20 0 19244 896 676 S 0.0 0.0 1:23.96 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.04 kthreadd
--//不熟悉top的使用,如果安裝我熟悉的smem,也許問題定位會更快一些.實際上預設按照CPU 使用排序的.spid=26346,CPU使用率100%,實
--//際上可能通過共用模式登陸還是報錯.從以上輸出可以發現啟動到圖形模式5,可以回到多用戶模式3,回收一些記憶體,切換到多用戶模式
--//,不啟動圖形模式X11,註意同時修改/etc/inittab文件:
--//id:3:initdefault:
[root@DZBL_DB_101 app]# who -r
run-level 5 2021-07-08 19:31
[root@DZBL_DB_101 app]# init 3
[root@DZBL_DB_101 app]# who -r
run-level 3 2023-08-10 09:33 last=5
# free -m
total used free shared buffers cached
Mem: 15953 14582 1371 0 9 5068
-/+ buffers/cache: 9503 6449
Swap: 15999 12577 3422
--//回收了一些記憶體.swap free增加3422M.
[root@DZBL_DB_101 app]# top
top - 09:57:24 up 762 days, 14:06, 2 users, load average: 1.03, 1.33, 1.23
Tasks: 225 total, 2 running, 223 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.9%us, 4.4%sy, 0.0%ni, 92.4%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16336568k total, 14947360k used, 1389208k free, 11068k buffers
Swap: 16383992k total, 12879480k used, 3504512k free, 5199580k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26346 oracle 20 0 6292m 313m 311m R 100.0 2.0 564378:32 oracle
3956 root 20 0 1394m 30m 3992 S 2.0 0.2 0:41.88 infoeyeagent_mq
1 root 20 0 19244 992 752 S 0.0 0.0 1:24.00 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.04 kthreadd
--//spid=26346還是占用許多記憶體,分析有誤,實際上它不是占用最多的.
--//後記:我這裡再次犯了嚴重錯誤,沒有按照記憶體使用排序,如果排序能很快定位問題.不過我當時想既然spid=26346進程對應
--//ora_d000_icare,kill -9應該下麵ora_s00N_icare進程也會一起kill掉,重新建立新的進程.
--//再嘗試前,我想看看先kill對應的session如何.
# ps -fp 26346
UID PID PPID C STIME TTY TIME CMD
oracle 26346 1 54 2021 ? 391-22:19:20 ora_d000_icare
--//後記:實際上這時我才想起共用連接不應該停在這裡,實際上前面的w3wp.exe執行卡在這裡,無法繼續下去.
--//取消kill這些連接應該沒有任何問題,註實際上很多,差不多有1XX個.
[email protected]:1521/icare:DEDICATED> alter system kill session '833,21702,@1' immediate -- ICARE@ (w3wp.exe);
alter system kill session '833,21702,@1' immediate -- ICARE@ (w3wp.exe)
*
ERROR at line 1:
ORA-00026: missing or invalid session ID
[email protected]:1521/icare:DEDICATED> alter system kill session '833,21702' immediate -- ICARE@ (w3wp.exe);
System altered.
--//昏!10g版本竟然不支持@1這樣的寫法.
[email protected]:1521/icare:DEDICATED> @ killi program='w3wp.exe'
COMMANDS_TO_VERIFY_AND_RUN
--------------------------------------------------------------------------------------
alter system kill session '1010,44238' immediate -- ICARE@WORKGROUP\DELL70 (w3wp.exe);
...
--//拷貝然後執行,再查看:
[root@DZBL_DB_101 app]# free -m
total used free shared buffers cached
Mem: 15953 14722 1230 0 13 5136
-/+ buffers/cache: 9572 6381
Swap: 15999 12557 3442
--//變化不是很大.
top - 10:07:41 up 762 days, 14:16, 2 users, load average: 1.10, 1.20, 1.19
Tasks: 227 total, 2 running, 225 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.9%us, 4.4%sy, 0.0%ni, 92.4%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16336568k total, 15085796k used, 1250772k free, 14212k buffers
Swap: 16383992k total, 12858468k used, 3525524k free, 5262764k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26346 oracle 20 0 6292m 316m 314m R 98.9 2.0 564388:39 oracle
1 root 20 0 19244 992 752 S 0.0 0.0 1:24.02 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.04 kthreadd
--//簡單一點直接kill掉,說明一下:在執行前我看過select * from V$BGPROCESS where PADDR<>'00';的輸出,
--//ora_d000_icare,ora_s00N_icare居然沒有出現在輸出列表裡面.有點奇怪!!
[root@DZBL_DB_101 app]# kill -9 26346
[root@DZBL_DB_101 app]# free -m
total used free shared buffers cached
Mem: 15953 14812 1141 0 15 5196
-/+ buffers/cache: 9599 6353
Swap: 15999 12552 3447
--//對比前面基本沒有任何變化.
[oracle@DZBL_DB_101 ~]$ ps -ef -u oracle | grep d0[0]
oracle 25655 1 0 10:08 ? 00:00:00 ora_d001_icare
root 30812 2 0 Jan19 ? 00:00:00 [kslowd000]
root 30813 2 0 Jan19 ? 00:00:00 [kslowd001]
--//啟動1個新的d001進程.
$ lsnrctl service
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 10-AUG-2023 10:16:38
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "icare" has 2 instance(s).
Instance "icare", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1419 refused:0
LOCAL SERVER
Instance "icare", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:3043 refused:0 state:ready
LOCAL SERVER
"D001" established:7 refused:0 current:3 max:1022 state:ready
DISPATCHER <machine: DZBL_DB_101.132, pid: 25655>
(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=33030))
Service "icaredg4" has 1 instance(s).
Instance "icare", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:3043 refused:0 state:ready
LOCAL SERVER
"D001" established:7 refused:0 current:3 max:1022 state:ready
DISPATCHER <machine: DZBL_DB_101.132, pid: 25655>
(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=33030))
Service "icaredg4_XPT" has 1 instance(s).
Instance "icare", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:3043 refused:0 state:ready
LOCAL SERVER
"D001" established:7 refused:0 current:3 max:1022 state:ready
DISPATCHER <machine: DZBL_DB_101.132, pid: 25655>
(ADDRESS=(PROTOCOL=tcp)(HOST=DZBL_DB_101.132)(PORT=33030))
The command completed successfully
--//使用d001替換原來的d000.
[oracle@DZBL_DB_101 bdump]$ ps -ef | grep s0[0]
oracle 1811 1 0 2022 ? 08:41:30 ora_s002_icare
oracle 2290 1 0 2022 ? 16:15:53 ora_s000_icare
oracle 20087 1 0 2022 ? 21:44:42 ora_s001_icare
oracle 26354 1 0 2021 ? 01:23:30 ora_s003_icare
--//這才發現ora_s00N_icare進程還在並沒有刪除.
[oracle@DZBL_DB_101 bdump]$ top -u oracle
top - 10:26:51 up 762 days, 14:35, 2 users, load average: 0.01, 0.06, 0.36
Tasks: 229 total, 1 running, 228 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.9%us, 4.4%sy, 0.0%ni, 92.4%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16336568k total, 15241824k used, 1094744k free, 19756k buffers
Swap: 16383992k total, 12854104k used, 3529888k free, 5416796k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1811 oracle 20 0 9057m 3.9g 2.8g S 0.0 25.1 521:30.70 oracle
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2290 oracle 20 0 13.2g 7.4g 2.5g S 0.0 47.5 975:53.23 oracle
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5500 oracle 20 0 180m 3064 1736 S 0.0 0.0 79:26.09 tnslsnr
15102 oracle 20 0 6338m 32m 6068 S 0.0 0.2 867:40.38 oracle
20087 oracle 20 0 15.3g 4.6g 2.7g S 0.0 29.3 1304:42 oracle
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
23203 oracle 20 0 6291m 33m 28m S 0.0 0.2 0:00.08 oracle
25655 oracle 20 0 6290m 19m 15m S 0.0 0.1 0:00.07 oracle
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ =>d0001
26324 oracle 20 0 6290m 14m 13m S 0.0 0.1 67:26.29 oracle
26326 oracle 20 0 6289m 3588 3440 S 0.0 0.0 30:28.83 oracle
26328 oracle 20 0 6289m 943m 942m S 0.0 5.9 60:37.91 oracle
26330 oracle 20 0 6302m 1.7g 1.7g S 0.0 11.0 91:47.32 oracle
26332 oracle 20 0 6312m 19m 18m S 0.0 0.1 114:29.21 oracle
26334 oracle 20 0 6295m 193m 188m S 0.0 1.2 197:47.28 oracle
26336 oracle 20 0 6292m 1.2g 1.2g S 0.0 7.7 17:08.92 oracle
26338 oracle 20 0 6289m 19m 19m S 0.0 0.1 0:10.50 oracle
26340 oracle 20 0 6296m 152m 147m S 0.0 1.0 115:34.77 oracle
26342 oracle 20 0 6294m 225m 222m S 0.0 1.4 137:20.15 oracle
26344 oracle 20 0 6289m 34m 34m S 0.0 0.2 638:24.48 oracle
26354 oracle 20 0 6290m 1.6g 1.6g S 0.0 10.3 83:30.18 oracle
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
26437 oracle 20 0 6315m 17m 6324 S 0.0 0.1 10:20.44 oracle
26439 oracle 20 0 6304m 16m 5456 S 0.0 0.1 5:52.89 oracle
26455 oracle 20 0 6289m 8480 8320 S 0.0 0.1 9:42.74 oracle
26522 oracle 20 0 6291m 31m 30m S 0.0 0.2 0:03.41 oracle
26950 oracle 20 0 6291m 134m 129m S 0.0 0.8 0:00.39 oracle
30800 oracle 20 0 6289m 3832 3700 S 0.0 0.0 4:56.27 oracle
31446 oracle 20 0 105m 1900 1420 S 0.0 0.0 0:00.02 bash
31831 oracle 20 0 6290m 30m 26m S 0.0 0.2 0:00.03 oracle
32310 oracle 20 0 15072 1204 812 R 0.0 0.0 0:00.00 top
--//昏,沒有按照記憶體使用排序,可以發現spid=1811,2290,20087,26354(註意坎%MEM列) 占用很大記憶體.
--//後記:自己對top使用的一些細節實在太不熟悉,還是最近幹活太少的原因.另外寫一篇使用top如何按照記憶體使用排序的文章.
--//順便說一下按c可以可以看到COMMAND更加詳細的內容.
--//找同事要安裝盤沒找到,管理混亂,想安裝自己熟悉的smem包看看,6.0版本在網上已經很難找到,安裝1個rh5的版本smem,居然可以安裝
--//成功.
[root@DZBL_DB_101 rpm_tgz]# smem -tk -U oracle -P "ora_|oracleicare"
PID User Command Swap USS PSS RSS
26326 oracle ora_psp0_icare 3.2M 160.0K 319.0K 3.5M
30800 oracle ora_q002_icare 3.3M 164.0K 328.0K 3.7M
26455 oracle ora_qmnc_icare 3.3M 200.0K 698.0K 8.3M
26324 oracle ora_pmon_icare 3.2M 628.0K 1.8M 14.0M
26338 oracle ora_reco_icare 3.7M 372.0K 1.9M 19.6M
26522 oracle ora_q000_icare 3.6M 1.0M 4.1M 31.7M
26332 oracle ora_lgwr_icare 5.5M 4.7M 6.4M 19.1M
25655 oracle ora_d001_icare 0 4.4M 7.2M 19.8M
3510 oracle oracleicare (LOCAL=NO) 0 4.7M 9.6M 36.5M
26344 oracle ora_mmnl_icare 3.1M 1.2M 10.7M 34.4M
31831 oracle oracleicare (LOCAL=NO) 0 4.9M 10.9M 48.9M
26439 oracle ora_arc1_icare 3.5M 11.2M 11.5M 16.5M
26437 oracle ora_arc0_icare 14.3M 11.9M 12.4M 17.9M
26334 oracle ora_ckpt_icare 3.5M 4.6M 25.3M 193.0M
26340 oracle ora_cjq0_icare 4.2M 8.6M 26.8M 152.1M
15102 oracle ora_lns1_icare 3.2M 26.8M 27.2M 32.5M
26342 oracle ora_mmon_icare 3.8M 4.5M 33.5M 225.6M
26950 oracle ora_j000_icare 0 20.5M 40.6M 137.5M
26328 oracle ora_mman_icare 3.2M 44.7M 206.7M 943.1M
26336 oracle ora_smon_icare 3.3M 12.6M 237.0M 1.2G
26354 oracle ora_s003_icare 2.9M 5.8M 324.4M 1.6G
26330 oracle ora_dbw0_icare 10.0M 182.6M 558.5M 1.7G
1811 oracle ora_s002_icare 1.5G 1.2G 1.8G 3.9G
20087 oracle ora_s001_icare 7.3G 1.9G 2.5G 4.6G
2290 oracle ora_s000_icare 2.1G 4.9G 5.5G 7.4G
-------------------------------------------------------------------------------
25 1 11.1G 8.4G 11.4G 22.3G
--//註意看s00X進程占用的記憶體空間.而且還使用了大量的swap記憶體.
[email protected]:1521/icare:DEDICATED> select * from V$BGPROCESS where PADDR<>'00';
PADDR PSERIAL# NAME DESCRIPTION ERROR
---------------- ---------- ----- ------------------------------ ---------
00000001FE637BB8 1 PMON process cleanup 0
00000001FB641E88 1 PSP0 process spawner 0 0
00000001FE6383A0 1 MMAN Memory Manager 0
00000001FB642670 1 DBW0 db writer process 0 0
00000001FE63DA98 2 ARC0 Archival Process 0 0
00000001FB647D68 1 ARC1 Archival Process 1 0
00000001FB648550 1 ARC2 Archival Process 2 448
00000001FB645DC8 55 LNS1 Network Server 1 0
00000001FE63E280 1 LNS4 Network Server 4 448
00000001FE638B88 1 LGWR Redo etc. 0
00000001FB642E58 1 CKPT checkpoint 0
00000001FE639370 1 SMON System Monitor Process 0
00000001FB643640 1 RECO distributed recovery 0
00000001FE639B58 1 CJQ0 Job Queue Coordinator 0
00000001FE63E280 5 QMNC AQ Coordinator 0
00000001FB643E28 1 MMON Manageability Monitor Process 0
00000001FE63A340 1 MMNL Manageability Monitor Process 0
17 rows selected.
--//居然S00N不是後臺進程應該可以kill掉.
--//後記:我在另外1台11g查看11g上有顯示.
[root@DZBL_DB_101 rpm_tgz]# kill -9 1811
[root@DZBL_DB_101 rpm_tgz]# ps -ef | grep s0[0]
oracle 2290 1 0 2022 ? 16:15:55 ora_s000_icare
oracle 14542 1 0 11:03 ? 00:00:00 ora_s002_icare
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
oracle 20087 1 0 2022 ? 21:44:42 ora_s001_icare
oracle 26354 1 0 2021 ? 01:23:30 ora_s003_icare
--//發現重新建立新的ora_s002_icare進程.
--//全部刪除ora_s00N_icare進程.
# ps -ef --no-header | grep s0[0] | awk '{print $2}' | xargs -IQ kill -9 Q
--//簡單說明:grep 過濾s0[0],使用awk取第2個欄位(就是進程號),通過xargs -IQ的模式傳參調用kill -9執行.
[root@DZBL_DB_101 rpm_tgz]# ps -ef --no-header | grep s0[0]
oracle 15769 1 0 11:08 ? 00:00:00 ora_s000_icare
oracle 15771 1 0 11:08 ? 00:00:00 ora_s001_icare
oracle 15773 1 0 11:08 ? 00:00:00 ora_s002_icare
oracle 15775 1 0 11:08 ? 00:00:00 ora_s003_icare
--//OK,已經重新啟動ora_s00N_icare進程,從時間列上也可以看出.
[root@DZBL_DB_101 rpm_tgz]# smem -tk -U oracle -P "ora_|oracleicare"
PID User Command Swap USS PSS RSS
26326 oracle ora_psp0_icare 3.2M 164.0K 333.0K 3.5M
30800 oracle ora_q002_icare 3.3M 172.0K 358.0K 3.7M
26455 oracle ora_qmnc_icare 3.3M 200.0K 836.0K 8.3M
26338 oracle ora_reco_icare 3.7M 400.0K 2.7M 19.6M
26324 oracle ora_pmon_icare 3.2M 1.2M 3.2M 14.6M
15773 oracle ora_s002_icare 0 3.2M 4.6M 16.7M
15771 oracle ora_s001_icare 0 3.2M 4.6M 16.7M
15775 oracle ora_s003_icare 0 3.2M 4.6M 16.7M
15769 oracle ora_s000_icare 0 3.2M 4.6M 16.7M
26522 oracle ora_q000_icare 3.6M 1.2M 6.3M 31.7M
25655 oracle ora_d001_icare 0 4.7M 6.7M 19.9M
26332 oracle or
