mysql 数据库坏块_ORA-01578坏块解决(1) – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 1342964878...

一、创建测试表

SQL> create table t_rep as

2  select * from all_objects;

Table created.

SQL> select count(*) from  t_rep;

COUNT(*)

———-

49857

二、使用bbed修改数据块

三、错误现象

1、sqlplus窗口

SQL> select count(*) from  t_rep;

select count(*) from  t_rep

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 1477)

ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’

2、alert.log文件中

Sun Aug 14 22:01:14 2011

Hex dump of (file 6, block 1477) in trace file /opt/oracle/admin/test/udump/test_ora_10785.trc

Corrupt block relative dba: 0x018005c5 (file 6, block 1477)

Bad check value found during buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x018005c5

last change scn: 0×0000.001328ef seq: 0×2 flg: 0×04

spare1: 0×0 spare2: 0×0 spare3: 0×0

consistency value in tail: 0x28ef0602

check value in block header: 0×493

computed block checksum: 0x44b9

Reread of rdba: 0x018005c5 (file 6, block 1477) found same corrupted data

Sun Aug 14 22:01:15 2011

Corrupt Block Found

TSN = 6, TSNAME = XFF

RFN = 6, BLK = 1477, RDBA = 25167301

OBJN = 52727, OBJD = 52728, OBJECT = T_REP, SUBOBJECT =

SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment

四、验证是否真的坏块

1、dbv验证

[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:08:37 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf

Page 1477 is marked corrupt

Corrupt block relative dba: 0x018005c5 (file 6, block 1477)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x018005c5

last change scn: 0×0000.001328ef seq: 0×2 flg: 0×04

spare1: 0×0 spare2: 0×0 spare3: 0×0

consistency value in tail: 0x28ef0602

check value in block header: 0×493

computed block checksum: 0x44b9

DBVERIFY – Verification complete

Total Pages Examined         : 2560

Total Pages Processed (Data) : 1371

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 48

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 1140

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 1256043 (0.1256043)

2、rman验证

RMAN> backup check logical validate datafile 6;

Starting backup at 2011-08-14 22:09:51

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2011-08-14 22:09:53

RMAN> exit

Recovery Manager complete.

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:10:00 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  select file#,block#,blocks from v$database_block_corruption;SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);

FILE#     BLOCK#     BLOCKS

———- ———- ———-

6       1477          1

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

SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);

PL/SQL procedure successfully completed.

SQL> select skip_corrupt from dba_tables where table_name=’T_REP’;

SKIP_COR

——–

ENABLED

SQL> select count(*) from t_rep;

COUNT(*)

———-

49794

说明：数据发生丢失6号文件的1477块中的数据丢失