问题描述
oracle11.2.0.1,os linux 6.6,存在一个坏块,后面发现对象是一个索引,对索引rebuild重建后,这个坏块还是存在,请教一下如何处理
现在的这个block 没有对象了,在dba_extents上面找不到记录了
专家解答
--未格式化的 See trace file /oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_12278.trc for details Reread of blocknum=1322478, file=+DATA/orcl/datafile/users02.dbf. found same corrupt data Reread of blocknum=1322478, file=+DATA/orcl/datafile/users02.dbf. found same corrupt data Reread of blocknum=1322478, file=+DATA/orcl/datafile/users02.dbf. found same corrupt data Reread of blocknum=1322478, file=+DATA/orcl/datafile/users02.dbf. found same corrupt data Reread of blocknum=1322478, file=+DATA/orcl/datafile/users02.dbf. found same corrupt data SQL> select file_id,tablespace_name from dba_data_files where file_name='+DATA/orcl/datafile/users02.dbf'; FILE_ID ---------- 83 set lines 300 pages 300 col tablespace_name for a20 col segment_type for a15 col owner for a30 col segment_name for a30 col partition_name for a30 SELECT tablespace_name, segment_type, owner, segment_name, partition_name FROM dba_extents WHERE file_id = 83 AND 1322478 BETWEEN block_id AND block_id + blocks - 1; no rows selected --不属于任何对象,未格式化 SQL> conn system/oracle Connected. create table s ( n number, c varchar2(4000) ) nologging tablespace USERS pctfree 99; --加快插入速度 Table created. SQL> select blocks from v$datafile where file#=83; BLOCKS ---------- 2621440 SQL> select (2621440-1322478)*8192/1024/1024 from dual; (2621440-1322478)*8192/1024/1024 -------------------------------- 10148.1406 --指定extent 为'+DATA/orcl/datafile/users02.dbf' SQL> alter table system.s allocate extent(datafile '+DATA/orcl/datafile/users02.dbf' size 10158m); Table altered. --已经把坏块包含到对象中 SQL> set lines 300 pages 300 col tablespace_name for a20 col segment_type for a15 col owner for a30 col segment_name for a30 col partition_name for a30 SELECT tablespace_name, segment_type, owner, segment_name, partitSQL> ion_name FROM dba_extents WHERE file_id = 83 AND 1322478 BETWEEN block_id AND block_id + blocks - 1; SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME -------------------- --------------- ------------------------------ ------------------------------ ------------------------------ USERS TABLE SYSTEM S --sys建corrupt_trigger CREATE OR REPLACE TRIGGER corrupt_trigger AFTER INSERT ON system.s REFERENCING OLD AS p_old NEW AS new_p FOR EACH ROW DECLARE corrupt EXCEPTION; BEGIN IF (dbms_rowid.rowid_block_number(:new_p.rowid)=1322478) and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=83) THEN RAISE corrupt; END IF; EXCEPTION WHEN corrupt THEN RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted'); END; / --插入数据 Begin FOR i IN 1..1000000000 loop for j IN 1..1000 loop Insert into system.s VALUES(i,'x'); end loop; commit; END LOOP; END; / --直到出现这个报错 ERROR at line 1: ORA-20000: Corrupt block has been formatted ORA-06512: at "SYS.CORRUPT_TRIGGER", line 10 ORA-04088: error during execution of trigger 'SYS.CORRUPT_TRIGGER' ORA-06512: at line 4 --验证,已经没有坏块 RMAN> validate check logical datafile 83 block 1322478; Starting validate at 05-JUN-18 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=00083 name=+DATA/orcl/datafile/users02.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:03 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 83 OK 0 0 1 13222753051654 File Name: +DATA/orcl/datafile/users02.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 1 Index 0 0 Other 0 0 Finished validate at 05-JUN-18 RMAN> validate datafile 83 block 1322478; Starting validate at 05-JUN-18 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=00083 name=+DATA/orcl/datafile/users02.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 ---- ------ -------------- ------------ --------------- ---------- 83 OK 0 0 1 13222753051654 File Name: +DATA/orcl/datafile/users02.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 1 Index 0 0 Other 0 0 Finished validate at 05-JUN-18 --删除 drop table system.s purge;
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。