Shulou(Shulou.com)06/02 Report--

Phenomenon description ITPUB personal space O Q9g.BJE cUniverse j

Operating system: Oracle Enterprise Linux 5.5ITPUB personal Space z7f$Lu#\ "f V"

Database: oracle 10.2.0.4 RAC+ASM

% r*T4a9 [x8Zd) ^% iC27042095 enters the DB database instance and queries the header_status status in the v$asm_disk view as UNKNOWN.

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

GROUP_NUMBER NAME MOUNT_STATUS HEADER_STATU

Gags zs "| * rcQo27042095

; ^ MN _ blank _ N0J27042095 _ 1 _ DG_DATA_0000 OPENED UNKNOWN

1 ~ O.D*} _ b#o27042095 2 VOLK OPENED UNKNOWN

ITPUB personal Space D'] * n8AWl

Enter the asm instance and query that the header_status status in the v$asm_disk view is MEMBER.

"Q ^ 4Ey @ 'l6N27042095SQL > select group_number, name, mount_status, header_status from v$asm_disk where group_number in (1Pol 2)

GROUP_NUMBER NAME MOUNT_STATUS HEADER_STATUS

R2 | "@! Halleldw. [27042095

W_$AVz.K'T9h37042095 1 DG_DATA_0000 CACHED MEMBER

.BAH? H W5?+R27042095 2 VOLK CACHED MEMBER

As you can see, this view can be queried in both asm and db instances. The HEADER_STATUS you see in these two views is different.

Header_status in the GU'p\ hF27042095db instance returns the UNKNOWNITPUB personal space "E; B1 ^ Z &} 3roomy\"

Header_status in asm instance returns MEMBER

M`b% ~ / | O3J27042095 in addition, the values of mount_status are "OPENED" and "CACHED" respectively, so we will not analyze them in this article. The idea is the same.

Analysis process

Let's take a look at the official document's description of the field header_status in v$asm_disk: ITPUB personal space? 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.

Take another look at the role and difference of this view in ASM instances and DB instances:

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

Next, let's see if the two v$asm_disk are the same internally. How about going through the pulse?

1. First check whether the structure information of the v$asm_disk view in the db and asm instances is the same

-- db instance

[oracle@rac1] $export ORACLE_SID=racdb1ITPUB personal space 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 personal space WMurawitf A\ & an I & |

Connected to:ITPUB personal space v2eU4_:av

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-ProductionITPUB personal space $V) MQj nternal

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 personal space? _ Mv/d

-

) x Q5XI ^ (_ 27042095 GROUP_NUMBER NUMBERITPUB personal space Wz+so,G

DISK_NUMBER NUMBER

$w1JP7e9i F5\ 27042095 COMPOUND_INDEX NUMBER

8e# {. B3H_wz!n c27042095 INCARNATION NUMBER

"LL% d ^ 9 ~ / RMagneFC dice 27042095 MOUNT_STATUS VARCHAR2 (7) ITPUB personal space _ TF Op#D*xo

HEADER_STATUS VARCHAR2 (12) ITPUB personal space s2n z P) X {) n2U (P.Smuri _

MODE_STATUS VARCHAR2 (7)

2C-fa\ $] O27042095 STATE VARCHAR2 (8)

7B bS yl (KK) ITPUB personal space bS\ yl

LIBRARY VARCHAR2 (64) ITPUB personal Space 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 personal space h`Vg) b [U9 {_

LABEL VARCHAR2 (31)

'Q 2nYlz g. |, z] 9o P27042095 PATH VARCHAR2 (256) ITPUB personal space 9h2F-`2nYlz

UDID VARCHAR2 (64)

S} MlIv Manning rc27042095 PRODUCT VARCHAR2 (32) ITPUB personal space kb2N j8b1R1\ b oa

CREATE_DATE DATE

$s "a kHo3Z27042095 MOUNT_DATE DATE

# ~ kg-k/} $I "A27042095 REPAIR_TIMER NUMBER

; zR z3p?7U0T [- Y "u27042095 READS NUMBERITPUB personal space $} x space ~ @ 2L * u9 [I

WRITES NUMBERITPUB personal Space 6wimpl Magi U3J /\ n (j

READ_ERRS NUMBER

P\ ^ I2D27042095 WRITE_ERRS NUMBER

& JDs&pyt27042095 READ_TIME NUMBERITPUB personal space sS Oa8k@

WRITE_TIME NUMBERITPUB personal space u.r.br0DbinoI / U) Q

BYTES_READ NUMBER

Qmax Y1Villa Wendy BYTES_WRITTEN NUMBER D "j27042095"

-- asm instance: ITPUB personal Space * 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 personal space aG#HUc

Connected to:ITPUB personal space KB?KzDh (B "~

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-ProductionITPUB personal space m z._w4vZ "p`

With the Partitioning, Real Application Clusters, OLAP, Data MiningITPUB personal space Qjo.aq "[c

And Real Application Testing options

SQL > desc vastly available diskspace ITPUB personal space 9 | / ia3sR: ^ h% Q

Name Null? Type

/ DS%AS5M#LH3S*?27042095-

8WY1ZMO6GlH~*g27042095 GROUP_NUMBER NUMBER

D * {KroomzBauer / DISK_NUMBER NUMBERITPUB x0s27042095 DISK_NUMBER NUMBERITPUB personal space 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 personal Space 0l`x?: ~% `t" `

MODE_STATUS VARCHAR2 (7) ITPUB personal Space iBm_%?$J;_Wkf

STATE VARCHAR2 (8)

O#RX:Qm [6C27042095 REDUNDANCY VARCHAR2 (7) ITPUB personal Space + B-d0aB email]

LIBRARY VARCHAR2 (64) ITPUB personal space ^ L [Q | .i ~

TOTAL_MB NUMBERITPUB personal Space, A5TU'sWo (H

FREE_MB NUMBER

O&B#w | 7xA (H | 2 ^ 27042095 NAME VARCHAR2 (30)

W.SecreteLytw | 27042095 FAILGROUP VARCHAR2 (30) ITPUB personal space Il {5vdpm JRV go

LABEL VARCHAR2 (31)

Nz "_ 7Rf$C f1K + [% Z27042095 PATH VARCHAR2 (256) ITPUB personal space\ hmury) V9k ([3D |% R]

UDID VARCHAR2 (64) ITPUB personal space ~ n5K TipPC Ag gy^

PRODUCT VARCHAR2 (32) ITPUB personal Space 7Z) hT) ostructures {^

CREATE_DATE DATEITPUB personal Space 7KpBA Z

MOUNT_DATE DATEITPUB personal space! [,} mD9@ Qz {

REPAIR_TIMER NUMBER

M5\ + K fO8b27042095 READS NUMBERITPUB personal space: SkFallPlancesjobs.YM

WRITES NUMBERITPUB personal space 0P6aa, o6 ^% g%\

READ_ERRS NUMBER

4G+t U D WRITE_ERRS NUMBER% l27042095

/ k / V1x`0q6J # w} 8s27042095 READ_TIME NUMBERITPUB personal space ['KnowQ], ovi^ r

WRITE_TIME NUMBERITPUB personal space uSOEj

BYTES_READ NUMBER

C / T: {2U_Z} C27042095 BYTES_WRITTEN NUMBER

As can be seen from the above, the structural information is the same.

2. Check the underlying table and related information of the v$asm_diskgroup view in db and asm instances

There are two ways to do this:

First: through the sql_trace or event event.

{i8Ya+ [; `27042095 second: use the autotrace function.

-- db example: (take autotrace as an example)

SQL > set autotrace on

! J-pj) B\ 27042095--dbITPUB personal space\ Q u/h:XK_#\ + yroomr@

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

GROUP_NUMBER NAME MOUNT_S HEADER_STATUITPUB personal space Id0o | + ["I

-ITPUB personal space TZ'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 personal Space 0^ / zk L0O3u`, v

-

M8zbaked Xitern Writer KE27042095 3 DG_RECOVERY_0000 CACHED MEMBERITPUB personal space Wb\ V# [Numf

1 DG_DATA_0000 CACHED MEMBER

J4N Sh R\ Sh "BH'U t27042095 4 VOLG CACHED MEMBERITPUB personal Space 8iQ;} H\ (\ 3rsquc\

4 VOLH CACHED MEMBERITPUB personal space: [@ / L Q] z `

2 VOLK CACHED MEMBER

5Qm _ p-Th37042095Execution Plan

8C [; P:mV:} 5 ~ A ^ d27042095-

, g "c3D'PXC2k27042095Plan hash value: 2910262982

-ITPUB personal space%] Renewal QD personal space G

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

4k OP&j_3K.~9} + k (~ 27042095)

M7STF _ X% E | `STF _ X% E | `STF | 0 | SELECT STATEMENT | | 1 | 77 | 1 | 00:00:01 | ITPUB personal space XD+ah5oI:g

| | * 1 | HASH JOIN OUTER | | 1 | 77 | 1 (100) | 00:00:01 | ITPUB personal space / C) masked]

| | * 2 | FIXED TABLE FULL | X$KFDSK | 1 | 73 | 0 (0) | 00:00:01 | ITPUB personal space "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 personal space PX xi1\\

1-access ("D". "KFKID_KFDSK" = "K". "IDPTR_KFKID" (+) ITPUB personal space n_/yOja

2-filter ("D". "MNTSTS_KFDSK" 0 ANDITPUB personal space; IJ gH [: ^ (f)

DECODE ("D". "HDRSTS_KFDSK", 1 minus Unknowne page2, 8ry 3, MEMBERB4, formeritpub, 8ry _ 9 | C:hQ

', 5 ITPUB personal space @ # wq/U&b/bdL

UNKNOWN' AND "D". "INST_ID" = USERENV ('INSTANCE')) ITPUB personal space {Z%mu$g

/ / it can be seen here that v$asm_disk is associated with X$KFKID and X$KFDSK, the internal table of oracle, while the status of HEADER_STATUS is obtained by the numbers 1-8 in X$KFDSK.

/ / if you use both sql_trace and 10046 events, you can find the underlying table. Parameter "Analysis of _ diskgroup status _ in asm instance and db instance (20120215)".

From the above analysis, it is found that the v$asm_disk view in both asm and db instances comes from the association between X$KFDSK and X$KFKID, an internal table of Oracle.

Let's query the contents of X$KFDSK in ASM and db instances to see if there are any similarities and differences:

After querying, the contents of the two basic tables are different, so we find out the HEADER_STATUS status of v$asm_disk, which is where the HDRSTS_KFDSK is called.

-- db example: ITPUB personal Space Lindsay NSM Magazine V W Tf2H/j

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

GRPNUM_KFDSK ASMNAME_KFDSK HDRSTS_KFDSK

GlY3x `ZyoggX27042095

~ 7\} Kw "g27042095 1 DG_DATA_0000 1ITPUB personal space x.JauKVWa# e`o

2 VOLK 1

ITPUB personal Space 8x`z;\ J6HV3M

-- asm instance:

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

GRPNUM_KFDSK ASMNAME_KFDSK HDRSTS_KFDSKITPUB personal space $N FjOL%fD&Ms

-ITPUB personal space X&F.dc (sn'J)

1 DG_DATA_0000 3

. |-c0o bv8_2Mv27042095 2 VOLK 3

ITPUB personal Space x c7k&mA

The result here coincides with the above autotrace result 1, which is exactly the result of the above autotrace. Now that we know the underlying call of the HEADER_STATUS status of v$asm_disk, how can we get the relationship between these numbers and the relationship between these numbers and the state? when the HDRSTS_KFDSK of v$asm_disk is 1, what process will it take to make the HEADER_STATUS of v$asm_disk be displayed as "UNKNOWN'"?

An example of Avatar GW Magazine ar salary 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

27042095 header_status, mode_status, state, redundancy, library, total_mb, free_mb, naITPUB personal space% C0G (F% rSNlabel cjinh (u)

Me, failgroup, label, path, udid, product, create_date, mount_date, repair_timITPUB personal space; `zC-a/V

Er, reads, writes, read_errs, write_errs, read_time, write_time, bytes_read, bITPUB personal Space # pW)] Y3Sdeband zfMagi L

Ytes_written from gv$asm_disk where inst_id = USERENV ('Instance')

Here you can see that the v$asm_disk view is created by the gv$asm_diskg view. ITPUB personal space m [\ W r*IQ

/ / specify "INST_ID" = USERENV ('INSTANCE')) here, which is to get the information in the current instance environment. This is the difference between v$ and gv$.

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

VIEW_DEFINITIONITPUB personal Space & masks +\ 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)

; `*} "UI" t-N1x27042095

^ P:V7Zqt9@27042095--asm instance:

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

VIEW_DEFINITIONITPUB personal Space 2F3`O & Y6D'J

- -ITPUB personal space 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')

Here you can see that the v$asm_disk view is created by the gv$asm_disk view.

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

VIEW_DEFINITIONITPUB personal space Group8 |-x r's YW

- -ITPUB personal space "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 ONLINE', ONLINE', 7,' PROVISIONED', 8, 'FOREIGN',' INVALID'), decode (d.mode_kfdsk, 0, 'FAILING', 4) DROPPING', 5,' HUNG',6, 'FORCING', 7), decode (d.state_kfdsk, 1,' UNKNOWN', 2, 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.udidyogkfdsk.productpaperkfdsk, d.crdate_kfdsk, d.mtdate_kfdsk, d.timer_kfdsk, k.read_kfkid, k.write_kfkid, k.rerr_kfkid, k.werrymkfkidcows1000000, k.wtime_kfkid/1000000 K.bytesr_kfkid, k.bytesw_kfkid from x$kfdsk djorkfkid k where d.mntsts_kfdsk! = 0 and d.kfkid_kfdsk = k.idptr_kfkid (+)

It can be found here that the GV$ASM_DISK view in asm and db is created by the association of two base tables, x$kfdsk and x$kfkid, and the definitions are the same.

In the definition of the GV$ASM_DISK view, the decode function is used, which defines the calling relationship between 1-8 numbers and different states.

Such as: decode function defines: hdrsts_kfdsk field if the value is 1, then display 'UNKNOWN'; if the value is 3, then display' MEMBER', so it is clear at a glance.

Because the decode function is used here, different trigger conditions display different results, which results in different HEADER_STATUS results of asm and view v$asm_disk in the db instance.

But how to modify 1-8 data within Oracle is difficult to find, because the X$ table is the basis for running the Oracle database and is dynamically created by Oracle applications when the database is started.

For internal X$ and v$ view restrictions, Oracle is implemented through software mechanisms, not database permission control, so, in fact, the V$ object accessed by most users is not a view, but a synonym to the view, which is based on the real V$ view (this view is based on the X$ table).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.