【Oracle】資料庫hang 診斷

一 什麼是資料庫hang 1 使用者不能登入資料庫

2 資料庫不能正常工作

3 select 1 from dual 不出結果

4 不能正常完成建表操作

二 資料庫被鎖住

   1  一個或多個會話停止工作

三 如果得知資料庫hang 或者被鎖

   1 測試

   2 使用者抱怨

   3 systemstate 或者hanganalze 操作檢視被阻塞的會話

   4 一個查詢hang 住不動

   5 會話耗費了大量的cpu

   6 ora-60 錯誤出現

四  上述現象可能會在以下操作之後：

   1 schema 變動

   2 資料庫參數變動

   3 應用程式的改動

   4 資料庫更新

五  理清 issue 發生的狀況，你必須了解如下

   1 受影響的使用者

   2 導緻問題的事件的發生的順序

   3 問題是從哪裡/如何被發現的

   4 問題的表現

   5 什麼正在工作

   6 最希望的或者最能夠接受的結果是什麼

   7 你做了什麼操作來解決這個問題

六 驗證工作 

   必須對資料庫是否hang 或者lock 進行驗證 否則會導緻采取錯誤的動作。也有可能是os 的性能問題導緻資料庫表現為hang 住的！！

   select 1 from dual;

七 收集資料

  1 使用 LTOM 收集資料

  2 使用 OSW 收集系統性能資料

  3 使用 EM 

八 使用hanganalyze 和systemstate 收集資料

  DUMP程序狀态可以使用： 

    alter sessions set events 'immediate trace name processstate level '；

  或者使用：

    oradebug setmypid

    oradebug ulimit

    oradebug dump processstate

當診斷資料庫挂起時，可以使用DUMP指令轉儲整個系統狀态：

alter sessions set events 'immediate trace name systemstate level '；

或：

oradebug setmypid

oradebug ulimit

oradebug dump systemstate

如果為了擷取全面一點的資訊，可以使用Level 10。

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug dump systemstate 10

另外如果系統挂起，無法用SQL*Plus連接配接，從Oracle 10g開始，可以使用sqlplus -prelim選項強制登入，然後即可進行系統狀态資訊轉儲：

sqlplus -prelim '/ as sysdba'

oradebug unlimit;

oradebug dump systemstate 10====================================

--for 單執行個體

SQL>ORADEBUG hanganalyze

--for RAC 執行個體

SQL>ORADEBUG setmypid

SQL>ORADEBUG setinst all

SQL>ORADEBUG -g def hanganalyze  

注意：如果Level過大的話會産生大量的跟蹤檔案并影響系統的I/O性能，Oracle建議不要采用3級以上的跟蹤。

以sysdba 登入

oradebug unlimit

oradebug -g  all hanganalyze 3

oradebug -g all dump systemstate 266

--等待2min

預設搜集資料的步驟如下：

1 hanhanalyze  level 3

2 systemstate level 266

3 wait 60 sec

4 hanhanalyze  level 3

5 systemstate level 266

對于單執行個體 trace file 檔案在 本地的user_dump_desttination 

對于rac 環境 trace file 檔案在每個節點的 backgroup_dump_destination

九 擷取v$性能資料

SPOOL v_views.log;

/*set linesize 130

col "Parameter" form. a50

col "Session Value" form. a30

col "Instance Value" form. a30

*/

select a.ksppinm  "Parameter",

       b.ksppstvl "Session Value",

       c.ksppstvl "Instance Value"

  from x$ksppi a, x$ksppcv b, x$ksppsv c

  where a.indx = b.indx

   and a.indx = c.indx

  order by 1 ;

SELECT class , value, name FROM v$sysstat;

SELECT sid , id1, id2, type, lmode, request FROM v$lock;

SELECT l.latch#,

       n.name,

       h.pid,

       l.gets,

       l.misses,

       l.immediate_gets,

       l.immediate_misses,

       l.sleeps

  FROM v$latchname n, v$latchholder h, v$latch l

  WHERE l.latch# = n.latch#

   AND l.addr = h.laddr(+);

SELECT * FROM v$session_wait ORDER BY sid ;

/* repeat last query 3 times - we want to see who's repeatedly waiting*/

SPOOL OFF;

擷取了資料之後 就是分析了！！