[Oracle]Redo log日志組故障分析

2024-08-29 13:50:22

字體：大中小

來源：轉載

供稿：網友

數據庫平臺：SunOS 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-EnterPRise　　數據庫版本：8.1.5.0.0　　數據庫癥狀：數據庫響應緩慢，應用請求無法返回，業務操作陷于停頓，此時需要DBA介入并進行問題診斷及故障處理。　　1. 登錄數據庫進行檢查　　首先我們登錄數據庫,檢查故障現象。
　　經過檢查發現，數據塊的所有重做日志組除current外都處于active狀態：

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 - Production
With the Partitioning and java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> select * from v$log;
    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1     520403   31457280          1 NO ACTIVE              1.3861E+10 23-JUN-05
         2          1     520404   31457280          1 NO ACTIVE              1.3861E+10 23-JUN-05
         3          1     520405   31457280          1 NO ACTIVE              1.3861E+10 23-JUN-05
         4          1     520406   31457280          1 NO CURRENT             1.3861E+10 23-JUN-05
         5          1     520398   31457280          1 NO ACTIVE              1.3860E+10 23-JUN-05
         6          1     520399   31457280          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-05
9 rows selected.
SQL> /
    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1     520403   31457280          1 NO ACTIVE              1.3861E+10 23-JUN-05
         2          1     520404   31457280          1 NO ACTIVE              1.3861E+10 23-JUN-05
         3          1     520405   31457280          1 NO ACTIVE              1.3861E+10 23-JUN-05
         4          1     520406   31457280          1 NO CURRENT             1.3861E+10 23-JUN-05
         5          1     520398   31457280          1 NO ACTIVE              1.3860E+10 23-JUN-05
         6          1     520399   31457280          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-05
9 rows selected.

　　我們知道，當數據庫發生日志切換時（Log Switch）,Oracle會觸發一個檢查點（Checkpoint），檢查點進程（Checkpoint Process，CKPT）會通知DBWR（Database?Writer）進程去執行寫操作。在日志文件所保護的處于Buffer cache中的臟數據（dirty buffer）未寫回磁盤之前，日志文件不能被覆蓋或重用?！　〖偃鐢祿飚惓７泵Γ蛘逥BWR的寫出過慢，就可能出現檢查點未完成，Oracle卻已經用完所有日志文件的情況。在這種情況下，數據庫的日志無法生成，整個數據庫將處于停頓狀態，此時日志文件中會記錄類似如下信息：

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進程

　　在本案例中，所有日志組都處于active狀態，那么顯然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的進程號是2266。
　　

　　使用Top命令觀察一下該進程的CPU耗用:

oracle:/oracle/oracle8>top

last pid: 21145; load averages: 3.38, 3.45, 3.67 18:53:38
725 processes: 711 sleeping, 1 running, 10 zombie, 3 on cpu
CPU states: 35.2% idle, 40.1% user, 9.4% kernel, 15.4% iowait, 0.0% swap
Memory: 3072M real, 286M free, 3120M swap in use, 1146M swap free

上一篇：[Oracle]用函數格式化EXPLAIN PLAN

下一篇：[Oracle]進程結構和內存結構的實例