转自asmsupportguy blog
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
#!$ORACLE_HOME/perl/bin/perl -w
#
# The find_block.pl constructs the command(s) to extract a block from ASM.
# For a complete info about this script see ASM Support Guy blog post:
# http://asmsupportguy.blogspot.com/2014/10/find-block-in-asm.html
#
# Copyright (C) 2014 Bane Radulovic
#
# This program is free software: you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, either version 3 of the License, or any later version.
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details
# at http://www.gnu.org/licenses/.
#
# Version 1.00, Oct 2014
# The initial release.
#
# Version 1.01, Oct 2014
# Minor improvements.
#
# Version 1.02, Oct 2014
# Added support for AFD disks.
#
# Version 1.03, Nov 2014
# Added sanity checks, e.g. if the requested block is reasonable,
# if the specified filename is valid, etc.
#
# Version 1.04, Nov 2014
# Improved the check for Exadata storage cell based disk.
#
use strict;
use DBI;
use DBD::Oracle qw(:ora_session_modes);
use POSIX;
# Handle the version query
die "find_block.pl version 1.04\n"
if ( $ARGV[0] =~ /^-v/i );
# Check the number of input arguments
die "Usage: \$ORACLE_HOME/perl/bin/perl find_block.pl filename block\n"
unless ( @ARGV == 2 );
# Get the filename from the first input argument
my $filename = shift @ARGV;
# Check if the filename makes sense.
# The 'minimum' filename is +DGNAME/filename,
# i.e. it has to begin with the '+' followed by a disk group name,
# followed by at least one '/', followed by directory or file name...
die "Error: The $filename is not a valid file name.\n"
unless ( $filename =~ /^\+\w/ && $filename =~ /\/\w/ );
# Get the disk group name out of the user specified filename
my $diskgroup_name = substr($filename, 1, index($filename, "/") -1 );
# Get the ASM file name out of the user specified filename
my $asmfile = substr($filename, rindex($filename, "/") +1 );
# Get the block number from the second input argument
my $block_number = shift @ARGV;
# Check if the block number is an integer
die "Usage: \$ORACLE_HOME/perl/bin/perl find_block.pl filename block\n"
unless ( $block_number =~ /^\d+$/ );
# Check if the ASM SID is set
die "Error: ASM SID not set.\n"
unless ( $ENV{ORACLE_SID} =~ /\+ASM/ );
# Connect to the (local) ASM instance
my $dbh = DBI->connect('dbi:Oracle:', "", "", { ora_session_mode => ORA_SYSDBA })
or die "$DBI::errstr\n";
# Check if the disk group exists and if it is mounted
my $group_number = &asm_diskgroup("group_number", $diskgroup_name);
die "Error: Disk group $diskgroup_name not mounted or does not exist.\n"
unless ( $group_number );
# Check if the user specified file exists in the disk group
my $file_number = &asm_alias("file_number", $asmfile, $group_number);
die "Error: File $asmfile does not exist in disk group $diskgroup_name.\n"
unless ( $file_number );
# Get the block size for the file
my $block_size = &asm_file("block_size", $group_number, $file_number);
# Get the number of blocks in the file
my $file_blocks = &asm_file("blocks", $group_number, $file_number);
# Check if the user specified block number makes sense
die "Error: Block range for file $asmfile is: 0 - $file_blocks.\n"
unless ( $block_number >= 0 && $block_number <= $file_blocks );
# Get the disk group AU size
my $au_size = &asm_diskgroup("allocation_unit_size", $diskgroup_name);
# Work out the blocks per AU and the virtual extent number
my $blocks_per_au = $au_size/$block_size;
my $xnum_kffxp = floor($block_number/$blocks_per_au);
# Get the disk and AU numbers into the @disk_au array
my @disk_au = &asm_kffxp($file_number, $group_number, $xnum_kffxp);
die "Could not get any disk and AU numbers for file $asmfile.\n"
unless ( @disk_au );
# Get the disk path(s) and generate the block extract command(s)
while ( @disk_au ) {
# Do not assume anything
my $storage_cell = "FALSE";
# Get the disk number from @disk_au
my $disk_number = shift @disk_au;
# Get the AU number from @disk_au
my $au_number = shift @disk_au;
# Get the path for that disk number
my $path = &asm_disk("path", $group_number, $disk_number);
# If there is no path move to the next disk
if ( ! $path ) {
next;
}
# If ASMLIB is in use, the path will return ORCL:DISKNAME.
# Set the path to /dev/oracleasm/disks/DISKNAME
elsif ( $path =~ /ORCL:(.*)/ ) {
$path = "/dev/oracleasm/disks/".$1;
}
# If ASM Filter Driver (AFD) is in use, the path will return AFD:DISKNAME.
# Get the actual path from /dev/oracleafd/disks/DISKNAME
elsif ( $path =~ /AFD:(.*)/ ) {
if ( ! open AFDDISK, "/dev/oracleafd/disks/".$1 ) { next }
else { chomp($path = <AFDDISK>) }
}
# For Exadata storage cell based disk, the path will start with o/IP address
elsif ( $path =~ /^o\/\d{1,3}\./ ) {
$storage_cell = "TRUE";
}
if ( $storage_cell eq "TRUE" ) {
# Construct the kfed command for Exadata storage cell based disk
# dev=$path ausz=$au_size aunum=$au_number blksz=$block_size blknum=$block_number
# The grep filters out the kfed stuff
print "kfed read dev=$path ausz=$au_size aunum=$au_number blksz=$block_size blknum=$block_number | grep -iv ^kf > block_$block_number.txt\n";
}
else {
# Construct the dd command
# if=$path bs=$block_size count=1 skip=$skip of=block_$block_number.dd
my $skip=$au_number*$blocks_per_au + $block_number%$blocks_per_au;
print "dd if=$path bs=$block_size count=1 skip=$skip of=block_$block_number.dd\n";
}
}
# We are done. Disconnect from the (local) ASM instance
$dbh->disconnect;
# Subs
# Get a column from v$asm_file for a given group number and file number
sub asm_file {
my $col = shift @_;
my $group_number = shift @_;
my $file_number = shift @_;
my $sql = $dbh->prepare("select $col from v\$asm_file where group_number=$group_number and file_number=$file_number");
$sql->execute;
my $col_value = $sql->fetchrow_array;
$sql->finish;
return $col_value;
}
# Get a column from v$asm_alias for a given (file) name and group number
sub asm_alias {
my $col = shift @_;
my $name = shift @_;
my $group_number = shift @_;
my $sql = $dbh->prepare("select $col from v\$asm_alias where lower(name)=lower('$name') and group_number=$group_number");
$sql->execute;
my $col_value = $sql->fetchrow_array;
$sql->finish;
return $col_value;
}
# Get a column from v$asm_diskgroup for a given disk group name
sub asm_diskgroup {
my $col = shift @_;
my $name = shift @_;
my $sql = $dbh->prepare("select $col from v\$asm_diskgroup where name=upper('$name')");
$sql->execute;
my $col_value = $sql->fetchrow_array;
$sql->finish;
return $col_value;
}
# Get a column from v$asm_disk for a given group number and disk number
sub asm_disk {
my $col = shift @_;
my $group_number = shift @_;
my $disk_number = shift @_;
my $sql = $dbh->prepare("select $col from v\$asm_disk where group_number=$group_number and disk_number=$disk_number");
$sql->execute;
my $col_value = $sql->fetchrow_array;
$sql->finish;
return $col_value;
}
# Get the disk and AU numbers from x$kffxp for a given virtual extent number.
# This will return one row for an external redundancy file,
# two rows for a normal redundancy and three rows for a high redundancy.
# Well, it will return an array with disk and AU pairs, not rows.
sub asm_kffxp {
my $file_number = shift @_;
my $group_number = shift @_;
my $xnum = shift @_;
# The @disk_au array to hold the disk number, AU number rows
my @disk_au;
my $sql = $dbh->prepare("select disk_kffxp, au_kffxp from x\$kffxp where number_kffxp=$file_number and group_kffxp=$group_number and xnum_kffxp=$xnum");
$sql->execute;
# Expecting one disk number and one AU number per row
while ( my @row = $sql->fetchrow_array) {
# Add each (element of the) row to @disk_au array
foreach ( @row ) { push @disk_au, $_ }
}
$sql->finish;
return @disk_au;
}
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 kfedtool, 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.
