首页 > 代码库 > ora-01578

ora-01578

SQL> exec  DBMS_STATS.GATHER_DATABASE_STATS;BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 4, block # 870212)ORA-01110: data file 4:‘/s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf‘ORA-06512: at "SYS.DBMS_STATS", line 15188ORA-06512: at "SYS.DBMS_STATS", line 15530ORA-06512: at "SYS.DBMS_STATS", line 15674ORA-06512: at "SYS.DBMS_STATS", line 15638ORA-06512: at line 1使用RMAN blockreocver命令试图修改该物理坏块:RMAN> blockrecover datafile 4 block 870212;Starting blockrecover at 08-NOV-12channel ORA_DISK_1: restored block(s) from backup piece 1piece handle=/s01/flash_recovery_area/G10R25/backupset/2012_08_06/o1_mf_nnndf_TAG20120806T075500_81zd4njn_.bkp tag=TAG20120806T075500channel ORA_DISK_1: block restore complete, elapsed time: 00:01:16starting media recoveryarchive log thread 1 sequence 467 is already on disk as file /s01/flash_recovery_area/G10R25/archivelog/2012_10_31/o1_mf_1_467_893571cm_.arcarchive log thread 1 sequence 468 is already on disk as file /s01/flash_recovery_area/G10R25/archivelog/2012_10_31/o1_mf_1_468_893pc84l_.arcarchive log thread 1 sequence 469 is already on disk as file /s01/flash_recovery_area/G10R25/archivelog/2012_11_01/o1_mf_1_469_894zsbym_.arcarchive log thread 1 sequence 470 is already on disk as file /s01/flash_recovery_area/G10R25/archivelog/2012_11_01/o1_mf_1_470_896b944y_.arc4_.arcRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of blockrecover command at 11/08/2012 06:19:40RMAN-06053: unable to perform media recovery because of missing logRMAN-06025: no backup of log thread 1 seq 466 lowscn 27762151 found to restoreRMAN-06025: no backup of log thread 1 seq 465 lowscn 27762145 found to restoreRMAN-06025: no backup of log thread 1 seq 464 lowscn 27762142 found to restore

 

由于缺少必要的归档日志导致blockrecover无法成功,需要另想办法。

首先确认该数据块属于哪个SEGMENT,如果是INDEX那么完全可以重建也不会丢失数据,但是如果是表数据则需要容忍丢失该坏块内的数据:

SQL> col tablespace_name for a20 SQL> col segment_type for a10SQL> col segment_name for a20SQL> col owner for a8SQL> SELECT tablespace_name, segment_type, owner, segment_name  2  FROM dba_extents  3  WHERE file_id = &fileid  4  and &blockid between block_id AND block_id + blocks - 1;Enter value for fileid: 4old   3: WHERE file_id = &fileidnew   3: WHERE file_id = 4Enter value for blockid: 870212old   4: and &blockid between block_id AND block_id + blocks - 1new   4: and 870212 between block_id AND block_id + blocks - 1TABLESPACE_NAME      SEGMENT_TY OWNER    SEGMENT_NAME-------------------- ---------- -------- --------------------USERS                TABLE      SYS      CORRUPT_MESQL> select count(*) from CORRUPT_ME;select count(*) from CORRUPT_ME                     *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 4, block # 870212)ORA-01110: data file 4:‘/s01/oradata/G10R25/datafile/o1_mf_users_7ch7d4nx_.dbf‘SQL> analyze table corrupt_me validate structure;analyze table corrupt_me validate structure*ERROR at line 1:ORA-01498: block check failure - see trace fileSQL> oradebug setmypidStatement processed.SQL> oradebug tracefile_name/s01/admin/G10R25/udump/g10r25_ora_19749.trcCorrupt block relative dba: 0x010d4744 (file 4, block 870212)Bad header found during buffer readData in bad block: type: 6 format: 2 rdba: 0x000d4744 last change scn: 0x0000.00000000 seq: 0xff flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x000006ff check value in block header: 0x6323 computed block checksum: 0x0Reread of rdba: 0x010d4744 (file 4, block 870212) found same corrupted data*** 2012-11-08 06:23:12.564table scan: segment: file# 4 block# 870211            skipping corrupt block file# 4 block# 870212*** 2012-11-08 06:23:36.955table scan: segment: file# 4 block# 870211            skipping corrupt block file# 4 block# 870212skipping corrupted block at rdba: 0x010d4744

 下面使用10231 level 10事件来避免发生ORA-01578错误,并将原坏块表复制出来:

SQL> alter session set events ‘10231 trace name context forever,level 10‘;Session altered.SQL>  select count(*) from CORRUPT_ME;  COUNT(*)----------     50857SQL> create table corrupt_me_copy tablespace users as select * from  CORRUPT_ME;Table created.SQL> analyze table corrupt_me_copy validate  structure;Table analyzed.

 之后仅需要将新表rename为旧表,并重建索引即可:

SQL>  alter table corrupt_me rename to corrupt_me_copy1;Table altered.SQL> alter table corrupt_me_copy rename to corrupt_me;Table altered.SQL> rebuild indexs

ora-01578