Where is my data

Sometimes we want to know where exactly is a particular database block - on which ASM disk, in which allocation unit on that disk and in which block of that allocation unit. In this post I will show how to work that out.

Database instance

In the first part of this exercise I am logged into the database instance. Let's create a tablespace first.

SQL> create tablespace T1 datafile '+DATA';

Tablespace created.

SQL> select f.FILE#, f.NAME "File", t.NAME "Tablespace"

from V$DATAFILE f, V$TABLESPACE t

where t.NAME='T1' and f.TS# = t.TS#;

FILE# File                               Tablespace

----- ---------------------------------- ----------

   6  +DATA/br/datafile/t1.272.797809075 T1

SQL>

Note the ASM file number is 272.

Let's now create a table and insert some data into it

SQL> create table TAB1 (n number, name varchar2(16)) tablespace T1;

Table created.

SQL> insert into TAB1 values (1, 'CAT');

1 row created.

SQL> commit;

Commit complete.

SQL>

Get the block number.

SQL> select ROWID, NAME from TAB1;

ROWID              NAME

------------------ ----

AAASxxAAGAAAACHAAA CAT

SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAASxxAAGAAAACHAAA') "Block number" from DUAL;

Block number

------------

         135

SQL>

Get the block size for the datafile.

SQL> select BLOCK_SIZE from V$DATAFILE where FILE#=6;

BLOCK_SIZE

----------

      8192

SQL>

From the above I see that the data is in block 135 and that the block size is 8KB.

ASM instance

I now connect to the ASM instance and first check the extent distributions for ASM datafile 272.

SQL> select GROUP_NUMBER from V$ASM_DISKGROUP where NAME='DATA';

GROUP_NUMBER

------------

           1

SQL> 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=272 -- ASM file 272

AND GROUP_KFFXP=1      -- group number 1

order by 1;

PXN_KFFXP  XNUM_KFFXP DISK_KFFXP   AU_KFFXP

---------- ---------- ---------- ----------

         0          0          0       1175

         1          0          3       1170

         2          1          3       1175

         3          1          2       1179

         4          2          1       1175

...

SQL>

As expected, the file extents are spread over all disks and each (physical) extent is mirrored, as this file is normal redundancy. Note that I said the file is normal redundancy. By default the file inherits the disk group redundancy. The controlfile is an exception, as it gets created as high redundancy, even in the normal redundancy disk group - if the disk group has at least three failgroups.

I also need to know the ASM allocation unit size for this disk group.

SQL> select VALUE from V$ASM_ATTRIBUTE where NAME='au_size' and GROUP_NUMBER=1;

VALUE

-------

1048576

SQL>

The allocation unit size is 1MB. Note that each disk group can have a different allocation unit size.

Where is my block

I know my data is in block 135 of ASM file 272. With the block size of 8K each allocation unit can hold 128 blocks (1MB/8KB=128). That means the block 135 is 7th (135-128=7) in the second virtual extent. The second virtual extent consists of allocation unit 1175 on disk 3 and allocation unit 1179 on disk 2, as per the select from X$KFFXP.

Let's get the names of disks 2 and 3.

SQL> select DISK_NUMBER, NAME

from V$ASM_DISK

where DISK_NUMBER in (2,3);

DISK_NUMBER NAME

----------- ------------------------------

         2 ASMDISK3

         3 ASMDISK4

SQL>

I am using ASMLIB, so at the OS level, those disks are /dev/oracleasm/disks/ASMDISK3 and /dev/oracleasm/disks/ASMDISK4.

Show me the money

Let's recap. My data is 7 blocks into the allocation unit 1175. That allocation unit is 1175 MB into the disk /dev/oracleasm/disks/ASMDISK4.

Let's first extract that allocation unit.

$ dd if=/dev/oracleasm/disks/ASMDISK4 bs=1024k count=1 skip=1175 of=AU1175.dd

1+0 records in

1+0 records out

1048576 bytes (1.0 MB) copied, 0.057577 seconds, 18.2 MB/s

$ ls -l AU1175.dd

-rw-r--r-- 1 grid oinstall 1048576 Oct 27 22:45 AU1175.dd

$

Note the arguments to the dd command:

• bs=1024k - allocation unit size
• skip=1175 - allocation unit I am interested in
• count=1 - I only need one allocation unit

Let's now extract block 7 out of that allocation unit.

$ dd if=AU1175.dd bs=8k count=1 skip=7 of=block135.dd

$

Note the arguments to the dd command now - bs=8k (data block size) and skip=7 (block I am interested in).

Let's now look at that block.

$ od -c block135.dd

...

0017760 \0 \0 , 001 002 002 301 002 003 C A T 001 006 332 217

0020000

$

At the bottom of that block I see my data (CAT). Remember that Oracle blocks are populated from the bottom up.

Note that I would see the same if I looked at the allocation unit 1179 on disk /dev/oracleasm/disks/ASMDISK3.

Conclusion

To locate an Oracle data block in ASM, I had to know in which datafile that block was stored. I then queried X$KFFXP in ASM to see the extent distribution for that datafile. I also had to know both the datafile block size and ASM allocation unit size, to work out in which allocation unit my block was.

None of this is ASM or RDBMS version specific (except the query from V$ASM_ATTRIBUTE, as there is no such view in 10g). The ASM disk group redundancy is also irrelevant. Of course, with normal and high redundancy we will have multiple copies of data, but the method to find the data location is exactly the same for all types of disk group redundancy.