數(shù)據(jù)庫平臺(tái)：SunOS 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-Enterprise數(shù)據(jù)庫版本：8.1.5.0.0數(shù)據(jù)庫癥狀：數(shù)據(jù)庫響應(yīng)緩慢，應(yīng)用請(qǐng)求無法返回，業(yè)務(wù)操作陷于停頓，此時(shí)需要DBA介入并進(jìn)行問題診斷及故障處理。 1. 登錄數(shù)據(jù)庫進(jìn)行檢查首先我們登錄數(shù)據(jù)庫,檢查故障現(xiàn)象。經(jīng)過檢查發(fā)現(xiàn)，數(shù)據(jù)塊的所有重做日志組除current外都處于active狀態(tài)：Oracle:/oracle/oracle8>sqlplus '/ as sysdba'SQL*Plus: Release 8.1.5.0.0 - ProdUCtion on Thu Jun 23 18:56:06 2005(c) Copyright 1999 Oracle Corporation.; All rights reserved.<Connected to:Oracle8i Enterprise Edition Release 8.1.5.0.0 - ProductionWith the Partitioning and Java optionsPL/SQL Release 8.1.5.0.0 - ProductionSQL> select * from v$log; GROUP#;THREAD#; SEQUENCE#;;;BYTES;MEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1; 1;;52040331457280; 1 NO; ACTIVE;;;1.3861E+10 23-JUN-05 2; 1;;52040431457280; 1 NO; ACTIVE;;;1.3861E+10 23-JUN-05 3; 1;;52040531457280; 1 NO; ACTIVE;;;1.3861E+10 23-JUN-05 4; 1;;52040631457280; 1 NO; CURRENT;;1.3861E+10 23-JUN-05 5; 1;;52039831457280; 1 NO; ACTIVE;;;1.3860E+10 23-JUN-05 6; 1;;52039931457280; 1 NO; ACTIVE;;;1.3860E+10 23-JUN-05 7; 1;;520400; 104857600; 1 NO; ACTIVE;;;1.3860E+10 23-JUN-05 8; 1;;520401; 104857600; 1 NO; ACTIVE;;;1.3860E+10 23-JUN-05 9; 1;;520402; 104857600; 1 NO; ACTIVE;;;1.3861E+10 23-JUN-059 rows selected.SQL> / GROUP#;THREAD#; SEQUENCE#;;;BYTES;MEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1; 1;;52040331457280; 1 NO; ACTIVE;;;1.3861E+10 23-JUN-05 2; 1;;52040431457280; 1 NO; ACTIVE;;;1.3861E+10 23-JUN-05 3; 1;;52040531457280; 1 NO; ACTIVE;;;1.3861E+10 23-JUN-05 4; 1;;52040631457280; 1 NO; CURRENT;;1.3861E+10 23-JUN-05 5; 1;;52039831457280; 1 NO; ACTIVE;;;1.3860E+10 23-JUN-05 6; 1;;52039931457280; 1 NO; ACTIVE;;;1.3860E+10 23-JUN-05 7; 1;;520400; 104857600; 1 NO; ACTIVE;;;1.3860E+10 23-JUN-05 8; 1;;520401; 104857600; 1 NO; ACTIVE;;;1.3860E+10 23-JUN-05 9; 1;;520402; 104857600; 1 NO; ACTIVE;;;1.3861E+10 23-JUN-059 rows selected.我們知道，當(dāng)數(shù)據(jù)庫發(fā)生日志切換時(shí)（Log Switch）,Oracle會(huì)觸發(fā)一個(gè)檢查點(diǎn)（Checkpoint），檢查點(diǎn)進(jìn)程（Checkpoint Process，CKPT）會(huì)通知DBWR（Database?Writer）進(jìn)程去執(zhí)行寫操作。在日志文件所保護(hù)的處于Buffer cache中的臟數(shù)據(jù)（dirty buffer）未寫回磁盤之前，日志文件不能被覆蓋或重用。假如數(shù)據(jù)庫異常繁忙，或者DBWR的寫出過慢，就可能出現(xiàn)檢查點(diǎn)未完成，Oracle卻已經(jīng)用完所有日志文件的情況。在這種情況下，數(shù)據(jù)庫的日志無法生成，整個(gè)數(shù)據(jù)庫將處于停頓狀態(tài)，此時(shí)日志文件中會(huì)記錄類似如下信息：Mon Jan 23 16:11:39 2006Thread 1 cannot allocate new log, sequence 5871Checkpoint not complete Current log# 2 seq# 5870 mem# 0: +ORADG/danaly/onlinelog/group_2.260.600173851 Current log# 2 seq# 5870 mem# 1: +ORADG/danaly/onlinelog/group_2.261.600173853　檢查v$session_wait視圖，我們可以從中看到很多session處于log file switch (checkpoint incomplete) 的等待。2. 檢查DBWR進(jìn)程在本案例中，所有日志組都處于active狀態(tài)，那么顯然DBWR的寫出存在問題。接下來讓我們檢查一下DBWR的繁忙程度:SQL> !oracle:/oracle/oracle8>ps -efgrep ora_ oracle; 2273 1 0 Mar 31 ?;;;;57:40 ora_smon_hysms02 oracle; 2266 1 0 Mar 31 ?;;;;811:42 ora_dbw0_hysms02 oracle; 2264 1 16 Mar 31 ?;;;;16999:57 ora_pmon_hysms02 oracle; 2268 1 0 Mar 31 ?;;;;1649:07 ora_lgwr_hysms02 oracle; 2279 1 0 Mar 31 ?;;;;;8:09 ora_snp1_hysms02 oracle; 2281 1 0 Mar 31 ?;;;;;4:22 ora_snp2_hysms02 oracle; 2285 1 0 Mar 31 ?;;;;;9:40 ora_snp4_hysms02 oracle; 2271 1 0 Mar 31 ?;;;;15:57 ora_ckpt_hysms02 oracle; 2283 1 0 Mar 31 ?;;;;;5:37 ora_snp3_hysms02 oracle; 2277 1 0 Mar 31 ?;;;;;5:58 ora_snp0_hysms02 oracle; 2289 1 0 Mar 31 ?;;;;;0:00 ora_d000_hysms02 oracle; 2287 1 0 Mar 31 ?;;;;;0:00 ora_s000_hysms02 oracle; 2275 1 0 Mar 31 ?;;;;;0:04 ora_reco_hysms02 oracle 21023 21012 0 18:52:59 pts/65 0:00 grep ora_DBWR的進(jìn)程號(hào)是2266。使用Top命令觀察一下該進(jìn)程的CPU耗用:oracle:/oracle/oracle8>toplast pid: 21145load averages:; 3.38,; 3.45,; 3.67;;;;18:53:38725 processes: 711 sleeping, 1 running, 10 zombie, 3 on cpuCPU states: 35.2% idle, 40.1% user,; 9.4% kernel, 15.4% iowait,; 0.0% swapMemory: 3072M real, 286M free, 3120M swap in use, 1146M swap free PID USERNAME THR PRI NICE; SIZERES STATE;TIME;CPU COMMAND11855 smspf;;;1; 59;0 1355M 1321M cpu/019:32 16.52% oracle 2264 oracle;;10;0 1358M 1316M run;283.3H 16.36% oracle11280 oracle;;1; 13;0 1356M 1321M sleep79.8H; 0.77% oracle 6957 smspf;;15; 291063M14M sleep; 107.7H; 0.76% java17393 smspf;;;1; 30;0 1356M 1322M cpu/1; 833:05; 0.58% oracle29299 smspf;;;5; 58;0 8688K 5088K sleep18.5H; 0.38% fee_FTP_get21043 oracle;;1; 43;0 3264K 2056K cpu/9;0:01; 0.31% top20919 smspf;;17; 291063M17M sleep; 247:02; 0.29% java25124 smspf;;;1; 58;016M 4688K sleep;0:35; 0.25% smif_status_rec 8086 smspf;;;5; 23;021M13M sleep41.1H; 0.24% fee_file_in16009 root;;;;1; 35;0 4920K 3160K sleep;0:03; 0.21% sshd225126 smspf;;;1; 58;0 1355M 1321M sleep;0:26; 0.20% oracle 2266 oracle;;1; 60;0 1357M 1317M sleep; 811:42; 0.18% oracle11628 smspf;;;7; 59;0 3440K 2088K sleep;0:39; 0.16% sgip_client_ltz26257 smspf;;82; 59;0; 447M; 178M sleep; 533:04; 0.15% java我們注重到，2266號(hào)進(jìn)程消耗的CPU不過0.18%,顯然并不繁忙,DBWR并不繁忙，但是檢查點(diǎn)無法完成，那么我們可以判定，瓶頸就很可能出現(xiàn)在IO上。3. 檢查IO狀況 我們可以使用IOSTAT工具檢查系統(tǒng)IO狀況：gqgai:/home/gqgai>iostat -xn 3 extended device statistics r/s;w/skr/skw/s wait actv wsvc_t asvc_t; %w; %b device...... 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 c0t6d0 1.838.432.4; 281.0; 0.0; 0.7;0.016.40; 29 c0t10d0 1.838.432.4; 281.0; 0.0; 0.5;0.013.50; 27 c0t11d0 24.861.3 1432.4; 880.1; 0.0; 0.5;0.0;5.40; 26 c1t1d0 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;9.100 hurraysms02:vold(pid238) extended device statistics r/s;w/skr/skw/s wait actv wsvc_t asvc_t; %w; %b device........ 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 c0t6d0 0.3;8.3;0.347.0; 0.0; 0.1;0.0;9.208 c0t10d0 0.0;8.3;0.047.0; 0.0; 0.1;0.0;8.007 c0t11d0 11.765.3; 197.2; 522.2; 0.0; 1.6;0.020.50 100 c1t1d0 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 hurraysms02:vold(pid238) extended device statistics r/s;w/skr/skw/s wait actv wsvc_t asvc_t; %w; %b device........ 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 c0t6d0 0.313.7;2.768.2; 0.0; 0.2;0.010.90; 12 c0t10d0 0.013.7;0.068.2; 0.0; 0.1;0.0;9.60; 11 c0t11d0 11.365.390.7; 522.7; 0.0; 1.5;0.019.50; 99 c1t1d0 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 hurraysms02:vold(pid238) extended device statistics r/s;w/skr/skw/s wait actv wsvc_t asvc_t; %w; %b device........ 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 c0t6d0 0.0;8.0;0.042.7; 0.0; 0.1;0.0;9.307 c0t10d0 0.0;8.0;0.042.7; 0.0; 0.1;0.0;9.107 c0t11d0 11.065.7; 978.7; 525.3; 0.0; 1.4;0.017.70; 99 c1t1d0 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 hurraysms02:vold(pid238) extended device statistics r/s;w/skr/skw/s wait actv wsvc_t asvc_t; %w; %b device........ 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 c0t6d0 0.387.7;2.7; 433.7; 0.0; 2.2;0.024.90; 90 c0t10d0 0.088.3;0.0; 436.5; 0.0; 1.8;0.019.90; 81 c0t11d0 89.054.0; 725.4; 432.0; 0.0; 2.1;0.014.80 100 c1t1d0 0.0;0.0;0.0;0.0; 0.0; 0.0;0.0;0.000 hurraysms02:vold(pid238)在以上輸出中，我們注重到，存放數(shù)據(jù)庫的主要卷c1t1d0的繁忙程度始終處于99~100,而寫速度卻只有500K/s左右，這個(gè)速度是極為緩慢的。根據(jù)IOSTAT的手冊(cè):(%b percent of time the disk is busy (transactions in progress)Kw/s kilobytes written per second)根據(jù)我們的常識(shí)，T3盤陣通常按Char寫速度可以達(dá)到10M/s左右，以前測(cè)試過一些Tpcc指標(biāo)，可以參考：www.eygle.com/unix/Use.Bonnie.To.Test.IO.speed.htm。而正常情況下的數(shù)據(jù)庫隨機(jī)寫通常都在1~2M左右，顯然此時(shí)的磁盤已經(jīng)處于不正常狀態(tài)，經(jīng)過確認(rèn)的確是硬盤發(fā)生了損壞，Raid5的Group中損壞了一塊硬盤。經(jīng)過更換以后系統(tǒng)逐漸恢復(fù)正常。