ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库坏块解决案例一则

数据库坏块解决案例一则

原创 Linux操作系统 作者:myownstars 时间:2010-12-13 13:41:27 0 删除 编辑
收到开发人员报告,oracle出现坏块,查看alert.log,信息如下:
Hex dump of (file 7, block 1407500) in trace file /data/oracle/admin/orcl/bdump/orcl_p004_7383.trc
Corrupt block relative dba: 0x01d57a0c (file 7, block 1407500)
Fractured block found during crash/instance recovery
Data in bad block:
type: 6 format: 2 rdba: 0x01d57a0c
last change scn: 0x0000.e02e5f2d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x88150601
check value in block header: 0x462f
computed block checksum: 0xd738
Reread of rdba: 0x01d57a0c (file 7, block 1407500) found same corrupted data



解决思路:

首先确认该坏块所属对象,发现为索引,名字为IDX_D_D_PRODUCT_ID
确认该坏块属于索引IDX_D_D_PRODUCT_ID。先重建索引暂时解决问题:
SQL> alter index IDX_D_D_PRODUCT_ID rebuild online tablespace pur_index ;

Index altered.
注:必须加上online关键字,一则因为是线上环境,二则不加Online会在原索引基础上重建,不会解决问题,即加上关键字online, 重建后的索引会从表里取数据

使用dbv检查:
[oracle@rac1 ~]$ dbv file=/data/oracle/oradata/orcl/justin03.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Thu Jul 29 10:53:48 2010

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

DBVERIFY - Verification starting : FILE = /data/oracle/oradata/orcl/justin03.dbf

DBV-00200: Block, dba 30767628, already marked corrupted

DBVERIFY - Verification complete

Total Pages Examined : 4194302
Total Pages Processed (Data) : 3508146
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 605887
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75876
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 3781089739 (0.3781089739)

我们也可以使用如下过程确定坏块属于哪个block:

SQL> select dbms_utility.data_block_address_file(30767628) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(30767628)



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

7

Elapsed: 00:00:00.23
SQL> select dbms_utility.data_block_address_block(30767628) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(30767628)



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

1407500



其次要确认坏块类型是物理坏块还是逻辑坏块
physical corruption check: backup validate datafile 'filename';
logical corruption check: backup check logical validate datafile 'filename'

先进行物理坏块检测
RMAN> backup validate datafile 7;

Starting backup at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1736 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/data/oracle/oradata/orcl/justin03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 29-JUL-10

sys@std01> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO



--------------------------------------------------------------------------------
---------- ---------- ------------------ ---------
7 1407500 1 0 CORRUPT

确认为物理坏块

最后也是最重要的一步,进行坏块恢复,
RMAN> BLOCKRECOVER CORRUPTION LIST;

Starting blockrecover at 29-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=455 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /backup/rman/rac1_ORCL_20100729_eoljvn41_1_1.bak


channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/rman/rac1_ORCL_20100729_eoljvn41_1_1.bak tag=TAG20100729T050504
channel ORA_DISK_1: block restore complete, elapsed time: 00:57:56
failover to previous backup

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /backup/rman/rac1_ORCL_20100728_eiljt2ju_1_1.bak


channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/rman/rac1_ORCL_20100728_eiljt2ju_1_1.bak tag=TAG20100728T050253
channel ORA_DISK_1: block restore complete, elapsed time: 00:58:36

starting media recovery

media recovery complete, elapsed time: 00:12:15

Finished blockrecover at 29-JUL-10

命令完成,分别使用DBV和backup validate命令进行验证
[oracle@rac1 ~]$ dbv file=/data/oracle/oradata/orcl/justin03.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Thu Jul 29 19:04:45 2010

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

DBVERIFY - Verification starting : FILE = /data/oracle/oradata/orcl/justin03.dbf


DBVERIFY - Verification complete

Total Pages Examined : 4194302
Total Pages Processed (Data) : 3507791
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 606239
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75879
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3785553739 (0.3785553739)

使用 backup validate datafile 进行验证:
验证前:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO



--------------------------------------------------------------------------------
---------- ---------- ------------------ ---------
7 1407500 1 0 CORRUPT

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 29 21:17:10 2010

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

connected to target database: ORCL (DBID=1185342296)

RMAN> backup validate datafile 7;

Starting backup at 29-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=455 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/data/oracle/oradata/orcl/justin03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
Finished backup at 29-JUL-10

执行完上述命令,坏块从数据字典里消失:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected

问题得到解决。

[ 本帖最后由 myownstars 于 2010-12-15 15:43 编辑 ]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-688357/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3158685