利用RMAN修复数据文件中的坏块
1 、创建测试表：
SQL> create table tab_dbv tablespace ghost as select * from dba_objects;
表已创建。
SQL> insert into tab_dbv select * from tab_dbv;
已创建 50363 行。
SQL> insert into tab_dbv select * from tab_dbv;
已创建 100726 行。
SQL> commit;
提交完成。
2 、查找 tab_dbv 的块头：
SQL> select segment_name,header_block from dba_segments where segment_name='TAB_DBV';
SEGMENT_NAME HEADER_BLOCK
------------------------- ------------
TAB_DBV 35
SQL> select to_char(35*8*1024,'xxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(35*8*1024,'XXXX
-----------------------
46000
SQL> select to_char(36*8*1024,'xxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(36*8*1024,'XXXX
-----------------------
48000
我们使用 UE 修改 46000H 到 48000H 之间的数据。
启动数据库，查询：
SQL> select count(*) from tab_dbv;
select count(*) from tab_dbv
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 560)
ORA-01110: data file 6: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\HUNT\GHOST_001.DBF'
使用 DBV 对数据文件进行检查：
C:\>dbv file='E:\oracle\product\10.2.0\oradata\hunt\GHOST_001.DBF'
......
Page 560 is marked corrupt
Corrupt block relative dba: 0x01800230 (file 6, block 560) --- 第一个坏块位置
……
Page 576 is marked corrupt
Corrupt block relative dba: 0x01800240 (file 6, block 576) --- 第二个坏块位置
DBVERIFY - Verification complete
Total Pages Examined : 14080
Total Pages Processed (Data) : 2782
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 88
Total Pages Failing (Index): 0
Total Pages Processed (Other): 80
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 11128
Total Pages Marked Corrupt : 2 ----- 有两个块损坏。
Total Pages Influx : 0
Highest block SCN : 1255980 (0.1255980)
2 、检查坏块：
RMAN> backup validate database;
SQL>select * from v$database_block_corruption;
|
| FILE#
| BLOCK#
| BLOCKS
| CORRUPTION_CHANGE#
| CORRUPTION_TYPE
|
| 1 | 6 | 576 | 1 | 0 | CORRUPT |
| 2 | 6 | 560 | 1 | 0 | CORRUPT |
与 DBV 报出的结果一致。
3 、修复坏块：
RMAN> blockrecover datafile 6 block 560;
--- 此命令是 10G 才有的， 9I 不支持。
RMAN> backup validate datafile 6;
--- 重新检验。
SQL>select * from v$database_block_corruption;
|
| FILE#
| BLOCK#
| BLOCKS
| CORRUPTION_CHANGE#
| CORRUPTION_TYPE
|
| 1 | 6 | 576 | 1 | 0 | CORRUPT |
SQL> select count(*) from tab_dbv;
select count(*) from tab_dbv
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 576)
--- 此时报 576 损坏，说明 560 已修复
ORA-01110: data file 6: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\HUNT\GHOST_001.DBF'
说明：
存在坏块时 RMAN 备份无法进行，如：
RMAN> backup datafile 6;
Starting backup at 26-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\HUNT\GHOST_001.DBF
channel ORA_DISK_1: starting piece 1 at 26-MAY-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/26/2012 08:54:40
ORA-19566: exceeded limit of 0 corrupt blocks for file E:\ORACLE\PRODUCT\10.2.0\ORADATA\HUNT\GHOST_001.DBF
我们可以修改 corrupt 的允许数量（默认为 0 ）对数据文件进行备份。在执行 RUN 脚本时指定 maxcorrupt 即可：
RUN {
Set maxcorrupt for datafile 6 to 1;
Backup datafile 6;
…..
}
在命令行方式下执行会报错：
RMAN> Set maxcorrupt for datafile 6 to 1;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03031: this option of set command needs to be used inside a run block
按同样方法修复 (file # 6, block # 576) ：
RMAN> blockrecover datafile 6 block 576;
RMAN> backup validate datafile 6;
SQL>select * from v$database_block_corruption;
no rows selected
查询数据：
SQL> select count(*) from tab_dbv;
COUNT(*)
----------
201452
修复成功！