利用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
修复成功!