oracle 跳过某块,Oracle 恢复坏块方法（ORA-01578)

一、创建测试表

SQL> create table test as select * from all_objects;

Table created.

SQL> select count(*) from test;

COUNT(*)

----------

80441

二、人为制造坏块

具体请参考：

http://blog.youkuaiyun.com/shiyu1157758655/article/details/79043034

和

http://blog.youkuaiyun.com/shiyu1157758655/article/details/79043618

三、错误现象

1.sqlplus窗口

SQL> select count(*) from test;

select count(*) from test

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 147)

ORA-01110: data file 5: '/data/CEBPM/datafile/test01.dbf'

2.alter.log文件中

Hex dump of (file 5, block 147) in trace file /u01/app/oracle/diag/rdbms/cebpm/cebpm/trace/cebpm_ora_8880.trc

Corrupt block relative dba: 0x01400093 (file 5, block 147)

Bad check value found during user buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x01400093

last change scn: 0x0000.0012c1e9 seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0xc1e90602

check value in block header: 0x4f1e

computed block checksum: 0x33c4

Reading datafile '/data/CEBPM/datafile/test01.dbf' for corruption at rdba: 0x01400093 (file 5, block 147)

Reread (file 5, block 147) found same corrupt data (no logical check)

Mon Jan 15 08:44:21 2018

Corrupt Block Found

TSN = 5, TSNAME = TEST

RFN = 5, BLK = 147, RDBA = 20971667

OBJN = 83326, OBJD = 83326, OBJECT = TEST, SUBOBJECT =

SEGMENT OWNER = TEST, SEGMENT TYPE = Table Segment

Errors in file /u01/app/oracle/diag/rdbms/cebpm/cebpm/trace/cebpm_ora_8880.trc (incident=52714):

ORA-01578: ORACLE data block corrupted (file # 5, block # 147)

ORA-01110: data file 5: '/data/CEBPM/datafile/test01.dbf'

Incident details in: /u01/app/oracle/diag/rdbms/cebpm/cebpm/incident/incdir_52714/cebpm_ora_8880_i52714.trc

Mon Jan 15 08:44:24 2018

Dumping diagnostic data in directory=[cdmp_20180115084424], requested by (instance=1, osid=8880), summary=[incident=52714].

Mon Jan 15 08:44:24 2018

Sweep [inc][52714]: completed

Sweep [inc2][52714]: completed

四、验证是否坏块

1.dbv 验证

cebpm:/home/oracle@cebpm>dbv file=/data/CEBPM/datafile/test01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jan 15 08:46:29 2018

DBVERIFY - Verification starting : FILE = /data/CEBPM/datafile/test01.dbf

Page 147 is marked corrupt

Corrupt block relative dba: 0x01400093 (file 5, block 147)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x01400093

last change scn: 0x0000.0012c1e9 seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0xc1e90602

check value in block header: 0x4f1e

computed block checksum: 0x33c4

DBVERIFY - Verification complete

Total Pages Examined : 4096

Total Pages Processed (Data) : 1158

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 161

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 2775

Total Pages Marked Corrupt : 2

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 1229337 (0.1229337)

2.rman验证

RMAN> backup check logical validate datafile 5;

Starting backup at 2018/01/15 08:49:17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=53 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=/data/CEBPM/datafile/test01.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 2775 4096 1229337

File Name: /data/CEBPM/datafile/test01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 2 1160

Index 0 0

Other 0 161

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/cebpm/cebpm/trace/cebpm_ora_9284.trc for details

Finished backup at 2018/01/15 08:49:25

SQL> select file#,block#,blocks from v$database_block_corruption;

FILE# BLOCK# BLOCKS

---------- ---------- ----------

5 147 1

五、跳过坏块读取其他数据

SQL> exec dbms_repair.skip_corrupt_blocks('TEST','TEST');

PL/SQL procedure successfully completed.

SQL> select skip_corrupt from user_tables where table_name='TEST';

SKIP_COR

--------

ENABLED

SQL> select count(*) from test;

COUNT(*)

----------

80353

说明：数据发生丢失5号文件的147块中的数据丢失

oracle 跳过某块,Oracle 恢复坏块方法（ORA-01578)—跳过坏块