Overview
- Create a smal tablespace with 5 Mbyte only ( == 12 AU if using NORMAL redundancy )
- You need to connect to your ASM instance to see data from X$KFFXP table
Setup test scenario
SQL script to create tablespace
col "File name" format A50
col "Tablespace name" format A15
connect / as sysdba
drop tablespace test_ts including contents;
create tablespace test_ts datafile '+TEST' size 5m;
select f.FILE#, f.NAME "File name", t.NAME "Tablespace name" from V$DATAFILE f, V$TABLESPACE t where t.NAME='TEST_TS' and f.TS# = t.TS#;
connect scott/tiger
create table test_tab (n number, name varchar2(16)) tablespace test_ts;
insert into test_tab values (1, 'ASM-TEST');
commit;
select ROWID, NAME from test_tab;
Check and verify ASM related data
SQL Script :
connect cott/tiger@grac41
@get_rowid
connect sys/sys@grac41 as sysdba
show parameter db_block_size
select f.FILE#, f.NAME "File name", t.NAME "Tablespace name" from V$DATAFILE f, V$TABLESPACE t where t.NAME='TEST_TS' and f.TS# = t.TS#;
connect / as sysasm
select GROUP_NUMBER from V$ASM_DISKGROUP where NAME='TEST';
select VALUE from V$ASM_ATTRIBUTE where NAME='au_size' and GROUP_NUMBER=4;
select GROUP_NUMBER, DISK_NUMBER, NAME, path from V$ASM_DISK where GROUP_NUMBER=4;
select PXN_KFFXP, -- physical extent number \
XNUM_KFFXP, -- virtual extent number
DISK_KFFXP, -- disk number
AU_KFFXP -- allocation unit number
from X$KFFXP
where NUMBER_KFFXP=256 -- ASM file 272
AND GROUP_KFFXP=4 -- group number 1
order by 1;
Output:
SQL> @check_it
Connected.
Rowid : AAAXgDAAHAAAACFAAA Block No:133
PL/SQL procedure successfully completed.
--> Data block Offset: 133
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
--> Database block size: 8k
FILE# File name Tablespace name
---------- -------------------------------------------------- ---------------
7 +TEST/grac4/datafile/test_ts.256.852905863 TEST_TS
--> ASM File number : 256
GROUP_NUMBER
------------
4
--> ASM DG : 4
VALUE
------------------------------------------------------------------------------------------------------------------------------------
1048576
--> AU size: 1Mbyte
GROUP_NUMBER DISK_NUMBER NAME PATH
------------ ----------- ------------------------------ ------------------------------
4 0 TEST_0000 /dev/asm_test_1G_disk1
4 1 TEST_0001 /dev/asm_test_1G_disk2
--> ASM disks : Disk# 0 : /dev/asm_test_1G_disk1 - Disk# 1: /dev/asm_test_1G_disk2
Summary of already collected data:
--> Data block OFFset: 133
--> Database block size: 8k
--> ASM File number : 256
--> ASM DG : 4
--> AU size: 1Mbyte
--> ASM disks : Disk# 0 : /dev/asm_test_1G_disk1 - Disk# 1: /dev/asm_test_1G_disk2
Mapping between AUs and OS Files
select PXN_KFFXP, -- physical extent number
XNUM_KFFXP, -- virtual extent number
DISK_KFFXP, -- disk number
AU_KFFXP -- allocation unit number
from X$KFFXP
where NUMBER_KFFXP=256 -- ASM file 256
AND GROUP_KFFXP=4 -- group number 1
order by 1;
PXN_KFFXP XNUM_KFFXP DISK_KFFXP AU_KFFXP
---------- ---------- ---------- ----------
0 0 0 144
1 0 1 144
2 1 1 145
3 1 0 145
4 2 0 146
5 2 1 146
6 3 1 147
7 3 0 147
8 4 0 148
9 4 1 148
10 5 1 149
11 5 0 149
12 rows selected.
--> As we have on 5 Myte we need 6 AUs
For normal reduncancy we need to double the AUs to 12 which matches perfect above print out
per AU we have 128 db blocks : 128 x 8k = 1Mbyte (== AU size )
Our block number is 133 ---> our block is in the second AU ( XNUM_KFFXP = 1 ) at offset of 5 ( 133 - 128 = 5 )
AU 1 is located at offset 145 MByte for disk 1 and disk 2 ( AU_KFFXP = 145 for DISK_KFFXP =1/2)
Testing that both disk already have written the block to disk:
[grid@grac41 Where_is_your_data]$ strings /dev/asm_test_1G_disk1 | grep ASM-TEST
ASM-TEST
[grid@grac41 Where_is_your_data]$ strings /dev/asm_test_1G_disk2 | grep ASM-TEST
ASM-TEST
--> Note you may need to wait until DBWR has written the data to disk
Read a block of 1 Mbyte ( == AU size ) and verify that we have picked up the right AU : disk offset 145 Mbyte
# dd if=/dev/asm_test_1G_disk1 bs=1024k count=1 skip=145 of=AU1_disk1
[grid@grac41 Where_is_your_data]$ strings AU1_disk1 | grep ASM-TEST
ASM-TEST
[grid@grac41 Where_is_your_data]$ od -c AU1_disk1
0137760 002 301 002 \b A S M - T E S T 001 006 i 356
Looks good - we have fount the correct AU
Extract Block 5 from AU[1] from disk /dev/asm_test_1G_disk1
[grid@grac41 Where_is_your_data]$ dd if=AU1_disk1 bs=8k count=1 skip=5 of=AU1_disk1_BLOCK_5
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.00119959 s, 6.8 MB/s
[grid@grac41 Where_is_your_data]$ strings AU1_disk1_BLOCK_5
ASM-TEST
--> We have found the correct block
Calculate the disk offset -as this easily allows us to easily manipulate the specific block
--> Calculated 8k blocks Offset for partition start :
(145*128 ) + 5 = 18565 ( 8Kb blocks )
- 145 AU Offset ( if AU size = 1 Mbyte this translates to 128 8k blocks )
- 5 block Offset in AU ( see above calculation )
Verify that our calculation is ok !
[grid@grac41 Where_is_your_data]$ dd if=/dev/asm_test_1G_disk1 bs=8k count=1 skip=18565 of=block_disk1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.00436751 s, 1.9 MB/s
[grid@grac41 Where_is_your_data]$ strings block_disk1
ASM-TEST
[grid@grac41 Where_is_your_data]$ dd if=/dev/asm_test_1G_disk2 bs=8k count=1 skip=18565 of=block_disk2
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.0110592 s, 741 kB/s
[grid@grac41 Where_is_your_data]$ strings block_disk1
ASM-TEST
Reference