这个版本的oracle互换性没有,Oracle

转自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 = ) }

}

# 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:

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

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