racle 15 58 0 682M 626M sleep 0:00 0.02% oracle
25075 oracle 1 48 0 17M 7208K sleep 0:00 0.01% tnslsnr
25151 oracle 11 58 0 676M 624M sleep 0:00 0.01% oracle
25366 oracle 1 10 0 674M 628M sleep 0:00 0.00% oracle
25356 oracle 1 18 0 674M 628M sleep 0:00 0.00% oracle
25360 oracle 1 20 0 674M 628M sleep 0:00 0.00% oracle
25364 oracle 1 20 0 674M 628M sleep 0:00 0.00% oracle
25362 oracle 1 20 0 674M 628M sleep 0:00 0.00% oracle
25330 oracle 1 28 0 674M 628M sleep 0:00 0.00% oracle
发现物理内存仅为1G,free部分为34M,交换区使用了752M,仅10M free 系统内存严重不足,Swap区不足
8. 检查数据库的SGA设置
发现SGA设置为: 622299344 bytes 接近600M
wapplatform:/>su - oracle Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. /export/home1/oracle>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on 星期二 3月 23 14:02:30 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production
SQL> show sga
Total System Global Area 622299344 bytes Fixed Size 731344 bytes Variable Size 268435456 bytes Database Buffers 352321536 bytes Redo Buffers 811008 bytes SQL>
对于RAM小于1G的系统,Dedicated模式下,Oracle的SGA一般不应超过1/2物理内存.
9.第一步调整 减小SGA,为系统保留足够的内存.
10.增加swap区
wapplatform:/>df -k 文件系统 千字节 用了 可用 容量 挂接在 /dev/dsk/c0t1d0s0 3099093 105421 2931691 4% / /dev/dsk/c0t2d0s0 10325760 8359637 1862866 82% /usr /proc 0 0 0 0% /proc fd 0 0 0 0% /dev/fd mnttab 0 0 0 0% /etc/mnttab /dev/dsk/c0t1d0s3 1018382 285914 671366 30% /var swap 3904 24 3880 1% /var/run swap 3936 56 3880 2% /tmp /dev/dsk/c0t1d0s5 1671823 459202 1162467 29% /opt /dev/dsk/c0t2d0s7 7087473 6068462 948137 87% /export/home /dev/dsk/c2t1d0s7 17413250 15900222 1338896 93% /export/home2 /dev/dsk/c0t3d0s7 17413250 13749782 3489336 80% /export/home1 /dev/dsk/c0t1d0s1 771110 382410 334723 54% /usr/openwin /export/home/wapgw/luke 7087473 6068462 948137 87% /home/wap
wapplatform:/var/swap>cd /export/home1 wapplatform:/export/home1>ls TT_DB lost+found oracle oracli9 wapplatform:/export/home1>mkdir swap wapplatform:/export/home1>cd swap wapplatform:/export/home1/swap>mkfile -v 1g swapfile1 swapfile1 1073741824 bytes wapplatform:/export/home1/swap>id uid=0(root) gid=1(other) wapplatform:/export/home1/swap>swap -a /export/home1/swap/swapfile1 wapplatform:/export/home1/swap>swap -s 总数:分配了 623160k 字节 + 保留 162704k = 已使用 785864k,1010936k 可用
11.连接测试
系统恢复正常,问题解决
wapplatform:/export/home1/swap>su - oracle Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. /export/home1/oracle>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 3月 25 11:56:28 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production
SQL> exit 从Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production中断开 /export/home1/oracle>top
last pid: 5372; load averages: 0.25, 0.22, 0.29
11:57:58 148 processes: 137 sleeping, 9 zombie, 2 on cpu CPU states: 98.8% idle, 0.2% user, 0.7% kernel, 0.2% iowait, 0.0% swap Memory: 1024M real, 17M free, 824M swap in use, 934M swap free
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND 5363 root 1 58 0 2680K 1736K sleep 0:00 0.24% top 5370 oracle 1 58 0 514M 469M sleep 0:00 0.18% oracle 5366 oracle 1 28 0 514M 469M sleep 0:00 0.11% oracle 5341 oracle 1 58 0 2680K 1736K cpu/2 0:00 0.10% top 5372 oracle 1 48 0 61M 3288K cpu/3 0:00 0.06% oracle 1288 oracle 1 48 0 514M 468M sleep 5:33 0.05% oracle 607 root 12 48 0 2768K 2312K sleep 1:48 0.03% mibiisa 25075 oracle 1 48 0 17M 7208K sleep 0:16 0.02% tnslsnr 1278 oracle 15 58 0 522M 466M sleep 0:49 0.02% oracle 374 root 11 53 0 3504K 2888K sleep 0:16 0.01% nscd 1280 oracle 19 58 0 518M 466M sleep 0:28 0.00% oracle 5361 root 1 46 0 1024K 680K sleep 0:00 0.00% sleep 5362 root 1 46 0 1024K 680K sleep 0:00 0.00% sleep 5469 root 1 36 0 1952K 1176K sleep 30:09 0.00% monithttp 4167 oracle 1 40 0 515M 471M sleep 29:38 0.00% oracle
问题总结:
Oracle数据库问题的解决从来就离不开操作系统
很多时候我们必须通过操作系统一级的手段来诊断并解决问题.
关于操作系统
一般Swap区的推荐值为2XRAM 如果Ram很大,不一定非要把Swap设置为2xSwap 但是通常至少设置Swap = Ram
如果Swap区过小,在系统繁忙期间 产生大量交换无法换到磁盘,就会出现问题. 如本案例就是这样。
另外,如果系统Ram较小 通常设置SGA < 1/2 Ram
要为Server process及OS保留足够的内存空间.
上一页 [1] [2] [3] [4] [ORACLE]Oracle诊断案例-Spfile案例一则 [ORACLE]Oracle诊断案例-SGA与Swap之二 [ORACLE]Oracle诊断案例-Sql_trace之二 [ORACLE]Oracle诊断案例-Sql_trace之一 [ORACLE]ORACLE SGA 的分配 [ORACLE]Oracle诊断案例-Job任务停止执行 [ORACLE]Oracle内存结构:SGA的区域信息 [MySql]如何在Linux下增加Swap
|