Monday 15 August 2011

Track ASM Extents by X$KFFXP

I heard about the X$ fixed table (X$KFFXP). It keeps position of striped and mirrored extents... and etc. If You connect to ASM Instance, You can check and retrieve data from it.

$ sqlplus / as sysasm

SQL>
desc X$KFFXP

Name Null? Type
----------------------------------------------------------------------------------- -------- ----------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
GROUP_KFFXP NUMBER
NUMBER_KFFXP NUMBER
COMPOUND_KFFXP NUMBER
INCARN_KFFXP NUMBER
PXN_KFFXP NUMBER
XNUM_KFFXP NUMBER
LXN_KFFXP NUMBER
DISK_KFFXP NUMBER
AU_KFFXP NUMBER
FLAGS_KFFXP NUMBER
CHK_KFFXP NUMBER
SIZE_KFFXP NUMBER

Some columns in X$KFFXP table:
GROUP_KFFXP = V$ASM_DISKGROUP.GROUP_NUMBER
NUMBER_KFFXP = V$ASM_FILE.FILE_NUMBER
COMPOUND_KFFXP = V$ASM_FILE.COMPOUND_INDEX
INCARN_KFFXP = V$ASM_FILE.INCARNATION
XNUM_KFFXP = Extent Number
LXN_KFFXP = Logical Extent Number (0 = primary, 1 = mirrored copy, 2 = mirrored copy[High redundancy])
DISK_KFFXP = V$ASM_DISK.DISK_NUMBER
AU_KFFXP = V$ASM_DISKGROUP.ALLOCATION_UNIT_SIZE

So, I used X$KFFXP table for testing (just tested)

select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

P = primary
M = mirrored copy
MM = mirrored copy[High redundancy]

- On ASM, that uses External redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
----- ----------- ------------- --------------- ---------------
DATA 316 0 P /dev/sdb
DATA 316 1 P /dev/sdb
DATA 316 2 P /dev/sdb
DATA 316 3 P /dev/sdb
DATA 316 4 P /dev/sdb
DATA 316 5 P /dev/sdb

- On ASM, that uses Normal redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
------------------------------ ----------- ------------- --------------- ------------------------------
DATA 261 0 P ORCL:DATA10
DATA 261 0 M ORCL:DATA02
DATA 261 1 P ORCL:DATA03
DATA 261 1 M ORCL:DATA07
DATA 261 2 P ORCL:DATA09
DATA 261 2 M ORCL:DATA05
DATA 261 3 P ORCL:DATA05
DATA 261 3 M ORCL:DATA07
DATA 261 4 P ORCL:DATA08
DATA 261 4 M ORCL:DATA04
DATA 261 5 P ORCL:DATA04
DATA 261 5 M ORCL:DATA06

- On ASM, that uses High redundancy

SQL> select dg.name, x.NUMBER_KFFXP "FILE NUMBER", x.XNUM_KFFXP "EXTENT NUMBER", decode(x.LXN_KFFXP,0,'P',1,'M','MM') "EXTENT TYPE", d.path from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg where x.GROUP_KFFXP = d.GROUP_NUMBER and x.DISK_KFFXP = d.DISK_NUMBER and x.GROUP_KFFXP=dg.GROUP_NUMBER and x.NUMBER_KFFXP in (select file_number from v$asm_alias where name like 'USERS%') order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP
/

NAME FILE NUMBER EXTENT NUMBER EXTENT TYPE PATH
------------------------------ ----------- ------------- --------------- --------------------------------------------------
DATA 917 0 P o/192.168.99.6/DATA_EXA_CD_11_exacel02
DATA 917 0 M o/192.168.99.11/DATA_EXA_CD_02_exacel07
DATA 917 0 MM o/192.168.99.7/DATA_EXA_CD_00_exacel03
DATA 917 1 P o/192.168.99.9/DATA_EXA_CD_01_exacel05
DATA 917 1 M o/192.168.99.10/DATA_EXA_CD_11_exacel06
DATA 917 1 MM o/192.168.99.7/DATA_EXA_CD_06_exacel03
DATA 917 2 P o/192.168.99.9/DATA_EXA_CD_11_exacel05
DATA 917 2 M o/192.168.99.10/DATA_EXA_CD_01_exacel06
DATA 917 2 MM o/192.168.99.8/DATA_EXA_CD_02_exacel04
DATA 917 3 P o/192.168.99.6/DATA_EXA_CD_04_exacel02
DATA 917 3 M o/192.168.99.5/DATA_EXA_CD_10_exacel01
DATA 917 3 MM o/192.168.99.11/DATA_EXA_CD_10_exacel07
DATA 917 4 P o/192.168.99.8/DATA_EXA_CD_09_exacel04
DATA 917 4 M o/192.168.99.10/DATA_EXA_CD_09_exacel06
DATA 917 4 MM o/192.168.99.9/DATA_EXA_CD_03_exacel05
DATA 917 5 P o/192.168.99.6/DATA_EXA_CD_06_exacel02
DATA 917 5 M o/192.168.99.7/DATA_EXA_CD_10_exacel03
DATA 917 5 MM o/192.168.99.8/DATA_EXA_CD_07_exacel04

That was example to use it. However, you can use X$KFFXP to join with other views(V$ASM_*)

No comments: