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

现象描述
操作系统：Oracle Enterprise Linux 5.5
数据库：oracle 10.2.0.4 RAC+ASM
进入到数据库实例中，查询v$asm_diskgroup磁盘组中的状态为connected。
SQL> select name,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DG_DATA CONNECTED
DG_GGDATA CONNECTED
DG_RECOVERY MOUNTED
DG_TEST MOUNTED
进入到asm实例中，查询v$asm_diskgroup磁盘组中的状态为mounted。
SQL> select name,state from v$asm_diskgroup;

NAME STATE
------------------------------------------------------------ ----------------------
DG_DATA MOUNTED
DG_GGDATA MOUNTED
DG_RECOVERY MOUNTED
DG_TEST MOUNTED
可以看到，此视图在asm实例和db 实例中都能查询到。在这两个视图中看到的state是不一样的。
db 实例 中state 返回 CONNECTED
asm实例 中state 返回MOUNTED

分析过程
可能看出，在数据库实例中diskgroup状态为connected，从这个状态猜测，应该是数据库实例连接了ASM的磁盘组。
我们看官方文档对v$asm_diskgroup中的字段state的说明：
http://docs.oracle.com/cd/E11882_01/server.112/e17110/dynviews_1027.htm
Column Datatype Description
STATE VARCHAR2(11) State of the disk group relative to the instance:
CONNECTED - Disk group is in use by the database instance
BROKEN - Database instance lost connectivity to the Automatic Storage Management instance that mounted the disk group
UNKNOWN - Automatic Storage Management instance has never attempted to mount the disk group
DISMOUNTED - Disk group was cleanly dismounted by the Automatic Storage Management instance following a successful mount
MOUNTED - Instance is successfully serving the disk group to its database clients
再看一下这个视图在ASM实例和DB实例中各自的的作用：
http://docs.oracle.com/cd/E11882_01/server.112/e17110/dynviews_1027.htm

接下来，我们看看这两个v$asm_diskgroup在内部是不是相同的，来胧去脉如何？
1、 首先检查db和asm实例中的v$asm_diskgroup视图的结构信息是否相同
--db实例
[oracle@rac1 ~]$ export ORACLE_SID=racdb1
[oracle@rac1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 15 13:11:24 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_diskgroup;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
UNBALANCED VARCHAR2(1)
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
--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 13:09: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_diskgroup;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
UNBALANCED VARCHAR2(1)
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
从上面可知，结构信息相同。
2、 检查db和asm实例中的v$asm_diskgroup视图的底层表及相关信息
可以采取以下两种方法：
第一：通过sql_trace或event事件。
第二：使用autotrace功能。
--db实例：（以autotrace来说明）
SQL> set autotrace on
SQL> select name,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DG_DATA CONNECTED
DG_GGDATA CONNECTED
DG_RECOVERY MOUNTED
DG_TEST MOUNTED

Execution Plan
----------------------------------------------------------
Plan hash value: 3236948515
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$KFGRP | 1 | 43 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATE_KFGRP"<>0 AND "G"."INST_ID"=USERENV('INSTANCE'))

SQL> select name,state from v$asm_diskgroup where STATE='CONNECTED';
NAME STATE
------------------------------ -----------
DG_DATA CONNECTED
DG_GGDATA CONNECTED

Execution Plan
----------------------------------------------------------
Plan hash value: 3236948515
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$KFGRP | 1 | 43 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATE_KFGRP"<>0 AND DECODE("G"."STATE_KFGRP",0,'INVALID',
1,'UNKNOWN',2,'DISMOUNTED',3,'CREATING',4,'MOUNTING',5,'MOUNTED',6,'DISM
OUNTING',7,'CONNECTED',8,'BROKEN',9,'CONNECTING',10,'BREAKING',11,'DROPP
ING')='CONNECTED' AND "G"."INST_ID"=USERENV('INSTANCE'))
//从这里可以看出来，v$asm_diskgroup是由oracle的内部表X$KFGRP由来，而且diskgroup的状态是由X$KFGRP中的数字0-11分别获取。
//如果使用sql_trace和10046事件都可查出底层表。
--asm实例：(以sql_trace、10046两种方式来说明)
SQL> alter session set sql_trace=TRUE;
Session altered.
SQL> select name,state from v$asm_diskgroup;
NAME STATE
------------------------------------------------------------ ----------------------
DG_DATA MOUNTED
DG_GGDATA MOUNTED
DG_RECOVERY MOUNTED
DG_TEST MOUNTED
SQL> select name,state from v$asm_diskgroup where STATE='CONNECTED';
no rows selected
SQL> alter session set sql_trace=FALSE;
Session altered.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> select name,state from v$asm_diskgroup;
NAME STATE
------------------------------------------------------------ ----------------------
DG_DATA MOUNTED
DG_GGDATA MOUNTED
DG_RECOVERY MOUNTED
DG_TEST MOUNTED
SQL> select name,state from v$asm_diskgroup where STATE='CONNECTED';
no rows selected
SQL> alter session set events '10046 trace name context off';
Session altered.
查看后台trace：
[oracle@rac1 udump]$ more +asm1_ora_12019.trc
usage: more [-dflpcsu] [+linenum | +/pattern] name1 name2 ...
---asm的trc文件因为有一个”+”，不能直接more，需要先复制一份再more.
[oracle@rac1 udump]$ cp +asm1_ora_12019.trc 1.trc
[oracle@rac1 udump]$ more 1.trc
/oracle/app/admin/+ASM/udump/+asm1_ora_12019.trc
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
ORACLE_HOME = /oracle/app/product/10.2.0/db_1
System name: Linux
Node name: rac1
Release: 2.6.18-194.el5
Version: #1 SMP Mon Mar 29 20:06:41 EDT 2010
Machine: i686
Instance name: +ASM1
Redo thread mounted by this instance: 0
Oracle process number: 17
Unix process pid: 12019, image: oracle@rac1 (TNS V1-V3)
*** 2012-02-15 13:36:18.348
*** ACTION NAME:() 2012-02-15 13:36:18.347
*** MODULE NAME:(sqlplus@rac1 (TNS V1-V3)) 2012-02-15 13:36:18.347
*** SERVICE NAME:() 2012-02-15 13:36:18.347
*** SESSION ID:(27.30) 2012-02-15 13:36:18.347
=====================
PARSING IN CURSOR #1 len=38 dep=0 uid=0 ct=3 lid=0 tim=1298129080417708 hv=3335781446 ad='2360fe04'
select name,state from v$asm_diskgroup
END OF STMT
PARSE #1:c=5999,e=25428,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1298129080417699
EXEC #1:c=0,e=238,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1298129080418393
FETCH #1:c=17997,e=92660,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1298129080511149
FETCH #1:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=3,dep=0,og=1,tim=1298129080511806
STAT #1 id=1 cnt=4 pid=0 pos=1 bj=0 p='FIXED TABLE FULL X$KFGRP (cr=0 pr=0 pw=0 time=92652 us)'
=====================
PARSING IN CURSOR #1 len=62 dep=0 uid=0 ct=3 lid=0 tim=1298129087448985 hv=3730836277 ad='23fd0660'
select name,state from v$asm_diskgroup where STATE='CONNECTED'
END OF STMT
PARSE #1:c=1999,e=18128,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1298129087448978
EXEC #1:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1298129087449117
FETCH #1:c=4000,e=3902,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1298129087453063
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='FIXED TABLE FULL X$KFGRP (cr=0 pr=0 pw=0 time=3903 us)'
=====================
PARSING IN CURSOR #1 len=33 dep=0 uid=0 ct=42 lid=0 tim=1298129099677792 hv=2795273217 ad='0'
alter session set sql_trace=FALSE
END OF STMT
PARSE #1:c=0,e=28677,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1298129099677787
EXEC #1:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1298129099677916
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1298129107368183
*** 2012-02-15 13:36:53.304
WAIT #1: nam='SQL*Net message from client' ela= 7186774 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1298129114555061
=====================
PARSING IN CURSOR #1 len=38 dep=0 uid=0 ct=3 lid=0 tim=1298129114555819 hv=3335781446 ad='2360fe04'
select name,state from v$asm_diskgroup
END OF STMT
PARSE #1:c=999,e=473,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1298129114555810
BINDS #1:
EXEC #1:c=0,e=284,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1298129114556199
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1298129114556252
WAIT #1: nam='rdbms ipc reply' ela= 377 from_process=13 timeout=2147483647 p3=0 obj#=-1 tim=1298129114573239
WAIT #1: nam='rdbms ipc reply' ela= 294 from_process=13 timeout=2147483647 p3=0 obj#=-1 tim=1298129114573598
WAIT #1: nam='rdbms ipc reply' ela= 84 from_process=13 timeout=2147483647 p3=0 obj#=-1 tim=1298129114669661
FETCH #1:c=14998,e=113472,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1298129114669761
WAIT #1: nam='SQL*Net message from client' ela= 6750 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1298129114676613
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1298129114676702
FETCH #1:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=3,dep=0,og=1,tim=1298129114676734
WAIT #1: nam='SQL*Net message from client' ela= 7596822 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1298129122273621
STAT #1 id=1 cnt=4 pid=0 pos=1 bj=0 p='FIXED TABLE FULL X$KFGRP (cr=0 pr=0 pw=0 time=113471 us)'
=====================
PARSING IN CURSOR #1 len=62 dep=0 uid=0 ct=3 lid=0 tim=1298129122273935 hv=3730836277 ad='23fd0660'
select name,state from v$asm_diskgroup where STATE='CONNECTED'
END OF STMT
PARSE #1:c=1000,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1298129122273929
BINDS #1:
EXEC #1:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1298129122274098
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1298129122274130
WAIT #1: nam='rdbms ipc reply' ela= 423 from_process=13 timeout=2147483647 p3=0 obj#=-1 tim=1298129122274705
WAIT #1: nam='rdbms ipc reply' ela= 279 from_process=13 timeout=2147483647 p3=0 obj#=-1 tim=1298129122275027
WAIT #1: nam='rdbms ipc reply' ela= 78 from_process=13 timeout=2147483647 p3=0 obj#=-1 tim=1298129122279336
FETCH #1:c=2000,e=5240,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1298129122279398
*** 2012-02-15 13:37:09.176
WAIT #1: nam='SQL*Net message from client' ela= 7775262 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1298129130054731
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='FIXED TABLE FULL X$KFGRP (cr=0 pr=0 pw=0 time=5241 us)'
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=0 ct=42 lid=0 tim=1298129130055208 hv=2217940283 ad='0'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=143,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1298129130055199
EXEC #1:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1298129130055321
从上面分析发现，asm和db实例中的v$asm_diskgroup视图均是来自同一个Oracle内部表X$KFGRP。
下面分别在ASM和db实例中查询一下X$KFGRP的内容，看是否有异同之处：

通过查询，两个基础表的内容果容不同，那么我们查出v$asm_diskgroup的state状态，就是在这里调用了。
--db实例
SQL> select NAME_KFGRP,STATE_KFGRP from X$KFGRP;
NAME_KFGRP STATE_KFGRP
------------------------------ -----------
0
DG_DATA 7
DG_GGDATA 7
DG_RECOVERY 5
DG_TEST 5

--asm实例：
SQL> select NAME_KFGRP,STATE_KFGRP from X$KFGRP;
NAME_KFGRP STATE_KFGRP
------------------------------------------------------------ -----------
0
DG_DATA 5
DG_GGDATA 5
DG_RECOVERY 5
DG_TEST 5
这里的结果，正好对应上面autotrace的结果5,'MOUNTED', 7,'CONNECTED',
既然知道了v$asm_diskgroup的state状态的底层调用，那么“5,'MOUNTED', 7,'CONNECTED',”这些数字与状态之关的关系如何得来呢，当X$KFGRP的STATE_KFGRP为7时，那么通过什么过程让v$asm_diskgroup的state显示为“'CONNECTED'”呢，继续往下看：

--db实例：
SQL> select view_definition from v$fixed_view_definition where view_name='V$ASM_DISKGROUP';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select group_number, name, sector_size, block_size, allocation_unit_size,state, type, total_mb,free_mb, required_mirror_free_mb, usable_file_mb, offline_disks, unbalanced, compatibility,database_compatibility from gv$asm_diskgroup where inst_id = USERENV('Instance')
这里可以看出v$asm_diskgroup视图是由gv$asm_diskgroup视图创建。
//在这里说明"INST_ID"=USERENV('INSTANCE')),这是获取当前实例环境下的信息，v$与gv$区别就在这里。
SQL> select view_definition from v$fixed_view_definition where view_name='GV$ASM_DISKGROUP';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select g.inst_id, g.number_kfgrp, g.name_kfgrp, g.sector_kfgrp, g.blksize_kfgrp, g.ausize_kfgrp, decode(g.state_kfgrp, 0, 'INVALID', 1, 'UNKNOWN', 2,'DISMOUNTED', 3, 'CREATING', 4, 'MOUNTING',5, 'MOUNTED', 6, 'DISMOUNTING',7, 'CONNECTED', 8, 'BROKEN', 9, 'CONNECTING', 10, 'BREAKING', 11, 'DROPPING'), decode(g.type_kfgrp, 1, 'EXTERN', 2, 'NORMAL', 3, 'HIGH'), g.totmb_kfgrp,g.freemb_kfgrp, g.minspc_kfgrp, g.usable_kfgrp, g.offline_kfgrp, decode(g.unbal_kfgrp, 0, 'N', 'Y'), g.compat_kfgrp, g.dbcompat_kfgrp from x$kfgrp g where state_kfgrp != 0
--asm实例:
SQL> select view_definition from v$fixed_view_definition where view_name='V$ASM_DISKGROUP';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select group_number, name, sector_size, block_size, allocation_unit_size,
state, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb, offl
ine_disks, unbalanced, compatibility, database_compatibility from gv$asm_dis
kgroup where inst_id = USERENV('Instance')

SQL> select view_definition from v$fixed_view_definition where view_name='GV$ASM_DISKGROUP';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select g.inst_id, g.number_kfgrp, g.name_kfgrp, g.sector_kfgrp, g.blksize_kf
grp, g.ausize_kfgrp, decode(g.state_kfgrp, 0, 'INVALID', 1, 'UNKNOWN', 2,
'DISMOUNTED', 3, 'CREATING', 4, 'MOUNTING', 5, 'MOUNTED', 6, 'DISMOUNTING',
7, 'CONNECTED', 8, 'BROKEN', 9, 'CONNECTING', 10, 'BREAKING', 11, 'DROPPING'
), decode(g.type_kfgrp, 1, 'EXTERN', 2, 'NORMAL', 3, 'HIGH'), g.totmb_kfgrp,
g.freemb_kfgrp, g.minspc_kfgrp, g.usable_kfgrp, g.offline_kfgrp, decode(g.u
nbal_kfgrp, 0, 'N', 'Y'), g.compat_kfgrp, g.dbcompat_kfgrp from x$kfgrp g wher
e state_kfgrp != 0
通过此处则可以发现，asm与db中的GV$ASM_DISKGROUP视图又是由x$kfgrp基表创建，而且定义都相同。
GV$ASM_DISKGROUP视图的定义中，用到了decode函数，这个函数则定义了0-11数字与不同状态之间的调用关系。
如：decode函数定义了：state_kfgrp字段如果值为5，则显示'MOUNTED'；如果值为7，则显示'CONNECTED'，这样就一目了然了。
由于此处使用了decode函数，触发条件不同则显示不同的结果，所以导致了asm与db实例的diskgroup stste结果不同而已。
但是Oracle内部又如何去修改0-11这些数字的呢，这些就很难查到了，因为X$表是Oracle数据库的运行基础，在数据库启动时由Oracle应用程序动态创建。
对于内部X$及v$视图的限制，Oracle是通过软件机制实现的，而并非通过数据库权限控制，所以，实际上通常大部用户访问的V$对象，并不是视图，而且是指向V_$视图的同义词，而V_$视图是基于真正的V$视图（这个视图是基于X$表建立的）创建的。