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

October 23, 2014

Find block in ASM

In the post Where is my data I have shown how to locate and extract an Oracle datafile block from ASM. To make things easier, I have now created a Perl script find_block.pl that automates the process - you provide the datafile name and the block number, and the script generates the command to extract the data block from ASM.

find_block.pl

The find_block.pl is a Perl script that constructs the dd or the kfed command to extract a block from ASM. It should work with all Linux and Unix ASM versions and with local (non-flex) ASM in the standalone (single instance) or cluster environments.

The script should be run as the ASM/Grid Infrastructure owner, using the perl binary in the ASM oracle home. In a cluster environment, the script can be run from any node. Before running the script, set the ASM environment and make sure the ORACLE_SID, ORACLE_HOME, LD_LIBRARY_PATH, etc are set correctly. For ASM versions 10g and 11gR1, also set the environment variable PERL5LIB, like this:

export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl

Run the script as follows:

$ORACLE_HOME/perl/bin/perl find_block.pl filename block

Where:
  • filename is the name of the file from which to extract the block. For a datafile, the file name can be obtained from the database instance with SELECT NAME FROM V$DATAFILE.
  • block is the block number to be extracted from ASM.

The output should look like this:

dd if=[ASM disk path] ... of=block_N.dd

Or in Exadata:

kfed read dev=[ASM disk path] ... > block_N.txt

If the file redundancy is external, the script would generate a single command. For a normal redundancy file, the script would generate two commands, and for the high redundancy file the script would generate three commands.

Example with ASM version 10.2.0.1

The first example is with a single instance ASM version 10.2.0.1. I first create the table and insert some data, in the database instance, of course.

[oracle@cat10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on [date]

SQL> create table TAB1 (name varchar2(16)) tablespace USERS;

Table created.

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

1 row created.

SQL> insert into TAB1 values ('DOG');

1 row created.

SQL> commit;

Commit complete.

SQL> select ROWID, NAME from TAB1;

ROWID              NAME
------------------ --------------------------------
AAANE+AAEAAAAGHAAA CAT
AAANE+AAEAAAAGHAAB DOG

SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAANE+AAEAAAAGHAAA') "Block" from dual;

    Block
---------
      391

SQL> select t.name "Tablespace", f.name "Datafile"
from v$tablespace t, v$datafile f
where t.ts#=f.ts# and t.name='USERS';

Tablespace   Datafile
------------ --------------------------------------
USERS        +DATA/cat/datafile/users.259.783204313

SQL>

Switch to the ASM environment, set PERL5LIB, and run the script.

$ export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl
$ $ORACLE_HOME/perl/bin/perl find_block.pl +DATA/cat/datafile/users.259.783204313 391
dd if=/dev/oracleasm/disks/ASMDISK01 bs=8192 count=1 skip=100359 of=block_391.dd
$

From the output of the find_block.pl, I see that the specified file is external redundancy, as the script produced a single dd command. Run the dd command:

$ dd if=/dev/oracleasm/disks/ASMDISK01 bs=8192 count=1 skip=100359 of=block_391.dd
$

Looking at the content of the block_3237.dd file, with the od utility, I see the data inserted in the table:

$ od -c block_391.dd | tail -3
0017740 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 , 001
0017760 001 003 D O G , 001 001 003 C A T 001 006 u   G
0020000
$

Example with ASM version 12.1.0.1 in Exadata

In Exadata we cannot use the dd command to extract the block, as the ASM disks are not visible from the database server. To get the database block, we can use the kfed tool, so the find_block.pl will construct a kfed command that can be used to extract the block from ASM.

Let's have a look at an example with ASM version 12.1.0.1, in a two node cluster, with the datafile in a pluggable database in Exadata.

As in the previous example, I first create the table and insert some data.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on [date]

SQL> alter pluggable database BR_PDB open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE   RESTRICTED
------ -------- ----------- ----------
       2 PDB$SEED READ ONLY   NO
...
       5 BR_PDB   READ WRITE  NO

SQL>

$ sqlplus bane/welcome1@BR_PDB

SQL*Plus: Release 12.1.0.1.0 Production on [date]

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

Table created.

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

1 row created.

SQL> insert into TAB1 values (2, 'DOG');

1 row created.

SQL> commit;

Commit complete.

SQL> select t.name "Tablespace", f.name "Datafile"
from v$tablespace t, v$datafile f
where t.ts#=f.ts# and t.name='USERS';

Tablespace Datafile
---------- ---------------------------------------------
USERS      +DATA/CDB/054.../DATAFILE/users.588.860861901

SQL> select ROWID, NAME from TAB1;

ROWID              NAME
------------------ ----
AAAWYEABfAAAACDAAA CAT
AAAWYEABfAAAACDAAB DOG

SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAWYEABfAAAACDAAA') "Block number" from dual;

Block number
------------
       131

SQL>

Switch to the ASM environment, and run the script.

$ $ORACLE_HOME/perl/bin/perl find_block.pl +DATA/CDB/0548068A10AB14DEE053E273BB0A46D1/DATAFILE/users.588.860861901 131
kfed read dev=o/192.168.1.9/DATA_CD_03_exacelmel05 ausz=4194304 aunum=16212 blksz=8192 blknum=131 | grep -iv ^kf > block_131.txt
kfed read dev=o/192.168.1.11/DATA_CD_09_exacelmel07 ausz=4194304 aunum=16267 blksz=8192 blknum=131 | grep -iv ^kf > block_131.txt

Note that the find_block.pl generated two commands, as that datafile is normal redundancy. Run one of the commands:

$ kfed read dev=o/192.168.1.9/DATA_CD_03_exacelmel05 ausz=4194304 aunum=16212 blksz=8192 blknum=131 | grep -iv ^kf > block_131.txt
$

Review the content of the block_131.txt file (note that this is a text file). Sure enough I see my DOG and my CAT:

$ more block_131.txt
...
FD5106080 00000000 00000000 ...  [................]
      Repeat 501 times
FD5107FE0 00000000 00000000 ...  [........,......D]
FD5107FF0 012C474F 02C10202 ...  [OG,......CAT..,-]
$

Find any block

The find_block.pl can be used to extract a block from any file stored in ASM. Just for fun, I ran the script on a controlfile and a random block:

$ $ORACLE_HOME/perl/bin/perl find_block.pl +DATA/CDB/CONTROLFILE/current.289.843047837 5
kfed read dev=o/192.168.1.9/DATA_CD_10_exacelmel05 ausz=4194304 aunum=73 blksz=16384 blknum=5 | grep -iv ^kf > block_5.txt
kfed read dev=o/192.168.1.11/DATA_CD_01_exacelmel07 ausz=4194304 aunum=66 blksz=16384 blknum=5 | grep -iv ^kf > block_5.txt
kfed read dev=o/192.168.1.10/DATA_CD_04_exacelmel06 ausz=4194304 aunum=78 blksz=16384 blknum=5 | grep -iv ^kf > block_5.txt
$

Keen observer will notice that the script worked out the correct block size for the controlfile (16k) and that it generated three different commands. While the disk group DATA is normal redundancy, the controlfile is high redundancy (default redundancy for the controlfile in ASM).

Conclusion

The find_block.pl is a Perl script that construct the dd or the kfed command to extract a block from a file in ASM. In most cases we want to extract a block from a datafile, but the script can be used to extract a block from a controlfile, redo log or any other file in ASM.

If the file is external redundancy, the script will generate a single command, that can be used to extract the block from the ASM disk.

If the file is normal redundancy, the script will generate two commands, that can be used to extract the (copies of the same) block from two different ASM disks. This can be handy, for example in cases where a corruption is reported against one of the blocks and for some reason the ASM cannot repair it.

If the file is high redundancy, the script will generate three commands.

To use the script you don't have to know the file redundancy, the block size or any other file attribute. All that is required is the file name and the block number.

September 27, 2014

REQUIRED_MIRROR_FREE_MB

The REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB are two very interesting columns in the V$ASM_DISKGROUP[_STAT] view. Oracle Support gets many questions about the meaning of those columns and how the values are calculated. I wanted to write about this, but I realised that I could not do it better than Harald van Breederode, so I asked him for permission to simply reference his write up. He agreed, so please have a look at his excellent post Demystifying ASM REQUIRED_MIRROR_FREE_MB and USABLE_FILE_MB.

How much space can I use

Now that the REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB have been explained, I would like to add that the ASM does not prevent you from using all available space - half of the total space for a normal redundancy disk group and one third of the total space for a high redundancy disk group. But if you do fill up your disk group to the brim, there will be no room to grow or add any files, and in the case of a disk failure, there will be no room to restore the redundancy for some data - until the failed disk is replaced and the rebalance completed.

Exadata with ASM version 11gR2

In Exadata with ASM version 11.2 the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup [1] in the disk group. To demonstrate, let's look at an Exadata system with ASM version 11.2.0.4.

As in most Exadata installations, I have three disk groups.

[grid@exadb01 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on [date]

SQL> select NAME, GROUP_NUMBER from v$asm_diskgroup_stat;

NAME      GROUP_NUMBER
--------- ------------
DATA                 1
DBFS_DG              2
RECO                 3

SQL>

For the purpose of this example, we will look at the disk group DBFS_DG. Normally there would be 10 disks per failgroup for disk group DBFS_DG. I have dropped few disks to demonstrate that the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup.

SQL> select FAILGROUP, count(NAME) "Disks", sum(TOTAL_MB) "MB"
from v$asm_disk_stat
where GROUP_NUMBER=2
group by FAILGROUP
order by 3;

FAILGROUP       Disks         MB
---------- ---------- ----------
EXACELL04           7     180096
EXACELL01           8     205824
EXACELL02           9     231552
EXACELL03          10     257280

SQL>

Note that the total space in the largest failgroup is 257280 MB.

Finally, we see that the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup:

SQL> select NAME, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB
from v$asm_diskgroup_stat
where GROUP_NUMBER=2;

NAME         TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------- ---------- ---------- ----------------------- --------------
DBFS_DG        874752     801420                  257280         272070

SQL>

The ASM calculates the USABLE_FILE_MB using the following formula:

USABLE_FILE_MB = (FREE_MB - REQUIRED_MIRROR_FREE_MB) / 2

Which gives 272070 MB.

[1] In Exadata, all failgroups are typically of the same size

Exadata with ASM version 12cR1

In Exadata with ASM version 12cR1, the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest disk [2] in the disk group.

Here is an example from an Exadata system with ASM version 12.1.0.2.0.

[grid@exadb03 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on [date]

SQL> select NAME, GROUP_NUMBER from v$asm_diskgroup_stat;

NAME     GROUP_NUMBER
-------- ------------
DATA                1
DBFS_DG             2
RECO                3

SQL>

Again, I have the failgroups of different sizes in the disk group DBFS_DG:

SQL> select FAILGROUP, count(NAME) "Disks", sum(TOTAL_MB) "MB"
from v$asm_disk_stat
where GROUP_NUMBER=2
group by FAILGROUP
order by 3;

FAILGROUP       Disks         MB
---------- ---------- ----------
EXACELL05           8     238592
EXACELL07           9     268416
EXACELL06          10     298240

SQL>

The total space in the largest failgroup is 298240 MB, but this time the REQUIRED_MIRROR_FREE_MB is reported as 29824 MB:

SQL> select NAME, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB
from v$asm_diskgroup_stat
where GROUP_NUMBER=2;  2    3

NAME         TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------- ---------- ---------- ----------------------- --------------
DBFS_DG        805248     781764                   29824         375970

SQL>

As we can see, that is the size of the largest disk, in the diskgroup:

SQL> select max(TOTAL_MB) from v$asm_disk_stat where GROUP_NUMBER=2;

MAX(TOTAL_MB)
-------------
        29824

SQL>

The USABLE_FILE_MB was calculated using the same formula:

USABLE_FILE_MB = (FREE_MB - REQUIRED_MIRROR_FREE_MB) / 2

Which gives 375970 MB.

[2] In Exadata, all disks are typically of the same size

Conclusion

The REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB are intended to assist the DBAs and storage administrators with planning the disk group capacity and redundancy. The values are reported, but not enforced by the ASM.

In Exadata with ASM version 12cR1, the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest disk in the disk group. This is by design, to reflect the experience from the field, which shows that the disks are the components that are failing, not the whole storage cells.