投稿
  1. 云编程开发首页
  2. 建站
  3. wordpress

【Oracle ASM】关于asm实例与db实例中的磁盘状态_详细分析过程

1524990177 • • wordpress • 阅读 290

现象描述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)^%iC27042095进入到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 UNKNOWNITPUB个人空间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.htmUNKNOWN – Automatic Storage Management disk header has not been readMEMBER – 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 2012Copyright (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 optionsSQL> desc v$asm_disk;
K’P0] Sq”r27042095Name Null? TypeITPUB个人空间?_Mv/d
—————————————– ——– —————————-
)x Q5XI^(_27042095GROUP_NUMBER NUMBERITPUB个人空间Wz+so,G
DISK_NUMBER NUMBER
$w1JP7e9i F527042095COMPOUND_INDEX NUMBER
8e#{.B3H_wz!n c27042095INCARNATION NUMBER
“lL%d^9~/R,FC d@27042095MOUNT_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$]O27042095STATE VARCHAR2(8)
7B,bi1I,WKF-_27042095REDUNDANCY VARCHAR2(7)ITPUB个人空间bS yl(KK
LIBRARY VARCHAR2(64)ITPUB个人空间9l3OjPm
TOTAL_MB NUMBER
*V2JQJ(XKy;F D27042095FREE_MB NUMBER
cAe|1D`27042095NAME VARCHAR2(30)
“mL0ro%P b@b8n27042095FAILGROUP VARCHAR2(30)ITPUB个人空间h`Vg)b[U9{_
LABEL VARCHAR2(31)
‘Q/g.|,z]9o P27042095PATH VARCHAR2(256)ITPUB个人空间9h2F-`2nYlz
UDID VARCHAR2(64)
s}MlIv mAN!rc27042095PRODUCT VARCHAR2(32)ITPUB个人空间kb2N j8b1R1b oa
CREATE_DATE DATE
$s”a kHo3Z27042095MOUNT_DATE DATE
#~kg-k/}$Y I”A27042095REPAIR_TIMER NUMBER
;zR z3p?7U0T[-Y”u27042095READS NUMBERITPUB个人空间$} x?~ @2L*u9[I
WRITES NUMBERITPUB个人空间6w’L,U3J/n(j
READ_ERRS NUMBER
P ^I2D27042095WRITE_ERRS NUMBER
&JDs&pyt27042095READ_TIME NUMBERITPUB个人空间sS Oa8k@
WRITE_TIME NUMBERITPUB个人空间u,br0D/oI U)Q�C
BYTES_READ NUMBER
q/Y1V!W+A;D”j27042095BYTES_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 2012Copyright (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个人空间Qjo.aq”[c
and Real Application Testing optionsSQL> desc v$asm_disk;ITPUB个人空间9|/ia3sR:^h%q
Name Null? Type
/DS%AS5M#LH3S*?27042095—————————————– ——– —————————-
8WY1ZMO6GlH~*g27042095GROUP_NUMBER NUMBER
d*{K_zB#_;x0s27042095DISK_NUMBER NUMBERITPUB个人空间qX1S X�Fl`’cp
COMPOUND_INDEX NUMBER
R2M1bZi c2EF27042095INCARNATION NUMBER
/Qx @ sN*b%UxD27042095MOUNT_STATUS VARCHAR2(7)
2LAk”F~$YM6p27042095HEADER_STATUS VARCHAR2(12)ITPUB个人空间0l`x?:~%`t”`
MODE_STATUS VARCHAR2(7)ITPUB个人空间 iBm_%?$J;_Wkf
STATE VARCHAR2(8)
O#RX:Qm[6C27042095REDUNDANCY 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^27042095NAME VARCHAR2(30)
w._e;Lyw|27042095FAILGROUP VARCHAR2(30)ITPUB个人空间Il {5vd!J:GO
LABEL VARCHAR2(31)
Nz”_7Rf$C f1K+[%Z27042095PATH 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 fO8b27042095READS NUMBERITPUB个人空间:SkF?/S*j!sY M
WRITES NUMBERITPUB个人空间0P6aa,o6^%g%
READ_ERRS NUMBER
4G+t U D,m%l27042095WRITE_ERRS NUMBER
/k/V1x`0q6J#w}8s27042095READ_TIME NUMBERITPUB个人空间[‘K$Q],ovi^r
WRITE_TIME NUMBERITPUB个人空间uSOEj
BYTES_READ NUMBER
C�^T:{2U_Z }C27042095BYTES_WRITTEN NUMBER从上面可知，结构信息相同。2、检查db和asm实例中的v$asm_diskgroup视图的底层表及相关信息可以采取以下两种方法：第一：通过 sql_trace或event事件。
{ i8Ya+[;`27042095第二：使用autotrace功能。–db实例：（以autotrace为例来说明）SQL> set autotrace on
!J-pj)B27042095–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个人空间TZ’G(@sP)EMo
1 DG_DATA_0000 OPENED UNKNOWN
?cWzt27042095 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 个人空间WbV#[ N#F
1 DG_DATA_0000 CACHED MEMBER
J4N,RSh”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/bdL
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*NSm,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 1ITPUB个人空间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 3ITPUB个人空间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{27042095header_status, mode_statu开发云主机域名s, 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/zf,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$表建立的）创建的。

相关推荐: dedecms按键盘方向键实现翻页功能的方法

这篇文章给大家分享的是开发云主机域名有关dedecms按键盘方向键实现翻页功能的方法的内容。小编觉得挺实用的，因此分享给大家做个参考。一起跟随小编过来看看吧。dedecms按键盘方向键怎么实现(上一篇下一篇翻页)功能？按键盘方向键实现上一篇下一篇翻页功能想必有…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。

赞 (0)
打赏 微信扫一扫 微信扫一扫
15249901771524990177
0 0
上一篇 03/16 09:24
下一篇 03/16 09:24

相关推荐