oracle10g關于資料庫壞塊的修複
一：建立測試用表
SQL> create table jiujian(des varchar(30)) tablespace zx;
Table created.
SQL> insert into jiujian values('zhangxu love oracle');
1 row created.
查詢該表所在的塊
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
------------------ ---------- ---------- ----------
AAAMmMAAFAAAAAkAAA 5 36 0
二 bbed構造壞塊
1 bbed要加載的檔案清單
[oracle@oracle ~]$ cat filelist
1 /oracle/CRM2/CRM/system01.dbf 503316480
2 /oracle/CRM2/CRM/undotbs01.dbf 26214400
3 /oracle/CRM2/CRM/sysaux01.dbf 251658240
4 /oracle/CRM2/CRM/users01.dbf 5242880
5 /oracle/CRM2/CRM/zx1.dbf
2 bbed的參數選項配置如下
[oracle@oracle ~]$ cat bbed.para
blocksize=8192
mode=edit
listfile=/oracle/filelist
3 運作bbed
[oracle@oracle ~]$ bbed parfile=bbed.para
Password: blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Sep 25 18:39:16 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
4 檢視配置
BBED> show
FILE# 1
BLOCK# 1
OFFSET 0
DBA 0x00400001 (4194305 1,1)
FILENAME /oracle/CRM2/CRM/system01.dbf
BIFILE bifile.bbd
LISTFILE /oracle/filelist
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
5 檢視bbed可處理的資料檔案
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /oracle/CRM2/CRM/system01.dbf 61440
2 /oracle/CRM2/CRM/undotbs01.dbf 3200
3 /oracle/CRM2/CRM/sysaux01.dbf 30720
4 /oracle/CRM2/CRM/users01.dbf 640
5 /oracle/CRM2/CRM/zx1.dbf 0
6 設定目前資料檔案号和目前資料塊
BBED> set dba 5,36
DBA 0x01400024 (20971556 5,36)
7 确認下配置是否正确
FILE# 5
BLOCK# 36
FILENAME /oracle/CRM2/CRM/zx1.dbf
8 查找字元zhangxu的位置
BBED> find /c zhangxu TOP
File: /oracle/CRM2/CRM/zx1.dbf (5)
Block: 36 Offsets: 8169 to 8191 Dba:0x01400024
------------------------------------------------------------------------
7a68616e 67787520 6c6f7665 206f7261 636c6502 061dc6
<32 bytes per line>
9 設定目前偏移量
BBED> set offset 8169
OFFSET 8169
10 從目前偏移量開始顯示資料塊内容
BBED> dump /v
Block: 36 Offsets: 8169 to 8191 Dba:0x01400024
-------------------------------------------------------
7a68616e 67787520 6c6f7665 206f7261 l zhangxu love ora
636c6502 061dc6 l cle...?
<16 bytes per line>
11 用字元jiujian開始從目前偏移量位置進行替換
BBED> modify /c jiujian
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
6a69756a 69616e20 6c6f7665 206f7261 636c6502 061dc6
12 檢視替換後的資料塊内容
6a69756a 69616e20 6c6f7665 206f7261 l jiujian love ora
三 對壞塊就行修複
1 offline，online 改表空間便可看到壞塊的效果
SQL> alter tablespace zx offline;
Tablespace altered.
SQL> alter tablespace zx online;
SQL> select * from jiujian;
select * from jiujian
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 36)
ORA-01110: data file 5: '/oracle/CRM2/CRM/zx1.dbf'
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 28 1 0 CHECKSUM
2 運作blockrecover datafile 5 block 36; 修複該資料塊
RMAN> blockrecover datafile 5 block 36;
Starting blockrecover at 25-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=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/05nkkvst_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/05nkkvst_1_1 tag=TAG20120908T000444
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:04
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 25-SEP-12
3 檢查資料塊的修複過程注意視圖v$database_block_corruption變化
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 25 19:02:04 2012
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
DES
------------------------------
zhangxu love oracle
注意視圖v$database_block_corruption 還有壞塊的記錄！！
4 重新驗證下資料檔案以清除視圖v$database_block_corruption關于壞塊的記錄
RMAN> backup validate datafile 5;
Starting backup at 25-SEP-12
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/oracle/CRM2/CRM/zx1.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 25-SEP-12
no rows selected
四 測試下指令blockrecover corruption list能幹啥 （此處已經重新構造了壞塊）
RMAN> blockrecover corruption list;
channel ORA_DISK_1: sid=142 devtype=DISK
media recovery complete, elapsed time: 00:00:00
注意上面的恢複過程沒有讀取備份片
-------------------------------------------------------------------
2 查詢表jiujian
3 注意此處，已經清除了 v$database_block_corruption 中關于壞塊的記錄
總結：看來10g的blockrecover僅僅清除 v$database_block_corruption中關于壞塊的記錄，并不修複資料塊，和11g的recover corruption list 差别大啊。
本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1006030，如需轉載請自行聯系原作者
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)