【Oracle ASM】關于asm實例與db實例中的磁盤狀態_詳細分析過程

現象描述ITPUB個人空間O Q9g.B,c/j
操作系統：Oracle Enterprise Linux 5.5ITPUB個人空間z7f$Lu#\"f V
數據庫：oracle 10.2.0.4 RAC+ASM
%r*T4a9[x8Zd)^%i
C27042095進入到DB數據庫實例中，查詢v$asm_disk視圖中的header_status狀態為UNKNOWN。

SQL> select group_number, name, mount_status, header_status from v$asm_disk where group_number in (1,2);

GROUP_NUMBER NAME                           MOUNT_STATUS    HEADER_STATU
G+s zs"|*rcQo27042095------------ ------------------------------ -----------    ------------
;^MN+l:R!E x N0J27042095           1 DG_DATA_0000                   OPENED          UNKNOWN
1~ O.D*} _
b#o27042095           2 VOLK                           OPENED          UNKNOWN

ITPUB個人空間D']*n8AWl
進入到asm實例中，查詢v$asm_disk視圖中的 header_status狀態為MEMBER。
"q^4Ey@'l6N27042095SQL> select group_number, name, mount_status, header_status from v$asm_disk where group_number in (1,2);

GROUP_NUMBER NAME                 MOUNT_STATUS   HEADER_STATUS
^ r2|"@!H!dw.[27042095------------ -------------------- -------------- ------------------------
w_$AVz.K'T9h37042095           1 DG_DATA_0000         CACHED         MEMBER
.BAH ? H W5?+R27042095           2 VOLK                 CACHED         MEMBER

可以看到，此視圖在asm實例和db 實例中都能查詢到。在這兩個視圖中看到的HEADER_STATUS是不一樣的。
GU'p \hF27042095db 實例 中header_status返回 UNKNOWNITPUB個人空間"E;B1^Z&}3?y\
asm實例 中header_status返回MEMBER
M`b%~/|
O3J27042095另外，mount_status的值分別為“OPENED”和“CACHED”，本文就不分析了，思路相同。

分析過程

我們看看官方文檔對 v$asm_disk中的字段header_status的說明：ITPUB個人空間? i:Ns X q
http://docs.oracle.com/cd/E11882_01/server.112/e17110/dynviews_1024.htm

UNKNOWN - Automatic Storage Management disk header has not been read

MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option.

再看一下這個視圖在ASM實例和DB實例中各自的的作用及區別：

http://docs.oracle.com/cd/E11882_01/server.112/e17110/dynviews_1024.htm

接下來，我們看看這兩個v$asm_disk在內部是不是相同的，來朧去脈如何？

1、 首先檢查db和asm實例中的 v$asm_disk視圖的結構信息是否相同

--db實例

[oracle@rac1 ~]$ export ORACLE_SID=racdb1ITPUB個人空間s m6b U#aR T \o
[oracle@rac1 ~]$ sqlplus "/as sysdba"

[oracle@rac1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 15 23:08:51 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ITPUB個人空間 W-a;f A\&a I&|
Connected to:ITPUB 個人空間v2eU4_:av
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionITPUB個人空間$V)MQj n;s
With the Partitioning, Real Application Clusters, OLAP, Data Mining
*c7L@-BU o+k27042095and Real Application Testing options

SQL> desc v$asm_disk;
K'P0] Sq"r27042095 Name                                      Null?    TypeITPUB個人空間?_Mv/d
 ----------------------------------------- -------- ----------------------------
)x Q5XI^(_27042095 GROUP_NUMBER                                       NUMBERITPUB個人空間Wz+so,G
 DISK_NUMBER                                        NUMBER
$w1JP7e9i F5\27042095 COMPOUND_INDEX                                     NUMBER
8e#{.B3H_wz!n c27042095 INCARNATION                                        NUMBER
"lL%d^9~/R,FC d@27042095 MOUNT_STATUS                                       VARCHAR2(7)ITPUB個人空間_TF Op#D*xo
 HEADER_STATUS                                      VARCHAR2(12)ITPUB個人空間s2n z P)X{)n2U(P.S-_
 MODE_STATUS                                        VARCHAR2(7)
2C-fa\$]O27042095 STATE                                              VARCHAR2(8)
7B,bi1I,WKF-_27042095 REDUNDANCY                                         VARCHAR2(7)ITPUB個人空間bS \yl(KK
 LIBRARY                                            VARCHAR2(64)ITPUB個人空間9l3OjPm
 TOTAL_MB                                           NUMBER
*V2JQJ(XKy;F D27042095 FREE_MB                                            NUMBER
cAe|1D`27042095 NAME                                               VARCHAR2(30)
"mL0ro%P \b
@b8n27042095 FAILGROUP                                          VARCHAR2(30)ITPUB個人空間h`Vg)b[U9{_
 LABEL                                              VARCHAR2(31)
'Q/g.|,z]9o P27042095 PATH                                               VARCHAR2(256)ITPUB個人空間9h2F-`2nY
lz
 UDID                                               VARCHAR2(64)
s}MlIv mAN!rc27042095 PRODUCT                                            VARCHAR2(32)ITPUB個人空間kb2N j8b1R1\b oa
 CREATE_DATE                                        DATE
$s"a kHo3Z27042095 MOUNT_DATE                                         DATE
#~kg-k/}$Y I"A27042095 REPAIR_TIMER                                       NUMBER
;zR z3p?7U0T[-Y"u27042095 READS                                              NUMBERITPUB個人空間$} x?~ @2L*u9[I
 WRITES                                             NUMBERITPUB個人空間6w'L,U3J/\n(j
 READ_ERRS                                          NUMBER
P \ ^I2D27042095 WRITE_ERRS                                         NUMBER
&JDs&pyt27042095 READ_TIME                                          NUMBERITPUB個人空間sS Oa8k@
 WRITE_TIME                                         NUMBERITPUB個人空間u,br0D/oI U)Q�C
 BYTES_READ                                         NUMBER
q/Y1V!W+A;D"j27042095 BYTES_WRITTEN                                      NUMBER

--asm實例：ITPUB 個人空間*JK ?Is6@1@ X V`'r
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
sAy&Ef S d27042095[oracle@rac1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 15 23:08:26 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ITPUB個人空間aG#HUc
Connected to:ITPUB個人空間KB?KzDh(B"~
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionITPUB個人空間m z._w4vZ"p`
With the Partitioning, Real Application Clusters, OLAP, Data MiningITPUB個人空間Q
jo.aq"[c
and Real Application Testing options

SQL> desc v$asm_disk;ITPUB個人空間9|/ia3s
R:^h%q
 Name                                      Null?    Type
/D
S%AS5M#LH3S*?27042095 ----------------------------------------- -------- ----------------------------
8WY1ZMO6GlH~*g27042095 GROUP_NUMBER                                       NUMBER
d*{K_zB#_;x0s27042095 DISK_NUMBER                                        NUMBERITPUB個人空間qX1S X�Fl`'cp
 COMPOUND_INDEX                                     NUMBER
R2M1bZi c2EF27042095 INCARNATION                                        NUMBER
/Qx @ sN*b%UxD27042095 MOUNT_STATUS                                       VARCHAR2(7)
2LAk"F~$YM6p27042095 HEADER_STATUS                                      VARCHAR2(12)ITPUB個人空間0l`x?:~%`t"`
 MODE_STATUS                                        VARCHAR2(7)ITPUB個人空間 iBm_%?$J;_Wkf
 STATE                                              VARCHAR2(8)
O#RX:Qm[6C27042095 REDUNDANCY                                         VARCHAR2(7)ITPUB個人空間+B-d0aB E'x]
 LIBRARY                                            VARCHAR2(64)ITPUB個人空間 ^L[Q|.i~
 TOTAL_MB                                           NUMBERITPUB個人空間,A5TU'sWo(H
 FREE_MB                                            NUMBER
o&B#w|7xA(H |2^27042095 NAME                                               VARCHAR2(30)
w._e;Lyw|27042095 FAILGROUP                                          VARCHAR2(30)ITPUB個人空間Il {5vd!J:GO
 LABEL                                              VARCHAR2(31)
N
z"_7Rf$C f1K+[%Z27042095 PATH                                               VARCHAR2(256)ITPUB個人空間\ h-y)V9k([3D|%R
 UDID                                               VARCHAR2(64)ITPUB個人空間~ n5K;pC Ag gy^
 PRODUCT                                            VARCHAR2(32)ITPUB個人空間7Z)hT)o*S {^
 CREATE_DATE                                        DATEITPUB個人空間7KpBA Z
 MOUNT_DATE                                         DATEITPUB個人空間![,}mD9@ Qz{
 REPAIR_TIMER                                       NUMBER
M5\+K fO8b27042095 READS                                              NUMBERITPUB個人空間:SkF?/S*j!sY M
 WRITES                                             NUMBERITPUB個人空間0P6aa,o6^%g%\
 READ_ERRS                                          NUMBER
4G+t U D,m%l27042095 WRITE_ERRS                                         NUMBER
/k/V1x`0q6J#w}8s27042095 READ_TIME                                          NUMBERITPUB個人空間['K$Q],ovi^r
 WRITE_TIME                                         NUMBERITPUB個人空間uSOEj
 BYTES_READ                                         NUMBER
C�^T:{2U_Z }C27042095 BYTES_WRITTEN                                      NUMBER

從上面可知，結構信息相同。

2、 檢查db和asm實例中的v$asm_diskgroup視圖的底層表及相關信息

可以采取以下兩種方法：

第一：通過 sql_trace或event事件。
{ i8Ya+[;`27042095第二：使用autotrace功能。

--db實例：（以autotrace為例來說明）

SQL> set autotrace on
!J-pj)B\27042095--dbITPUB個人空間 \ Q u/h:XK_#\+y+r@
SQL> select group_number, name, mount_status, header_status from v$asm_disk where header_status='UNKNOWN';

GROUP_NUMBER NAME                           MOUNT_S HEADER_STATUITPUB個人空間Id0o|+["I
------------ ------------------------------ ------- ------------ITPUB個人空間T
Z'G(@sP)EMo
           1 DG_DATA_0000                   OPENED  UNKNOWN
?c
\Wzt27042095           2 VOLK                           OPENED  UNKNOWN

--asm:

SQL> select group_number, name, mount_status, header_status from v$asm_disk where header_status='MEMBER;

SQL> select group_number, name, mount_status, header_status from v$asm_disk where header_status='MEMBER';

GROUP_NUMBER NAME                 MOUNT_STATUS   HEADER_STATUSITPUB個人空間0^/zk L0O3u`,v
------------ -------------------- -------------- ------------------------
M8zb+Xi;W:KE27042095           3 DG_RECOVERY_0000     CACHED         MEMBERITPUB 個人空間Wb\V#[ N#F
           1 DG_DATA_0000         CACHED         MEMBER
J4N,R\S
h"BH'U t27042095           4 VOLG                 CACHED         MEMBERITPUB個人空間8iQ;} H\(\3r!c
\
           4 VOLH                 CACHED         MEMBERITPUB個人空間:[@/L q]z `
           2 VOLK                 CACHED         MEMBER

5Qm;WD"G_w1~ p-Th37042095Execution Plan
8C[;P:mV:}5~ A^d27042095----------------------------------------------------------
,g"c3D'PXC2k27042095Plan hash value: 2910262982

-----------------------------------------------------------------------------ITPUB個人空間%]R!~:qD�p,G
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
4k OP&j_3K.~9}+k(~27042095-----------------------------------------------------------------------------
m7STF;X%E|`27042095|   0 | SELECT STATEMENT  |         |     1 |    77 |     1 (100)| 00:00:01 |ITPUB個人空間XD+ah5oI:g
|*  1 |  HASH JOIN OUTER  |         |     1 |    77 |     1 (100)| 00:00:01 |ITPUB個人空間�C)m!RY ]
|*  2 |   FIXED TABLE FULL| X$KFDSK |     1 |    73 |     0   (0)| 00:00:01 |ITPUB個人空間"u
`k[#l:|
E5R
|   3 |   FIXED TABLE FULL| X$KFKID |   100 |   400 |     0   (0)| 00:00:01 |
-OR)RM)Nx1L27042095-----------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB 個人空間PX xi1\\
---------------------------------------------------

   1 - access("D"."KFKID_KFDSK"="K"."IDPTR_KFKID"(+))ITPUB個人空間n_/yOja
   2 - filter("D"."MNTSTS_KFDSK"<>0 ANDITPUB 個人空間;IJ gH[:^(f
              DECODE("D"."HDRSTS_KFDSK",1,'UNKNOWN',2,'CANDIDATE',3,'MEMBER',4,'FORMERITPUB個人空間8ry _9|C:hQ
              ',5,'CONFLICT',6,'INCOMPATIBLE',7,'PROVISIONED',8,'FOREIGN','INVALID')='ITPUB個人空間@#wq/U&b/b
dL
              UNKNOWN' AND "D"."INST_ID"=USERENV('INSTANCE'))ITPUB個人空間 {Z%mu$g
    

//從這里可以看出來，v$asm_disk是由oracle的內部表X$KFDSK與X$KFKID關聯由來，而 HEADER_STATUS的狀態是由X$KFDSK中的數字1-8分別獲取。

// 如果使用sql_trace和10046事件都可查出底層表。可參數“關于asm實例與db實例中的_磁盤組狀態_的分析(20120215)”一文。

從上面分析發現，asm和db實例中的v$asm_disk視圖均是來自個Oracle內部表X$KFDSK與 X$KFKID的關聯。

下面分別在ASM和db實例中查詢一下X$KFDSK 的內容，看是否有異同之處：

經過查詢，兩個基礎表的內容果容不同，那么我們查出v$asm_disk的HEADER_STATUS狀態，就是在這里的HDRSTS_KFDSK調用了。

--db實例：ITPUB個人空間 l*NS
m,V W Tf2H/j
SQL> SQL> select GRPNUM_KFDSK, ASMNAME_KFDSK, HDRSTS_KFDSK from X$KFDSK where GRPNUM_KFDSK in (1,2) ;

GRPNUM_KFDSK ASMNAME_KFDSK                  HDRSTS_KFDSK
glY3x `Z+gX27042095------------ ------------------------------                         ------------
~7\}Kw"g27042095           1 DG_DATA_0000                               1ITPUB個人空間 x.JauKVWa#e`o
           2 VOLK                                        1

ITPUB個人空間8x`z;\ J6HV3M
--asm實例：

SQL> select GRPNUM_KFDSK, ASMNAME_KFDSK, HDRSTS_KFDSK from X$KFDSK where GRPNUM_KFDSK in (1,2) ;

GRPNUM_KFDSK ASMNAME_KFDSK                         HDRSTS_KFDSKITPUB個人空間$N FjOL%fD&Ms
------------ ------------------------------------------------------------            ------------ITPUB個人空間X&F.dc(sn'J
           1 DG_DATA_0000                                      3
.|-c0o bv8_2Mv27042095           2 VOLK                                               3

ITPUB個人空間x c7k&mA
這里的結果，正好對應上面autotrace的結果1,'UNKNOWN'',3,'MEMBER'，既然知道 v$asm_disk的HEADER_STATUS狀態的底層調用，那么“1,'UNKNOWN'',3,'MEMBER'”這些數字與狀態之關的關系如何得來呢，當X$KFDSK的HDRSTS_KFDSK為1時，那么通過什么過程讓v$asm_disk的HEADER_STATUS顯示為 “UNKNOWN'”呢，繼續往下看：

a$T$GW,Ar�r27042095--db實例：

SQL> select view_definition from v$fixed_view_definition where view_name='V$ASM_DISK';

select   group_number, disk_number, compound_index, incarnation,   mount_status,
0k!IMj+Iy/P.RE{27042095 header_status, mode_status, state, redundancy, library,   total_mb, free_mb, naITPUB個人空間%C0G(F%rSN/c,h(u
me, failgroup, label, path, udid, product,   create_date, mount_date, repair_timITPUB個人空間;` zC-a/V(j;gm
er, reads, writes, read_errs,   write_errs, read_time, write_time, bytes_read, bITPUB個人空間#pW)]Y3S/z
f,L
ytes_written  from gv$asm_disk  where inst_id = USERENV('Instance')

這里可以看出v$asm_disk視圖是由gv$asm_diskg視圖創建。ITPUB個人空間m [\ W r*IQ
//在這里說明"INST_ID"=USERENV('INSTANCE')),這是獲取當前實例環境下的信息，v$與 gv$區別就在這里。

SQL> select view_definition from v$fixed_view_definition where view_name='GV$ASM_DISK';

VIEW_DEFINITIONITPUB個人空間&m?+\dS0J
-------------------------------------------------------------------------------------------------------------------------------
3@6V/y;t$|8a$E27042095select   d.inst_id, d.grpnum_kfdsk, d.number_kfdsk,   d.compound_kfdsk, d.incarn_kfdsk,   decode(d.mntsts_kfdsk,    1, 'MISSING', 2, 'CLOSED', 3, 'OPENED', 4, 'CACHED',    5, 'IGNORED', 6, 'IGNORED', 7, 'CLOSING', 'INVALID'),   decode(d.hdrsts_kfdsk,    1, 'UNKNOWN', 2, 'CANDIDATE', 3, 'MEMBER', 4, 'FORMER',    5, 'CONFLICT', 6, 'INCOMPATIBLE', 7, 'PROVISIONED', 8, 'FOREIGN',    'INVALID'),   decode(d.mode_kfdsk,    0, 'UNKNOWN', 4, 'OFFLINE', 5, 'PROTECT', 6, 'PENDING',    7, 'ONLINE', 'INVALID'),   decode(d.state_kfdsk,    1, 'UNKNOWN', 2, 'NORMAL', 3, 'FAILING', 4, 'DROPPING',    5, 'HUNG', 6, 'FORCING', 7, 'DROPPED', 8, 'ADDING', 'INVALID'),   decode(d.redun_kfdsk,        16, 'UNPROT', 17, 'UNPROT', 18, 'MIRROR', 19, 'MIRROR',    20, 'MIRROR', 21, 'MIRROR', 22, 'MIRROR', 23, 'MIRROR',    32, 'PARITY', 33, 'PARITY', 34, 'PARITY', 35, 'PARITY',        36, 'PARITY', 37, 'PARITY', 38, 'PARITY', 39, 'PARITY',    'UNKNOWN'),   d.libnam_kfdsk, d.totmb_kfdsk, d.totmb_kfdsk - d.usedmb_kfdsk,   d.asmname_kfdsk, d.failname_kfdsk, d.label_kfdsk, d.path_kfdsk,   d.udid_kfdsk, d.product_kfdsk, d.crdate_kfdsk, d.mtdate_kfdsk,   d.timer_kfdsk,   k.read_kfkid, k.write_kfkid, k.rerr_kfkid, k.werr_kfkid,   k.rtime_kfkid/1000000, k.wtime_kfkid/1000000,   k.bytesr_kfkid, k.bytesw_kfkid  from x$kfdsk d, x$kfkid k  where d.mntsts_kfdsk != 0 and d.kfkid_kfdsk = k.idptr_kfkid(+) and GRPNUM_KFDSK in (1,2);
;`*}�UI�t-N1x27042095 
^ P:V7Zqt9@27042095--asm實例:

SQL> select view_definition from v$fixed_view_definition where view_name='V$ASM_DISK';

VIEW_DEFINITIONITPUB個人空間2F3`O&Y6D'J
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ITPUB個人空間3G*EVn%PkM#if
select   group_number, disk_number, compound_index, incarnation,   mount_status, header_status, mode_status, state, redundancy, library,   total_mb, free_mb, name, failgroup, label, path, udid, produc
pDN/n2R2M3^27042095t,   create_date, mount_date, repair_timer, reads, writes, read_errs,   write_errs, read_time, write_time, bytes_read, bytes_written  from gv$asm_disk      where inst_id = USERENV('Instance')

這里可以看出v$asm_disk視圖是由gv$asm_disk視圖創建。

SQL> select view_definition from v$fixed_view_definition where view_name='GV$ASM_DISK';

VIEW_DEFINITIONITPUB個人空間g@8|-x r's YW
-------------------------------------------------------------------------------------------------------------------------------ITPUB個人空間�B]|)F bW
select   d.inst_id, d.grpnum_kfdsk, d.number_kfdsk,   d.compound_kfdsk, d.incarn_kfdsk, decode(d.mntsts_kfdsk,   1, 'MISSING', 2, 'CLOSED', 3, 'OPENED', 4, 'CACHED',    5, 'IGNORED', 6, 'IGNORED',7, 'CLOSING', 'INVALID'),  decode(d.hdrsts_kfdsk,    1, 'UNKNOWN', 2, 'CANDIDATE', 3, 'MEMBER', 4, 'FORMER',    5, 'CONFLICT', 6,'INCOMPATIBLE', 7, 'PROVISIONED', 8, 'FOREIGN',    'INVALID'),   decode(d.mode_kfdsk,    0, 'UNKNOWN', 4,'OFFLINE', 5, 'PROTECT', 6, 'PENDING',    7, 'ONLINE', 'INVALID'),   decode(d.state_kfdsk,    1, 'UNKNOWN', 2,'NORMAL', 3, 'FAILING', 4, 'DROPPING',    5, 'HUNG',6, 'FORCING', 7, 'DROPPED', 8, 'ADDING', 'INVALID'),  decode(d.redun_kfdsk,    16, 'UNPROT', 17, 'UNPROT', 18, 'MIRROR', 19, 'MIRROR',    20, 'MIRROR', 21, 'MIRROR',22, 'MIRROR', 23, 'MIRROR',    32, 'PARITY', 33, 'PARITY', 34, 'PARITY', 35, 'PARITY',    36, 'PARITY', 37, 'PARITY', 38, 'PARITY', 39, 'PARITY',    'UNKNOWN'),   d.libnam_kfdsk, d.totmb_kfdsk, d.totmb_kfdsk - d.usedmb_kfdsk,   d.asmname_kfdsk, d.failname_kfdsk, d.label_kfdsk, d.path_kfdsk,   d.udid_kfdsk,d.product_kfdsk, d.crdate_kfdsk, d.mtdate_kfdsk,   d.timer_kfdsk,       k.read_kfkid, k.write_kfkid, k.rerr_kfkid, k.werr_kfkid,k.rtime_kfkid/1000000, k.wtime_kfkid/1000000,   k.bytesr_kfkid, k.bytesw_kfkid  from x$kfdsk d,x$kfkid k  where d.mntsts_kfdsk != 0 and d.kfkid_kfdsk = k.idptr_kfkid(+)

通過此處則可以發現，asm與db中的GV$ASM_DISK視圖又是由x$kfdsk、x$kfkid兩張基表關聯創建，而且定義都相同。

GV$ASM_DISK視圖的定義中，用到了 decode函數，這個函數則定義了1-8數字與不同狀態之間的調用關系。

如：decode 函數定義了：hdrsts_kfdsk字段如果值為1，則顯示'UNKNOWN'；如果值為3，則顯示'MEMBER'，這樣就一目了然了。

由于此處使用了decode函數，觸發條件不同則顯示不同的結果，所以導致了asm與db實例中視圖 v$asm_disk的HEADER_STATUS結果不同而已。

但是 Oracle內部又如何去修改1-8這些數據的呢，這些就很難查到了，因為X$表是Oracle數據庫的運行基礎，在數據庫啟動時由Oracle應用程序動態創建。

對于內部X$及v$視圖的限制，Oracle是通過軟件機制實現的，而并非通過數據庫權限控制，所以，實際上通常大部用戶訪問的V$對象，并不是視圖，而且是指向V_$視圖的同義詞，而V_$視圖是基于真正的V$視圖（這個視圖是基于X$表建立的）創建的。