关于oracle asm实例与oracle db实例中的磁盘状态_深入分析过程

现象描述
操作系统：Oracle Enterprise Linux 5.5
数据库：oracle 10.2.0.4 RAC+ASM
进入到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
------------ ------------------------------ ----------- ------------
1 DG_DATA_0000 OPENED UNKNOWN
2 VOLK OPENED UNKNOWN

进入到asm实例中，查询v$asm_disk视图中的header_status状态为MEMBER。
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_STATUS
------------ -------------------- -------------- ------------------------
1 DG_DATA_0000 CACHED MEMBER
2 VOLK CACHED MEMBER
可以看到，此视图在asm实例和db 实例中都能查询到。在这两个视图中看到的HEADER_STATUS是不一样的。
db 实例 中header_status返回 UNKNOWN
asm实例 中header_status返回MEMBER
另外，mount_status的值分别为“OPENED”和“CACHED”，本文就不分析了，思路相同。
分析过程
我们看看官方文档对v$asm_disk中的字段header_status的说明：
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=racdb1
[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.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> desc v$asm_disk;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
DISK_NUMBER NUMBER
COMPOUND_INDEX NUMBER
INCARNATION NUMBER
MOUNT_STATUS VARCHAR2(7)
HEADER_STATUS VARCHAR2(12)
MODE_STATUS VARCHAR2(7)
STATE VARCHAR2(8)
REDUNDANCY VARCHAR2(7)
LIBRARY VARCHAR2(64)
TOTAL_MB NUMBER
FREE_MB NUMBER
NAME VARCHAR2(30)
FAILGROUP VARCHAR2(30)
LABEL VARCHAR2(31)
PATH VARCHAR2(256)
UDID VARCHAR2(64)
PRODUCT VARCHAR2(32)
CREATE_DATE DATE
MOUNT_DATE DATE
REPAIR_TIMER NUMBER
READS NUMBER
WRITES NUMBER
READ_ERRS NUMBER
WRITE_ERRS NUMBER
READ_TIME NUMBER
WRITE_TIME NUMBER
BYTES_READ NUMBER
BYTES_WRITTEN NUMBER
--asm实例：
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[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.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> desc v$asm_disk;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
DISK_NUMBER NUMBER
COMPOUND_INDEX NUMBER
INCARNATION NUMBER
MOUNT_STATUS VARCHAR2(7)
HEADER_STATUS VARCHAR2(12)
MODE_STATUS VARCHAR2(7)
STATE VARCHAR2(8)
REDUNDANCY VARCHAR2(7)
LIBRARY VARCHAR2(64)
TOTAL_MB NUMBER
FREE_MB NUMBER
NAME VARCHAR2(30)
FAILGROUP VARCHAR2(30)
LABEL VARCHAR2(31)
PATH VARCHAR2(256)
UDID VARCHAR2(64)
PRODUCT VARCHAR2(32)
CREATE_DATE DATE
MOUNT_DATE DATE
REPAIR_TIMER NUMBER
READS NUMBER
WRITES NUMBER
READ_ERRS NUMBER
WRITE_ERRS NUMBER
READ_TIME NUMBER
WRITE_TIME NUMBER
BYTES_READ NUMBER
BYTES_WRITTEN NUMBER
从上面可知，结构信息相同。
2、 检查db和asm实例中的v$asm_diskgroup视图的底层表及相关信息
可以采取以下两种方法：
第一：通过sql_trace或event事件。
第二：使用autotrace功能。
--db实例：（以autotrace为例来说明）
SQL> set autotrace on
--db
SQL> select group_number, name, mount_status, header_status from v$asm_disk where header_status='UNKNOWN';
GROUP_NUMBER NAME MOUNT_S HEADER_STATU
------------ ------------------------------ ------- ------------
1 DG_DATA_0000 OPENED UNKNOWN
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_STATUS
------------ -------------------- -------------- ------------------------
3 DG_RECOVERY_0000 CACHED MEMBER
1 DG_DATA_0000 CACHED MEMBER
4 VOLG CACHED MEMBER
4 VOLH CACHED MEMBER
2 VOLK CACHED MEMBER

Execution Plan
----------------------------------------------------------
Plan hash value: 2910262982
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 77 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 77 | 1 (100)| 00:00:01 |
|* 2 | FIXED TABLE FULL| X$KFDSK | 1 | 73 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL| X$KFKID | 100 | 400 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."KFKID_KFDSK"="K"."IDPTR_KFKID"(+))
2 - filter("D"."MNTSTS_KFDSK"<>0 AND
DECODE("D"."HDRSTS_KFDSK",1,'UNKNOWN',2,'CANDIDATE',3,'MEMBER',4,'FORMER
',5,'CONFLICT',6,'INCOMPATIBLE',7,'PROVISIONED',8,'FOREIGN','INVALID')='
UNKNOWN' AND "D"."INST_ID"=USERENV('INSTANCE'))

//从这里可以看出来，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实例：
SQL> SQL> select GRPNUM_KFDSK, ASMNAME_KFDSK, HDRSTS_KFDSK from X$KFDSK where GRPNUM_KFDSK in (1,2) ;
GRPNUM_KFDSK ASMNAME_KFDSK HDRSTS_KFDSK
------------ ------------------------------ ------------
1 DG_DATA_0000 1
2 VOLK 1

--asm实例：
SQL> select GRPNUM_KFDSK, ASMNAME_KFDSK, HDRSTS_KFDSK from X$KFDSK where GRPNUM_KFDSK in (1,2) ;
GRPNUM_KFDSK ASMNAME_KFDSK HDRSTS_KFDSK
------------ ------------------------------------------------------------ ------------
1 DG_DATA_0000 3
2 VOLK 3

这里的结果，正好对应上面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'”呢，继续往下看：

--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,
header_status, mode_status, state, redundancy, library, total_mb, free_mb, na
me, failgroup, label, path, udid, product, create_date, mount_date, repair_tim
er, reads, writes, read_errs, write_errs, read_time, write_time, bytes_read, b
ytes_written from gv$asm_disk where inst_id = USERENV('Instance')
这里可以看出v$asm_disk视图是由gv$asm_diskg视图创建。
//在这里说明"INST_ID"=USERENV('INSTANCE')),这是获取当前实例环境下的信息，v$与gv$区别就在这里。
SQL> select view_definition from v$fixed_view_definition where view_name='GV$ASM_DISK';
VIEW_DEFINITION
-------------------------------------------------------------------------------------------------------------------------------
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(+) and GRPNUM_KFDSK in (1,2);

--asm实例:
SQL> select view_definition from v$fixed_view_definition where view_name='V$ASM_DISK';
VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
t, 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_DEFINITION
-------------------------------------------------------------------------------------------------------------------------------
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$表建立的）创建的。