Subject: DBMS_REPAIR comprehensive example Type: BULLETIN Creation Date: 09-JAN-1999 PURPOSE This INTERNAL document provides a comprehensive example of DBMS_REPAIR as introduced in Oracle 8i. Since this document can be used as both a training tool and to assist you to gain an understanding of package usage, the example includes the complete setup of a tablespace and a new table, T1. ORAPATCH is then used to corrupt a row pointer then DBMS_REPPAIR procedures are used to 'fix' the corruption and make the object usable. SCOPE & APPLICATION This INTERNAL article is designed for support analysts with block experience. A complete understanding of the DBMS_REPAIR package, block analysis and corruption detection techniques is assumed. RELATED DOCUMENTS Oracle 8i Administrator Guide - DBMS_REPAIR Note:45852.1 Oracle8 Blockdumps Note:45885.1 Oracle 8 BLOCKDUMP Output - Segment Header Introduction___________________________________________________________________ Important: The DBMS_REPAIR package is used to work with corruption in the transaction layer and the data layer only (software corrupt blocks). Blocks with physical corruption (ex. fractured block) are marked as the block is read into the buffer cache and DBMS_REPAIR ignores all blocks marked corrupt (sequence = ff). The only block repair in the initial release of DBMS_REPAIR is to ** mark the block software corrupt **. DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM are both FALSE. Testcase Setup_________________________________________________________________ SQL> connect system/manager Connected. SQL> -- Create tablespace (new datafile) SQL> create tablespace ts_corrupt datafile '/tmp/ts_corrupt.dbf' size 1m; Tablespace created. SQL> -- Create table (db_block_size = 2048) SQL> create table t1 ( 2 col1 int constraint t1_pk primary key, 3 col2 char(512)) 4 tablespace ts_corrupt; Table created. SQL> insert into t1 values (1,'aaaa'); 1 row created. SQL> insert into t1 values (2,'bbbb'); 1 row created. SQL> insert into t1 values (3,'cccc'); 1 row created. SQL> insert into t1 values (4,'dddd'); 1 row created. SQL> insert into t1 values (5,'eeee'); 1 row created. SQL> commit; Commit complete. SQL> select file_id, block_id from dba_extents 2 where segment_name = 'T1'; FILE_ID BLOCK_ID ---------- ---------- 6 2 Block Dump (before corruption)__________________________________________________ Notes: * Block 1 contains the file header for ts_corrupt.dbf. * Block 2 (rdba: 0x01800002) contains the segment header for table object T1. * Block 3 (rdba: 0x01800003) data block for table T1 - 3 rows. * Block 4 (rdba: 0x01800004) data block for table T1 - 2 rows. * Block 5 (rdba: 0x01800005) is unused. SQL> -- Note: T1 is the only object in tablespace ts_corrupt. SQL> set time on 14:37:28 SQL> alter system dump datafile 6 block min 1 block max 5; System altered. Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_24007.trc Oracle8 Enterprise Edition Release 8.1.5.0.0 - Beta With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production ORACLE_HOME = /export/home/oracle/product/8.1.5 System name: SunOS Node name: wssgst Release: 5.6 Version: Generic Machine: sun4u Instance name: V815 Redo thread mounted by this instance: 1 Oracle process number: 13 Unix process pid: 24007, image: oracle@wssgst (TNS V1-V3) *** SESSION ID:(7.20) 1998.12.16.14.37.28.000 Start dump data blocks tsn: 5 file#: 6 minblk 1 maxblk 5 Block 1 (file header) not dumped: use dump file header command buffer tsn: 5 rdba: 0x01800002 (6/2) scn: 0x0000.0001cf5f seq: 0x03 flg: 0x00 tail: 0xcf5f1003 frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 space2: 0 #extents: 1 #blocks: 4 last map 0x00000000 #maps: 0 offset: 1056 Highwater:: 0x01800005 ext#: 0 blk#: 2 ext size: 4 #blocks in seg. hdr's freelists: 1 #blocks below: 2 mapblk 0x00000000 offset: 0 Unlocked Map Header:: next 0x00000000 #extents: 1 obj#: 2925 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x01800003 length: 4 nfl = 1, nfb = 1 typ = 1 nxf = 0 SEG LST:: flg: USED lhd: 0x01800004 ltl: 0x01800004 buffer tsn: 5 rdba: 0x01800003 (6/3) scn: 0x0000.0001cf60 seq: 0x01 flg: 0x02 tail: 0xcf600601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Block header dump: 0x01800003 Object id on Block? Y seg/obj: 0xb6d csc: 0x00.1cf5f itc: 1 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0002.011.00000121 uba: 0x008018fb.0345.0d --U- 3 fsc 0x00 00.0001cf60 data_block_dump =============== tsiz: 0x7b8 hsiz: 0x18 pbl: 0x0191c724 bdba: 0x01800003 flag=----------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0x19d avsp=0x185 tosp=0x185 0xe:pti[0] nrow=3 offs=0 0x12:pri[0] offs=0x5af 0x14:pri[1] offs=0x3a6 0x16:pri[2] offs=0x19d block_row_dump: tab 0, row 0, @0x5af tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [512] 61 61 61 61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 tab 0, row 1, @0x3a6 tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [512] 62 62 62 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 tab 0, row 2, @0x19d tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 04 col 1: [512] 63 63 63 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 end_of_block_dump buffer tsn: 5 rdba: 0x01800004 (6/4) scn: 0x0000.0001cf60 seq: 0x01 flg: 0x02 tail: 0xcf600601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Block header dump: 0x01800004 Object id on Block? Y seg/obj: 0xb6d csc: 0x00.1cf5f itc: 1 flg: O typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0002.011.00000121 uba: 0x008018fc.0345.01 --U- 2 fsc 0x0000.0001cf60 data_block_dump =============== tsiz: 0x7b8 hsiz: 0x16 pbl: 0x0191c724 bdba: 0x01800004 flag=----------- ntab=1 nrow=2 frre=-1 fsbo=0x16 fseo=0x3a6 avsp=0x390 tosp=0x390 0xe:pti[0] nrow=2 offs=0 0x12:pri[0] offs=0x5af 0x14:pri[1] offs=0x3a6 block_row_dump: tab 0, row 0, @0x5af tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 05 col 1: [512] 64 64 64 64 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 tab 0, row 1, @0x3a6 tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 06 col 1: [512] 65 65 65 65 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 end_of_block_dump buffer tsn: 5 rdba: 0x00000005 (0/5) scn: 0x0000.00000000 seq: 0x01 flg: 0x01 tail: 0x00000001 frmt: 0x02 chkval: 0x0000 type: 0x00=unknown Hex dump of corrupt header 4 = CORRUPT Dump of memory from 0x191C6E0 to 0x191C6F4 191C6E0 00020000 00000005 00000000 00000101 00000000 Dump of memory from 0x191C6F4 to 0x191CEDC 191C6E0 00000000 00000000 00000000 191C700 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Repeat 61 times 191CEC0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 End dump data blocks tsn: 5 file#: 6 minblk 2 maxblk 5 Block Corruption (orapatch)____________________________________________________ Suggestion: Shutdown database before running orapatch. Reference: WevIV: Note.28864.1 SUPTOOL ORAPATCH Quick Reference Notes: orapatch block size 512 and db_block_size 2048. * Oracle block 2 (rdba: 0x01800002) <=> orapatch block 9-12 * Oracle block 3 (rdba: 0x01800003) <=> orapatch block 13-16 * Oracle block 4 (rdba: 0x01800004) <=> orapatch block 17-20 Action: Corrupt row index of orapatch block 13: change 0x05af to 0x05ff (t1.col1 = 1) $ orapatch Patch Utility: Version 1.0.12.3 - Restricted on Wed Dec 16 15:23:27 1998 Copyright (c) 1988, Oracle Corporation, The Netherlands. All rights reserved. PATCH> help Commands: DISPLAY { FIRST | LAST | CURRENT | [+|-]n } [ m ] EXIT FIND [ string ] HELP [ command ] HOST [ command ] LAST MODIFY [ string ] NEXT [ n ] OFFSET [+|-]offset OPEN name [ FILE | GBLSEC | RAW n ] [ READ | WRITE ] PREVIOUS [ n ] READ n REDISPLAY REPEAT [ n ] cmd [ ; cmd ] [ ; cmd ] ... SET { FORWARD | BACKWARD | IGNORE | RESPECT | CHAR | HEX | AUTODISPLAY | NOAUTODISPLAY | CMDSEP char | MULTIBLOCK n } SHOW SPOOL { file | ON | OFF } START file WRITE PATCH> open /tmp/ts_corrupt.dbf file write %OK, file open for write. PATCH> 9 Dump of file /tmp/ts_corrupt.dbf block 9 of 2052 10020000 01800002 0001CF5F 00000300 00000000 00000000 ..........._............ 00000000 00000000 00000000 00000001 00000004 04200000 ..................... .. 00000000 00000002 00000004 01800005 00000000 00000000 ........................ 00000001 00000002 00000000 00000000 00000000 00000001 ........................ 00000000 00000B6D 40000000 01800003 00000004 00000000 .......m@............... 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 ........ PATCH> 10 Dump of file /tmp/ts_corrupt.dbf block 10 of 2052 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 ........ PATCH> 11 Dump of file /tmp/ts_corrupt.dbf block 11 of 2052 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000001 00010100 00010000 00000000 00000000 ........................ 01800004 01800004 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 ........ PATCH> 12 Dump of file /tmp/ts_corrupt.dbf block 12 of 2052 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 CF5F1003 ....._.. PATCH> 13 Dump of file /tmp/ts_corrupt.dbf block 13 of 2052 06020000 01800003 0001CF60 00000102 00000000 01000000 ...........`............ 00000B6D 0001CF5F 00000000 00010200 00000000 00020011 ...m..._................ 00000121 008018FB 03450D00 20030000 0001CF60 00010003 ...!.....E.. ......`.... FFFF0018 019D0185 01850000 000305AF 03A6019D 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 002C0102 02C104FE 02006363 63632020 20202020 20202020 .,........cccc 20202020 20202020 PATCH> 14 Dump of file /tmp/ts_corrupt.dbf block 14 of 2052 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202C01 0202C103 FE020062 62626220 ,........bbbb 20202020 20202020 PATCH> 15 Dump of file /tmp/ts_corrupt.dbf block 15 of 2052 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 2020202C 010202C1 ,.... 02FE0200 61616161 ....aaaa PATCH> 16 Dump of file /tmp/ts_corrupt.dbf block 16 of 2052 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 CF600601 .`.. PATCH> 17 Dump of file /tmp/ts_corrupt.dbf block 17 of 2052 06020000 01800004 0001CF60 00000102 00000000 01000000 ...........`............ 00000B6D 0001CF5F 00000000 00010300 00000000 00020011 ...m..._................ 00000121 008018FC 03450100 20020000 0001CF60 00010002 ...!.....E.. ......`.... FFFF0016 03A60390 03900000 000205AF 03A60000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 ........ PATCH> 18 Dump of file /tmp/ts_corrupt.dbf block 18 of 2052 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00002C01 0202C106 FE020065 65656520 ..........,........eeee 20202020 20202020 PATCH> 19 Dump of file /tmp/ts_corrupt.dbf block 19 of 2052 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 2020202C 010202C1 ,.... 05FE0200 64646464 ....dddd PATCH> 20 Dump of file /tmp/ts_corrupt.dbf block 20 of 2052 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020 CF600601 .`.. PATCH> 13 Dump of file /tmp/ts_corrupt.dbf block 13 of 2052 06020000 01800003 0001CF60 00000102 00000000 01000000 ...........`............ 00000B6D 0001CF5F 00000000 00010200 00000000 00020011 ...m..._................ 00000121 008018FB 03450D00 20030000 0001CF60 00010003 ...!.....E.. ......`.... FFFF0018 019D0185 01850000 000305AF 03A6019D 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 002C0102 02C104FE 02006363 63632020 20202020 20202020 .,........cccc 20202020 20202020 PATCH> offset 88 Dump of file /tmp/ts_corrupt.dbf block 13 of 2052 06020000 01800003 0001CF60 00000102 00000000 01000000 ...........`............ 00000B6D 0001CF5F 00000000 00010200 00000000 00020011 ...m..._................ 00000121 008018FB 03450D00 20030000 0001CF60 00010003 ...!.....E.. ......`.... FFFF0018 019D0185 01850000 000305AF 03A6019D 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 002C0102 02C104FE 02006363 63632020 20202020 20202020 .,........cccc 20202020 20202020 PATCH> set hex %OK, enter strings in hexadecimal mode. PATCH> modify FF Dump of file /tmp/ts_corrupt.dbf block 13 of 2052 06020000 01800003 0001CF60 00000102 00000000 01000000 ...........`............ 00000B6D 0001CF5F 00000000 00010200 00000000 00020011 ...m..._................ 00000121 008018FB 03450D00 20030000 0001CF60 00010003 ...!.....E.. ......`.... FFFF0018 019D0185 01850000 000305FF 03A6019D 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 00000000 00000000 00000000 00000000 00000000 00000000 ........................ 002C0102 02C104FE 02006363 63632020 20202020 20202020 .,........cccc 20202020 20202020 PATCH> exit ORA-1498_______________________________________________________________________ SQL> analyze table t1 validate structure; analyze table t1 validate structure * ERROR at line 1: ORA-01498: block check failure - see trace file Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_2835.trc Oracle8 Enterprise Edition Release 8.1.5.0.0 - Beta With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production ORACLE_HOME = /export/home/oracle/product/8.1.5 System name: SunOS Node name: wssgst Release: 5.6 Version: Generic Machine: sun4u Instance name: V815 Redo thread mounted by this instance: 1 Oracle process number: 13 Unix process pid: 2835, image: oracle@wssgst (TNS V1-V3) *** 1998.12.16.15.53.02.000 *** SESSION ID:(7.6) 1998.12.16.15.53.02.000 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=32 ktbbhitc=1 Block header dump: 0x01800003 Object id on Block? Y seg/obj: 0xb6d csc: 0x00.1cf5f itc: 1 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0002.011.00000121 uba: 0x008018fb.0345.0d --U- 3 fsc 0x0000.0001cf60 data_block_dump =============== tsiz: 0x7b8 hsiz: 0x18 pbl: 0x28088044 bdba: 0x01800003 flag=----------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0x19d avsp=0x185 tosp=0x185 0xe:pti[0] nrow=3 offs=0 0x12:pri[0] offs=0x5ff 0x14:pri[1] offs=0x3a6 0x16:pri[2] offs=0x19d block_row_dump: tab 0, row 0, @0x5ff tl: 2520 fb: --H----- lb: 0x20 cc: 32 nrid: 0x20202020.2020 col 0: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 1: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 2: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 3: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 4: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 5: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 6: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 7: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 8: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 9: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 10: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 11: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 12: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 13: [32] 20 20 cf dmp lmt after 3 col 14: [0] col 15: [37] dmp lmt after 0 col 16: [0] col 17: [12] dmp lmt after 0 col 18: [72] dmp lmt after 0 col 19: [ 6] dmp lmt after 0 col 20: [144] dmp lmt after 0 col 21: [ 2] dmp lmt after 0 col 22: [206] dmp lmt after 0 col 23: [194] dmp lmt after 0 col 24: [194] dmp lmt after 0 col 25: [194] dmp lmt after 0 col 26: [194] dmp lmt after 0 col 27: [194] dmp lmt after 0 col 28: [194] dmp lmt after 0 col 29: [194] dmp lmt after 0 col 30: [194] dmp lmt after 0 col 31: [0] tab 0, row 1, @0x3a6 tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [512] 62 62 62 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 tab 0, row 2, @0x19d tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 04 col 1: [512] 63 63 63 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 end_of_block_dump DBMS_REPAIR.ADMIN_TABLES (repair and orphan key)_______________________________ SQL> @adminCreate SQL> connect sys/change_on_install Connected. SQL> SQL> -- Repair Table SQL> SQL> declare 2 begin 3 -- Create repair table 4 dbms_repair.admin_tables ( 5 -- table_name => 'REPAIR_TABLE', 6 table_type => dbms_repair.repair_table, 7 action => dbms_repair.create_action, 8 tablespace => 'USERS'); -- default TS of SYS if not specified 9 end; 10 / PL/SQL procedure successfully completed. SQL> select owner, object_name, object_type 2 from dba_objects 3 where object_name like '%REPAIR_TABLE'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------------------------------------------ SYS DBA_REPAIR_TABLE VIEW SYS REPAIR_TABLE TABLE SQL> SQL> -- Orphan Key Table SQL> SQL> declare 2 begin 3 -- Create orphan key table 4 dbms_repair.admin_tables ( 5 table_type => dbms_repair.orphan_table, 6 action => dbms_repair.create_action, 7 tablespace => 'USERS'); -- default TS of SYS if not specified 8 end; 9 / PL/SQL procedure successfully completed. SQL> select owner, object_name, object_type 2 from dba_objects 3 where object_name like '%ORPHAN_KEY_TABLE'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------------------------------------------ SYS DBA_ORPHAN_KEY_TABLE VIEW SYS ORPHAN_KEY_TABLE TABLE DBMS_REPAIR.CHECK_OBJECT_______________________________________________________ Note: In the initial release of DBMS_REPAIR the only repair is to mark the block as software corrupt. SQL> @checkObject SQL> set serveroutput on SQL> SQL> declare 2 rpr_count int; 3 begin 4 rpr_count := 0; 5 dbms_repair.check_object ( 6 schema_name => 'SYSTEM', 7 object_name => 'T1', 8 repair_table_name => 'REPAIR_TABLE', 9 corrupt_count => rpr_count); 10 dbms_output.put_line('repair count: ' || to_char(rpr_count)); 11 end; 12 / repair count: 1 PL/SQL procedure successfully completed. SQL> desc repair_table Name Null? Type ----------------------------------------- -------- ---------------------------- OBJECT_ID NOT NULL NUMBER TABLESPACE_ID NOT NULL NUMBER RELATIVE_FILE_ID NOT NULL NUMBER BLOCK_ID NOT NULL NUMBER CORRUPT_TYPE NOT NULL NUMBER SCHEMA_NAME NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) BASEOBJECT_NAME VARCHAR2(30) PARTITION_NAME VARCHAR2(30) CORRUPT_DESCRIPTION VARCHAR2(2000) REPAIR_DESCRIPTION VARCHAR2(200) MARKED_CORRUPT NOT NULL VARCHAR2(10) CHECK_TIMESTAMP NOT NULL DATE FIX_TIMESTAMP DATE REFORMAT_TIMESTAMP DATE SQL> select object_name, block_id, corrupt_type, marked_corrupt, 2 corrupt_description, repair_description 3 from repair_table; OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR ------------------------------ ---------- ------------ ---------- CORRUPT_DESCRIPTION -------------------------------------------------------------------------------- REPAIR_DESCRIPTION -------------------------------------------------------------------------------- T1 3 1 FALSE kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=32 ktbbhitc=1 mark block software corrupt Data Extraction________________________________________________________________ The repair table indicates that block 3 of file 6 is corrupt - but remember this block has not yet been marked as corrupt, therefore now is the time to extract any meaningful data. After the block is marked corrupt, the entire block must be skipped. This is the reason to use DBMS_REPAIR! 1. Determine the number of rows in the block from ALTER SYSTEM DUMP (nrows = 3). 2. Query the corrupt object and extract as much information as possible. SQL> -- the following query we can tell that 1 row is missing and in this SQL> -- test case we know that row exists in block 3. SQL> select * from system.t1; COL1 COL2 -------------------------------------------------------------------- 2 bbbb 3 cccc 4 dddd 5 eeee SQL> -- A much more meaningful query follows. This query can be used to salvage SQL> -- data from every corrupt block. Creating a temporary table facilitates SQL> -- data insertion. SQL> -- Query the non-corrupt data from the block SQL> create table temp_t1 as 2 select * from system.t1 3 where dbms_rowid.rowid_block_number(rowid) = 3 4 and dbms_rowid.rowid_to_absolute_fno (rowid, 'SYSTEM','T1') = 6; Table created. SQL> select col1 from temp_t1; COL1 ---------- 2 3 3. In order to capture all information in the corrupt block, use the Unix dd command to dump the corrupted blocks. Although the process is far from trivial, tools (ex. orapatch, bbed) can be used to mine data from the hex dumps. DBMS_REPAIR.FIX_CORRUPT_BLOCKS (ORA-1578)_____________________________________ SQL> declare 2 fix_count int; 3 begin 4 fix_count := 0; 5 dbms_repair.fix_corrupt_blocks ( 6 schema_name => 'SYSTEM', 7 object_name => 'T1', 8 object_type => dbms_repair.table_object, 9 repair_table_name => 'REPAIR_TABLE', 10 fix_count => fix_count); 11 dbms_output.put_line('fix count: ' || to_char(fix_count)); 12 end; 13 / fix count: 1 PL/SQL procedure successfully completed. SQL> select object_name, block_id, marked_corrupt 2 from repair_table; OBJECT_NAME BLOCK_ID MARKED_COR ------------------------------ ---------- ---------- T1 3 TRUE SQL> alter system dump datafile 6 block 3; System altered. SQL> select * from system.t1; select * from system.t1 * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 3) ORA-01110: data file 6: '/tmp/ts_corrupt.dbf' Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_6057.trc Oracle8 Enterprise Edition Release 8.1.5.0.0 - Beta With the Partitioning option PL/SQL Release 8.1.5.0.0 - Production ORACLE_HOME = /export/home/oracle/product/8.1.5 System name: SunOS Node name: wssgst Release: 5.6 Version: Generic Machine: sun4u Instance name: V815 Redo thread mounted by this instance: 1 Oracle process number: 13 Unix process pid: 6057, image: oracle@wssgst (TNS V1-V3) *** SESSION ID:(7.14) 1998.12.16.16.20.42.000 Start dump data blocks tsn: 5 file#: 6 minblk 3 maxblk 3 buffer tsn: 5 rdba: 0x01800003 (6/3) scn: 0x0000.00021e15 seq: 0xff flg: 0x00 tail: 0x1e1506ff frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Block header dump: 0x01800003 Object id on Block? Y seg/obj: 0xb6d csc: 0x00.1cf5f itc: 1 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0002.011.00000121 uba: 0x008018fb.0345.0d --U- 3 fsc 0x0000.0001cf60 data_block_dump =============== tsiz: 0x7b8 hsiz: 0x18 pbl: 0x0191c724 bdba: 0x01800003 flag=----------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0x19d avsp=0x185 tosp=0x185 0xe:pti[0] nrow=3 offs=0 0x12:pri[0] offs=0x5ff 0x14:pri[1] offs=0x3a6 0x16:pri[2] offs=0x19d block_row_dump: tab 0, row 0, @0x5ff tl: 612 fb: --H----- lb: 0x20 cc: 32 nrid: 0x20202020.2020 col 0: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 1: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 2: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 3: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 4: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 5: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 6: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 7: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 8: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 9: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 10: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 11: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 12: [32] 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 col 13: [32] 20 20 1e dmp lmt after 3 col 14: [0] col 15: [ 5] dmp lmt after 0 col 16: [118] dmp lmt after 0 col 17: [0] col 18: [0] col 19: [0] col 20: [0] col 21: [0] col 22: [0] col 23: [0] col 24: [0] col 25: [0] col 26: [0] col 27: [0] col 28: [0] col 29: [0] col 30: [0] col 31: [0] tab 0, row 1, @0x3a6 tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 03 col 1: [512] 62 62 62 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 tab 0, row 2, @0x19d tl: 521 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 04 col 1: [512] 63 63 63 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 end_of_block_dump End dump data blocks tsn: 5 file#: 6 minblk 3 maxblk 3 DBMS_REPAIR.DUMP_ORPHAN_KEYS___________________________________________________ Note: This should be run for every index associated with a table identified in the repair table. (PLAN) If the object is a table then all associated indexes are processed. SQL> select index_name from dba_indexes 2 where table_name in (select distinct object_name from repair_table); INDEX_NAME ------------------------------ T1_PK SQL> @dumpOrphanKeys SQL> set serveroutput on SQL> SQL> declare 2 key_count int; 3 begin 4 key_count := 0; 5 dbms_repair.dump_orphan_keys ( 6 schema_name => 'SYSTEM', 7 object_name => 'T1_PK', 8 object_type => dbms_repair.index_object, 9 repair_table_name => 'REPAIR_TABLE', 10 orphan_table_name => 'ORPHAN_KEY_TABLE', 11 key_count => key_count); 12 dbms_output.put_line('orphan key count: ' || to_char(key_count)); 13 end; 14 / orphan key count: 3 PL/SQL procedure successfully completed. SQL> desc orphan_key_table Name Null? Type ----------------------------------------- -------- ---------------------------- SCHEMA_NAME NOT NULL VARCHAR2(30) INDEX_NAME NOT NULL VARCHAR2(30) IPART_NAME VARCHAR2(30) INDEX_ID NOT NULL NUMBER TABLE_NAME NOT NULL VARCHAR2(30) PART_NAME VARCHAR2(30) TABLE_ID NOT NULL NUMBER KEYROWID NOT NULL ROWID KEY NOT NULL ROWID DUMP_TIMESTAMP NOT NULL DATE SQL> select index_name, count(*) from orphan_key_table 2 group by index_name; INDEX_NAME COUNT(*) ------------------------------ ---------- T1_PK 3 Important: Index entry in the orphan key table implies that the index should be rebuilt to guarantee a table probe and an index probe return the same result set. DBMS_REPAIR.SKIP_CORRUPT_BLOCKS________________________________________________ Important: If an index and table are out of sync, then a SET TRANSACTION READ ONLY transaction may be inconsistent in situations where one query probes only the index and then a subsequent query probes both the index and the table. If the table block is marked corrupt, then the two queries will return different results. Suggestion: If skip_corrupt_blocks, then rebuild any indexes identified in the orphan key table. SQL> @skipCorruptBlocks SQL> declare 2 begin 3 dbms_repair.skip_corrupt_blocks ( 4 schema_name => 'SYSTEM', 5 object_name => 'T1', 6 object_type => dbms_repair.table_object, 7 flags => dbms_repair.skip_flag); 8 end; 9 / PL/SQL procedure successfully completed. SQL> select table_name, skip_corrupt from dba_tables 2 where table_name = 'T1'; TABLE_NAME SKIP_COR ------------------------------ -------- T1 ENABLED SQL> -- rows in corrupt block skipped, no errors SQL> select * from system.t1; COL1 COL2 -------------------------------------------------------------------------------- 4 dddd 5 eeee -- Notice the pk index has not yet been corrected. SQL> insert into system.t1 values (1,'aaaa'); insert into system.t1 values (1,'aaaa') * SQL> select * from system.t1 where col1 = 1; no rows selected DBMS_REPAIR.REBUILD_FREELISTS__________________________________________________ Important: When a block marked corrupt is found at the head or tail of a freelist, the freelist is reinitialized and an error is returned. Although this takes the offending block off the freelist, all freelist blocks that followed the corrupt block can no longer be accessed. This procedure scans the object and rebuilds the freelist - any blocks marked corrupt are ignored during the rebuild. (PLAN) DBMS_REPAIR.REFORMAT_CORRUPT_BLOCKS. SQL> declare 2 begin 3 dbms_repair.rebuild_freelists ( 4 schema_name => 'SYSTEM', 5 object_name => 'T1', 6 object_type => dbms_repair.table_object); 7 end; 8 / PL/SQL procedure successfully completed. Rebuild Index__________________________________________________________________ Note: Every index identified in the orphan key table should be rebuilt to ensure consistent results. SQL> alter index system.t1_pk rebuild online; Index altered. SQL> insert into system.t1 values (1, 'aaaa'); 1 row created. SQL> select * from system.t1; COL1 COL2 -------------------------------------------------------------------------------- 4 dddd 5 eeee 1 aaaa Important: The above insert statement was used to provide a simple example. This is the perfect world - we know the data that was lost. The temporary table (temp_t1) should also be used to include all rows extracted from the corrupt block. Conclusion____________________________________________________________________ At this point the table T1 is available but data loss was incurred. In general data loss must be seriously considered before using the DBMS_REPAIR package for mining the index segment and/or table block dumps is very complicated and logical inconsistencies may be introduced. In the initial release, the only repair effected by DBMS_REPAIR is to mark the block as software corrupt (seq = ff).