oracle關於坏塊的修複一

                        oracle11g關於坏塊的修複
一：bbed的命令簡單介紹，後面用該工具構造塊校正和不一致以達到類比壞塊目的

show 顯示當前所有配置選項
info：列出當前bbed能處理的檔案
set dba fileid,block:設定當前要處理的資料檔案id和塊號
set dba fileid，block 也可以用 set file  fileid 和set block  blockno 代替
set offset xxx  offset 以set block 塊號的設定為基準位移當前塊號的位元組數
dump  /v  顯示當前資料區塊的內容 預設從當前資料區塊設定的offset位元組數處開始顯示。/v 參數顯示詳細內容，詳細到啥境界，後面就曉得了。
find /c 尋找的內容 TOP   /c 指定尋找內容為字元  TOP指定從資料區塊頭部位移量為0處開始整塊搜尋搜到一處顯示一處，如果要尋找下一個該字元則指定f即可
modify /c 要修改內容  預設從set指定的檔案、塊號、offset位移位元組數處進行修改。

二： 建立測試用的表
SQL> create table jiujian(des varchar(30)) tablespace pos;

Table created.

SQL> insert into jiujian values('zhangxu love oracle');

1 row created.

SQL> select * from jiujian;

DES
------------------------------
zhangxu love oracle

2 查詢表jiujian所在的塊
語句：select rowid,
      dbms_rowid.rowid_relative_fno(rowid) rel_fno,
      dbms_rowid.rowid_block_number(rowid) blockno,
      dbms_rowid.rowid_row_number(rowid) rowno
      from jiujian;
SQL> select rowid,
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from jiujian;

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAATqzAAFAAAACHAAA          5        135          0
欄位BLOCKNO 135即為表jiujian所在的塊

三：建立bbed的設定檔
[oracle@oracle ~]$ cat bbed.par
blocksize=8192
listfile=/oracle/file
mode=edit
文字檔file 內容
[oracle@oracle ~]$ cat file
1 /oracle/CRM/system01.dbf 786432000
2 /oracle/CRM/sysaux01.dbf 566231040
3 /oracle/CRM/undotbs01.dbf 104857600
4 /backup/users01.dbf 1827143680
5 /oracle/CRM/pos.dbf 524288000
6 /oracle/CRM/erp.dbf 104857600
7 /oracle/CRM/user01.dbf 5242880
10 /oracle/CRM/undotbs02.dbf 104857600
file 內容由以下語句擷取：
SQL> select file#||' '||name||' '||bytes from v$datafile;

FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 /oracle/CRM/system01.dbf 786432000
2 /oracle/CRM/sysaux01.dbf 566231040
3 /oracle/CRM/undotbs01.dbf 104857600
4 /backup/users01.dbf 1827143680
5 /oracle/CRM/pos.dbf 524288000
6 /oracle/CRM/erp.dbf 104857600
7 /oracle/CRM/user01.dbf 5242880
10 /oracle/CRM/undotbs02.dbf 104857600

四 開始用bbed構造壞塊

[oracle@oracle ~]$ bbed parfile=bbed.par 
Password:blockedit

BBED> show   顯示配置
        FILE#           1
        BLOCK#          1
        OFFSET          0
        DBA             0x00400001 (4194305 1,1)
        FILENAME        /oracle/CRM/system01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /oracle/file
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

BBED> info  顯示bbed能處理的資料檔案編號和資料檔案位置
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /oracle/CRM/system01.dbf                                         96000
     2  /oracle/CRM/sysaux01.dbf                                         69120
     3  /oracle/CRM/undotbs01.dbf                                        12800
     4  /backup/users01.dbf                                             223040
     5  /oracle/CRM/pos.dbf                                              64000
     6  /oracle/CRM/erp.dbf                                              12800
     7  /oracle/CRM/user01.dbf                                             640
    10  /oracle/CRM/undotbs02.dbf                                        12800

BBED> set dba 5,135   設定當前資料檔案號和塊號
        DBA             0x01400087 (20971655 5,135)

BBED> show           用show命令確認下設定
        FILE#           5
        BLOCK#          135
        OFFSET          0
        DBA             0x01400087 (20971655 5,135)
        FILENAME        /oracle/CRM/pos.dbf
        BIFILE          bifile.bbd
        LISTFILE        /oracle/file
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

BBED> find /c zhangxu TOP  尋找當前資料區塊第一處字元zhangxu的位置
 File: /oracle/CRM/pos.dbf (5)
 Block: 135              Offsets: 8169 to 8191           Dba:0x01400087
------------------------------------------------------------------------
 7a68616e 67787520 6c6f7665 206f7261 636c6501 066c05

 <32 bytes per line>
從上面輸出可看到當前字串zhangxu位於位移量8169處
 
BBED> set offset 8169   更改當前位移量為 8169
        OFFSET          8169

BBED> dump /v    從指定位移量處開始顯示資料區塊內容
 File: /oracle/CRM/pos.dbf (5)
 Block: 135     Offsets: 8169 to 8191  Dba:0x01400087
-------------------------------------------------------
 7a68616e 67787520 6c6f7665 206f7261 l zhangxu love ora
 636c6501 066c05                     l cle..l.

 <16 bytes per line>
從上句和下句便可看處dump /v 比dump多詳細啊，能看到這個16進位相應的內容。
BBED> dump
 File: /oracle/CRM/pos.dbf (5)
 Block: 135              Offsets: 8169 to 8191           Dba:0x01400087
------------------------------------------------------------------------
 7a68616e 67787520 6c6f7665 206f7261 636c6501 066c05      注意這裡連毛都沒有

 <32 bytes per line>

BBED> modify /c jiujian  從位移量8169處開始用字元jiujian 進行替換。
 File: /oracle/CRM/pos.dbf (5)
 Block: 135              Offsets: 8169 to 8191           Dba:0x01400087
------------------------------------------------------------------------
 6a69756a 69616e20 6c6f7665 206f7261 636c6501 06b7f5

 <32 bytes per line>

BBED> dump /v 顯示是否替換成功
 File: /oracle/CRM/pos.dbf (5)
 Block: 135     Offsets: 8169 to 8191  Dba:0x01400087
-------------------------------------------------------
 6a69756a 69616e20 6c6f7665 206f7261 l jiujian love ora
 636c6501 06b7f5                     l cle..孵

 <16 bytes per line>
設定資料表空間offline再online或者重啟資料庫便可看到壞塊效果
SQL> select * from jiujian;
select * from jiujian
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 135)
ORA-01110: data file 5: '/oracle/CRM/pos.dbf'

五 對壞塊修複
1 查詢壞塊資訊
SQL> select  * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         5        135          1                  0 CHECKSUM
2 修複該塊 下面給出兩種方法
 運行 recover …. Block 命令恢複指定的塊
[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Sep 23 11:09:32 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CRM (DBID=3599153036)

RMAN> recover datafile 5 block 135;

Starting recover at 2012-09-23 11:10:32
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=72 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /backup/20120913_ebnl4bil_1_1
channel ORA_DISK_1: piece handle=/backup/20120913_ebnl4bil_1_1 tag=TAG20120913T195604
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35

starting media recovery

archived log for thread 1 with sequence 82 is already on disk as file /oracle/archive/1_82_791488634.dbf
archived log for thread 1 with sequence 83 is already on disk as file /oracle/archive/1_83_791488634.dbf
archived log for thread 1 with sequence 84 is already on disk as file /oracle/archive/1_84_791488634.dbf
archived log for thread 1 with sequence 85 is already on disk as file /oracle/archive/1_85_791488634.dbf
archived log for thread 1 with sequence 86 is already on disk as file /oracle/archive/1_86_791488634.dbf
archived log for thread 1 with sequence 87 is already on disk as file /oracle/archive/1_87_791488634.dbf
archived log for thread 1 with sequence 88 is already on disk as file /oracle/archive/1_88_791488634.dbf
archived log for thread 1 with sequence 89 is already on disk as file /oracle/archive/1_89_791488634.dbf
archived log for thread 1 with sequence 90 is already on disk as file /oracle/archive/1_90_791488634.dbf
archived log for thread 1 with sequence 91 is already on disk as file /oracle/archive/1_91_791488634.dbf
archived log for thread 1 with sequence 92 is already on disk as file /oracle/archive/1_92_791488634.dbf
archived log for thread 1 with sequence 93 is already on disk as file /oracle/archive/1_93_791488634.dbf
archived log for thread 1 with sequence 94 is already on disk as file /oracle/archive/1_94_791488634.dbf
archived log for thread 1 with sequence 95 is already on disk as file /oracle/archive/1_95_791488634.dbf
archived log for thread 1 with sequence 96 is already on disk as file /oracle/archive/1_96_791488634.dbf
archived log for thread 1 with sequence 97 is already on disk as file /oracle/archive/1_97_791488634.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=81
channel ORA_DISK_1: reading from backup piece /backup/20120913_ednl4bp0_1_1
channel ORA_DISK_1: piece handle=/backup/20120913_ednl4bp0_1_1 tag=TAG20120913T195928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
media recovery complete, elapsed time: 00:00:03
Finished recover at 2012-09-23 11:11:19

SQL> select * from jiujian;

DES
------------------------------
zhangxu love oracle

SQL> select * from v$database_block_corruption;

no rows selected
可看到運行 recover …. Block 命令恢複指定的塊後清除視圖v$database_block_corrutption中壞塊內容

2對視圖V$DATABASE_BLOCK_CORRUPTION中的所有壞塊進行修複 Recover corruption list;此處已經重新構造了壞塊）
[oracle@oracle ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Sep 24 21:41:42 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CRM (DBID=3599153036)

RMAN> recover corruption list;

Starting recover at 2012-09-24 21:42:01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=131 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /backup/20120913_ebnl4bil_1_1
channel ORA_DISK_1: piece handle=/backup/20120913_ebnl4bil_1_1 tag=TAG20120913T195604
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished recover at 2012-09-24 21:42:47

SQL>select * from v$database_block_corruption;

no rows selected

總結：資料區塊恢複命令 recover datafile xxx  block xxx 和命令 recover corruption list 都可以恢複壞塊，並自動清除視圖v$database_block_corruption 中關於坏塊的記錄

本文出自 “myblog” 部落格，請務必保留此出處http://jiujian.blog.51cto.com/444665/1006025