ASM 翻譯系列第三十二彈：ASM INTERNAL Find block in ASM

沃趣科技發表於2017-02-23
ASMBloC

原文： Find block in ASM 
作者： Bane Radulovic 
譯者：魏興華，沃趣科技高階技術專家，主要參與公司一體機產品、監控產品、容災產品、DBaaS平臺的研發和設計。曾就職於東軟集團，阿里巴巴集團，Oracle ACE組成員，DBGEEK 使用者組發起人，ITPUB認證部落格專家，ACOUG、SHOUG核心成員。曾在中國大會、Oracle技術嘉年華、ORCL-CON、YY分享平臺等公開場合多次做過資料庫技術專題分享。對Oracle 並行機制、資料庫異常恢復方法、ASM等有深入的研究，人稱”Oracle Internal達人”，對企業資料庫設計、故障恢復、高併發下資料庫效能調優有豐富的經驗，擅長從等待事件角度分析解決資料庫效能問題，是OWI方法論的提倡者和踐行者。 
審校：魏興華 
責編：仲培藝

在本系列文章《Where is my data》中，我已經演示瞭如何從ASM磁碟中定位和抽取一個Oracle的block，為了讓這件事做起來不那麼複雜，我又寫了一個perl指令碼find_block.pl來簡化整個操作，只需要提供資料檔案的名稱和需要提取的block，這個指令碼就可以輸出從ASM磁碟組中抽取塊的命令。

find_block.pl

find_block.pl是一個perl指令碼，指令碼里整合了dd或kfed命令來從ASM磁碟中抽取一個塊，指令碼可以在Linux和Unix的ASM版本下工作，且不管是單例項還是RAC環境。（不能是FLEX ASM）

指令碼需要以Grid軟體owner的身份來執行，而且要確保pert的二進位制檔案來自於Oracle安裝軟體的home目錄下。在叢集環境下，這個指令碼可以執行在任意節點上，在執行指令碼前，請檢查ASM的環境變數，確定ORACLE_SID、ORACLE_HOME、LD_LIBRARY_PATH設定正確，而且對於10G和11GR1版本，需要設定PERL5LIB環境變數： 

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

可以以如下的方式執行指令碼： 

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

其中：filename是要抽取的塊所在的檔名，對於資料檔案來說，這個檔名可以從V$DATAFILE的NAME欄位獲取到，block代表要從ASM抽取的塊號，這個塊號是資料庫的塊號，而不是ASM的塊號。

這個指令碼的輸出如下： 

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

在Exadata中是這樣： 

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

對於資料檔案來說，如果檔案的冗餘度是external外部冗餘模式，這個指令碼將產生一條單一的命令，若為normal冗餘，這個指令碼將產生2個命令，對於high冗餘，將產生3條命令。

Example with ASM version 10.2.0.1

第一個例子是單例項10.2.0.1的ASM版本，首先我在資料庫中建立了一張表，插入一些資料。 

[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>
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

以上我們造取了兩條資料，並且定位到了資料所在的檔案和BLOCK號，切換到ASM環境，注意設定正確的環境變數PERL5LIB，然後執行指令碼： 

$ 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
$
  • 1
  • 2
  • 3
  • 4
  • 1
  • 2
  • 3
  • 4

find_block.pl指令碼如預期產生了輸出，由於這是一個外部冗餘的磁碟組，這個指令碼只產生了一行dd命令的輸出，我們把輸出的dd命令複製後執行： 

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

執行後會將塊的內容輸出到文字檔案block_3237.dd中，然後使用作業系統的OD工具，可以看到插入表中的資料： 

$ 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 $
  • 1
  • 2
  • 3
  • 4
  • 5
  • 1
  • 2
  • 3
  • 4
  • 5

非常好，正是我們插入的資料！

Example with ASM version 12.1.0.1 in Exadata

在Exadata中我們不能使用dd命令抽取資料塊，因為ASM的磁碟對於資料庫的server來說是不可見的，為了獲得資料塊，我們可以使用kfed工具，因此find_block.pl指令碼做了這種自適應，如果是Exadata的環境，會使用kfed工具從ASM磁碟中抽取塊。

我們來看一個ASM 12.1.0.1版本下的例子，是一個Exadata環境下雙節點的RAC，資料檔案是PDB中的一個資料檔案。

和上面的例子一樣，首先建立一張表然後插入一些資料： 

$ 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>
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60

同樣獲得插入資料的檔案號和塊號，切換到ASM的環境，然後執行perl指令碼： 

$ $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
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

我們觀察到，find_block.pl指令碼這次產生了2個命令，因此我們可以知道這是一個normal冗餘的磁碟組，我們執行其中一個命令： 

$ 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
$
  • 1
  • 2
  • 1
  • 2

我們將塊的內容輸出到了文字檔案block_131.txt中，然後看到了我上面插入的資料DOG和CAT： 

$ more block_131.txt ... FD5106080 00000000 00000000 ... [................]
      Repeat 501 times
FD5107FE0 00000000 00000000 ... [........,......D]
FD5107FF0 012C474F 02C10202 ... [OG,......CAT..,-]
$
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

Find any block

find_block.pl用來從ASM磁碟組中的任何一個檔案中抽取塊，不僅僅是資料檔案，我對控制檔案和控制檔案上一個隨機的塊執行這個指令碼： 

$ $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
$
  • 1
  • 2
  • 3
  • 4
  • 5
  • 1
  • 2
  • 3
  • 4
  • 5

我們注意到指令碼正確計算出控制檔案的block size（不同於資料塊的大小8K，為16K），並且指令碼產生了3個不同的命令，雖然磁碟組DATA是normal冗餘，但是控制檔案卻做了high冗餘，也就是做了三副本，控制檔案在這一點上跟ASM的後設資料檔案一樣。

Conclusion

find_block.pl指令碼透過dd或者kfed命令從ASM磁碟組的檔案中抽取塊，可能大多數情況下，我們想要從資料檔案中抽取一個塊，但是這個指令碼不僅僅適用於資料檔案，也可以從控制檔案、日誌檔案、任何的ASM檔案中抽取塊。

如果檔案是external外部冗餘的，那麼這個指令碼將輸出一個單一的命令，執行這個命令可以直接從ASM的磁碟中抽取塊。

如果檔案是normal冗餘，這個指令碼將輸出2個命令，它用來從不同的磁碟中抽取塊，這可能會比較有用，例如後臺日誌提示資料塊損壞，ASM不能修復它，那麼就可以透過映象塊來修復。

如果檔案是high冗餘，這個指令碼將產生3個命令。

最後，使用這個指令碼你不用知道檔案的冗餘度、塊的大小，和任何其它屬性，你只需要關心檔名和塊號。

附指令碼 

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2134158/，如需轉載，請註明出處，否則將追究法律責任。

相關文章