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.
