Block Corruption(坏块)
Block Corruption Symptoms: ORA-01578
The error ORA-01578: "ORACLE data block corrupted (file # %s, block # %s)":
错误信息：
在你访问这个个块的时候 给你提示
或者alert.log file
如何处理ora-01578错误
1. 建立一个表空间，数据存储在文件系统上，然后要对表空间备份，用于后面的恢复
create tablespace test datafile '/u01/app/oracle/test01.dbf' size 10M;
create table scott.test tablespace test as select * from scott.emp;
SQL> select count(*) from scott.test;
COUNT(*)
----------
14
rman target
RMAN> backup tablespace "TEST";
2. 找出test表所在的文件编号
select file_id,block_id from dba_extents where segment_name='TEST'and owner='SCOTT';
FILE_ID BLOCK_ID
---------- ----------
6 128
dba_extents 视图
字段1 ： file_id 文件编号
字段2 ： block_id: Starting block number of the extent 区的起始块号
3. 找出文件6的文件名
SQL> select file_name from dba_data_files where file_id=6;
FILE_NAME
--------------------------------
/u01/app/oracle/test01.dbf
4. 脚本：corruption_block.sh
cd home/oracle
vi corruption_block.sh
#!/bin/bash
# Oracle Database 11g: Administration Workshop I
# Oracle Server Technologies - Curriculum Development
#
# ***Training purposes only***
# ***Not appropriate for production use***
#
FILE=${1:?'Parameter 1 should be set to file name'}
BLOCK=${2:?'Parameter 2 should be set to the block to be corrupted'}
BLOCKSIZE=${3:?'Parameter 3 should be set to the database block size'}
dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=$BLOCK <<EOF
CORRUPT
EOF
dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=`expr 1 + $BLOCK` <<EOF
CORRUPT
EOF
dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=`expr 2 + $BLOCK` <<EOF
CORRUPT
EOF
dd of=$FILE bs=$BLOCKSIZE conv=notrunc seek=`expr 3 + $BLOCK` <<EOF
CORRUPT
EOF
chmod u+x corruption_block.sh
5. 执行脚本模拟3个坏块
./corruption_block.sh 文件的名字 文件的块号 块大小
./corruption_block.sh u01/app/oracle/test01.dbf 130 8192
6. 清除缓存
alter system flush buffer_cache;
7. 访问test表
SQL> select * from scott.test;
select * from scott.test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/u01/app/oracle/test01.dbf'
报错，如何找出哪些块坏了。
8. 查找坏块，下面列出几种查找的方法
借助DRA找出坏块
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
588 HIGH OPEN 16-SEP-17 Datafile 6: '/u01/app/oracle/test01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 588
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
609 HIGH OPEN 16-SEP-17 Block 131 in datafile 6: '/u01/app/oracle/test01.dbf' is media corrupt
Impact: Object owned by might be unavailable
603 HIGH OPEN 16-SEP-17 Block 130 in datafile 6: '/u01/app/oracle/test01.dbf' is media corrupt
Impact: Object owned by might be unavailable
597 HIGH OPEN 16-SEP-17 Block 129 in datafile 6: '/u01/app/oracle/test01.dbf' is media corrupt
Impact: Object owned by might be unavailable
591 HIGH OPEN 16-SEP-17 Block 128 in datafile 6: '/u01/app/oracle/test01.dbf' is media corrupt
Impact: Object owned by might be unavailable
使用dbv检查坏块
TIPS: 关于工具如何使用 联机丛书Utilities --> 17 DBVERIFY: Offline Database Verification Utility
$ dbv file=/u01/app/oracle/test01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.1.0 - Production on Sun Sep 16 09:26:55 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = u01/app/oracle/test01.dbf
Page 128 is marked corrupt
Corrupt block relative dba: 0x01800080 (file 6, block 128)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0x0000.00101ec1 seq: 0x2 flg: 0x04
spare1: 0x52 spare2: 0x52 spare3: 0x0
consistency value in tail: 0x1ec12002
check value in block header: 0xd86f
computed block checksum: 0xe430
Page 129 is marked corrupt
Corrupt block relative dba: 0x01800081 (file 6, block 129)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0x0000.00101ebb seq: 0x2 flg: 0x04
spare1: 0x52 spare2: 0x52 spare3: 0x0
consistency value in tail: 0x1ebb2102
check value in block header: 0xa55e
computed block checksum: 0xe430
Page 130 is marked corrupt
Corrupt block relative dba: 0x01800082 (file 6, block 130)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0x0000.00101ec5 seq: 0x1 flg: 0x04
spare1: 0x52 spare2: 0x52 spare3: 0x0
consistency value in tail: 0x1ec52301
check value in block header: 0x88f2
computed block checksum: 0xe431
Page 131 is marked corrupt
Corrupt block relative dba: 0x01800083 (file 6, block 131)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0x0000.00101ec1 seq: 0x2 flg: 0x04
spare1: 0x52 spare2: 0x52 spare3: 0x0
consistency value in tail: 0x1ec10602
check value in block header: 0xc997
computed block checksum: 0xe415
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1149
Total Pages Marked Corrupt : 4
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1056443 (0.1056443)
通过警告日志检查坏块
$ tail -50 u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
Hex dump of (file 6, block 131) in trace file u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_18289/orcl_m001_20434_i18289_a.trc
Corrupt block relative dba: 0x01800083 (file 6, block 131)
Bad header found during validation
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0x0000.00101ec1 seq: 0x2 flg: 0x04
spare1: 0x52 spare2: 0x52 spare3: 0x0
consistency value in tail: 0x1ec10602
check value in block header: 0xc997
computed block checksum: 0xe415
Reread of blocknum=131, file=/u01/app/oracle/test01.dbf. found same corrupt data
Reread of blocknum=131, file=/u01/app/oracle/test01.dbf. found same corrupt data
Reread of blocknum=131, file=/u01/app/oracle/test01.dbf. found same corrupt data
Reread of blocknum=131, file=/u01/app/oracle/test01.dbf. found same corrupt data
Reread of blocknum=131, file=/u01/app/oracle/test01.dbf. found same corrupt data
Hex dump of (file 6, block 130) in trace file u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_18289/orcl_m001_20434_i18289_a.trc
Corrupt block relative dba: 0x01800082 (file 6, block 130)
Bad header found during buffer read
Data in bad block:
type: 67 format: 7 rdba: 0x0a545055
last change scn: 0x0000.00101ec5 seq: 0x1 flg: 0x04
spare1: 0x52 spare2: 0x52 spare3: 0x0
consistency value in tail: 0x1ec52301
check value in block header: 0x88f2
computed block checksum: 0xe431
Reading datafile '/u01/app/oracle/test01.dbf' for corruption at rdba: 0x01800082 (file 6, block 130)
Reread (file 6, block 130) found same corrupt data
Corrupt Block Found
TSN = 7, TSNAME = TEST
RFN = 6, BLK = 130, RDBA = 25165954
OBJN = -1, OBJD = 74600, OBJECT = TEST, SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment
Errors in file u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_20434.trc (incident=18380):
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/u01/app/oracle/test01.dbf'
Incident details in: u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_18380/orcl_m001_20434_i18380.trc
Sun Sep 16 09:26:05 2017
Trace dumping is performing id=[cdmp_20130421092605]
Sun Sep 16 09:26:06 2017
Checker run found 4 new persistent data failures
Trace dumping is performing id=[cdmp_20130421092607]
Sun Sep 16 09:26:59 2017
Sweep [inc][18380]: completed
Sweep [inc][18379]: completed
Sweep [inc][18378]: completed
Sweep [inc][18377]: completed
Sweep [inc2][18380]: completed
Sweep [inc2][18379]: completed
Sweep [inc2][18378]: completed
Sweep [inc2][18377]: completed
通过VALIDATE查出坏块
RMAN> validate datafile 6;
Starting validate at 16-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/u01/app/oracle/test01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 FAILED 0 1147 1280 1050309
File Name: u01/app/oracle/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 4 133
validate found one or more corrupt blocks
See trace file u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_21786.trc for details
Finished validate at 16-SEP-17
通过上面分析，发现有坏块，不能做备份，不能做导入导出 ，坏块的数据无法访问
影响备份：
RMAN> backup datafile 6;
Starting backup at 16-SEP-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/test01.dbf
channel ORA_DISK_1: starting piece 1 at 16-SEP-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/16/2017 01:23:57
ORA-19566: exceeded limit of 0 corrupt blocks for file u01/app/oracle/test01.dbf
影响逻辑备份：
exp system/oracle tables=scott.test file=test.dmp
About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TEST
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/u01/app/oracle/test01.dbf'
Export terminated successfully with warnings.
9. 通过Block Media Recovery修复
(只修复损坏的块，节约时间，在线的时候做）
使用命令 RECOVER...BLOCK 恢复坏块
可以从哪里找数据块来恢复：
flashback logs and full or level 0 backups #还需要日志
V$DATABASE_BLOCK_CORRUPTION 列出所有被标记为坏块的信息
什么时候会把坏块的信息刷新到该视图呢？
当访问到坏块或者使用validate database;
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
6 128 4 0 CORRUPT
使用bmr的前提：一定处于归档模式，你的备份集包含坏块的备份
修复的方法：
修复一个数据文件的一个块
RECOVER DATAFILE 6 BLOCK 3; #Recover a single block
修复多个文件的多个块 #Recover multiple blocks in multiple data files
RECOVER
DATAFILE 2 BLOCK 43
DATAFILE 2 BLOCK 79
DATAFILE 6 BLOCK 183;
上面案例查到有4个坏块，分别是128,129,130,131
rman target
blockrecover datafile 6 block 128,129,130,131
或者
recover datafile 6 block 128 to 131
或者
RECOVER CORRUPTION LIST; #Recover all blocks logged in
完成后再执行一次：
validate tablespace "TEST";
或者用DBV
10. 修复后检查
alter system flush buffer_cache;
SQL> select * from scott.test;
成功
再操作一次主动验证
RMAN> validate database;
……
Finished validate at 16-SEP-17
RMAN> validate tablespace "TEST";
Starting validate at 16-SEP-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/u01/app/oracle/test01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 1149 1280 1047190
File Name: u01/app/oracle/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1
Index 0 0
Other 0 130
Finished validate at 16-SEP-17
如果没有备份，数据库也不是归档模式，使用exp修复数据 肯定有数据丢失。
使用exp/imp恢复
核心思想：就是丢弃哪些损坏的 ，只能访问哪些有用的块
SQL> select * from scott.test;
select * from scott.test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/u01/app/oracle/test01.dbf'
在这种情况下肯定会造成数据的丢失，采取将数据导出然后重建表再进行导入的方法，尽量恢复损坏数据块中的数据，但是在有坏块的情况下是不允许导出的，如下：
exp scott/oracle file=test.dmp tables=test;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table TEST
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/u01/app/oracle/test01.dbf'
Export terminated successfully with warnings.
导出命令在执行中会报ORA-01578错误.
针对警告日志的提示首先查询那些对象被损坏：(查出坏块里存储的是什么对象)
Select tablespace_name,segment_type,owner,segment_name From dba_extents Where file_id=6 and 130 between block_id and block_id+blocks-1;
如果被损坏的块是索引，通常可以通过索引重建来解决，如果损坏的是数据（segment_type为table），那么通过设置如下内部事件使得Exp操作跳过坏块
alter system set events='10231 trace name context forever,level 10';
然后重新执行导出命令，导出相关的表，然后执行Drop Table命令删除相关表，之后重建表最后导入数据
exp scott/oracle file=test.dmp tables=test
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table TEST 99092 rows exported
Export terminated successfully without warnings.
sqlplus / as sysdba
drop table scott.test ;
再导入
$ imp scott/oracle tables=test file=test.dmp;
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "TEST" 99092 rows imported
Import terminated successfully without warnings.
最后关闭该事件
alter system set events='10231 trace name context off';
