ORACLE-坏块修复

 

RMAN备份报错如下：

[oracle@cuug ~]$ tail -f ora_rman.log

channel c2: starting full datafile backupset

channel c2: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/prod/system01.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/prod/example01.dbf

channel c2: starting piece 1 at 10-MAY-14

channel c3: starting full datafile backupset

channel c3: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/prod/users01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/prod/sysaux01.dbf

channel c3: starting piece 1 at 10-MAY-14

channel c2: starting full datafile backupset

channel c2: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/prod/system01.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/prod/example01.dbf

channel c2: starting piece 1 at 10-MAY-14

channel c3: starting full datafile backupset

channel c3: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/prod/users01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/prod/sysaux01.dbf

channel c3: starting piece 1 at 10-MAY-14RMAN-03009: failure of backup command on c3 channel at 05/10/2014 22:08:40

ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/prod/sysaux01.dbf

continuing other job steps, job failed will not be re-run

channel c3: starting full datafile backupset

channel c3: specifying datafile(s) in backupset

including current control file in backupset

channel c3: starting piece 1 at 10-MAY-14

channel c2: finished piece 1 at 10-MAY-14

piece handle=/u01/backup/full_db_0ep7veju_1_1_847231614.bak tag=TAG20140510T220654 comment=NONE

channel c2: backup set complete, elapsed time: 00:01:57

channel c2: starting full datafile backupset

channel c2: specifying datafile(s) in backupset

channel c3: finished piece 1 at 10-MAY-14

piece handle=/u01/backup/full_db_0gp7vena_1_1_847231722.bak tag=TAG20140510T220654 comment=NONE

channel c3: backup set complete, elapsed time: 00:00:11

including current SPFILE in backupset

channel c2: starting piece 1 at 10-MAY-14

channel c2: finished piece 1 at 10-MAY-14

piece handle=/u01/backup/full_db_0hp7venl_1_1_847231733.bak tag=TAG20140510T220654 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:05

channel c1: finished piece 1 at 10-MAY-14

piece handle=/u01/backup/full_db_0dp7veju_1_1_847231614.bak tag=TAG20140510T220654 comment=NONE

channel c1: backup set complete, elapsed time: 00:02:40

released channel: c1

released channel: c2

released channel: c3

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

 

RMAN-03009: failure of backup command on c3 channel at 05/10/2014 22:08:40

ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/prod/sysaux01.dbf

 

 

查看告警日志，错误如下

Sat May 10 22:08:31 2014

Hex dump of (file 3, block 30825) in trace file /u01/app/oracle/admin/prod/udump/prod_ora_3545.trc

Corrupt block relative dba: 0x00c07869 (file 3, block 30825)

Bad check value found during backing up datafile

Data in bad block:

 type: 32 format: 2 rdba: 0x00c07869

 last change scn: 0x0000.0034095a seq: 0x2 flg: 0x04

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

 consistency value in tail: 0x095a2002

 check value in block header: 0x4687

 computed block checksum: 0x10e0

Reread of blocknum=30825, file=/u01/app/oracle/oradata/prod/sysaux01.dbf. found same corrupt data

Reread of blocknum=30825, file=/u01/app/oracle/oradata/prod/sysaux01.dbf. found same corrupt data

Reread of blocknum=30825, file=/u01/app/oracle/oradata/prod/sysaux01.dbf. found same corrupt data

Reread of blocknum=30825, file=/u01/app/oracle/oradata/prod/sysaux01.dbf. found same corrupt data

Reread of blocknum=30825, file=/u01/app/oracle/oradata/prod/sysaux01.dbf. found same corrupt data

 

检查可知 ：是 数据文件 sysaux01.dbf. 的 blocknum=30825

 

查看该数据块 所属哪个对象

SYS @ prod >SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents where file_id =3 and 30825 between block_id AND block_id + blocks - 1;

 

TABLESPACE_NAME      SEGMENT_TYPE       OWNER  SEGMENT_NAME

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

SYSAUX          INDEX PARTITION    SYS   WRH$_SYSSTAT_PK

 

该数据块 所属 分区索引：WRH$_SYSSTAT_PK

 

查看该 分区索引的定义

 

SELECT DBMS_METADATA.GET_DDL('INDEX','WRH$_SYSSTAT_PK','SYS') FROM DUAL;

 

  CREATE UNIQUE INDEX "SYS"."WRH$_SYSSTAT_PK" ON "SYS"."WRH$_SYSSTAT" ("DBID", "SNAP_ID", "INSTANCE_NUMBER", "STAT_ID")

  PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT)

  TABLESPACE "SYSAUX"  LOCAL

 (PARTITION "WRH$_SYSSTA_256363152_110"

  PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SYSAUX" ,

 PARTITION "WRH$_SYSSTAT_MXDB_MXSN"

  PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SYSAUX" )

 

重建该分区索引

SYS @ prod >alter index WRH$_SYSSTAT_PK rebuild online;

alter index WRH$_SYSSTAT_PK rebuild online

            *

ERROR at line 1:

ORA-14086: a partitioned index may not be rebuilt as a whole

 

SYS @ prod >alter index WRH$_SYSSTAT_PK rebuild PARTITION "WRH$_SYSSTA_256363152_110";

 

Index altered.

 

SYS @ prod >alter index WRH$_SYSSTAT_PK rebuild PARTITION "WRH$_SYSSTAT_MXDB_MXSN";

 

Index altered.

 

oerr工具查看该报错信息

[oracle@cuug backup]$ oerr ORA 14086

14086, 00000, "a partitioned index may not be rebuilt as a whole"

// *Cause:  User attempted to rebuild a partitioned index using

//          ALTER INDEX REBUILD statement, which is illegal

// *Action: Rebuild the index a partition at a time (using

//          ALTER INDEX REBUILD PARTITION) or drop and recreate the

//          entire index

 

 

 

 

 

 

 

用dbv工具检测该数据文件

[oracle@cuug ~]$ dbv file=/u01/app/oracle/oradata/prod/sysaux01.dbf

 

DBVERIFY: Release 10.2.0.1.0 - Production on Sat May 10 22:40:41 2014

 

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

 

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/prod/sysaux01.dbf

Page 30825 is marked corrupt

Corrupt block relative dba: 0x00c07869 (file 3, block 30825)

Bad check value found during dbv:

Data in bad block:

 type: 32 format: 2 rdba: 0x00c07869

 last change scn: 0x0000.0034095a seq: 0x2 flg: 0x04

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

 consistency value in tail: 0x095a2002

 check value in block header: 0x4687

 computed block checksum: 0x10e0

 

 

 

DBVERIFY - Verification complete

 

Total Pages Examined         : 32000

Total Pages Processed (Data) : 3655

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 3091

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 13213

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 12040

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 6213704 (0.6213704)

 

 

 

修复坏块

RMAN> blockrecover datafile 3 block 30825;

 

Starting blockrecover at 10-MAY-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=141 devtype=DISK

 

starting media recovery

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

 

Finished blockrecover at 10-MAY-14

 

再次查看告警日志