The views expressed on this blog are my own and do not necessarily reflect the views of Oracle

October 27, 2012

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 database instance. Let's create a tablespace first.

SQL> create tablespace T1 datafile '+DATA';
Tablespace created.

SQL> select f.FILE#, f.NAME "File name", t.NAME "Tablespace name"
where t.NAME='T1' and f.TS# = t.TS#;

     FILE# File name                                Tablespace name
---------- ---------------------------------------- ----------------
         6 +DATA/br/datafile/t1.272.797809075       T1


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.

Now find out the block number where that data is and check the block size.

SQL> select ROWID, NAME from TAB1;

ROWID              NAME
------------------ ----------------


Block number

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

From the above I see that Oracle block size is 8KB and that my data is in block 135 in ASM datafile 272.

ASM instance

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



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;

---------- ---------- ---------- ----------
         0          0          0       1175
         1          0          3       1170
         2          1          3       1175
         3          1          2       1179
         4          2          1       1175

As expected, the file extents are spread over all disks and each extent is mirrored as disk group DATA is normal redundancy.

I also need to know the ASM allocation unit size.

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


The allocation unit size is 1MB.

Where is my block

I know my data is in block 135 of ASM file 272. With block size of 8K each allocation unit can hold 128 blocks (128x8K=1MB). That means block 135 is block 7 (135-128=7) in the second allocation unit. The second allocation unit is 1175 on disk 3 (also allocation unit 1179 on disk 2 - remember this is a normal redundancy disk group, so my data is mirrored).

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);

----------- ------------------------------
          2 ASMDISK3
          3 ASMDISK4


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 (CAT) is 7 blocks (of size 8KB) into allocation unit 1175. That allocation unit is 1175MB into 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=/tmp/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 /tmp/AU1175.dd
-rw-r--r-- 1 root root 1048576 Oct 27 22:45 /tmp/AU1175.dd

Note the arguments to the dd command - bs=1024k (allocation unit size), skip=1175 (allocation unit I am interested in) and count=1 (I only need one allocation unit).

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

# dd if=/tmp/AU1175.dd bs=8k count=1 skip=7 of=/tmp/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 /tmp/block135.dd
0017760  \0  \0   , 001 002 002 301 002 003   C   A   T 001 006 332 217

At the bottom of that block I see my data (CAT). Remember that Oracle blocks are populated from the bottom up. I would see the same if I looked at allocation unit 1179 on disk /dev/oracleasm/disks/ASMDISK3.


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 Oracle 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 was 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.