check and recover data block corrupted

来源：互联网发布：淘宝海景房编辑：程序博客网时间：2024/06/08 01:50

一、测试手工生成数据文件坏块
创建测试表：
SQL> create tablespace blocktest datafile '/u01/app/oradata/amber/blocktest01.dbf' size 1m;

SQL> select file#,name from v$datafile;

FILE# NAME
---------- ----------------------------------------
1 /u01/app/oradata/amber/system01.dbf
2 /u01/app/oradata/amber/sysaux01.dbf
3 /u01/app/oradata/amber/undotbs01.dbf
4 /u01/app/oradata/amber/users01.dbf
5 /u01/app/oradata/amber/blocktest01.dbf

SQL> create table test tablespace blocktest as select file#,name from v$datafile;
SQL> insert into test select * from test;
SQL> /
SQL> /
直到ORA-1653: unable to extend table SYS.TEST by 8 in tablespace BLOCKTEST
SQL> commit;
SQL> alter system checkpoint;

备份测试测试数据文件：
[oracle@amber amber]$ rman target /
RMAN> backup datafile 5;

当然，手工生成坏块的方式很多：dd命令，ultraedit，bbed等等，下面我们用ultraedit编辑的方式：
SQL> alter tablespace BLOCKTEST offline;
sz blocktest01.dbf
用ultraedit打开并在中部编辑制造坏块
rz
SQL> alter tablespace BLOCKTEST online;

SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 41)
ORA-01110: data file 5: '/u01/app/oradata/amber/blocktest01.dbf'

二、数据文件坏块检测：
①用dbv命令检查数据文件坏块：
[oracle@amber amber]$ dbv file=./blocktest01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 26 22:39:02 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oradata/amber/blocktest01.dbf
Page 41 is marked corrupt
Corrupt block relative dba: 0x01400029 (file 5, block 41)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01400029
last change scn: 0x0000.0005d928 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xd9280601
check value in block header: 0xe136
computed block checksum: 0x565f

DBVERIFY - Verification complete

Total Pages Examined : 128
Total Pages Processed (Data) : 109
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 17
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 383272 (0.383272)

②用rman检测数据文件坏块：
RMAN> backup validate datafile 5;

Starting backup at 26-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oradata/amber/blocktest01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 FAILED 0 1 128 383372
File Name: /u01/app/oradata/amber/blocktest01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 110
Index 0 0
Other 0 17

validate found one or more corrupt blocks
See trace file /u01/app/diag/rdbms/amber/amber/trace/amber_ora_4783.trc for details
Finished backup at 26-NOV-13

查看坏块的trace文件：
[oracle@amber amber]$ more /u01/app/diag/rdbms/amber/amber/trace/amber_ora_4783.trc
Trace file /u01/app/diag/rdbms/amber/amber/trace/amber_ora_4783.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/product/11.2.0.4
System name: Linux
Node name: amber
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: amber
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 4783, image: oracle@amber (TNS V1-V3)

*** 2013-11-26 22:42:44.753
*** SESSION ID:(31.87) 2013-11-26 22:42:44.753
*** CLIENT ID:() 2013-11-26 22:42:44.753
*** SERVICE NAME:(SYS$USERS) 2013-11-26 22:42:44.753
*** MODULE NAME:(backup full datafile) 2013-11-26 22:42:44.753

*** ACTION NAME:(0000015 STARTED19) 2013-11-26 22:42:44.753

Hex dump of (file 5, block 41)
Dump of memory from 0xB7B5B000 to 0xB7B5D000
B7B5B000 0000A206 01400029 0005D928 06010000 [....).@.(.......]
B7B5B010 0000E136 00000001 00003F95 0005D917 [6........?......]
B7B5B020 00000000 00320002 01400028 00210004 [......2.(.@...!.]
B7B5B030 00000113 00C01793 00200044 000020A2 [........D. .. ..]

③Analyze命令：

SQL> analyze table test validate structure;

analyze table test validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 41)

ORA-01110: data file 5: '/u01/app/oradata/amber/blocktest01.dbf'

也可以通过数据字典查询坏块：

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 41 1 0 CHECKSUM

三、数据文件坏块修复：
①在有rman可用备份的情况下，用rman恢复坏块
[oracle@amber amber]$ rman target /
RMAN> recover datafile 5 block 41;

Starting recover at 26-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=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 /u01/app/product/11.2.0.4/dbs/09opv866_1_1
channel ORA_DISK_1: piece handle=/u01/app/product/11.2.0.4/dbs/09opv866_1_1 tag=TAG20131126T222926
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 26-NOV-13

SQL> select count(*) from test;

COUNT(*)
----------
10240

②用dbms_repair包修复
当然在用rman或者dbv检测到数据文件有坏块后，我们可以通过下面语句查出坏块所在的segment，然后用dbms_repair包修复该坏块。
select owner,file_id,segment_name, segment_type, block_id, blocks from dba_extents where file_id=5 and block_id<=41 and (block_id + blocks-1) >= 41;

（待续）