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

本文主要是介绍Oracle 恢复坏块方法（ORA-01578)—跳过坏块，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、创建测试表

SQL> create table test as select * from all_objects;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
80441

二、人为制造坏块

具体请参考：

http://blog.csdn.net/shiyu1157758655/article/details/79043034

和

http://blog.csdn.net/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
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
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

这篇关于Oracle 恢复坏块方法（ORA-01578)—跳过坏块的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---