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

10 comments:

  1. Bane

    Your the great support

    I wish I will continue enjoying getting more concepts and knowledge in Oracle Database as a whole from professionals like you. Thanks to whoever make Internet running

    Sadock

    ReplyDelete
  2. Hi Bane,

    Interesting document!!! Thank you, I now have a slightly better understanding of the internals of ASM.

    Can you explain how you related block 7 to the AU of 1175? I am trying do the same in my env.

    ReplyDelete
    Replies
    1. The data of interest is in 135th block (from the beginning of the file). We got that from DBMS_ROWID.ROWID_BLOCK_NUMBER.

      Extent 0 of that file is in AU 1175 on disk 0, extent 1 is in AU 1175 on disk 3, etc. We got that from X$KFFXP.

      Extent 0 holds data blocks 1-128 of our file (AU=1MB so it can hold 128 blocks of size 8KB), extent 1 holds data blocks 129, 130, 131, 132, 133, 134, 135, 136 etc. As you can see, the block of interest is 7th block in extent 1.

      Does that make sense?

      Delete
  3. Hi Bane, thank you for your blog.

    I'm looking for an exact way to verify the mapping from a block in a datafile to an ASM allocation unit on a disk.
    Is there a function, let's say:

    dbms_my_wishes.read_block(
    file_number IN NUMBER,
    block_number IN NUMBER)
    RETURN BLOB;

    If there was, I would compare the contents of that BLOB to what I would get from a dd command I would run against the physical location on the disk.

    Thank you,
    Ilan.

    ReplyDelete
    Replies
    1. Hi Ilan,

      No such thing, yet. Good idea though.

      A colleague of mine wrote a PL/SQL script that takes a file name and a block number as arguments and produces the dd command to extract the data. I can ask him if I can share it, but it's not really what you want.

      Cheers,
      Bane

      Delete
  4. Hi Bane,

    No need to bother your colleague, I have many scripts over dd already :)

    Thank you,
    Ilan.

    ReplyDelete
  5. Hi Bane,

    Is these steps applicable for 10g?

    Because i am trying on my test environment (10.2.0.1) but not getting expected results.

    Regards


    ReplyDelete
    Replies
    1. Yes, this should work fine with 10g.
      If you want me to have a closer look, email me [bane.radulovic at gmail.com] the details of what you are doing.
      Cheers,
      Bane

      Delete