#!$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;

 }