Table of Contents
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