很期待10g的blockrecover，可以在线的进行recover，dba的压力相对小多了。
-------------------环境准备：
CREATE TABLESPACE USER_DATA DATAFILE
'/home1/oradata/dwtest/user_data.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 800M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE USER_INDEX DATAFILE
'/home1/oradata/dwtest/user_index.dbf' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 800M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
create table dwtest.test_tab (id number,name varchar2(20)) TABLESPACE USER_DATA;
[@more@]--插入测试记录
declare
begin
for n_row in 1..100000 loop
insert into dwtest.test_tab values(n_row,to_char(n_row));
end loop;
commit;
end;
--创建索引
CREATE INDEX dwtest.id_test_tab ON dwtest.test_tab(id)
tablespace USER_INDEX ;
--------------------------------首先做全备份：
run
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/home1/rmanbak/pcard_%T_%U';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
backup database tag='all';
release CHANNEL dev1;
}
shutdown后，将user_data.dbf传到本地，使用ultraedit进行修改，传回到服务器上。
----------------------------dbv检查
dbv检查可以发现坏块：
[oracle@test55 ~]$ dbv file=/home1/oradata/dwtest/user_data.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Apr 28 12:56:47 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /home1/oradata/dwtest/user_data.dbf
Page 63 is marked corrupt
Corrupt block relative dba: 0x0140003f (file 5, block 63)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0140003f
last change scn: 0x0000.0006f33c seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xf33c0601
check value in block header: 0x4ee0
computed block checksum: 0x4e
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 233
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 30
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12536
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 455485 (0.455485)
-----启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1220820 bytes
Variable Size 167776044 bytes
Database Buffers 432013312 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.
不会提示错误
----使用rman检查文件：
RMAN> backup validate datafile 5;
Starting backup at 28-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/home1/oradata/dwtest/user_data.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-APR-08
rman检查后，alert日志中可以看到坏块的提示：
Mon Apr 28 12:59:17 2008
Hex dump of (file 5, block 63) in trace file /home/oracle/admin/dwtest/udump/dwtest_ora_24211.trc
Corrupt block relative dba: 0x0140003f (file 5, block 63)
Bad check value found during backing up datafile
Data in bad block:
type: 6 format: 2 rdba: 0x0140003f
last change scn: 0x0000.0006f33c seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xf33c0601
check value in block header: 0x4ee0
computed block checksum: 0x4e
Reread of blocknum=63, file=/home1/oradata/dwtest/user_data.dbf. found same corrupt data
Reread of blocknum=63, file=/home1/oradata/dwtest/user_data.dbf. found same corrupt data
Reread of blocknum=63, file=/home1/oradata/dwtest/user_data.dbf. found same corrupt data
Reread of blocknum=63, file=/home1/oradata/dwtest/user_data.dbf. found same corrupt data
Reread of blocknum=63, file=/home1/oradata/dwtest/user_data.dbf. found same corrupt data
--------这个时候，访问v$database_block_corruption可以看到详细的坏块的信息：
SQL> select * from v$database_block_corruption where file#=5;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ---------------------------
5 63 1 0 CHECKSUM
全表扫描会提示错误，但是不是坏块的记录可以访问：
SQL> select count(*) from dwtest.test_tab;
select count(*) from dwtest.test_tab
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 63)
ORA-01110: data file 5: '/home1/oradata/dwtest/user_data.dbf'
SQL> select * from dwtest.test_tab where rownum < 2;
ID NAME
---------- ------------------------------------------------------------
10000 6209
------------使用rman进行块恢复：
RMAN> blockrecover datafile 5 block 63 from backupset;
Starting blockrecover at 28-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 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 /home1/rmanbak/pcard_20080428_01jeuv37_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/home1/rmanbak/pcard_20080428_01jeuv37_1_1 tag=TAG20080428T123239
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished blockrecover at 28-APR-08
--------再进行dbv检查，坏块没有了
[oracle@test55 bdump]$ dbv file=/home1/oradata/dwtest/user_data.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Apr 28 13:04:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /home1/oradata/dwtest/user_data.dbf
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 234
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 30
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12536
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 455485 (0.455485)
应用可以正常访问。
-------------------------------------------------------------------------------------------
下面测试了备份部分表空间恢复的情况，首先删除原来的备份：
delete backupset 5;
-----备份user_data和user_index两个表空间：
RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/home1/rmanbak/pcard_%T_%U';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
BACKUP (TABLESPACE USER_DATA,USER_INDEX
) ;
release CHANNEL dev1;
}
------相同的方法修改user_data.dbf，模拟坏块的出现
[oracle@test55 ~]$ dbv file=/home1/oradata/dwtest/user_data.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Apr 28 17:16:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /home1/oradata/dwtest/user_data.dbf
Page 153 is marked corrupt
Corrupt block relative dba: 0x01400099 (file 5, block 153)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01400099
last change scn: 0x0000.0006f33c seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xf33c0601
check value in block header: 0xbc14
computed block checksum: 0x7954
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 473
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 30
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12296
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 466531 (0.466531)
-----------------------------------使用rman恢复：
RMAN> blockrecover datafile 5 block 153 from backupset ;
Starting blockrecover at 28-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 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 /home1/rmanbak/pcard_20080428_09jevem8_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/home1/rmanbak/pcard_20080428_09jevem8_1_1 tag=TAG20080428T165848
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 28-APR-08
-----------------------块恢复后，v$database_block_corruption得记录仍然存在，需要重新进行backup validate datafile 5;后才会消失。
-----也可以执行BLOCKRECOVER CORRUPTION LIST，会自动按照V$DATABASE_BLOCK_CORRUPTION进行修复。
--检查所有数据文件是否存在坏块
RMAN> BACKUP VALIDATE DATABASE;
Starting backup at 28-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home1/oradata/dwtest/system01.dbf
input datafile fno=00003 name=/home1/oradata/dwtest/sysaux01.dbf
input datafile fno=00005 name=/home1/oradata/dwtest/user_data.dbf
input datafile fno=00006 name=/home1/oradata/dwtest/user_index.dbf
input datafile fno=00002 name=/home1/oradata/dwtest/undotbs01.dbf
input datafile fno=00004 name=/home1/oradata/dwtest/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 28-APR-08
RMAN> BLOCKRECOVER CORRUPTION LIST;
Starting blockrecover at 28-APR-08
using channel ORA_DISK_1
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 /home1/rmanbak/pcard_20080428_09jevem8_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/home1/rmanbak/pcard_20080428_09jevem8_1_1 tag=TAG20080428T165848
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 28-APR-08
------如果在rman备份表空间之前数据文件已经有坏块，则提示错误退出。
Starting backup at 28-APR-08
channel dev1: starting full datafile backupset
channel dev1: specifying datafile(s) in backupset
input datafile fno=00005 name=/home1/oradata/dwtest/user_data.dbf
input datafile fno=00006 name=/home1/oradata/dwtest/user_index.dbf
channel dev1: starting piece 1 at 28-APR-08
released channel: dev1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on dev1 channel at 04/28/2008 18:13:28
ORA-19566: exceeded limit of 0 corrupt blocks for file /home1/oradata/dwtest/user_data.dbf
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/51862/viewspace-1003124/ ，如需转载，请注明出处，否则将追究法律责任。
