【识记】修复oracle的坏块

使用blockrecover 对有坏块的数据文件进行恢复。

先做一个测试环境。
SQL> create tablespace tbs4 datafile '/oracle/oradata/pradb/tbs4.dbf' size 20m;

Tablespace created.

SQL> create table test_blk tablespace tbs4 as select * from dba_objects;

Table created.

SQL> select count(*) from test_blk;

COUNT(*)
----------
50720

到此做一个数据的全备份。
RMAN> run{
2> allocate channel dev1 type disk;
3> allocate channel dev2 type disk;
4> backup tag='db_full_back_200907011656' database;
5> release channel dev1;
6> release channel dev2;
7> }

为了保证数据文本版本的一致性，我把数据库关闭后模拟数据块的损坏。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

用二进制编辑器编辑数据文件tbs4.dbf，我用的是FlexHEX.
说明:用vi或windows下的notepad都会造成整个数据文件的损坏，不建议使用文本编辑器。
把数据文本下载到windows下，用FlexHEX打开后，在主编辑窗口修改几个数值，然后保存。
把原来的数据文件tbs4.dbf改名为tbs4_back.dbf.然后把编辑过的文件传回来。

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size                  1267044 bytes
Variable Size             155191964 bytes
Database Buffers          121634816 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.

数据块有损坏，在启动过程中不会有任何错误提示。
但是我们在对驻留在该文件中的表做查询时就会报错：
SQL> select count(*) from test_blk;
select count(*) from test_blk
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 56)
ORA-01110: data file 14: '/oracle/oradata/pradb/tbs4.dbf'

根据提示我们发现该文件有数据块损坏。
我们用dbv实用程序对该文件做数据块的检验:
[oracle@ceedb pradb]$ dbv file=tbs4.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Wed Jul 1 17:14:03 200

DBVERIFY - Verification starting : FILE = tbs4.dbf
Page 56 is marked corrupt
Corrupt block relative dba: 0x03800038 (file 14, block 56)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03800038
last change scn: 0x0000.003598ce seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x98ce0601
check value in block header: 0xd1d8
computed block checksum: 0x2400

Page 107 is marked corrupt
Corrupt block relative dba: 0x0380006b (file 14, block 107)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0380006b
last change scn: 0x0000.003598e2 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x98e20601
check value in block header: 0x4b8b
computed block checksum: 0x3364

Page 276 is marked corrupt
Corrupt block relative dba: 0x03800114 (file 14, block 276)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03800114
last change scn: 0x0000.003598eb seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x98eb0602
check value in block header: 0x384c
computed block checksum: 0x202d

Page 517 is marked corrupt
Corrupt block relative dba: 0x03800205 (file 14, block 517)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03800205
last change scn: 0x0000.003598f3 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x98f30601
check value in block header: 0xab3e
computed block checksum: 0x2d20

DBVERIFY - Verification complete

Total Pages Examined         : 2560
Total Pages Processed (Data) : 693
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 28
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 1835
Total Pages Marked Corrupt   : 4
Total Pages Influx           : 0
Highest block SCN            : 3512569 (0.3512569)

可见有4个块损坏。分别是56，107，276，517块。

我们还可以用
RMAN> backup validate database;
对数据库中的坏块进行验证。
RMAN> backup validate database;

Starting backup at 01-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oradata/pradb/system01.dbf
input datafile fno=00003 name=/oracle/oradata/pradb/sysaux01.dbf
input datafile fno=00007 name=/oracle/oradata/pradb/idx_tbs.dbf
input datafile fno=00005 name=/oracle/oradata/pradb/example01.dbf
input datafile fno=00011 name=/oracle/oradata/pradb/PRADB/datafile/o1_mf_tbs2_52hhn82h_.dbf
input datafile fno=00008 name=/oracle/oradata/pradb/undo2.dbf
input datafile fno=00009 name=/oracle/oradata/pradb/audit_tab.dbf
input datafile fno=00010 name=/oracle/oradata/pradb/audit_idx.dbf
input datafile fno=00002 name=/oracle/oradata/pradb/tbs_test02.dbf
input datafile fno=00006 name=/oracle/oradata/pradb/tbs_test01.dbf
input datafile fno=00012 name=/oracle/oradata/pradb/testtbs.dbf
input datafile fno=00014 name=/oracle/oradata/pradb/tbs4.dbf
input datafile fno=00013 name=/oracle/oradata/pradb/test3.dbf
input datafile fno=00004 name=/oracle/oradata/pradb/users01.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 01-JUL-09

检验后我们查 V$DATABASE_BLOCK_CORRUPTION
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        14        276          1                  0 CHECKSUM
        14        517          1                  0 CHECKSUM
        14        107          1                  0 CHECKSUM
        14         56          1                  0 CHECKSUM

刚才那些坏块都被列入到了视图V$DATABASE_BLOCK_CORRUPTION中。

为了不影响业务的运行，通过RMAN使用oracle BMR(Block Media Recover).
BMR可以比使用restore database节省大量的宕机时间，在恢复期间只有坏块的文件不可访问，而
其他可以继续访问。

RMAN> blockrecover datafile 14 block 56,107,276,517;

Starting blockrecover at 01-JUL-09
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 00014
channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/PRADB/backup...
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/oracle/flash_recovery_area/PRADB/backupset/2009_07_01/o1_mf_nnndf_DB_FUL...
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:04

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

Finished blockrecover at 01-JUL-09

还可以通过RMAN> blockrecover corruption list进行块的恢复，这是在大量块损坏时或全部块损坏时使用，
前提是先执行RMAN>backup validate database,在V$DATABASE_BLOCK_CORRUPTION里有对应的坏块的列表。
RMAN> blockrecover corruption list;

Starting blockrecover at 01-JUL-09
using channel ORA_DISK_1

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

Finished blockrecover at 01-JUL-09

恢复完成后，我们进行一下验证
[oracle@ceedb pradb]$ dbv file=tbs4.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Wed Jul 1 17:31:19 2009

DBVERIFY - Verification starting : FILE = tbs4.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 2560
Total Pages Processed (Data) : 697
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 28
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 1835
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3512569 (0.3512569)

没有报告坏块。
再对test_blk执行查询
SQL> select count(*) from test_blk;

COUNT(*)
----------
50720

可见恢复成功。

posted on 2014-01-21 09:45 tangoo 阅读(268) 评论(0) 编辑收藏举报